Practical cheat sheet for PostgreSQL admins

Getting started with PostgreSQL

A no-nonsense reference for PostgreSQL 16+ administration. Bookmark this for when things go wrong at 3am.

Connecting to PostgreSQL

# Basic connection
psql -U username -d database_name

# Connect to remote host
psql -h hostname -U username -d database_name -p 5432

# Connect with specific options
psql -U postgres -d mydb -h localhost -W

# Connect and run a command
psql -U username -d database_name -c "SELECT version();"

# Connect and run a file
psql -U username -d database_name -f script.sql

Using .pgpass for passwordless authentication

The .pgpass file stores passwords so you don’t have to type them repeatedly. Create it in your home directory.

# Create .pgpass file
touch ~/.pgpass
chmod 600 ~/.pgpass

# Format: hostname:port:database:username:password
# Examples:
localhost:5432:mydb:appuser:secret_password
*:5432:*:postgres:admin_password
192.168.1.100:5432:production_db:readonly:read_pass

# Wildcards allowed for hostname, port, and database
# Each line is one connection credential

After setting up .pgpass, connections work without password prompts:

psql -U appuser -d mydb
# No password prompt, uses .pgpass automatically

Listing databases

-- Using psql metacommand
\l

-- Or with more details
\l+

-- Using SQL
SELECT datname FROM pg_database;

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

Using service files for multiple database connections

When managing multiple database servers and databases, the service file approach keeps your connection details organised and makes switching between environments straightforward.

Creating the pg_service.conf file

The service file can be in two locations:

# System-wide (all users)
/etc/postgresql-common/pg_service.conf

# User-specific (recommended)
~/.pg_service.conf

Create your service file:

touch ~/.pg_service.conf
chmod 600 ~/.pg_service.conf

Basic service file format

# Development database
[dev]
host=localhost
port=5432
dbname=myapp_dev
user=devuser

# Staging database
[staging]
host=staging-db.company.com
port=5432
dbname=myapp_staging
user=appuser

# Production database (primary)
[prod]
host=prod-db-primary.company.com
port=5432
dbname=myapp_production
user=readonly

# Production database (replica)
[prod-replica]
host=prod-db-replica.company.com
port=5432
dbname=myapp_production
user=readonly

# Analytics database
[analytics]
host=analytics.company.com
port=5433
dbname=warehouse
user=analyst

# Legacy system
[legacy]
host=old-server.company.com
port=5432
dbname=legacy_db
user=migration_user

Connecting using service names

# Connect using service name
psql service=dev

# Or with the environment variable
export PGSERVICE=staging
psql

# Run a query against a specific service
psql service=prod -c "SELECT count(*) FROM users;"

# Run a script against a service
psql service=analytics -f report_query.sql

# Multiple commands
psql service=prod << EOF
SELECT version();
\dt
EOF

Combining service files with .pgpass

Service files work brilliantly with .pgpass for complete passwordless authentication:

# ~/.pg_service.conf
[prod]
host=prod-db.company.com
port=5432
dbname=myapp
user=appuser

# ~/.pgpass
prod-db.company.com:5432:myapp:appuser:secret_password

Now connect without any credentials:

psql service=prod
# Instantly connected, no prompts

Advanced service file options

# Service with all options
[advanced]
host=db.example.com
port=5432
dbname=mydb
user=myuser
# Password in .pgpass recommended instead
# password=not_recommended
connect_timeout=10
application_name=my_application
sslmode=require
sslcert=/path/to/client-cert.pem
sslkey=/path/to/client-key.pem
sslrootcert=/path/to/ca-cert.pem

# Connection pooler
[pooled]
host=pgbouncer.company.com
port=6432
dbname=myapp
user=pooled_user

# Read-only replica with connection limits
[replica]
host=replica.company.com
port=5432
dbname=myapp
user=readonly
options=-c default_transaction_read_only=on
connect_timeout=5

# Multiple hosts for failover (PostgreSQL 10+)
[ha-cluster]
host=db1.company.com,db2.company.com,db3.company.com
port=5432
dbname=myapp
user=appuser
target_session_attrs=read-write

Using services in scripts

#!/bin/bash

# Backup script using services
SERVICES=("dev" "staging" "prod")

for service in "${SERVICES[@]}"; do
    echo "Backing up $service..."
    pg_dump service=$service -Fc -f "backup_${service}_$(date +%Y%m%d).dump"
done

# Query multiple databases
for service in dev staging prod; do
    echo "Checking $service..."
    psql service=$service -c "SELECT count(*) FROM users;"
done

Python example with services

import psycopg2

# Connect using service name
conn = psycopg2.connect(service='prod')
cur = conn.cursor()
cur.execute("SELECT version();")
print(cur.fetchone())
conn.close()

