PostgreSQL useful commands

February 21, 2021May 13, 2022 by wujt

Below is a list of commands common used when working with PostgreSQL, which hopefully will make your work with this type of database more pleasant. If there is no explanation what command does, it should be self-descriptive. This post will be updated with new ones as time goes by, so bookmark it and feel free to come back here from time to time.

Commands which starts with \, eg \l should be run in postgres terminal. SQL queries also. Commands with prefix pg_ should be run from terminal as bash command, usually as postgres user.

General

# list databases
\l

# connect to test_db database
\c test_db

# list all the tables of current database
\dt

# describe a table
\d table_name

# list all schemas of the currently connected database
\dn

# list users
\du

# execute previous command (arrow up should work also)
\g

# show history of commands
\s

# execute psql commands from file commands.sql
\i commands.sql

# turn on query execution time
\timing

Show all the settings that have changed from default ones.

SELECT sourcefile, name, setting FROM pg_settings WHERE boot_val <> reset_val;

Check if Postgres is in recovery mode

SELECT pg_is_in_recovery();

Measure PITR progress

SELECT pg_last_xlog_replay_location(), pg_last_xact_replay_timestamp(), now(), now() - pg_last_xact_replay_timestamp();

Show changed settings, which are waiting for restart to be applied

SELECT name, context, pending_restart FROM pg_settings where context ='postmaster' and pending_restart='t';

Show 10 biggest tables

SELECT table_name,pg_relation_size(table_schema || '.' || table_name) as size
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'pg_catalog')
ORDER BY size DESC
LIMIT 10;

Estimated number of rows (useful in big tables)

SELECT (CASE WHEN reltuples > 0 THEN pg_relation_size(oid)*reltuples/(8192*relpages) 
ELSE 0 END)::bigint AS estimated_row_count
FROM pg_class
WHERE oid = 'mytable'::regclass;

Check replication status

// on master
select * from pg_stat_replication;

// on replica
select * from pg_stat_wal_receiver;

// show replication lag 
SELECT
  pg_is_in_recovery() AS is_slave,
  pg_last_wal_receive_lsn() AS receive,
  pg_last_wal_replay_lsn() AS replay,
  pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() AS synced,
  (
   EXTRACT(EPOCH FROM now()) -
   EXTRACT(EPOCH FROM pg_last_xact_replay_timestamp())
  )::int AS lag;

// show replicas status as seen from master
select client_addr, state, write_lag, flush_lag, replay_lag  from pg_stat_replication;

Show active connections

SELECT count(*) from pg_stat_activity WHERE state = 'active'; \watch

SELECT 
    pid
    ,datname
    ,usename
    ,application_name
    ,client_hostname
    ,client_port
    ,backend_start
    ,query_start
    ,query
    ,state
FROM pg_stat_activity
WHERE state = 'active';

Kill all connections that last longer than 2min

WITH inactive_connections AS (
    SELECT  pid, rank() over (partition by client_addr order by backend_start ASC) as rank
    FROM 
        pg_stat_activity
    WHERE
        pid <> pg_backend_pid( )
    AND
        datname = 'booksy-pl' 
    AND
        usename != 'postgres' 
    AND
        state in ('idle', 'idle in transaction (aborted)', 'disabled') 
    AND
        current_timestamp - state_change > interval '2 minutes' 
)
SELECT  pg_terminate_backend(pid)
FROM  inactive_connections 
WHERE rank > 1

Restoring

// extract the table you want from the dumpfile
pg_restore -t mydroppedtable dumpfile | psql

Backups of database object definitions

// dumps all databases
pg_dumpall 
// dumps only some_db database
pg_dump -d some_db 

// includes all objects, including roles, tablespaces, databases, schemas, tables, indexes, triggers, constraints, views, functions, ownerships, and privileges.
pg_dumpall --schema-only > schema-only.sql

pg_dumpall --roles-only > roles-only.sql
pg_dumpall --tablespaces-only > tablespaces-only.sql

// dump both roles and tablespaces
pg_dumpall --globals-only > globals-only.sql

Cancel query

# graceful
SELECT pg_cancel_backend(PID);
# not so graceful
SELECT pg_terminate_backend(PID);

Show long lasting queries

SELECT now() - query_start as "runtime", pid, user, datname,  state, query
  FROM  pg_stat_activity
  WHERE now() - query_start > '2 minutes'::interval
 ORDER BY runtime DESC;

Viewing the path of a relation/index

SELECT pg_relation_filepath('tablename');

SELECT relfilenode FROM pg_class where relname LIKE 'user_user_pkey';

Vacuuming

# check vacuum statuses
select * from pg_stat_progress_vacuum;

# ...then get table name:
SELECT relid, relname
FROM pg_catalog.pg_statio_user_tables
WHERE relid = '123456789';

Logging settings

# show log_destination ;
 log_destination 
-----------------
 stderr
(1 row)

# show logging_collector ;
 logging_collector 
-------------------
 on
(1 row)

# show log_directory ;
      log_directory      
-------------------------
 /home/josh/devel/pg_log
(1 row)

# show data_directory ;
       data_directory       
----------------------------
 /home/josh/devel/pgdb/data
(1 row)

# show log_filename ;
          log_filename          
--------------------------------
 postgresql-%Y-%m-%d_%H%M%S.log
(1 row)

# select pg_backend_pid() ;
 pg_backend_pid 
----------------
          31950
(1 row)

then check /proc/YOUR_PID_HERE/fd/2 which is a symlink to log destination

Viewing the details about the row

select ctid,* from foo;
 ctid  | id | name
-------+----+------
 (0,1) |  1 | Mike
 (0,2) |  2 | Mati
(2 rows)

 block number ->(0,1) <- offset

Show which queries make heavy usage of temp files

SELECT interval '1 millisecond' * total_time AS total_exec_time,
to_char(calls, 'FM999G999G999G990') AS ncalls,
total_time / calls AS avg_exec_time_ms,
interval '1 millisecond' * (blk_read_time + blk_write_time) AS sync_io_time,
temp_blks_written,
query AS query
FROM pg_stat_statements WHERE userid = (SELECT usesysid FROM pg_user WHERE usename = current_user LIMIT 1)
AND temp_blks_written > 0
ORDER BY temp_blks_written DESC
LIMIT 20;

Show publications (logical replication)

select * from pg_catalog.pg_publication;

select * from pg_publication_tables;

Misc

SELECT current_database();

SELECT current_user;

// DB address. Will be blank when using sockets
SELECT inet_server_addr(), inet_server_port();

SELECT version();

SELECT date_trunc('second', current_timestamp - pg_postmaster_start_time()) as uptime;

\conninfo

// number of tables in given db
SELECT count(*) FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','pg_catalog'); 

SELECT pg_size_pretty(pg_database_size(current_database()));

SELECT pg_size_pretty(pg_relation_size('some_table'));

// total size of a table, including indexes and other related spaces
SELECT pg_size_pretty(pg_total_relation_size('some_table');

SELECT * FROM pg_extension;

// print file locations
SHOW config_file;
SHOW hba_file;
SHOW ident_file;