PostgreSQL Production Operations Checklist with Practical Examples

Intro

Running PostgreSQL in production means planning for the day a query is slow, a connection fails, or a configuration change causes unexpected behavior. A checklist removes guesswork by turning vague worries into observable checks and reversible actions. This article provides a practical operations checklist for PostgreSQL, covering version and environment inventory, safe configuration changes, verification and diagnostics, failure modes and recovery, common pitfalls, and a consolidated checklist with ownership and review cadence.

The goal is operational safety: observe before changing, limit the blast radius, use placeholders instead of secrets, verify the result, and document how to recover if the expected state is not reached. Each section includes concrete commands, expected outputs, and recovery paths. You can adapt the examples to your environment, but the sequence matters: check version and topology, capture state, make one scoped change, verify, and roll back if needed.

Version and Environment Inventory

Before touching anything, know what you are running. Inventory the PostgreSQL version, operating system, deployment topology (single instance, streaming replication, or a managed service), and the tools available for administration. A production environment may have multiple PostgreSQL versions; check each one separately.

Read-only observation commands:

-- Connect to a database and get server version
SELECT version();

Expected output includes the PostgreSQL version and build details:

PostgreSQL 14.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0, 64-bit

From the shell, use pg_config to see installation details:

pg_config --version
# PostgreSQL 14.2

Check the data directory and configuration file location:

SHOW data_directory;
SHOW config_file;
SHOW hba_file;

These settings are read-only and tell you where PostgreSQL reads its configuration. The output includes the full path, for example:

   data_directory
---------------------
 /var/lib/postgresql/14/main

       config_file
-------------------------
 /etc/postgresql/14/main/postgresql.conf

Topology discovery:

Determine if the instance is a primary or a standby. On a primary, run:

SELECT pg_is_in_recovery();

Expected output: f (false) for a primary; t (true) for a standby. To see replication status on a primary:

SELECT client_addr, state, sync_state FROM pg_stat_replication;

Example output:

 client_addr  |   state   | sync_state
--------------+-----------+------------
 10.0.0.2     | streaming | async

If you are on a managed service like Amazon RDS or Google Cloud SQL, some commands may be restricted; use the provider-specific read-only views or APIs. For example, on RDS you can run SELECT aurora_version(); on Aurora PostgreSQL or check the AWS console for version information.

Blast radius and recovery: These commands are read-only and do not change state. There is no recovery path needed beyond re-running them later to confirm consistency. If the version is end-of-life, plan an upgrade before other changes; upgrading is a separate, higher-risk operation with its own checklist.

Prerequisites:

• Access to a PostgreSQL superuser or a role with pg_monitor permissions for views like pg_stat_replication.
• psql client installed locally or access to the host shell for pg_config.

Safe Configuration Path

PostgreSQL configuration lives in postgresql.conf, pg_hba.conf, and postgresql.auto.conf for settings changed via ALTER SYSTEM. Changing configuration can affect performance, security, and availability, so follow a safe path: observe current settings, change one value, reload or restart as required, verify, and know how to revert.

Step 1: Observe current settings

To see all current settings and their source:

SELECT name, setting, source, pending_restart
FROM pg_settings
WHERE name IN ('shared_buffers', 'work_mem', 'max_connections');

Example output:

      name       | setting |       source        | pending_restart
-----------------+---------+---------------------+----------------
 max_connections | 100     | configuration file  | f
 shared_buffers  | 16384   | configuration file  | f
 work_mem        | 4096    | default             | f

Units: shared_buffers and work_mem are in 8kB blocks by default, so shared_buffers = 16384 means 128 MB. pending_restart shows t if the setting requires a restart to take effect.

Step 2: Make one scoped change

Example: increase work_mem from 4 MB to 8 MB for a session to test a query without affecting the whole instance. Use SET inside your session:

SET work_mem = '8MB';
-- Run your query, then reset
RESET work_mem;

For a permanent change, use ALTER SYSTEM:

ALTER SYSTEM SET work_mem = '8MB';
SELECT pg_reload_conf();

To revert, run:

ALTER SYSTEM RESET work_mem;
SELECT pg_reload_conf();

Step 3: Verify

Check that the new value is active:

SHOW work_mem;

Expected output: 8MB. If pending_restart was t, a restart is needed; plan for downtime and perform it during a maintenance window.