# Or with environment variable
import os
os.environ['PGSERVICE'] = 'staging'
conn = psycopg2.connect('')  # Uses PGSERVICE

Environment variable for services

# Set default service
export PGSERVICE=dev

# Now psql uses dev service by default
psql

# Override with specific service
psql service=prod

# Temporarily use different service
PGSERVICE=staging psql

Checking which service is active

-- Show current connection info
SELECT 
    current_database() AS database,
    current_user AS user,
    inet_server_addr() AS host,
    inet_server_port() AS port;

-- Show application name (if set in service)
SHOW application_name;

Switching between databases

-- Inside psql
\c database_name

-- Or with username
\c database_name username

-- From command line
psql -U username -d different_database

Listing schemas

-- Using psql
\dn

-- Or with details
\dn+

-- Using SQL
SELECT schema_name 
FROM information_schema.schemata;

-- Show schemas with their owners
SELECT 
    nspname AS schema_name,
    pg_get_userbyid(nspowner) AS owner
FROM pg_namespace
WHERE nspname NOT LIKE 'pg_%'
AND nspname != 'information_schema';

Setting the search path

-- Show current search path
SHOW search_path;

-- Set search path for current session
SET search_path TO myschema, public;

-- Set search path for a specific user
ALTER USER username SET search_path TO myschema, public;

-- Set default search path in postgresql.conf
# search_path = '"$user", public, myschema'

Listing tables

-- Using psql (current schema)
\dt

-- All schemas
\dt *.*

-- Specific schema
\dt myschema.*

-- With details (size, description)
\dt+

-- Using SQL
SELECT 
    table_schema,
    table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
AND table_schema NOT IN ('pg_catalog', 'information_schema');

-- With row counts and sizes
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size,
    n_live_tup AS estimated_rows
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

Describing tables

-- Using psql
\d table_name

-- With more details
\d+ table_name

-- Show only columns
\d+ table_name

-- Using SQL for column information
SELECT 
    column_name,
    data_type,
    character_maximum_length,
    is_nullable,
    column_default
FROM information_schema.columns
WHERE table_name = 'your_table';

Listing indexes

-- Using psql
\di

-- For a specific table
\di table_name

-- With details
\di+

-- Using SQL
SELECT
    schemaname,
    tablename,
    indexname,
    indexdef
FROM pg_indexes
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY schemaname, tablename;

-- Indexes for a specific table
SELECT
    indexname,
    indexdef
FROM pg_indexes
WHERE tablename = 'your_table';

Listing views

-- Using psql
\dv

-- With details
\dv+

-- All schemas
\dv *.*

-- Using SQL
SELECT 
    table_schema,
    table_name
FROM information_schema.views
WHERE table_schema NOT IN ('pg_catalog', 'information_schema');

-- View definition
SELECT definition 
FROM pg_views 
WHERE viewname = 'your_view';

Listing functions

-- Using psql
\df

-- With details
\df+

-- Specific schema
\df myschema.*

-- Functions matching a pattern
\df *user*

-- Using SQL
SELECT
    routine_schema,
    routine_name,
    routine_type,
    data_type AS return_type
FROM information_schema.routines
WHERE routine_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY routine_schema, routine_name;

-- With function arguments
SELECT
    n.nspname AS schema,
    p.proname AS function_name,
    pg_get_function_arguments(p.oid) AS arguments,
    pg_get_function_result(p.oid) AS return_type
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY schema, function_name;

Listing sequences

-- Using psql
\ds

-- Using SQL
SELECT 
    sequence_schema,
    sequence_name
FROM information_schema.sequences;

-- Current sequence values
SELECT
    schemaname,
    sequencename,
    last_value,
    increment_by
FROM pg_sequences;

Listing triggers

-- Using SQL
SELECT
    trigger_schema,
    trigger_name,
    event_manipulation,
    event_object_table,
    action_statement
FROM information_schema.triggers
WHERE trigger_schema NOT IN ('pg_catalog', 'information_schema');

-- Triggers for a specific table
SELECT
    trigger_name,
    event_manipulation,
    action_timing,
    action_statement
FROM information_schema.triggers
WHERE event_object_table = 'your_table';

Listing extensions

-- Using psql
\dx

-- With details
\dx+

-- Using SQL
SELECT
    extname,
    extversion,
    extrelocatable
FROM pg_extension;

Getting help

-- Help on psql commands
\?

-- Help on SQL commands
\h

-- Help on specific SQL command
\h CREATE TABLE
\h SELECT

-- Show PostgreSQL version
SELECT version();

-- Show server encoding
SHOW server_encoding;

-- Show all settings
SHOW ALL;

Basic psql settings

-- Turn on timing for queries
\timing on

-- Toggle expanded display (vertical format)
\x

