Hprc banner tamu.png

Difference between revisions of "SW:PostgreSQL"

From TAMU HPRC
Jump to: navigation, search
(PostgreSQL)
(PostgreSQL Module)
 
(8 intermediate revisions by 2 users not shown)
Line 9: Line 9:
  
 
===PostgreSQL Module===
 
===PostgreSQL Module===
TAMU HPRC currently supports the user of PostgreSQL though the PostgreSQL modules. There are several PostgreSQL modules available on Ada and Terra.  
+
TAMU HPRC currently supports the user of PostgreSQL though the PostgreSQL modules. There are several PostgreSQL modules available on Terra and Grace.  
  
  
 
You can learn more about the module system on our [[SW:Modules]] page.
 
You can learn more about the module system on our [[SW:Modules]] page.
  
 +
=== PostgreSQL on Grace ===
 +
Start an interactive job either using the portal VNC app or with the srun command.
  
 +
(1) mkdir $SCRATCH/postgresql
 +
(2) module load PostgreSQL/13.4-GCCcore-11.2.0
 +
(3) initdb -D $SCRATCH/postgresql/my_psql_db
 +
(4) pg_ctl -o "-p 9999" -D $SCRATCH/postgresql/my_psql_db -l $SCRATCH/postgresql/my_psql_db.log start
 +
        # comment: the -o provides extra options. In this example, the extra option is the port by -p
 +
        # The -D option specifies the postgresql database directory.
 +
        # The -l option specify the log file for the database instance.
 +
(5) createdb employee -p 9999        # create a database named employee
 +
(6) psql -d employee -p 9999          # connect to employee database on port 9999
 +
(7) execute your sql statements in psql; type exit to exit psql prompt
 +
(8) pg_ctl -D $SCRATCH/postgresql/my_psql_db stop  # stop the database
  
===PostgreSQL on Ada ===
+
You may set password for your database if it is needed. These are the commands
 +
for the first time set up of PostgresSQL. After this, you only need to start the
 +
server, connect to it, and stop it, i.e., step (3), (5), (6) and (7). You only need to initdb (step 1), createdb (step 4) for one
 +
time.
  
1) module load PostgreSQL/9.6.1-GCCcore-6.3.0-Python-2.7.12-bare
+
Note: the database directory and name, database username, and port should be changed by users.
(2) initdb -D /scratch/user/yangliu/Test/postresql/test.db
 
(3) pg_ctl -o "-p 9999" -D /scratch/user/yangliu/Test/postresql/test.db -l
 
        /scratch/user/yangliu/Test/postresql/test.log start
 
        #comment: the -o provides extra options. In this example, the extra option is
 
        the port by -p. the -D option specified where is the database file. The -l
 
        option specify the log file for the database instance.
 
(4) createdb yang -D /scratch/user/yangliu/Test/postresql/test.db -p 9999
 
(5) psql -d yang -p 999
 
(6) execute your sql statments in psql
 
(7) pg_ctl -D /scratch/user/yangliu/Test/postresql/test.db stop
 
  
You may set password for your database if it is needed. This is the commands
+
If you see a message like the following, check the logfile (my_psql_db.log in this example) you may have forgotten to stop your postgresql server on the specified port (9999 in this example) or another user is using the specified port. If someone else is using port 9999, you can try port 9998.
for the first time set up of postgres. After this, you only need to startup the
+
<pre>
server, connect to it, and stop it. You only need to initdb, createdb for one
+
waiting for server to start.... stopped waiting
time. Let us know If you have further questions.
+
pg_ctl: could not start server
 +
</pre>

Latest revision as of 14:16, 20 September 2022

PostgreSQL

Description

PostgreSQL is a powerful, open source object-relational database system.

Access

PostgreSQL is open to all HPRC users.

PostgreSQL Module

TAMU HPRC currently supports the user of PostgreSQL though the PostgreSQL modules. There are several PostgreSQL modules available on Terra and Grace.


You can learn more about the module system on our SW:Modules page.

PostgreSQL on Grace

Start an interactive job either using the portal VNC app or with the srun command.

(1) mkdir $SCRATCH/postgresql
(2) module load PostgreSQL/13.4-GCCcore-11.2.0
(3) initdb -D $SCRATCH/postgresql/my_psql_db
(4) pg_ctl -o "-p 9999" -D $SCRATCH/postgresql/my_psql_db -l $SCRATCH/postgresql/my_psql_db.log start
       # comment: the -o provides extra options. In this example, the extra option is the port by -p
       # The -D option specifies the postgresql database directory.
       # The -l option specify the log file for the database instance.
(5) createdb employee -p 9999         # create a database named employee
(6) psql -d employee -p 9999          # connect to employee database on port 9999
(7) execute your sql statements in psql; type exit to exit psql prompt
(8) pg_ctl -D $SCRATCH/postgresql/my_psql_db stop  # stop the database

You may set password for your database if it is needed. These are the commands for the first time set up of PostgresSQL. After this, you only need to start the server, connect to it, and stop it, i.e., step (3), (5), (6) and (7). You only need to initdb (step 1), createdb (step 4) for one time.

Note: the database directory and name, database username, and port should be changed by users.

If you see a message like the following, check the logfile (my_psql_db.log in this example) you may have forgotten to stop your postgresql server on the specified port (9999 in this example) or another user is using the specified port. If someone else is using port 9999, you can try port 9998.

waiting for server to start.... stopped waiting
pg_ctl: could not start server