Blast radius and recovery: Session-level SET only affects the current session and rolls back at disconnect. ALTER SYSTEM affects all new sessions after reload; existing sessions keep the old value. Reverting uses ALTER SYSTEM RESET and reload. Always test changes in a staging environment first.

Prerequisites:

• Superuser or pg_reload_conf privilege to reload configuration.
• For settings requiring restart, schedule downtime and have a rollback plan (e.g., revert the value and restart).

Verification and Diagnostics

Once a change is made, verify it works as expected and gather diagnostics if something is off. Use PostgreSQL's built-in views and tools to compare current state against expected behavior.

Example: Verify connection limits and active connections

Check max_connections and current usage:

SELECT count(*) AS total_connections FROM pg_stat_activity;
SELECT max_connections FROM pg_settings WHERE name = 'max_connections';

If total_connections is close to max_connections, you may be near exhaustion. Diagnose which connections are idle or long-running:

SELECT pid, usename, state, query_start, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY duration DESC
LIMIT 10;

Example: Diagnose slow queries

Enable log_min_duration_statement to log queries exceeding a threshold (e.g., 500 ms) for a specific database or user:

ALTER DATABASE mydb SET log_min_duration_statement = 500;

Then check the PostgreSQL log file for slow queries. For real-time analysis, use pg_stat_statements if the extension is enabled:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

Example output:

                     query                      | calls | total_exec_time | mean_exec_time
------------------------------------------------+-------+-----------------+----------------
 SELECT * FROM events WHERE user_id = $1       |  1023 |        12345 ms |         12.07 ms
 UPDATE accounts SET balance = balance + $1    |   512 |         8765 ms |         17.12 ms

Blast radius and recovery: Verification queries are read-only; logging settings like log_min_duration_statement affect log volume and can be reverted with ALTER DATABASE ... RESET or ALTER SYSTEM RESET. Extensions like pg_stat_statements add overhead; monitor and drop if not needed (DROP EXTENSION pg_stat_statements;).

Prerequisites:

• pg_stat_statements requires shared_preload_libraries in postgresql.conf and a restart to load.
• Access to the PostgreSQL log file location (log_directory setting) to review slow query logs.

Failure Modes and Recovery

Production databases fail in predictable ways: disk full, connection exhaustion, replication lag, corrupted indexes, or accidental data deletion. Knowing how to detect and recover from each is essential.

1. Disk Full

Detection: Monitor disk usage with OS commands (df -h) and PostgreSQL's tablespace sizes. A full disk can cause PostgreSQL to stop accepting writes and may lead to data corruption.

SELECT pg_size_pretty(pg_database_size('mydb'));
SELECT pg_size_pretty(pg_tablespace_size('pg_default'));

Recovery:

• Free space immediately: delete old WAL files if safe (pg_archivecleanup or based on your archive settings), truncate large log tables, or move a tablespace to another volume.
• Add disk space or expand the volume.
• After freeing space, run VACUUM to clean up dead tuples and verify with df -h.

2. Connection Exhaustion

Detection: Applications receive FATAL: sorry, too many clients already. Check:

SELECT count(*) FROM pg_stat_activity;
SELECT max_connections FROM pg_settings WHERE name = 'max_connections';

Recovery:

• Identify and terminate idle connections carefully:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle' AND now() - state_change > interval '10 minutes';

• Increase max_connections only after understanding memory implications; each connection consumes memory. Better: use a connection pooler like PgBouncer.
• For long-term fix, review application connection handling and implement pooling.

3. Replication Lag

Detection: On primary, check pg_stat_replication:

SELECT application_name, client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn
FROM pg_stat_replication;

On standby, run:

SELECT now() - pg_last_xact_replay_timestamp() AS lag;

If lag is large or pg_last_xact_replay_timestamp() is null for a long time, the standby is behind.

Recovery:

• Check network and standby resource issues.
• If the standby is too far behind, consider rebuilding it from a fresh base backup using pg_basebackup.
• Verify by re-running the lag query; lag should reduce.

4. Accidental Data Deletion

Detection: Application errors or user reports. If point-in-time recovery (PITR) is configured, you can restore to a previous state.

Recovery:

• Stop further writes if possible.
• Use pg_basebackup from a recent backup plus WAL archives to perform PITR. Example:

# On recovery server, restore base backup, then configure recovery.conf with recovery_target_time
recovery_target_time = '2024-03-15 10:00:00'
restore_command = 'cp /var/lib/postgresql/archive/%f %p'

• Start PostgreSQL; it replays to the target time. Verify data restoration.

