Introduction

PostgreSQL, also known as Postgres, is a free and open-source relational database management system (RDBMS) which features transactions with Atomicity, Consistency, Isolation, Durability (ACID) properties, automatically updatable views, triggers, foreign keys, and stored procedures. It is a powerful database system with over 35 years of active development that has earned itself a strong reputation for reliability, feature robustness, and performance.

What is interesting to note is that it is the default database for macOS Server and is also available for Linux, FreeBSD, OpenBSD, and Windows. Database administrators dote over Postgres, all thanks to its feature rich system and open-soruce active development.

In this blog post, we'll take a look at the most commonly used Postgres commands. This does not include the normal SQL commands.

Connection Command

When working with Postgres, we would require the following command very often when connecting to DB instances from a command line.

To connect to the localhost postgres installation, we use the psql command, and provide the values of -h, -p and -U flags to setup the connection, as shown below.

psql -h <HOST> -p <PORT> -U <USER_NAME>

e.g,

thesmartbug@user ~ % psql -h localhost -p 5432 -U postgres
Password for user postgres: 
psql (14.5 (Homebrew), server 13.4)
Type "help" for help.
postgres=#

Note : This above command will only work if we've installed the Postgres DB System on our machine locally.

Utility Commands

Get Info

After entering the psql, to view connection info, we can use the command \conninfo, as shown below.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "localhost" (address "::1") at port "5432".
postgres=#

Get List of DB

To get a list of all the Databases present in the Postgres Instance, \l command is used, as shown below.

postgres=# \l
                                      List of databases
            Name             |  Owner   | Encoding | Collate | Ctype  
-----------------------------+----------+----------+---------+-------
 postgres                    | postgres | UTF8     | C       | C     
 thesmartbug                 | postgres | UTF8     | C       | C     
 template0                   | postgres | UTF8     | C       | C     
 template1                   | postgres | UTF8     | C       | C     
(4 rows)
postgres=#

Switch Database

To Switch to some other DB, we can use \c command along with the name of the DB we want to switch to, as shown below.

postgres=# \c thesmartbug 
psql (14.5 (Homebrew), server 13.4)
You are now connected to database "thesmartbug" as user "postgres".
thesmartbug=#

Get List of Tables

Once a database has been selected, we can take a look on which all tables exists in the database using the \d command as shown below.

thesmartbug=# \d
                               List of relations
 Schema |                      Name                       |   Type   |  Owner   
--------+-------------------------------------------------+----------+----------
 public | admin_permissions                               | table    | postgres
 public | admin_permissions_id_seq                        | sequence | postgres
 public | admin_permissions_role_links                    | table    | postgres
 public | admin_permissions_role_links_id_seq             | sequence | postgres
 public | admin_roles                                     | table    | postgres
 public | admin_roles_id_seq                              | sequence | postgres
 public | admin_users                                     | table    | postgres
 public | admin_users_id_seq                              | sequence | postgres
 public | admin_users_roles_links                         | table    | postgres
 public | admin_users_roles_links_id_seq                  | sequence | postgres
 public | articles                                        | table    | postgres
 public | articles_id_seq                                 | sequence | postgres
 public | articles_localizations_links                    | table    | postgres
 public | articles_localizations_links_id_seq             | sequence | postgres
 public | articles_user_links                             | table    | postgres
 public | articles_user_links_id_seq                      | sequence | postgres
 (16 rows)
thesmartbug=#

Get Table Schema

Each table contains different columns in a Database. To check the schema (structure) of the table like names of the columns that exists in a tables, name and number of indexes created on a table, and so on, we can use the \d+ command to get further details of a table, as shown below.

thesmartbug~ \d+ admin_permissions
                                                               Table "public.admin_permissions"
    Column     |              Type              | Collation | Nullable 
---------------+--------------------------------+-----------+----------
 id            | integer                        |           | not null 
 action        | character varying(255)         |           |          
 subject       | character varying(255)         |           |          
 properties    | jsonb                          |           |          
 conditions    | jsonb                          |           |          
 created_at    | timestamp(6) without time zone |           |          
 updated_at    | timestamp(6) without time zone |           |          
 created_by_id | integer                        |           |          
 updated_by_id | integer                        |           |          
