PostgreSQL Cheat Sheet: Database Administration Commands

This cheat sheet collects practical PostgreSQL commands for administration, monitoring, and maintenance. Commands target PostgreSQL 12+ and assume superuser or equivalent privileges where noted.

Database Connection Management

Close all connections to a database

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'TARGET_DB' -- change this to your DB
  AND pid <> pg_backend_pid();

Notes:

• Replace TARGET_DB with the target database name.
• Requires superuser or a role with pg_signal_backend.
• Excludes the current session to avoid self-termination.

---

Backup and Restore Operations

Restore a pg_dump SQL backup

Before restoring, ensure a matching or newer psql client (e.g., postgresql-client-12/13/14) is installed.

PGPASSWORD=<password> psql -U <username> -h <ip address> -d <targetdb> -f <backupfile>

Parameters:

• <password>: Database user password
• <username>: Database username
• <ip address>: Database server hostname or IP
• <targetdb>: Target database name
• <backupfile>: Path to SQL dump file

For custom-format dumps (pg_dump -Fc), use pg_restore -d <targetdb> -U <username> -h <ip> <backupfile>.

---

Docker Setup

PostgreSQL Docker Compose configuration

version: '3.8'
services:
  db:
    image: postgres:12-alpine
    restart: always
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
    ports:
      - '5432:5432'
    volumes:
      - db12:/var/lib/postgresql/data
volumes:
  db12:
    driver: local

Notes:

• Change POSTGRES_USER/POSTGRES_PASSWORD for production.
• Mount a separate volume for backups or WAL archives if needed.

---

Database Size Monitoring

Top 5 largest tables in a database

SELECT
  rel.relname AS relation,
  pg_size_pretty(pg_total_relation_size(rel.oid)) AS total_size
FROM pg_class AS rel
LEFT JOIN pg_namespace AS ns ON ns.oid = rel.relnamespace
WHERE ns.nspname NOT IN ('pg_catalog', 'information_schema')
  AND rel.relkind <> 'i'
  AND ns.nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(rel.oid) DESC
LIMIT 5;

Size of all databases

SELECT
  d.datname,
  pg_size_pretty(pg_database_size(d.datname)) AS size
FROM pg_database d;

---

Connection Monitoring

Basic connection statistics

Count all active connections:

SELECT count(*) FROM pg_stat_activity;

Connections per database:

SELECT datname, numbackends FROM pg_stat_database;

Databases sorted by connection count:

SELECT datname, numbackends
FROM pg_stat_database
ORDER BY numbackends DESC;

Advanced connection analysis

Method 1:

SELECT max_conn,
       used,
       res_for_super,
       max_conn - used - res_for_super AS res_for_normal
FROM (
  SELECT count(*) AS used FROM pg_stat_activity
) t1,
(
  SELECT setting::int AS res_for_super
  FROM pg_settings
  WHERE name = 'superuser_reserved_connections'
) t2,
(
  SELECT setting::int AS max_conn
  FROM pg_settings
  WHERE name = 'max_connections'
) t3;

Method 2 (alternative):

SELECT
  MAX(CASE WHEN name = 'max_connections' THEN setting::int END) AS max_conn,
  COUNT(*) AS used,
  MAX(CASE WHEN name = 'superuser_reserved_connections' THEN setting::int END) AS res_for_super,
  MAX(setting::int) - COUNT(*) - MAX(CASE WHEN name = 'superuser_reserved_connections' THEN setting::int END) AS res_for_normal
FROM pg_settings
WHERE name IN ('max_connections', 'superuser_reserved_connections');

---

Table Analysis

Find tables without primary keys

Method 1 (catalog views):

SELECT pgc.relname
FROM pg_class pgc
JOIN pg_namespace pgns ON pgns.oid = pgc.relnamespace
WHERE pgns.nspname = 'public'
  AND pgc.relkind = 'r'
  AND pgc.oid NOT IN (
    SELECT pgc.oid
    FROM pg_class pgc
    JOIN pg_index pgi ON pgi.indrelid = pgc.oid
    JOIN pg_namespace pgns ON pgns.oid = pgc.relnamespace
    WHERE pgi.indisprimary = true
      AND pgc.relkind = 'r'
      AND pgns.nspname = 'public'
  );

Method 2 (information_schema):

SELECT
  tables.table_schema,
  tables.table_name
FROM information_schema.tables AS tables
WHERE table_type = 'BASE TABLE'
  AND table_schema NOT IN ('pg_catalog', 'information_schema')
  AND NOT EXISTS (
    SELECT 1
    FROM information_schema.table_constraints tco
    WHERE tables.table_schema = tco.table_schema
      AND tables.table_name = tco.table_name
      AND tco.constraint_type = 'PRIMARY KEY'
  )
ORDER BY
  table_schema,
  table_name;

Check replica identity settings

SELECT
  nspname AS schemaname,
  relname AS tablename,
  CASE relreplident
    WHEN 'd' THEN 'default'
    WHEN 'n' THEN 'nothing'
    WHEN 'f' THEN 'full'
    WHEN 'i' THEN 'index'
  END AS replica_identity
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE relkind = 'r'
  AND relname NOT LIKE 'pg_%'
  AND nspname NOT LIKE 'pg_%'
  AND nspname != 'information_schema';

Additional Resources

🔗 PostgreSQL Documentation: https://pgpedia.info/

🔗 Stack Exchange Reference: Tables without Primary Keys