databases, postgres

PostgreSQL useful commands

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

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
# 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
# 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
# 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.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
SELECT sourcefile, name, setting FROM pg_settings WHERE boot_val <> reset_val;
SELECT sourcefile, name, setting FROM pg_settings WHERE boot_val <> reset_val;
SELECT sourcefile, name, setting FROM pg_settings WHERE boot_val <> reset_val;

Check if Postgres is in recovery mode

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
SELECT pg_is_in_recovery();
SELECT pg_is_in_recovery();
SELECT pg_is_in_recovery();

Measure PITR progress

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
SELECT pg_last_xlog_replay_location(), pg_last_xact_replay_timestamp(), now(), now() - pg_last_xact_replay_timestamp();
SELECT pg_last_xlog_replay_location(), pg_last_xact_replay_timestamp(), now(), now() - pg_last_xact_replay_timestamp();
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

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
SELECT name, context, pending_restart FROM pg_settings where context ='postmaster' and pending_restart='t';
SELECT name, context, pending_restart FROM pg_settings where context ='postmaster' and pending_restart='t';
SELECT name, context, pending_restart FROM pg_settings where context ='postmaster' and pending_restart='t';

Show 10 biggest tables

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
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;
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;
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)

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
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;
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;
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

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
// 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;
// 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;
// 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

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
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';
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';
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

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
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
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
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

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
// extract the table you want from the dumpfile
pg_restore -t mydroppedtable dumpfile | psql
// extract the table you want from the dumpfile pg_restore -t mydroppedtable dumpfile | psql
// extract the table you want from the dumpfile
pg_restore -t mydroppedtable dumpfile | psql

Backups of database object definitions

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
// 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
// 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
// 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

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
# graceful
SELECT pg_cancel_backend(PID);
# not so graceful
SELECT pg_terminate_backend(PID);
# graceful SELECT pg_cancel_backend(PID); # not so graceful SELECT pg_terminate_backend(PID);
# graceful
SELECT pg_cancel_backend(PID);
# not so graceful
SELECT pg_terminate_backend(PID);

Show long lasting queries

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
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;
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;
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

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
SELECT pg_relation_filepath('tablename');
SELECT relfilenode FROM pg_class where relname LIKE 'user_user_pkey';
SELECT pg_relation_filepath('tablename'); SELECT relfilenode FROM pg_class where relname LIKE 'user_user_pkey';
SELECT pg_relation_filepath('tablename');

SELECT relfilenode FROM pg_class where relname LIKE 'user_user_pkey';

Vacuuming

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
# 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';
# 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';
# 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

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
# 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
# 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
# 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

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
select ctid,* from foo;
ctid | id | name
-------+----+------
(0,1) | 1 | Mike
(0,2) | 2 | Mati
(2 rows)
block number ->(0,1) <- offset
select ctid,* from foo; ctid | id | name -------+----+------ (0,1) | 1 | Mike (0,2) | 2 | Mati (2 rows) block number ->(0,1) <- offset
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

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
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;
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;
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)

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
select * from pg_catalog.pg_publication;
select * from pg_publication_tables;
select * from pg_catalog.pg_publication; select * from pg_publication_tables;
select * from pg_catalog.pg_publication;

select * from pg_publication_tables;

Misc

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
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;
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;
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;