PostgreSQL is one of the most powerful database systems. This is my favourite database too and the with which I have to most experiences.

As I am not a DB Administrator, I need to remember some useful and lifesaver PosgreSQL commands.

This post will be updated as often as I need new useful commands.

PostgreSQL Version

Which version of PostgreSQL:

SELECT version();

Result:

PostgreSQL 11.5 on x86_64-apple-darwin19.0.0, compiled by Apple clang version 11.0.0 (clang-1100.0.33.8), 64-bit

Database Size

Most of time when working Cloud hosted database, you need to know the of your database. Here is a command that display in human readable units the size of a specified database, database_name:

SELECT pg_size_pretty(pg_database_size('database_name'));

Result:

pg_size_pretty|
--------------|
11 MB         |

For a detailed view by table (size, indexes, …) – from here:

select current_database() as database,
       pg_size_pretty(total_database_size) as total_database_size,
       schema_name,
       table_name,
       pg_size_pretty(total_table_size) as total_table_size,
       pg_size_pretty(table_size) as table_size,
       pg_size_pretty(index_size) as index_size
       from ( select table_name,
                table_schema as schema_name,
                pg_database_size(current_database()) as total_database_size,
                pg_total_relation_size(table_name) as total_table_size,
                pg_relation_size(table_name) as table_size,
                pg_indexes_size(table_name) as index_size
                from information_schema.tables
                where table_schema=current_schema() and table_name like 'table_%'
                order by total_table_size
            ) as sizes;

Result:

database  | total_database_size | schema_name | table_name | total_table_size | table_size | index_size
-----------+---------------------+-------------+------------+------------------+------------+------------
 vigneshdb | 1586 MB             | corpdata    | table_aaa  | 16 kB            | 0 bytes    | 8192 bytes
 vigneshdb | 1586 MB             | corpdata    | table_bbb  | 24 kB            | 0 bytes    | 16 kB
 vigneshdb | 1586 MB             | corpdata    | table_ccc  | 640 kB           | 112 kB     | 488 kB
 vigneshdb | 1586 MB             | corpdata    | table_ddd  | 9760 kB          | 3152 kB    | 6568 kB
 vigneshdb | 1586 MB             | corpdata    | table_eee  | 1120 MB          | 311 MB     | 808 MB
 

List all Tables by Number of Rows

Listing from largest to smallest tables by number of rows :

select n.nspname as table_schema,
       c.relname as table_name,
       c.reltuples as rows
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where c.relkind = 'r'
      and n.nspname not in ('information_schema','pg_catalog')
order by c.reltuples desc;

Result:

	
table_schema  | table_name | rows
--------------+------------+-----
public        | foo        | 100
public        | bar        | 42

Backup

Checking if a backup is in progress:

SELECT pg_is_in_backup();

Result:

pg_is_in_backup|
---------------|
false          |

More backup commands here

Query Activities

  • Last queries:
SELECT datname, application_name, pid, backend_start, query_start, state_change, state, query 
  FROM pg_stat_activity 
  WHERE datname='database_name';

Result:

datname |application_name        |pid  |backend_start      |query_start        |state_change       |state|query                                                                                                                                                                                                                                                          |
--------|------------------------|-----|-------------------|-------------------|-------------------|-----|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
qsmarket|DBeaver 5.3.2 - Main    |69602|2019-10-31 19:31:46|2019-10-31 19:34:04|2019-10-31 19:34:04|idle |select current_database() as database,¶       pg_size_pretty(total_database_size) as total_database_size,¶       schema_name,¶       table_name,¶       pg_size_pretty(total_table_size) as total_table_size,¶       pg_size_pretty(table_size) as table_size,¶|
qsmarket|DBeaver 5.3.2 - Metadata|69603|2019-10-31 19:31:46|2019-10-31 19:34:55|2019-10-31 19:34:55|idle |SELECT i.*,c.relnamespace FROM pg_catalog.pg_inherits i,pg_catalog.pg_class c WHERE i.inhrelid=$1 AND c.oid=i.inhparent ORDER BY i.inhseqno                                                                                                                    |
  • Active queries:
SELECT * FROM pg_stat_activity WHERE state='active';

Result:

datid|datname |pid  |usesysid|usename |application_name    |client_addr|client_hostname|client_port|backend_start      |xact_start         |query_start        |state_change       |wait_event_type|wait_event|state |backend_xid|backend_xmin|query                                              |backend_type  |
-----|--------|-----|--------|--------|--------------------|-----------|---------------|-----------|-------------------|-------------------|-------------------|-------------------|---------------|----------|------|-----------|------------|---------------------------------------------------|--------------|
16403|postgres|69206|   16385|postgres|DBeaver 5.3.2 - Main|127.0.0.1  |               |      65255|2019-10-31 18:44:10|2019-10-31 19:49:20|2019-10-31 19:49:20|2019-10-31 19:49:20|               |          |active|           |1761        |SELECT * FROM pg_stat_activity WHERE state='active'|client backend|
  • Idle in transaction queries:
SELECT * FROM pg_stat_activity WHERE state='idle in transaction';
  • Running queries with PID:
SELECT pg_stat_get_backend_pid(s.backendid) AS procpid, 
  pg_stat_get_backend_activity(s.backendid) AS current_query
FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;

Result:

procpid|current_query                                                                                                                                                                                                                                                  |
-------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
  69206|SELECT pg_stat_get_backend_pid(s.backendid) AS procpid, ¶  pg_stat_get_backend_activity(s.backendid) AS current_query¶FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s                                                                              |
  69207|SELECT t.oid,t.* FROM pg_catalog.pg_type t WHERE oid=$1                                                                                                                                                                                                        |
  69602|select current_database() as database,¶       pg_size_pretty(total_database_size) as total_database_size,¶       schema_name,¶       table_name,¶       pg_size_pretty(total_table_size) as total_table_size,¶       pg_size_pretty(table_size) as table_size,¶|
  69603|SELECT i.*,c.relnamespace FROM pg_catalog.pg_inherits i,pg_catalog.pg_class c WHERE i.inhrelid=$1 AND c.oid=i.inhparent ORDER BY i.inhseqno                                                                                                                    |
  69707|select current_database() as database,¶       pg_size_pretty(total_database_size) as total_database_size,¶       schema_name,¶       table_name,¶       pg_size_pretty(total_table_size) as total_table_size,¶       pg_size_pretty(table_size) as table_size,¶|
  69708|SELECT c.relname,a.*,ad.oid as attr_id,pg_catalog.pg_get_expr(ad.adbin, ad.adrelid, true) as def_value,dsc.description¶FROM pg_catalog.pg_attribute a¶INNER JOIN pg_catalog.pg_class c ON (a.attrelid=c.oid)¶LEFT OUTER JOIN pg_catalog.pg_attrdef ad ON (a.att|
  • Number of queries:
SELECT count(*) FROM pg_stat_activity;
  • Number of queries grouped by state:
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;
  • Getting the longest running query:
SELECT max(now() - xact_start) FROM pg_stat_activity WHERE state IN ('idle in transaction', 'active');
  • Getting the execution time running queries (transaction age):
SELECT pid, (now() -xact_start), state, query FROM pg_stat_activity WHERE state IN ('idle in transaction','active');

Focus: Lock

  • Connections waiting for a lock:
SELECT count(distinct pid) FROM pg_locks WHERE granted = false;
  • Listing locks that are currently open (or waiting) on your database rows and table:
SELECT t.relname,l.locktype,page,virtualtransaction,pid,mode,granted FROM pg_locks l, pg_stat_all_tables t WHERE l.relation=t.relid ORDER BY RELATION ASC;

Terminate / Kill Queries

A soft terminate:

SELECT pg_cancel_backend(process_pid);

If it is not enought, the kill:

SELECT pg_terminate_backend(process_pid);

Optimisation / Administration

Database Analysis

pgx_scripts is a repository full of scripts for analysing databases.

Some scripts for indexing optimisation:

Statement Timeout

Long running queries are poisonous and should be run on dedicated read only replica. For preventing from long running queries you can set a timeout:

ALTER DATABASE mydatabase SET statement_timeout = '60s';

You can do the same on not working transactions:

ALTER DATABASE mydatabase SET idle_in_transaction = '60s';

Query Monitoring

Set this for recording queries in order to understand your queries and optimise them:

CREATE EXTENSION pg_stat_statements;

You can also monitor only the slow queries:

ALTER DATABASE us SET log_min_duration_statement = '100ms';

More details here

Cleaning

After deleting a large number of data, rows and more specially BLOB, it is important to run some maintenance commands for freeing some space:

-- Updating tables statistics
VACUUM ANALYSE

By command line in a shell, deleting orphans BLOBs:

vacuumlo -v -h database_address -p data base_port -U database_user  database_name

The cleaning, in SQL prompt:

VACUUM FULL

Tools

So far, the best client that I found is DBeaver.

Last releases of pgAdmin are full of bugs and missing features. I miss the version 3 which was just doing the job.

References