StackPrism

Production checklist

PostgreSQL production checklist: 5 checks before launch

5 checks for running PostgreSQL in production: 1 must-do, 3 should-dos and 1 good-to-have. Each one says why it matters, how to verify it and where the docs are.

Updated . Every check links the vendor’s own docs.

Check my repo

Must do (1)

  • Automated backups with point-in-time recovery, and a tested restore

    A backup you have never restored is a guess.

    Verify with SQL

    If you archive WAL yourself, last_archived_time should be recent and failed_count shouldn’t grow. On a managed service, check point-in-time recovery is on in its settings. Either way, restore into a scratch instance once.

    select archived_count, last_archived_time, failed_count
from pg_stat_archiver;

    Read more on postgresql.org

Should do (3)

  • Put a connection pooler in front (e.g. PgBouncer)

    Postgres forks a process per connection; app servers and serverless functions run out of connections fast.

    Verify with SQL

    Compare open connections with max_connections under normal load. If you are close to the limit, the app needs a pooler (or a smaller pool per instance).

    select count(*) as open_connections,
  current_setting('max_connections') as max_connections
from pg_stat_activity;

    Read more on pgbouncer.org

  • Require TLS for client connections

    Credentials and data otherwise cross the network in plain text.

    Verify with SQL

    Every client connection should show ssl = true. In pg_hba.conf, remote entries should be hostssl, not host.

    select a.usename, a.client_addr, s.ssl
from pg_stat_ssl s join pg_stat_activity a using (pid)
where a.client_addr is not null;

    Read more on postgresql.org

  • Connect the app with a least-privilege role, not a superuser

    An injection bug then can’t drop tables or read other schemas.

    Verify with SQL

    Run this connected as the app’s user. Every column should be false.

    select rolname, rolsuper, rolcreaterole, rolcreatedb, rolbypassrls
from pg_roles where rolname = current_user;

    Read more on postgresql.org

Good to have (1)

  • Set statement_timeout and idle_in_transaction_session_timeout

    One runaway query or stuck transaction shouldn’t hold locks forever.

    Verify with SQL

    Both settings should be non-zero for the app’s role or database (0 means no timeout).

    select name, setting from pg_settings
where name in ('statement_timeout', 'idle_in_transaction_session_timeout');

    Read more on postgresql.org

Get the checklist for your whole stack

Design your stack or map your repo in StackPrism, and the Improve panel lists the checks for every technology on it and keeps track of what you’ve done. Building with an AI app builder? Start with the vibe coding checklist.

Vibe coding security checklistMap my repo