5. Corrupted Index

Detection: Queries using an index fail with errors like index "idx_name" contains unexpected zero page at block 0.

Recovery:

• Reindex the affected index or table:

REINDEX INDEX idx_name;
-- Or for a whole table
REINDEX TABLE my_table;

• To avoid blocking, use REINDEX INDEX CONCURRENTLY idx_name; (requires PostgreSQL 12+).
• Verify by running a query that uses the index and checking for errors.

Blast radius and recovery for all failure modes: Most recovery actions involve changing state; test in a staging environment first. Always have recent backups and documented restore procedures. For PITR, verify backups regularly by performing restore drills.

Prerequisites:

• Backups and WAL archiving must be configured and tested before a failure occurs.
• Monitoring (e.g., Prometheus, Nagios) should alert on disk usage, connection count, and replication lag.

Common Pitfalls

Even experienced operators fall into traps. Here are frequent mistakes and how to avoid or recover from them.

1. Changing Configuration Without Testing

Why it happens: Pressure to fix a performance issue quickly leads to changing shared_buffers, work_mem, or max_connections directly in production.

How to avoid: Test changes in a staging environment that mirrors production. Use ALTER SYSTEM and pg_reload_conf for reloadable parameters; for restart-required parameters, schedule maintenance and have a rollback plan.

Recovery: If a change causes problems, revert immediately: ALTER SYSTEM RESET parameter; SELECT pg_reload_conf(); or edit postgresql.conf and restart if necessary.

2. Ignoring Vacuum and Autovacuum

Why it happens: Autovacuum runs in the background, so people assume it is always working. If tables grow large and bloat accumulates, performance degrades and transaction ID wraparound can occur.

How to avoid: Monitor autovacuum activity: SELECT relname, last_autovacuum, n_dead_tup FROM pg_stat_user_tables WHERE n_dead_tup > 1000;. Tune autovacuum thresholds if needed.

Recovery: If bloat is severe, run VACUUM FULL or pg_repack during downtime. To prevent, ensure autovacuum is enabled and adjust autovacuum_vacuum_scale_factor appropriately.

3. Not Setting Up Monitoring and Alerting

Why it happens: Small teams may rely on manual checks. When something breaks, they discover it from users.

How to avoid: Set up basic monitoring: a script that queries pg_stat_activity, pg_stat_replication, and disk usage every minute, alerting on thresholds. Use tools like pgwatch2, check_postgres, or cloud monitoring.

Recovery: If you lack monitoring, start with a simple cron job that checks SELECT count(*) FROM pg_stat_activity WHERE state = 'active'; and logs to a file. Gradually add more checks.

4. Using Superuser for Application Connections

Why it happens: Convenience; application developers may use the postgres superuser role to avoid permission issues.

How to avoid: Create dedicated roles with least privilege. For example:

CREATE ROLE app_user LOGIN PASSWORD 'strong_password';
GRANT CONNECT ON DATABASE mydb TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

Recovery: If you discover superuser usage, create new roles and gradually migrate applications. Revoke superuser from application roles.

5. Skipping Backup Verification

Why it happens: Backups run automatically, so people assume they work. A corrupted or incomplete backup is only discovered during a restore attempt.

How to avoid: Regularly test restores. Schedule a monthly restore drill: restore the latest backup to a test instance and verify data integrity. Automate with scripts.

Recovery: If you find backups are failing, fix the backup process immediately and create a fresh full backup. Document the failure and add monitoring for backup job status.

Operations Checklist

Below is a consolidated checklist for routine and emergency operations. Assign a single accountable owner (name a real or example role) and a review cadence for each item. The owner is responsible for executing or ensuring execution, not necessarily performing every task personally.

Each owner should document the outcome of their review and any actions taken. The review cadence assumes a production environment with moderate change frequency; adjust based on your risk profile.

Conclusion

A PostgreSQL production operations checklist is only useful if it is version-scoped, observable, and reversible where the technology permits. Copying a command without checking prerequisites and expected output is not an operations procedure.

Start with one low-risk verification from this article: check your PostgreSQL version and topology, record the current state, and compare it with your baseline. Then move to safe configuration changes, diagnostics, and recovery drills. Assign owners and review cadences to keep the checklist alive.

Reliable operations make failure visible, protect sensitive values, limit changes to the intended resource, and define recovery verification before an incident forces the decision. Use this article as a starting point, adapt the commands to your environment, and continuously improve your runbooks.