-- Toggle auto mode for expanded display
\x auto

-- Set null display
\pset null '(null)'

-- Show query buffer
\p

-- Edit query in editor
\e

-- Execute previous command
\g

-- Save query results to file
\o output.txt
SELECT * FROM users;
\o

-- Run shell command
\! ls -la

-- Change prompt
\set PROMPT1 '%n@%/%R%# '

Environment variables

# Set default database
export PGDATABASE=mydb

# Set default user
export PGUSER=postgres

# Set default host
export PGHOST=localhost

# Set default port
export PGPORT=5432

# Set password (not recommended, use .pgpass instead)
export PGPASSWORD=secret

# Set client encoding
export PGCLIENTENCODING=UTF8

# All at once
export PGDATABASE=mydb PGUSER=appuser PGHOST=localhost PGPORT=5432

Quick table creation and basic operations

-- Create a simple table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert data
INSERT INTO users (username, email) 
VALUES ('john_doe', 'john@example.com');

-- Query data
SELECT * FROM users;

-- Update data
UPDATE users SET email = 'newemail@example.com' WHERE username = 'john_doe';

-- Delete data
DELETE FROM users WHERE username = 'john_doe';

-- Copy data from CSV
COPY users(username, email) FROM '/path/to/file.csv' DELIMITER ',' CSV HEADER;

-- Export data to CSV
COPY users TO '/path/to/output.csv' DELIMITER ',' CSV HEADER;

Transaction basics

-- Start transaction
BEGIN;

-- Do some work
INSERT INTO users (username, email) VALUES ('test', 'test@example.com');
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;

-- Check if everything looks good
SELECT * FROM users WHERE username = 'test';

-- Commit if happy
COMMIT;

-- Or rollback if not
ROLLBACK;

Simulating and stopping long running queries

Create a long running query

-- Simulate a 5 minute query
SELECT pg_sleep(300);

-- Or something more realistic
SELECT COUNT(*) FROM generate_series(1, 1000000000);

Find long running queries

SELECT 
    pid,
    now() - pg_stat_activity.query_start AS duration,
    usename,
    state,
    query
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes'
AND state = 'active';

Stop a query

-- Graceful termination (preferred)
SELECT pg_cancel_backend(pid);

-- Force kill if cancel doesn't work
SELECT pg_terminate_backend(pid);

Connection management

Check current connections

SELECT count(*) FROM pg_stat_activity;

-- Detailed connection info
SELECT 
    datname,
    usename,
    application_name,
    client_addr,
    state,
    count(*)
FROM pg_stat_activity
GROUP BY datname, usename, application_name, client_addr, state;

Check onnection limits

SHOW max_connections;

-- Current usage
SELECT count(*) * 100.0 / current_setting('max_connections')::int 
AS connection_usage_percent
FROM pg_stat_activity;

Kill idle connections

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
AND state_change < current_timestamp - interval '1 hour';

Database size and bloat

Database sizes

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;

Table sizes

SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 20;

Index sizes

SELECT
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_indexes
JOIN pg_stat_user_indexes USING (schemaname, tablename, indexname)
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

Table bloat check

SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
    n_dead_tup,
    n_live_tup,
    round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

Vacuum operations

Manual vacuum

-- Standard vacuum
VACUUM VERBOSE tablename;

-- Full vacuum (locks table, reclaims space)
VACUUM FULL VERBOSE tablename;

-- Analyse statistics
ANALYZE tablename;

-- Both together
VACUUM ANALYZE tablename;

Check last vacuum/analyse

SELECT
    schemaname,
    relname,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze,
    n_dead_tup
FROM pg_stat_user_tables
ORDER BY last_autovacuum NULLS FIRST;

Autovacuum settings

-- Check global settings
SHOW autovacuum;
SHOW autovacuum_naptime;

-- Table-specific settings
SELECT
    relname,
    reloptions
FROM pg_class
WHERE reloptions IS NOT NULL;

Index management

Unused indexes

SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexrelname NOT LIKE '%pkey%'
ORDER BY pg_relation_size(indexrelid) DESC;

Missing indexes (Sequential scans)

SELECT
    schemaname,
    tablename,
    seq_scan,
    seq_tup_read,
    idx_scan,
    seq_tup_read / seq_scan AS avg_seq_read
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 20;

Duplicate indexes

SELECT
    pg_size_pretty(sum(pg_relation_size(idx))::bigint) AS size,
    (array_agg(idx))[1] AS idx1,
    (array_agg(idx))[2] AS idx2,
    (array_agg(idx))[3] AS idx3,
    (array_agg(idx))[4] AS idx4
