PostgreSQL Administrative Queries

These are operations that I commonly use to aid supporting PostgreSQL databases.

Updates

Disable triggers for session

SET session_replication_role = replica;

Use this in conunction with Iterative Large Updates.

Copying a Database

Dump/restore globals and one database to another server

pg_dumpall -g | ssh $host psql -f -
pg_dump -Fc $name  | ssh $host pg_restore -C -U $owner -d $name

Create a database on the same server

CREATE DATABASE test_localharvest WITH TEMPLATE localharvest;

Discovery

Find columns that are not used for a group of tables

SELECT tablename, attname, n_distinct, correlation, null_frac
FROM pg_stats
WHERE tablename ~ 'log_'
AND null_frac = 1;

Show which databases are handling large operations that are spooled to disk

SELECT datname,temp_files, temp_bytes/(2^30) AS "temp_files (GB)"
FROM pg_stat_database
WHERE temp_files > 1;

Databases listed by size

SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

Tables by size

SELECT
  nspname AS namespace,
  pg_get_userbyid(c.relowner) AS owner,
  relname,
  pg_size_pretty(pg_table_size(c.oid)) AS table_size
FROM pg_class c
LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
AND n.nspname !~ '^pg_toast'
AND pg_relation_size(c.oid) > 10*1048576
AND c.relkind = 'r'
ORDER BY pg_total_relation_size(c.oid) DESC;

Indexes by size

SELECT
  nspname AS namespace,
  pg_get_userbyid(c.relowner) AS owner,
  relname,
  pg_size_pretty(pg_relation_size(c.oid)) AS relation_size
FROM pg_class c
LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
AND n.nspname !~ '^pg_toast'
AND pg_relation_size(c.oid) > 10*1048576
AND c.relkind = 'i'
ORDER BY pg_total_relation_size(c.oid) DESC;

References: catalog-pg-class

Sum of objects sorted by size

WITH kinds AS (
  SELECT *
  FROM (VALUES
    ('r', 'ordinary table'),
    ('i', 'index'),
    ('S', 'sequence'),
    ('t', 'TOAST table'),
    ('v', 'view'),
    ('m', 'materialized view'),
    ('c', 'composite type'),
    ('f', 'foreign table'),
    ('p', 'partitioned table'),
    ('I', 'partitioned index')) AS t (relkind, description)
)
SELECT c.relkind, kinds.description,
  sum(pg_relation_size(c.oid)) AS total_size,
  pg_size_pretty(sum(pg_relation_size(c.oid))) AS total_size_pretty
FROM pg_class c
JOIN kinds ON kinds.relkind = c.relkind
WHERE pg_total_relation_size(c.oid) > 0
GROUP BY c.relkind, kinds.description
ORDER BY total_size DESC;

Show all non-default settings

SELECT name, current_setting(name)
FROM pg_settings
WHERE source NOT IN ('default', 'override');

Find tables and indexes with a large number of rows (approximate)

SELECT relname, reltuples::bigint
FROM pg_class
WHERE reltuples > 10000000
ORDER BY reltuples DESC;

List privileges by role

SELECT table_name, privilege_type, array_agg(grantee ORDER BY grantee) AS grantees
FROM information_schema.table_privileges
WHERE grantee IN ('user1', 'user2')
GROUP BY table_name, privilege_type;

System Status and Intervention

Find a query that is blocking others

SELECT pid, usename, pg_blocking_pids(pid) AS blocked_by, query AS blocked_query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

Terminate all remote connections

SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE client_addr IS NOT NULL;

View progress and phase of vacuum

SELECT pid, relid, phase,
       heap_blks_scanned::float/heap_blks_total as "%",
       heap_blks_total heap_blks_scanned
FROM pg_stat_progress_vacuum;

Compare backup archive with last WAL flushed

[postgres@gdb3 ~]$ pgbackrest info
stanza: main
    status: ok
    cipher: none

    db (current)
        wal archive min/max (12-2): 000000010000F34C000000B2/000000010000F34D00000010

postgres=# select pg_walfile_name(pg_current_wal_flush_lsn());

Updating Configuration

For parameters that can be updated without a server restart

SELECT pg_reload_conf();

To see host base rules

SELECT * FROM pg_hba_file_rules;

And which settings are not default

SELECT name, source, setting FROM pg_settings
WHERE source != 'default';

Insert Sample Data

Insert a random fixed-length string and a sequential id

INSERT INTO uid_mapping (uid, name)
SELECT uid, substr(md5(random()::text), 0, 10)
FROM generate_series(5000, 10000) AS uid;

Autovacuum

Determine stage and progress for autovacuum workers

SELECT pid, relid, phase,
       heap_blks_scanned::float/heap_blks_total AS "%",
       heap_blks_total, heap_blks_scanned
FROM pg_stat_progress_vacuum;
/*
initializing
scanning heap
vacuuming indexes
vacuuming heap
cleaning up indexes
truncating heap
performing final cleanup
*/

Rate-limit the I/O caused by autovacuum on a large table

ALTER TABLE files SET (autovacuum_vacuum_cost_limit = 500);
ALTER TABLE files SET (autovacuum_vacuum_cost_delay = 10);

Set back to defaults using

ALTER TABLE files RESET (autovacuum_vacuum_cost_delay, autovacuum_vacuum_cost_delay);