Index of /examples/db/postgresql/basic

Name	Last modified	Size

Parent Directory		-
my_pgserver.log	2020-01-22 14:21	1.5K
my_pgserver/	2020-01-22 14:21	-

This example page is to show how to use PostgreSQL on SCC

step 1: go to the project directory (below we assume 'my_project_dir' is the full path directory you are going to exercise with this example), and create a data directory for database file to be put, say, 'my_pgserver':


cd my_project_dir
mkdir my_pgserver

step 2: load postgresql module:


module load postgresql/11.1

step 3: initialize server instance, this step is only needed to do once:


initdb -D my_pgserver

step 4: start db server instance, note, even though you can specify any log file name, it's recommended to specify a meaningful and distinguishing name to fit the multi-user environment:


pg_ctl -D my_pgserver -l my_pgserver.log start

step 5: there are two ways to create new database:

method 1: logon to postgres db (it is a PostgreSQL built-in db) through commandline interactive utility 'psql', and use 'CREATE DATABASE' sql command in psql shell, and you then can use '\c' sql command to change the current database to work on:


psql postgres
postgres=# CREATE DATABASE testdb;
CREATE DATABASE
postgres=# \l
                                List of databases
   Name    |  Owner  | Encoding |   Collate   |    Ctype    |  Access privileges  
-----------+---------+----------+-------------+-------------+---------------------
 postgres  | yshen16 | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | yshen16 | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/yshen16         +
           |         |          |             |             | yshen16=CTc/yshen16
 template1 | yshen16 | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/yshen16         +
           |         |          |             |             | yshen16=CTc/yshen16
 testdb    | yshen16 | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(4 rows)

postgres=# \c testdb
You are now connected to database "testdb" as user "yshen16".
testdb=#

method 2: use 'createdb' commandline tool to create database, the following command is to create a db called 'testdb2':

createdb testdb2

step 6:connect to database:

psql testdb2
testdb2=# \l
                                List of databases
   Name    |  Owner  | Encoding |   Collate   |    Ctype    |  Access privileges  
-----------+---------+----------+-------------+-------------+---------------------
 postgres  | yshen16 | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | yshen16 | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/yshen16         +
           |         |          |             |             | yshen16=CTc/yshen16
 template1 | yshen16 | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/yshen16         +
           |         |          |             |             | yshen16=CTc/yshen16
 testdb    | yshen16 | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 testdb2   | yshen16 | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(5 rows)

step 7: use '\q' SQL command in 'psql' to quit from database:

testdb=# \q
[yshen16@scc1 my_pgserver]$

step 8: don't forget to shutdown the server instance before you are done with using postgresql, note:you must specify the full path of the database location if you are not doing this step in the parent directory of the data directory for the server:
if done from parent directory:
pg_ctl -D my_pgserver stop
Or done from any directory:
pg_ctl -D my_project_dir/my_pgserver stop

Contact Information:
Research Computing Services: help@scc.bu.edu

Note: Research Computing Services (RCS) example programs are provided "as is" without any warranty of any kind. The user assumes the entire risk of quality, performance, and repair of any defects. You are encouraged to copy and modify any of the given examples for your own use.