Eric Radman : a Journal

PostgreSQL Administrative Queries

The following is a collection of 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 pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size
FROM pg_database
ORDER BY pg_database_size(pg_database.datname) DESC;

Show all non-default settings

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

Estimate number of rows

SELECT reltuples::bigint AS approximate_row_count
FROM pg_class
WHERE relname = 'record_descriptions';

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;

Compare backup archive with last WAL flushed

[postgres@enpgdb3 ~]$ 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());

Last updated on March 02, 2021