Indexes:
    "admin_permissions_pkey" PRIMARY KEY, btree (id)
    "admin_permissions_created_by_id_fk" btree (created_by_id)
    "admin_permissions_updated_by_id_fk" btree (updated_by_id)
Foreign-key constraints:
    "admin_permissions_created_by_id_fk" FOREIGN KEY (created_by_id) REFERENCES admin_users(id) ON DELETE SET NULL
    "admin_permissions_updated_by_id_fk" FOREIGN KEY (updated_by_id) REFERENCES admin_users(id) ON DELETE SET NULL
Referenced by:
    TABLE "admin_permissions_role_links" CONSTRAINT "admin_permissions_role_links_fk" FOREIGN KEY (permission_id) REFERENCES admin_permissions(id) ON DELETE CASCADE
Access method: heap
thesmartbug~

Creating Curated Data Dump (Selective Backup)

Data Dump of the entire DB can be created using simple commands as described in the other blog post here. However, often times, requirement arises to take a Database backup of the selective chunk of data. In such cases, Curated Data Dump can be created, which will only include records that are requested from the entire DB.

This can be achieved using two methods.

METHOD I

Fetching Selective data and passing it on to standard output (STDOUT) which is then piped to the standard input (STDIN) which further saves the data in the target DB.

postgres~ psql -c "COPY (SELECT * FROM my_table WHERE created_at > '2012-05-01') TO STDOUT;" source_db | psql -c "COPY my_table FROM STDIN;" target_db

METHOD II

Next method is simply to use the COPY command to copy the data to a file.

postgres~ COPY (SELECT * FROM big_table WHERE created_at > '2012-05-01') TO '/path/to/a/dump/file';

Inspecting DB

Update or Alter commands needs to acquire a lock on the table before they are executed so that no other process attempts to write to (update/alter) the same row. But sometimes, the DBs get stuck while acquiring the lock. The processes keep waiting and no lock is actually acquired.

So, an alter or update command may get stuck and we require to further investigate about the processes which are using the Database, or perhaps we might only wish to check the processes occupying the DB in general.

In such cases, we can use the following query to get more information

select * from pg_locks where granted and relation = '<TABLE_NAME>'::regclass \x\g\x;

e.g,

postgres~ select * from pg_locks where granted and relation = 'public.admin_permissions'::regclass \g\x;

 locktype | database | relation | page | tuple | pid 
----------+----------+----------+------+-------+-----
(0 rows)
postgres~

Inspecting Process using pid

If there is any process that is holding up the lock, we can request further details of the process which has acquired the lock, using the following query.

select * from pg_stat_activity where pid=<PROCESS_ID>

Get Current Active Sessions

To get a list of currently active sessions running for the DB, following query can be used

postgres~ SELECT 
    application_name, client_addr, client_hostname, state, query
FROM 
    pg_stat_activity
WHERE
    pid <> pg_backend_pid()
    AND datname = 'public.admin_permissions';

 application_name | client_addr | client_hostname | state | query 
------------------+-------------+-----------------+-------+-------
(0 rows)
postgres~

Terminate Current Active Sessions (CAUTION)

In case a situation arise, that we need to terminate all the active sessions to a DB, we can use the following query.

postgres~ SELECT
  pg_terminate_backend(pid)
FROM 
    pg_stat_activity 
WHERE
    pid <> pg_backend_pid()
    AND datname = 'public.admin_permissions';

 pg_terminate_backend 
----------------------
(0 rows)
postgres~

The above query requires to have admin rights, or permissions to terminate other active sessions. If the logger in user does not have sufficient permissions, it will not terminate any session.

Conclusion

As seen above, we can get a whole lot of information out of our Postgres DB once we've the right command/query at hand. If you like this post, don't forget to bookmark it. You can leave your comments below.

Some commonly used Postgres commands