FROM (
    SELECT
        indexrelid::regclass AS idx,
        (indrelid::text ||E'\n'|| indclass::text ||E'\n'|| indkey::text ||E'\n'||
         coalesce(indexprs::text,'')||E'\n' || coalesce(indpred::text,'')) AS key
    FROM pg_index
) sub
GROUP BY key
HAVING count(*) > 1
ORDER BY sum(pg_relation_size(idx)) DESC;

Rebuild index

REINDEX INDEX CONCURRENTLY index_name;
REINDEX TABLE CONCURRENTLY table_name;

Query performance

Slowest Queries (requires pg_stat_statements)

SELECT
    round(total_exec_time::numeric, 2) AS total_time,
    calls,
    round(mean_exec_time::numeric, 2) AS mean_time,
    round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS percentage,
    query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Cache hit ratio

SELECT
    sum(heap_blks_read) AS heap_read,
    sum(heap_blks_hit) AS heap_hit,
    round(sum(heap_blks_hit) * 100.0 / nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0), 2) AS cache_hit_ratio
FROM pg_statio_user_tables;

Active locks

SELECT
    locktype,
    database,
    relation::regclass,
    page,
    tuple,
    virtualxid,
    transactionid,
    mode,
    granted
FROM pg_locks
WHERE NOT granted;

Replication status

Check replication lag

-- On primary
SELECT
    client_addr,
    state,
    sync_state,
    pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS sending_lag,
    pg_wal_lsn_diff(sent_lsn, write_lsn) AS receiving_lag,
    pg_wal_lsn_diff(write_lsn, flush_lsn) AS flushing_lag,
    pg_wal_lsn_diff(flush_lsn, replay_lsn) AS replay_lag
FROM pg_stat_replication;

-- On replica
SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;

Replication slots

SELECT
    slot_name,
    slot_type,
    active,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;

Backup and recovery

Create backup

# Full backup
pg_dump -Fc -f database_backup.dump database_name

# Schema only
pg_dump -s -f schema_backup.sql database_name

# Specific tables
pg_dump -t table1 -t table2 -f tables_backup.sql database_name

# Directory format (parallel)
pg_dump -Fd -j 4 -f backup_dir database_name

Restore backup

# Custom format
pg_restore -d database_name database_backup.dump

# Parallel restore
pg_restore -d database_name -j 4 backup_dir

# Plain SQL
psql database_name < backup.sql

WAL archiving status

SELECT
    archived_count,
    last_archived_time,
    failed_count,
    last_failed_time
FROM pg_stat_archiver;

Configuration

Show all settings

SELECT name, setting, unit, context
FROM pg_settings
WHERE context != 'internal'
ORDER BY name;

Critical settings to review

SELECT name, setting, unit
FROM pg_settings
WHERE name IN (
    'shared_buffers',
    'effective_cache_size',
    'maintenance_work_mem',
    'work_mem',
    'max_connections',
    'checkpoint_timeout',
    'wal_buffers',
    'max_wal_size'
);

Reload configuration

SELECT pg_reload_conf();

User and permission management

List users

\du

-- Or with SQL
SELECT
    usename,
    usesuper,
    usecreatedb,
    usecreaterole,
    valuntil
FROM pg_user;

Check user permissions

SELECT
    grantee,
    table_schema,
    table_name,
    privilege_type
FROM information_schema.table_privileges
WHERE grantee = 'username';

Create user with permissions

CREATE USER appuser WITH PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE dbname TO appuser;
GRANT USAGE ON SCHEMA public TO appuser;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO appuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO appuser;

Useful psql commands

\l          List databases
\c dbname   Connect to database
\dt         List tables
\di         List indexes
\dv         List views
\df         List functions
\dn         List schemas
\du         List users
\dp         List table permissions
\d+ table   Describe table with details
\x          Toggle expanded display
\timing     Show query execution time
\! command  Execute shell command

Emergency procedures

Database won’t start

# Check logs
tail -f /var/log/postgresql/postgresql-16-main.log

# Check if process is running
ps aux | grep postgres

# Check port
netstat -tlnp | grep 5432

# Start manually
pg_ctl start -D /var/lib/postgresql/16/main

Out of disk space

-- Find what's using space
SELECT oid::regclass, pg_size_pretty(pg_total_relation_size(oid))
FROM pg_class
ORDER BY pg_total_relation_size(oid) DESC
LIMIT 20;

-- Drop old partitions if using partitioning
DROP TABLE old_partition_name;

-- Truncate logs
TRUNCATE TABLE log_table;

Database unresponsive

# Check system resources
top
iostat -x 1

# Check connections
psql -c "SELECT count(*) FROM pg_stat_activity;"

# Kill runaway queries
psql -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'active' AND query_start < now() - interval '10 minutes';"

This cheat sheet covers the essentials. Keep it handy and adjust the queries to match your monitoring needs.