Basic commands

PostgreSQL

• Basic login procedure
• Other useful commands
• Special notes

1. Basic login procedure

1. Make sure the Postgres server is running.

   shell> ps aux|grep postgres

   You will see something like

   postgres  1208  0.0  0.0  45104  5380 ?        S    Aug10   0:02 /usr/lib/postgresql/8.4/bin/postgres
                                                                    -D /var/lib/postgresql/8.4/main
                                                                    -c config_file=/etc/postgresql/8.4/main/postgresql.conf
   postgres  1219  0.0  0.0  45104  1512 ?        Ss   Aug10   0:19 postgres: writer process
   postgres  1220  0.0  0.0  45104  1272 ?        Ss   Aug10   0:15 postgres: wal writer process
   postgres  1221  0.0  0.0  45240  1516 ?        Ss   Aug10   0:04 postgres: autovacuum launcher process
   postgres  1222  0.0  0.0  13272  1224 ?        Ss   Aug10   0:04 postgres: stats collector process
   

2. Login to the server if it is running

   shell> psql -U user -h host -d dbname

   Then, input the password. E.g.,

   shell> psql -U postgres -h localhost -d template1

   shell> psql -U testuser -h localhost -d testdb

3. After login, check what are the databases:

   psql> \l

   It will show something like:

   postgres=# \l
                                       List of databases
        Name      |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges   
   ---------------+----------+----------+-------------+-------------+-----------------------
    postgres      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
    template0     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
                                                                    : postgres=CTc/postgres
    template1     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
                                                                    : postgres=CTc/postgres
    testdb          | testuser | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
   (4 rows)
   

4. Check what are the tables inside this database.

   testdb> \d

   You will see similar information as follows.

           List of relations
    Schema |  Name  | Type  | Owner 
   --------+--------+-------+-------
    public | dblp   | table | asp
    public | edge   | table | asp
    public | vertex | table | asp
   (3 rows)
   

5. Check the definition of a table.

   testdb> \d dblp

   You can see the following information:

                 Table "public.dblp"
     Column  |          Type          | Modifiers 
   ----------+------------------------+-----------
    dblp_key | character varying(100) | not null
    title    | character varying(500) | 
    doi      | character varying(40)  | 
    year     | character(4)           | 
   Indexes:
       "vertex_pkey" PRIMARY KEY, btree (dblp_key)
   

6. You can test a lot of other SQL statements.
7. Disconnect from postgres server.

   testdb> \q

2. Management

• If forget password for the postgres user, run

  su postgres -c psql

  and input the OS password of postgres user. Then, reset password

  postgres=# alter user postgres PASSWORD 'newpasswd';

3. Special notes