Some commonly used Postgres commands
Taking selective backup in Postgres or inspecting which process is holding up transaction, you can get it all done pretty quickly. Here's how.
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.
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,
This above command will only work if we've installed the Postgres DB System on our machine locally.
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.
If you want to learn how to take a data dump from production / dev database in an efficient way, we also wrote a blog post to cover that topic.
Simar Mann Singh
