11. Administration and Operations

Designing a good schema and writing fast queries matter only if the server underneath stays configured correctly, keeps data safe across crashes, can be restored after a disaster, can be upgraded without losing a weekend, and is watched by people who notice problems before users do. This chapter covers that operational side of PostgreSQL: how the server is configured, how the write-ahead log provides durability, how backups and point-in-time recovery work, how to upgrade and migrate, and the routine practices that keep a production database healthy. These are the skills that separate "it works on my machine" from "it has run for five years without losing a row".

<a id="111-server-configuration"></a>

11.1 Server Configuration

PostgreSQL exposes several hundred settings that control memory, connections, logging, durability, and more; this subchapter explains where those settings live, how and when changes take effect, how to scope them to a database or role, and which limits and logging settings every production server should set deliberately.

postgresql.conf Structure

A database server needs to know hundreds of things before it can serve a single query: how much memory to use for caching, which network interfaces to listen on, where to write logs, how aggressively to flush data to disk. Hard-coding these would make one server fit nobody, so PostgreSQL reads them from configuration. The main source is a plain text file called postgresql.conf, but it is only one layer of a stack of sources that are merged into the final value of each setting (each setting is also called a parameter or a GUC, short for Grand Unified Configuration, the name of the subsystem that manages them).

Analogy. Think of the configuration as a stack of transparent sheets laid on top of each other. The bottom sheet holds the compiled-in defaults. Each sheet above it (the config file, ALTER SYSTEM, the database, the role, the session) can write a new value over a spot on the sheet below. What you see from above, the effective value, is the top-most value written for each setting.

Finding the file

SHOW config_file;
--                config_file
-- -----------------------------------------
--  /etc/postgresql/17/main/postgresql.conf     -- Debian/Ubuntu; $PGDATA/postgresql.conf for most other installs

SHOW data_directory;
SHOW hba_file;      -- pg_hba.conf (10.1)
SHOW ident_file;    -- pg_ident.conf

From the shell, the server binary itself can report a value without a running server, which is handy in scripts:

postgres -D /var/lib/postgresql/17/main -C shared_buffers   # prints the value the server would use

File syntax. The file is a list of name = value lines. The = is optional, whitespace is ignored, and # starts a comment. The shipped file is mostly commented-out defaults grouped into sections, which serves as documentation.

# -----------------------------
# CONNECTIONS AND AUTHENTICATION
# -----------------------------
listen_addresses = '10.0.1.10, localhost'   # strings are single-quoted
port = 5432                                 # integers need no quotes
max_connections = 200

# -----------------------------
# RESOURCE USAGE
# -----------------------------
shared_buffers = 8GB                        # memory units: B, kB, MB, GB, TB (base 1024)
work_mem = 32MB
maintenance_work_mem = 1GB

# -----------------------------
# WRITE-AHEAD LOG
# -----------------------------
checkpoint_timeout = 15min                  # time units: us, ms, s, min, h, d
synchronous_commit = on                     # booleans: on/off, true/false, yes/no, 1/0
wal_compression = zstd                      # enums are unquoted words (quotes also work)

# A later line for the same parameter wins over an earlier one:
work_mem = 64MB                             # effective value is 64MB, not 32MB

Units matter: a parameter has a base unit (shared_buffers is measured in 8 kB blocks, work_mem in kB, statement_timeout in ms). A bare number is interpreted in that base unit, so shared_buffers = 1024 means 8 MB, not 1024 MB. Always write the unit explicitly.

The configuration stack. For any setting, later sources override earlier ones:

 highest precedence
   ^   SET LOCAL inside a transaction           (until COMMIT/ROLLBACK)
   |   SET / set_config() in the session        (until RESET or disconnect)
   |   Function-level SET clause                (while the function runs, 9.1)
   |   ALTER ROLE ... IN DATABASE ... SET        (new sessions of that role in that db)
   |   ALTER ROLE ... SET                        (new sessions of that role)
   |   ALTER DATABASE ... SET                    (new sessions in that db)
   |   Client connection options (PGOPTIONS, options= in the connection string)
   |   Command line (postgres -c name=value, pg_ctl -o)
   |   postgresql.auto.conf                      (ALTER SYSTEM)
   |   Files included from postgresql.conf       (in the order they are included)
   |   postgresql.conf
   |   Compiled-in default
 lowest precedence

Inspecting the effective configuration. The pg_settings view is the authoritative source. It shows the current value, its unit, where it came from, and whether it needs a restart.

SELECT name, setting, unit, source, sourcefile, sourceline, pending_restart
FROM pg_settings
WHERE name IN ('shared_buffers', 'work_mem', 'max_connections', 'log_line_prefix');
--        name       |  setting  | unit |       source       |               sourcefile                | sourceline | pending_restart
-- ------------------+-----------+------+--------------------+-----------------------------------------+------------+-----------------
--  log_line_prefix  | %m [%p]   |      | configuration file | /etc/postgresql/17/main/postgresql.conf |        533 | f
--  max_connections  | 200       |      | configuration file | /etc/postgresql/17/main/postgresql.conf |         65 | f
--  shared_buffers   | 1048576   | 8kB  | configuration file | /etc/postgresql/17/main/postgresql.conf |        131 | f   -- 1048576 x 8kB = 8GB
--  work_mem         | 65536     | kB   | configuration file | /etc/postgresql/17/main/postgresql.conf |        142 | f

-- Everything that differs from the built-in default:
SELECT name, current_setting(name) AS value, source
FROM pg_settings
WHERE source NOT IN ('default', 'override')
ORDER BY name;

SHOW work_mem and current_setting('work_mem') return the value already formatted with its unit, which is easier to read than the raw setting column.

The companion view pg_file_settings shows what is written in the files, line by line, including lines that were overridden or contain errors. It is the tool for checking a file before you reload it:

SELECT sourcefile, sourceline, name, setting, applied, error
FROM pg_file_settings
WHERE NOT applied OR error IS NOT NULL;
--                sourcefile                | sourceline |   name   | setting | applied |            error
-- -----------------------------------------+------------+----------+---------+---------+------------------------------
--  /etc/postgresql/17/main/postgresql.conf |        142 | work_mem | 32MB    | f       |                                -- overridden by a later line
--  /etc/postgresql/17/main/postgresql.conf |        210 | wal_levl | replica | f       | unrecognized configuration parameter

Key takeaways

• postgresql.conf holds name = value lines; later lines for the same parameter win, and # starts a comment.
• The effective value is the top of a stack: defaults, files, ALTER SYSTEM, command line, database, role, session, transaction.
• Always write units (8GB, 30s); a bare number uses the parameter's base unit, which is often not what you expect.
• pg_settings shows effective values and where they came from; pg_file_settings shows file contents and parse errors before you reload.
• SHOW config_file and postgres -C name locate and query configuration without guessing paths.

🧪 Practice

1. (Beginner) Find your server's postgresql.conf, then list every parameter whose source in pg_settings is not default. For three of them, explain what the setting controls and why the packager or you changed it.
2. (Intermediate) Add the line work_mem = 4096 (no unit) and a misspelled parameter to the file. Before reloading, use pg_file_settings to spot both problems. What value would work_mem have had if you reloaded?
3. (Interview) "A colleague changed work_mem in postgresql.conf and reloaded, but SHOW work_mem in the application still shows the old value. List the possible reasons." Hint: Walk up the configuration stack: a later duplicate line, postgresql.auto.conf, ALTER ROLE/ALTER DATABASE settings, a session-level SET, or a connection pooler keeping old sessions.

Configuration Contexts and Reload Behavior

Not every setting can change at any time. Some decide how much shared memory the server allocates at startup, and that memory cannot be resized while hundreds of processes are using it. Others only affect how a single query is planned and can safely differ from one session to the next. PostgreSQL records this for each parameter as its context, and the context tells you exactly what you must do for a change to take effect.

Analogy. Think of a building. Moving a load-bearing wall (postmaster context) requires evacuating the building, which means a restart. Changing the thermostat schedule for the whole building (sighup) is done centrally and every room picks it up shortly after. Adjusting the lamp on your own desk (user) affects only you, and you can do it whenever you like.

The contexts

Context	Who can change it	How it takes effect	Examples
internal	Nobody (fixed at compile time or initdb)	Never changes	block_size, data_checksums, server_version
postmaster	Superuser, in config files or command line	Full server restart	shared_buffers, max_connections, listen_addresses, wal_level, shared_preload_libraries
sighup	Superuser, in config files	Reload (pg_reload_conf(), pg_ctl reload, SIGHUP)	log_line_prefix, checkpoint_timeout, max_wal_size, autovacuum_naptime
superuser-backend	Superuser, config or at connection start	Reload affects only new sessions	log_connections, log_disconnections
backend	Any user at connection start	Only new sessions	ignore_system_indexes, jit_debugging_support
superuser	Superuser (or a role granted SET on it)	Immediately with SET; reload for the default	log_min_duration_statement, log_statement, session_replication_role
user	Any user	Immediately with SET; reload for the default	work_mem, statement_timeout, search_path, timezone

-- What context does a parameter have, and does it need a restart right now?
SELECT name, context, setting, pending_restart
FROM pg_settings
WHERE name IN ('shared_buffers', 'max_wal_size', 'work_mem', 'log_statement');
--       name      |  context   | setting | pending_restart
-- ----------------+------------+---------+-----------------
--  log_statement  | superuser  | none    | f
--  max_wal_size   | sighup     | 4096    | f
--  shared_buffers | postmaster | 1048576 | t     -- edited in the file, waiting for a restart
--  work_mem       | user       | 65536   | f

Reloading. A reload makes the postmaster re-read the configuration files and signal every child process to do the same. It does not drop connections, so it is safe to do at any time.

SELECT pg_reload_conf();   -- returns true if the signal was sent, not whether the file was valid

pg_ctl reload -D /var/lib/postgresql/17/main
sudo systemctl reload postgresql@17-main      # Debian/Ubuntu service name

If the file contains an invalid value, the server logs an error and keeps the previous value for that parameter. Always check the log or pg_file_settings after a reload. Parameters with postmaster context that you edited are silently ignored until restart; pending_restart = true is how you find them:

SELECT name, setting FROM pg_settings WHERE pending_restart;

LOG:  received SIGHUP, reloading configuration files
LOG:  parameter "shared_buffers" cannot be changed without restarting the server
LOG:  parameter "checkpoint_timeout" changed to "15min"
LOG:  configuration file "/etc/postgresql/17/main/postgresql.conf" contains errors; unaffected changes were applied

Session and transaction scope. Parameters with user (and, for privileged roles, superuser) context can be changed at runtime:

SET work_mem = '256MB';                -- for the rest of this session
SHOW work_mem;                         -- 256MB
RESET work_mem;                        -- back to the value the session started with

BEGIN;
SET LOCAL statement_timeout = '5s';    -- only until COMMIT or ROLLBACK
SELECT expensive_report();
COMMIT;                                -- statement_timeout reverts automatically

SELECT set_config('search_path', 'app, public', false);  -- function form; true = transaction-local

SET LOCAL is the right tool inside connection-pooled applications (PgBouncer in transaction mode, 7.4), because a plain SET would leak into whichever client gets the connection next.

Delegating parameters. Since PostgreSQL 15, a superuser can let other roles change specific superuser-context parameters without making them superusers:

GRANT SET ON PARAMETER log_min_duration_statement TO support_engineer;
GRANT ALTER SYSTEM ON PARAMETER checkpoint_timeout TO ops_admin;

Restart versus reload in practice

 Edit postgresql.conf
        |
        v
 Check pg_file_settings for errors ----- errors? --> fix the file
        |
        v
 SELECT pg_reload_conf();
        |
        v
 SELECT name FROM pg_settings WHERE pending_restart;
        |                         |
     (none)                    (some rows)
        |                         |
      done              schedule a restart (failover to a standby
                        first if downtime is not acceptable, 12.3)

Key takeaways

• Each parameter's context determines whether it needs a restart (postmaster), a reload (sighup), a new session (backend), or can be SET at runtime (user, superuser).
• A reload never drops connections; invalid values are logged and ignored, so check the log or pg_file_settings.
• pending_restart in pg_settings lists edits that are waiting for a restart.
• Use SET LOCAL for transaction-scoped changes, especially behind a transaction-mode connection pooler.
• GRANT SET ON PARAMETER and GRANT ALTER SYSTEM ON PARAMETER delegate specific settings without granting superuser.

🧪 Practice

1. (Beginner) Count how many parameters fall in each context with SELECT context, count(*) FROM pg_settings GROUP BY context. Pick one parameter from each of postmaster, sighup, and user, and describe how you would change it.
2. (Intermediate) In one session, run BEGIN; SET LOCAL work_mem = '1GB'; SHOW work_mem; ROLLBACK; SHOW work_mem;. Then repeat with plain SET and explain the difference. Why does the difference matter behind PgBouncer in transaction mode?
3. (Interview) "You changed max_connections and max_wal_size in the config file and ran pg_reload_conf(). What happened to each change, and how do you verify it?" Hint: Compare the contexts of the two parameters, then look at pending_restart and the server log.

ALTER SYSTEM and Include Files

Editing postgresql.conf requires shell access to the database host, a text editor, and care not to break the syntax. That is awkward when you only have a SQL connection, and it is fragile when several people or tools edit the same file. PostgreSQL offers two complementary mechanisms: ALTER SYSTEM, which changes server-wide settings from SQL, and include directives, which split the configuration into several files that different owners can manage.

Analogy. postgresql.conf is the printed company handbook. ALTER SYSTEM is a sticky note the administrator puts on the handbook from a remote terminal; it overrides the printed text without touching it. Include files are separate chapters (security, logging, tuning) that different teams maintain and that the handbook pulls in by reference.

ALTER SYSTEM

ALTER SYSTEM SET checkpoint_timeout = '15min';
ALTER SYSTEM SET log_min_duration_statement = '500ms';
SELECT pg_reload_conf();                  -- ALTER SYSTEM only writes the file; it does not apply anything

ALTER SYSTEM RESET log_min_duration_statement;   -- remove one entry from postgresql.auto.conf
ALTER SYSTEM RESET ALL;                          -- remove every entry

ALTER SYSTEM writes to postgresql.auto.conf in the data directory, which is read after postgresql.conf and therefore wins over it:

# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
checkpoint_timeout = '15min'
log_min_duration_statement = '500ms'

Important rules:

• The command validates the value before writing it, so a typo is rejected instead of breaking the next restart.
• It cannot run inside a transaction block, and changes still need a reload or restart according to the parameter's context.
• It requires superuser, or GRANT ALTER SYSTEM ON PARAMETER name (PostgreSQL 15+).
• Never edit postgresql.auto.conf by hand while the server runs; the next ALTER SYSTEM rewrites the whole file.
• Because it overrides postgresql.conf, a value set with ALTER SYSTEM makes later edits of the same parameter in postgresql.conf appear to do nothing. This is the most common source of "I changed the config and nothing happened".

Disabling ALTER SYSTEM. When configuration is managed by Ansible, Puppet, Kubernetes operators, or Patroni, changes made from SQL drift away from the declared state. PostgreSQL 17 added allow_alter_system = off to reject the command. It is a guard rail for operators, not a security boundary: a superuser can still write files through other means.

Include directives. postgresql.conf can pull in other files:

# At the end of postgresql.conf
include_dir = 'conf.d'                 # every *.conf file in the directory, in file-name order
include_if_exists = 'local.conf'       # no error if the file is missing
include = '/etc/postgresql/shared/security.conf'   # error if the file is missing

Relative paths are resolved against the directory of the file that contains the directive. Files in an include_dir are processed in ASCII order of their names, so a numeric prefix gives a predictable override order:

/etc/postgresql/17/main/
  postgresql.conf          # packaged defaults, rarely edited
  conf.d/
    00-memory.conf         # generated from host size by configuration management
    10-logging.conf        # owned by the observability team
    20-replication.conf    # owned by the HA tooling
    90-overrides.conf      # emergency, temporary overrides with a ticket reference

Choosing a method

Method	Good for	Watch out for
Edit postgresql.conf	Small, single-admin servers	Merge conflicts with package upgrades, syntax mistakes
include_dir with snippets	Configuration management, multiple owners	Override order depends on file names
ALTER SYSTEM	Managed services, SQL-only access, quick changes	Silently overrides the files; can drift from version control
Command line -c	Containers, one-off tests	Hidden from anyone reading only the files

To see which layer set a parameter, pg_settings.sourcefile tells you the exact file and sourceline the line, including postgresql.auto.conf:

SELECT name, setting, sourcefile, sourceline
FROM pg_settings
WHERE sourcefile LIKE '%auto.conf' OR sourcefile LIKE '%conf.d%';

Key takeaways

• ALTER SYSTEM validates values and writes them to postgresql.auto.conf, which overrides postgresql.conf; you still need a reload or restart.
• Never hand-edit postgresql.auto.conf; use ALTER SYSTEM RESET to remove entries.
• include, include_if_exists, and include_dir let you split configuration by owner; include_dir files apply in file-name order.
• When configuration is managed as code, set allow_alter_system = off (PostgreSQL 17+) to prevent drift.
• pg_settings.sourcefile and sourceline reveal which file actually set a value.

🧪 Practice

1. (Beginner) Use ALTER SYSTEM to set log_min_duration_statement to 250ms, reload, and confirm the value with SHOW. Open postgresql.auto.conf to see what was written, then remove the entry with ALTER SYSTEM RESET.
2. (Intermediate) Create conf.d/10-a.conf with work_mem = 16MB and conf.d/20-b.conf with work_mem = 32MB, include the directory, and reload. Predict the effective value, verify it with pg_settings, and then add an ALTER SYSTEM setting to see which layer wins.
3. (Interview) "Your team manages PostgreSQL with Ansible. During an incident, someone ran ALTER SYSTEM SET max_wal_size = '50GB'. Months later, an Ansible change to max_wal_size has no effect. Why, and how do you prevent it from happening again?" Hint: Think about the order in which the files are read, how to detect entries in postgresql.auto.conf, and allow_alter_system.

Per-Database and Per-Role Settings

One server often hosts very different workloads: an OLTP application that must never run a query longer than a few seconds, a reporting team whose queries sort millions of rows, and a nightly ETL job. A single server-wide value for statement_timeout or work_mem cannot suit all of them. PostgreSQL lets you attach defaults to a database, a role, or a combination of both, so each workload gets sensible settings automatically when it connects.

Analogy. A hotel has a default room temperature for the whole building, but VIP guests have a preferred temperature on file, and the conference wing is kept cooler. When a guest checks into a room, the most specific preference applies: this guest in this wing, then this guest anywhere, then this wing, then the building default.

Setting defaults

-- Database level: every session connected to the 'analytics' database
ALTER DATABASE analytics SET work_mem = '128MB';
ALTER DATABASE analytics SET default_transaction_read_only = on;

-- Role level: every session of this role, in any database
ALTER ROLE app_user SET statement_timeout = '5s';
ALTER ROLE app_user SET idle_in_transaction_session_timeout = '30s';
ALTER ROLE reporting SET work_mem = '256MB';

-- Role in a specific database: the most specific level
ALTER ROLE etl_job IN DATABASE warehouse SET maintenance_work_mem = '2GB';

-- Removing settings
ALTER ROLE app_user RESET statement_timeout;
ALTER DATABASE analytics RESET ALL;

Precedence. When a session starts, PostgreSQL applies these defaults on top of the server configuration, from least to most specific:

 server config (postgresql.conf, ALTER SYSTEM)
        |
        v
 ALTER DATABASE analytics SET ...              applies to everyone in 'analytics'
        |
        v
 ALTER ROLE reporting SET ...                  overrides the database value for 'reporting'
        |
        v
 ALTER ROLE reporting IN DATABASE analytics SET ...   overrides both
        |
        v
 SET in the session / SET LOCAL in a transaction      the client always has the last word

The client can still override any user-context setting with SET. Per-role defaults are therefore defaults, not enforcement: a statement_timeout set on a role protects against accidents, not against a client that deliberately runs SET statement_timeout = 0.

Inspecting the settings

-- psql meta-command: list per-database and per-role settings
\drds

-- Or query the catalog directly
SELECT coalesce(r.rolname, '(all roles)')     AS role,
       coalesce(d.datname, '(all databases)') AS database,
       s.setconfig
FROM pg_db_role_setting s
LEFT JOIN pg_roles    r ON r.oid = s.setrole
LEFT JOIN pg_database d ON d.oid = s.setdatabase
ORDER BY 1, 2;
--     role     |    database     |                       setconfig
-- -------------+-----------------+-------------------------------------------------------
--  (all roles) | analytics       | {work_mem=128MB,default_transaction_read_only=on}
--  app_user    | (all databases) | {statement_timeout=5s,idle_in_transaction_session_timeout=30s}
--  etl_job     | warehouse       | {maintenance_work_mem=2GB}
--  reporting   | (all databases) | {work_mem=256MB}

-- In a live session, pg_settings.source tells you which level applied
SELECT name, setting, source FROM pg_settings WHERE name = 'statement_timeout';
--        name        | setting | source
-- -------------------+---------+--------
--  statement_timeout | 5000    | user          -- 'database', 'user', 'database user', or 'session'

When the settings apply. These defaults are read only when a session starts. Existing sessions keep their values, so long-lived pooled connections may need to be recycled (for example, RECONNECT in the PgBouncer admin console, or restarting application pools) before a new role setting takes effect everywhere.

Typical patterns

Workload	Setting	Level	Why
Web application	statement_timeout = '5s'	Role	Kill runaway queries before they pile up
Web application	idle_in_transaction_session_timeout = '30s'	Role	Release locks held by forgotten transactions
Reporting users	work_mem = '256MB'	Role	Large sorts and hashes stay in memory
Read-only replica database for analysts	default_transaction_read_only = on	Database	Guard against accidental writes
Migrations role	lock_timeout = '3s'	Role	DDL fails fast instead of queueing behind long queries
One noisy service	log_min_duration_statement = '100ms'	Role	Detailed logs for one client only (needs superuser to set)
Multi-tenant schemas	search_path = 'tenant_42, public'	Role	Each tenant role resolves its own tables first

A function can also carry its own settings with a SET clause (CREATE FUNCTION ... SET search_path = app, pg_temp), which applies only while the function runs. This is essential for SECURITY DEFINER functions (Security Definer vs. Invoker).

Key takeaways

• ALTER DATABASE ... SET, ALTER ROLE ... SET, and ALTER ROLE ... IN DATABASE ... SET attach defaults that apply when a session starts.
• Precedence goes from server config, to database, to role, to role-in-database, to the session itself.
• They are defaults, not limits: a client can still SET user-context parameters.
• Settings apply to new sessions only; recycle pooled connections after changes.
• Inspect them with \drds or pg_db_role_setting, and check pg_settings.source in a live session.

🧪 Practice

1. (Beginner) Create roles oltp_app and analyst. Give oltp_app a statement_timeout of 2 seconds and analyst a work_mem of 128 MB. Connect as each role and verify the values with SHOW and pg_settings.source.
2. (Intermediate) Set work_mem at the database level to 64 MB, at the role level to 128 MB, and at the role-in-database level to 256 MB. Predict the value a session sees in each of three connection scenarios, then verify. Finally, show how the client can override all of them.
3. (Interview) "How would you make sure the application cannot run queries longer than 10 seconds, even if a developer adds SET statement_timeout = 0 in code?" Hint: Per-role defaults are only defaults. Consider what can and cannot be enforced inside PostgreSQL, and what a pooler, a proxy, or monitoring with pg_terminate_backend could add.

Resource Limit Parameters

A database server is a shared resource: memory, CPU, connections, disk, and locks are all finite. Without limits, a single bad query can fill the disk with temporary files, one forgotten transaction can block vacuum for days, and a connection storm can exhaust memory until the operating system kills the server. Resource limit parameters set boundaries so one misbehaving client degrades only itself, not everyone.

Analogy. A shared kitchen has rules: a maximum number of cooks at once, a time limit per burner, a size limit per pot, and a rule that nobody may leave food on a burner unattended. None of these rules makes the food better, but together they stop one cook from ruining dinner for everyone.

Connection limits

max_connections = 200                  # hard ceiling on client sessions (restart required)
superuser_reserved_connections = 3     # slots kept for superusers when the server is "full"
reserved_connections = 5               # PG 16+: slots for roles granted pg_use_reserved_connections

ALTER ROLE app_user CONNECTION LIMIT 150;          -- per-role cap
ALTER DATABASE reporting CONNECTION LIMIT 20;      -- per-database cap
GRANT pg_use_reserved_connections TO monitoring;   -- monitoring can still log in during a storm

Each connection is a separate operating system process with its own memory, so max_connections in the thousands is almost always a mistake. Use a connection pooler to serve many clients with few server connections (Connection Pooling with PgBouncer).

Memory limits. PostgreSQL does not have a single "maximum memory" setting. Memory use is the sum of shared memory and per-operation private memory:

Parameter	Scope	What it limits
shared_buffers	Shared, allocated at start	Page cache shared by all processes
work_mem	Per sort or hash operation	Memory before spilling to temp files; one query can use it many times
hash_mem_multiplier	Multiplier of work_mem for hash nodes	Default 2.0
maintenance_work_mem	Per maintenance operation	CREATE INDEX, VACUUM, ALTER TABLE ADD FOREIGN KEY
autovacuum_work_mem	Per autovacuum worker	Defaults to maintenance_work_mem (-1)
temp_buffers	Per session	Temporary tables
logical_decoding_work_mem	Per replication connection	Changes kept in memory before spilling

A rough worst-case estimate for capacity planning:

 shared_buffers                                         8 GB
 + max_connections x work_mem x ops_per_query           200 x 32 MB x 3   = 18.75 GB   (worst case, rarely reached)
 + autovacuum_max_workers x autovacuum_work_mem         3 x 1 GB          =  3 GB
 + OS page cache and other processes                    the rest

The worst case is pessimistic because most connections are idle and most queries use one or two memory-hungry nodes, but it shows why raising work_mem server-wide is dangerous. Raise it per role or per transaction instead. Detailed tuning of shared_buffers and work_mem is in Memory Parameters work_mem and shared_buffers.

Time limits

Parameter	Kills or cancels	Typical value
statement_timeout	Any single statement running longer	5s–60s for OLTP roles; 0 (off) server-wide
lock_timeout	A statement waiting for a lock longer	2s–5s for migrations
idle_in_transaction_session_timeout	A session idle inside an open transaction	30s–10min
idle_session_timeout (PG 14+)	A session idle outside a transaction	Hours, or 0; beware poolers that keep idle connections on purpose
transaction_timeout (PG 17+)	A whole transaction longer than the limit	Optional safety net for application roles

idle_in_transaction_session_timeout deserves special attention. A session that runs BEGIN, does some work, and then waits for a slow API call or a crashed client holds its locks and its snapshot. That snapshot prevents vacuum from removing dead rows in every table, causing bloat across the whole database (8.5).

Disk and other limits

temp_file_limit = 20GB             # per-process cap on temporary file space; the query fails instead of filling the disk
max_locks_per_transaction = 64     # size of the shared lock table (raise for many partitions)
max_worker_processes = 16          # all background workers, including parallel workers
max_parallel_workers = 8
max_parallel_workers_per_gather = 2

A sensible baseline

-- Server-wide safety nets that rarely hurt
ALTER SYSTEM SET idle_in_transaction_session_timeout = '10min';
ALTER SYSTEM SET temp_file_limit = '50GB';

-- Strict limits for the application role
ALTER ROLE app_user SET statement_timeout = '15s';
ALTER ROLE app_user SET idle_in_transaction_session_timeout = '60s';
ALTER ROLE app_user SET lock_timeout = '5s';

-- Generous limits for reporting, but bounded
ALTER ROLE reporting SET statement_timeout = '15min';
ALTER ROLE reporting SET work_mem = '256MB';

SELECT pg_reload_conf();

When a limit triggers, the client receives a specific error, which makes the cause easy to identify in logs and application monitoring:

ERROR:  canceling statement due to statement timeout
ERROR:  canceling statement due to lock timeout
FATAL:  terminating connection due to idle-in-transaction timeout
ERROR:  temporary file size exceeds temp_file_limit (52428800kB)
FATAL:  sorry, too many clients already
FATAL:  too many connections for role "app_user"

Outside PostgreSQL. The operating system adds its own limits: systemd LimitNOFILE for file descriptors, cgroup memory limits in containers, and the Linux OOM killer. If the OOM killer terminates a backend, the postmaster restarts all sessions and runs crash recovery. On dedicated Linux hosts, set vm.overcommit_memory = 2 so allocations fail with an error inside PostgreSQL instead of triggering the OOM killer.

Key takeaways

• Each connection is a process; keep max_connections modest and use a pooler. Reserve slots for superusers and monitoring.
• work_mem applies per operation, not per query or per server; raise it per role or per transaction, not globally.
• Time limits (statement_timeout, lock_timeout, idle_in_transaction_session_timeout, transaction_timeout) stop runaway work and forgotten transactions.
• temp_file_limit turns a disk-filling query into a single failed query.
• Set strict limits on application roles and looser, still bounded, limits on reporting and maintenance roles.

🧪 Practice

1. (Beginner) Set statement_timeout = '2s' in a session and run SELECT pg_sleep(5). Then set idle_in_transaction_session_timeout = '5s', run BEGIN;, wait 10 seconds, and run SELECT 1. Record both errors.
2. (Intermediate) Compute the worst-case memory estimate for a server with 64 GB RAM, shared_buffers = 16GB, max_connections = 300, and work_mem = 64MB. Propose new values for max_connections, work_mem, and per-role settings that keep the worst case under the available RAM, and explain the role of a pooler.
3. (Interview) "Disk usage on the database server jumped by 200 GB in ten minutes and then dropped back. What could cause this, and which setting would have limited the damage?" Hint: Think about where sorts and hashes spill when they exceed work_mem, and how log_temp_files and temp_file_limit help.

Logging Configuration

When something goes wrong at 3 a.m., the server log is often the only record of what happened: which query failed, who was connected, how long a checkpoint took, which process waited for a lock. A default PostgreSQL installation logs very little, and in a format that is hard to analyze. Deliberate logging configuration, done before you need it, is one of the cheapest investments in operability.

Analogy. Logging is a flight data recorder. You configure what it records before takeoff; after a crash, you can only analyze what was recorded. Recording too little leaves you guessing, and recording everything fills the recorder with noise and slows the plane.

Where logs go

logging_collector = on                    # a background process captures stderr into files (restart required)
log_destination = 'stderr'                # also: 'csvlog', 'jsonlog' (PG 15+), 'syslog', 'eventlog'; comma list allowed
log_directory = 'log'                     # relative to the data directory, or an absolute path
log_filename = 'postgresql-%Y-%m-%d_%H%M.log'   # strftime escapes
log_rotation_age = 1d                     # start a new file daily
log_rotation_size = 1GB                   # or when the file gets this big
log_truncate_on_rotation = off            # on + a cyclic file name (e.g. %a) = fixed retention without cleanup
log_file_mode = 0640                      # let a log-reading group access files

Debian and Ubuntu packages disable the collector and let the service write to /var/log/postgresql/, rotated by logrotate. Containers usually log to stderr and let the platform collect output. Structured formats are easier to ship to log platforms:

{"timestamp":"2026-10-06 09:14:02.118 UTC","user":"app_user","dbname":"shop","pid":48211,
 "remote_host":"10.0.1.25","session_id":"6702b4a1.bc53","line_num":7,"ps":"SELECT",
 "error_severity":"ERROR","state_code":"57014","message":"canceling statement due to statement timeout",
 "statement":"SELECT * FROM orders WHERE note ILIKE '%refund%'","application_name":"checkout-api",
 "backend_type":"client backend","query_id":-512839208833012}

The log line prefix. For plain stderr logs, log_line_prefix decides what context precedes every message. The default '%m [%p] ' (timestamp and process ID) is not enough to tell users, databases, and applications apart.

log_line_prefix = '%m [%p] %q%u@%d app=%a client=%h xid=%x '

Escape	Meaning
%m / %t	Timestamp with / without milliseconds
%p	Process ID
%u / %d	User name / database name
%a	application_name set by the client
%h	Client host
%c	Session ID (stable for the whole session, useful to group lines)
%l	Line number within the session
%x / %v	Transaction ID / virtual transaction ID
%e	SQLSTATE error code
%Q	Query ID (needs compute_query_id), links log lines to pg_stat_statements
%q	Stop here for non-session processes (checkpointer, autovacuum), which have no user or database

Log analyzers such as pgBadger need to know the prefix you chose (Log Analysis with pgBadger), so pick one and keep it stable.

What to log

Parameter	Recommended production value	What it captures
log_min_messages	warning (default)	Severity threshold for server messages
log_min_error_statement	error (default)	Includes the failing SQL text with each error
log_min_duration_statement	250ms–1s	Every statement slower than the threshold (Slow Query Logging)
log_statement	ddl	All DDL, a cheap audit trail of schema changes; avoid all in production
log_connections / log_disconnections	on	Who connected, from where, and for how long
log_lock_waits	on	Any lock wait longer than deadlock_timeout (1s)
log_temp_files	0 or 10MB	Queries that spill to disk
log_checkpoints	on (default since 15)	Checkpoint frequency and duration (11.2)
log_autovacuum_min_duration	10min (default) or lower	Slow or heavy autovacuum runs
log_replication_commands	on on primaries with replicas	Replication connections and slot commands
log_timezone	'UTC'	Unambiguous timestamps across servers
lc_messages	'C'	English messages, which log analyzers expect

PostgreSQL 18 also accepts a list for log_connections (for example 'authentication,authorization') to choose which connection stages are logged, while on and off keep their old meaning.

A baseline block

# conf.d/10-logging.conf
log_destination = 'stderr,jsonlog'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d.log'
log_rotation_age = 1d
log_rotation_size = 0
log_line_prefix = '%m [%p] %q%u@%d app=%a client=%h '
log_timezone = 'UTC'
lc_messages = 'C'

log_min_duration_statement = 500ms
log_statement = 'ddl'
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 10MB
log_checkpoints = on
log_autovacuum_min_duration = 1min

Sensitive data. Logs can contain secrets. log_statement = 'all', slow query logs, and error statements may include personal data or a password in ALTER ROLE ... PASSWORD '...'. Mitigations: set passwords with \password in psql (sends a hash), restrict log file permissions, avoid log_statement = 'all', and use log_parameter_max_length and log_parameter_max_length_on_error to cap the size of logged bind parameters. For a proper audit trail, use pgaudit (Auditing with pgaudit).

Reading logs from SQL

SELECT pg_current_logfile();                 -- path of the file currently being written
SELECT * FROM pg_ls_logdir() ORDER BY modification DESC LIMIT 5;   -- needs pg_monitor or superuser

Key takeaways

• Enable the logging collector (or rely on the platform's collector) with rotation, and log in UTC.
• Use a rich log_line_prefix (time, PID, user, database, application, client); %q keeps background-process lines clean.
• Log slow statements, DDL, connections, lock waits, temp files, checkpoints, and slow autovacuum runs by default.
• jsonlog and csvlog give structured logs for log platforms; set lc_messages = 'C' for analyzers.
• Treat logs as sensitive data: avoid log_statement = 'all' and protect file permissions.

🧪 Practice

1. (Beginner) Set the baseline logging block above on a development server, reload, and trigger one of each: a slow query (pg_sleep(1)), a DDL statement, a failed login, and a query that spills to disk (large ORDER BY with work_mem = '64kB'). Find each entry in the log.
2. (Intermediate) Enable jsonlog alongside stderr. Write a short jq command that lists the ten most common state_code values in today's JSON log, and explain what three of them mean.
3. (Interview) "Production logs are 50 GB per day and nobody reads them, yet during the last incident they lacked the information you needed. How do you redesign the logging configuration?" Hint: Distinguish volume from value: lower log_statement, raise thresholds, add context to the prefix, use structured output, ship to a searchable store, and define retention.

<a id="112-write-ahead-logging"></a>

11.2 Write-Ahead Logging

The write-ahead log (WAL) is the mechanism behind PostgreSQL's durability, crash recovery, backups, and replication; this subchapter explains what the WAL is, how checkpoints bound it, which settings trade durability for speed, and how the server uses it to recover after a crash.

WAL Purpose and Structure

When a transaction commits, PostgreSQL promises that its changes survive a crash or power loss. The obvious way to keep that promise would be to write every modified data page to disk before reporting success. That would be very slow. A single UPDATE may touch a table page, several index pages, and a visibility map page, all in different places on disk, and each random write costs far more than a sequential one. Worse, if the power fails halfway through writing those pages, the data files are left in a state that is neither old nor new.

The write-ahead log solves both problems with one rule: before a change to a data page reaches disk, a record describing that change must already be safely on disk in the log. The log is append-only and sequential, so writing it is fast. At commit, PostgreSQL only has to flush the log up to the commit record; the modified data pages stay in memory (shared_buffers) and are written later, in the background, in efficient batches.

Analogy. An accountant does not rewrite every ledger page the moment a transaction happens. They first write a line in a bound journal: "moved 100 from account A to account B". The ledgers are brought up to date later. If the office burns down halfway through updating the ledgers, the journal (kept in a fireproof safe) is enough to redo all the work.

What happens on an UPDATE

 Client: UPDATE accounts SET balance = balance - 100 WHERE id = 1; COMMIT;

 1. Backend modifies the page in shared_buffers          (memory only, page marked "dirty")
 2. Backend creates a WAL record describing the change   (in the WAL buffers, wal_buffers)
 3. COMMIT: WAL is written and fsync'ed up to the commit record   <-- the only disk wait
 4. Client receives COMMIT
 5. Later: checkpointer / background writer write the dirty page to the data file
           (never before the WAL record that describes it is on disk)

Log sequence numbers. Every byte position in the WAL has an address called a log sequence number (LSN), a 64-bit number written as two hexadecimal halves (0/3A4F1B8). LSNs only grow. Every data page stores the LSN of the last WAL record that modified it, which lets recovery decide whether a record still needs to be applied. LSNs also measure distances: replication lag, the amount of WAL since a checkpoint, the WAL generated by a batch job.

SELECT pg_current_wal_lsn();                       -- current write position, e.g. 2B/7F1A9C28

-- How much WAL does an operation generate?
SELECT pg_current_wal_lsn() AS before \gset
UPDATE accounts SET balance = balance + 1;
SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), :'before')) AS wal_generated;
--  wal_generated
-- ---------------
--  142 MB

Segments and file names. The WAL is stored as a sequence of files called segments, 16 MB each by default (chosen at initdb --wal-segsize), in the pg_wal directory of the data directory. Each segment is made of 8 kB WAL pages that contain variable-length records. Segment file names have 24 hexadecimal digits:

 000000010000002B0000007F
 \______/\______/\______/
 timeline  high 32   segment number
   (1)    bits of     within that
          the LSN     4 GB range
          (0x2B)      (0x7F)

SELECT pg_walfile_name(pg_current_wal_lsn());     -- 000000010000002B0000007F

-- Size of pg_wal right now (needs superuser or pg_monitor)
SELECT count(*) AS segments, pg_size_pretty(sum(size)) AS total FROM pg_ls_waldir();

The timeline is a counter that increases every time a server is promoted or a point-in-time recovery ends, so that WAL from alternative histories never collides (Continuous Archiving and PITR).

What a WAL record contains. Each record has a header (total length, transaction ID, previous record's LSN, a CRC checksum, and the resource manager responsible for it, such as Heap, Btree, Transaction, or XLOG) and a body with the information needed to redo the change, plus references to the blocks it touches. The pg_waldump tool decodes segments into readable text:

pg_waldump -p /var/lib/postgresql/17/main/pg_wal -s 2B/7F1A9C28 -n 5

rmgr: Heap        len (rec/tot):     72/    72, tx:     918273, lsn: 2B/7F1A9C28, prev 2B/7F1A9BF0, desc: HOT_UPDATE old_xmax: 918273, old_off: 4, old_infobits: [], flags: 0x10, new_xmax: 0, new_off: 9, blkref #0: rel 1663/16384/24576 blk 17
rmgr: Btree       len (rec/tot):     64/    64, tx:     918273, lsn: 2B/7F1A9C70, prev 2B/7F1A9C28, desc: INSERT_LEAF off: 112, blkref #0: rel 1663/16384/24581 blk 3
rmgr: Transaction len (rec/tot):     34/    34, tx:     918273, lsn: 2B/7F1A9CB0, prev 2B/7F1A9C70, desc: COMMIT 2026-10-06 09:20:11.481203 UTC

The pg_walinspect extension (PostgreSQL 15+) offers similar information from SQL, and pg_stat_wal reports totals since the last statistics reset:

SELECT wal_records, wal_fpi, pg_size_pretty(wal_bytes) AS wal_bytes, wal_buffers_full
FROM pg_stat_wal;

One log, many uses. Because the WAL is a complete, ordered description of every change, it is reused well beyond crash recovery:

Use	How it uses the WAL
Crash recovery	Replays records since the last checkpoint (Crash Recovery Process)
Point-in-time recovery	Replays archived WAL on top of a base backup up to a chosen moment (11.3)
Physical replication	Ships WAL to standbys, which replay it continuously (12.1)
Logical replication and change data capture	Decodes WAL into row-level changes (12.2)

Key takeaways

• WAL rule: a change is logged, and the log flushed, before the data page itself may be written to disk.
• Commit waits only for a sequential WAL flush; data pages are written later in the background.
• LSNs are byte positions in the WAL; use pg_current_wal_lsn() and pg_wal_lsn_diff() to measure WAL volume and lag.
• WAL lives in 16 MB segment files in pg_wal; names encode the timeline and position.
• The same log powers crash recovery, PITR, physical replication, and logical decoding.

🧪 Practice

1. (Beginner) Record pg_current_wal_lsn(), insert 100,000 rows into a test table, and compute how much WAL was generated. Repeat with an UNLOGGED table and compare.
2. (Intermediate) Use pg_walfile_name() to find the current segment, then run pg_waldump on it and identify the records produced by a single INSERT followed by COMMIT. Which resource managers appear?
3. (Interview) "Why is it faster to write every change twice (once to the WAL, once to the data file) than to write it once directly to the data file?" Hint: Compare sequential and random I/O, consider how many commits can share one WAL flush, and what happens to data page writes when they are deferred and batched.

Checkpoints and Tuning

If dirty pages were never written to the data files, the WAL would have to be kept forever and crash recovery would have to replay everything since the cluster was created. A checkpoint is the point at which PostgreSQL guarantees that every page modified before a certain WAL position has been written and fsynced to the data files. After a checkpoint, older WAL is no longer needed for crash recovery and can be recycled or removed, and recovery can start from the checkpoint instead of the beginning of time.

Analogy. Saving a long document. Between saves, your edits exist only in memory plus an undo history. Saving (the checkpoint) costs some time, but afterwards the history before the save is no longer needed to rebuild the document. Saving every second wastes time; saving once a day means a crash costs hours of replay.

What a checkpoint does

 1. Record the current WAL insert position as the REDO point
 2. Write every dirty buffer in shared_buffers to the data files
    (spread over time, see checkpoint_completion_target)
 3. fsync the data files
 4. Write a checkpoint record to the WAL and update pg_control with its location
 5. Remove or recycle WAL segments older than the REDO point
    (unless needed by archiving, replication slots, or wal_keep_size)

What triggers a checkpoint

Trigger	Setting	Log reason
Time since the last checkpoint	checkpoint_timeout (default 5min)	checkpoint starting: time
WAL generated since the last checkpoint	max_wal_size (default 1GB, a soft limit)	checkpoint starting: wal
Manual	CHECKPOINT;	checkpoint starting: immediate force wait
Shutdown, start of a base backup, CREATE DATABASE	n/a	shutdown, force wait

Ideally almost all checkpoints are triggered by time. Checkpoints triggered by WAL volume mean max_wal_size is too small for the write load, so checkpoints happen more often than planned.

Spreading the I/O. Writing gigabytes of dirty pages at full speed would starve queries of disk bandwidth. The checkpointer paces itself to finish within checkpoint_completion_target (default 0.9) of the interval, so a 15-minute checkpoint spreads its writes over about 13.5 minutes.

The trade-off

Shorter interval (frequent checkpoints)	Longer interval (rare checkpoints)
Faster crash recovery (less WAL to replay)	Slower crash recovery
Less WAL on disk	More WAL in pg_wal (up to about max_wal_size)
More total writes: a hot page is written once per checkpoint	Hot pages are written less often
More full-page images in WAL (Full Page Writes)	Fewer full-page images, much less WAL

For most write-heavy servers, the default 5 minutes is too short. Typical production values are 15 to 30 minutes, with max_wal_size large enough that WAL volume rarely forces a checkpoint.

checkpoint_timeout = 15min
max_wal_size = 16GB                 # soft limit; size it to cover ~1 timeout at peak write rate
min_wal_size = 2GB                  # keep this much WAL recycled instead of deleted
checkpoint_completion_target = 0.9  # default since PG 14
checkpoint_warning = 30s            # log a hint when wal-triggered checkpoints come closer than this
log_checkpoints = on                # default since PG 15

Sizing max_wal_size. Measure the WAL rate at peak and multiply by the timeout, with headroom:

-- Sample the WAL position twice, 5 minutes apart, during peak load
SELECT pg_current_wal_lsn();                               -- 2B/10000000
-- ... 5 minutes later ...
SELECT pg_size_pretty(pg_wal_lsn_diff('2B/A0000000', '2B/10000000'));   -- 2304 MB in 5 min
-- About 460 MB/min -> 15 min timeout needs ~7 GB; with headroom and full-page-image spikes, 12-16 GB

Monitoring checkpoints. The log line tells you how long each checkpoint took and how much it wrote:

LOG:  checkpoint starting: time
LOG:  checkpoint complete: wrote 48211 buffers (4.6%); 0 WAL file(s) added, 0 removed, 112 recycled;
      write=809.412 s, sync=1.203 s, total=810.957 s; sync files=341, longest=0.311 s, average=0.004 s;
      distance=1843522 kB, estimate=1901244 kB; lsn=2B/A3F00218, redo lsn=2B/7F1A9C28

PostgreSQL 17 moved checkpoint counters into their own view (earlier versions use checkpoints_timed and checkpoints_req in pg_stat_bgwriter):

SELECT num_timed, num_requested,
       round(100.0 * num_requested / nullif(num_timed + num_requested, 0), 1) AS pct_requested,
       buffers_written, write_time, sync_time
FROM pg_stat_checkpointer;
--  num_timed | num_requested | pct_requested | buffers_written | write_time | sync_time
-- -----------+---------------+---------------+-----------------+------------+-----------
--       2310 |           618 |          21.1 |        91827364 |  1.81e+09  |   912345
-- More than ~10% requested checkpoints: raise max_wal_size.

Warning signs

• checkpoint starting: wal appears often, or checkpoints are occurring too frequently warnings: raise max_wal_size.
• Large sync= times: the storage cannot absorb the writes; look at disk latency and checkpoint_completion_target.
• I/O latency spikes at regular intervals matching checkpoint_timeout: checkpoints are not spread enough, or the background writer is doing too little.
• Crash recovery takes too long: the checkpoint interval is too long for your recovery time objective (Recovery Objectives RPO and RTO).

Key takeaways

• A checkpoint writes all dirty pages and marks a REDO point; WAL older than that point is no longer needed for crash recovery.
• Checkpoints are triggered by checkpoint_timeout, by max_wal_size, or manually; most should be timed.
• Longer intervals reduce write amplification and full-page images but increase recovery time and WAL on disk.
• Size max_wal_size from the measured peak WAL rate times the checkpoint interval, with headroom.
• Monitor with log_checkpoints and pg_stat_checkpointer (PG 17+) or pg_stat_bgwriter (earlier).

🧪 Practice

1. (Beginner) Enable log_checkpoints, run CHECKPOINT;, and read the resulting log line. Explain each part: buffers written, WAL files added/removed/recycled, write, sync, and distance.
2. (Intermediate) With max_wal_size = 64MB on a test server, run pgbench -i -s 50 followed by a 5-minute pgbench run. Count timed versus requested checkpoints. Repeat with max_wal_size = 4GB and compare checkpoint counts and total WAL generated (pg_stat_wal.wal_bytes).
3. (Interview) "Users report that every five minutes, latency spikes for a few seconds. How would you check whether checkpoints are responsible, and what would you change?" Hint: Correlate the spikes with log_checkpoints output, look at sync times and requested checkpoints, and consider checkpoint_timeout, max_wal_size, checkpoint_completion_target, and the storage itself.

WAL Levels

The WAL always contains enough information for crash recovery on the same server. Other consumers need more. A standby needs the WAL from every operation, including bulk loads that crash recovery could skip. Logical replication needs to turn physical page changes back into rows ("customer 42's email changed"), which requires extra information such as the old key of an updated row. The wal_level setting decides how much information is written, trading WAL volume for capabilities.

Analogy. A security camera system. "Minimal" records only what is needed to replace a broken window. "Replica" records everything so a second building can be kept identical. "Logical" adds a narrator who also describes which person did what, so that someone without the floor plan can follow along.

The three levels

wal_level	What it adds	Enables	Cost
minimal	Only what crash recovery needs; skips WAL for some bulk operations	Crash recovery only	Least WAL; no archiving, no replicas, no PITR
replica (default)	All changes, plus information needed for read-only queries on standbys	WAL archiving, PITR, streaming replication, hot standby, pg_basebackup	Baseline
logical	Extra information for decoding rows (old key values, catalog changes)	Logical replication, logical decoding, CDC (Debezium), upgrades via logical replication	Somewhat more WAL, mostly for UPDATE/DELETE on tables with REPLICA IDENTITY FULL

Changing wal_level requires a restart. Each level includes everything in the levels below it.

SHOW wal_level;
--  wal_level
-- -----------
--  replica

-- Prepare a primary for logical replication or CDC
ALTER SYSTEM SET wal_level = 'logical';
-- restart required: pg_ctl restart / systemctl restart postgresql@17-main

What minimal skips. With wal_level = minimal, a table that is created or truncated in the same transaction that loads it does not need WAL for the loaded rows: if the transaction aborts, the new file is simply discarded; if it commits, the file is fsynced at commit. This makes bulk loads much faster:

-- Only fast under wal_level = minimal: no WAL for the COPY data
BEGIN;
CREATE TABLE staging_events (LIKE events INCLUDING ALL);
COPY staging_events FROM '/data/events.csv' WITH (FORMAT csv, HEADER);
COMMIT;

The catch is that nothing downstream can follow along: there can be no archive, no standby, and therefore no point-in-time recovery. minimal also requires max_wal_senders = 0 and archive_mode = off. It is suitable for a one-time initial load into a fresh server (followed by a switch to replica and a new base backup), or for disposable test databases, almost never for production.

replica versus logical in practice. Since PostgreSQL 10 replica is the default, which is correct for most servers. Switch to logical when you need any of: native logical replication (publications and subscriptions), change data capture tools such as Debezium, major-version upgrades by logical replication (Logical Replication Upgrades), or logical decoding plugins. The extra WAL volume is usually small, but measure it, because tables with REPLICA IDENTITY FULL log the entire old row on every update and delete.

-- How much more WAL? Compare wal_bytes over the same workload before and after the switch
SELECT pg_size_pretty(wal_bytes) FROM pg_stat_wal;

-- Tables that make logical WAL expensive
SELECT relname, relreplident
FROM pg_class
WHERE relkind = 'r' AND relreplident = 'f';   -- f = FULL: the whole old row is logged

Related setting: wal_log_hints. Setting hint bits on a page normally does not generate WAL. Tools such as pg_rewind, which resynchronizes a former primary after failover (12.3), need those changes logged. wal_log_hints = on, or data checksums (enabled by default in initdb since PostgreSQL 18), turn this on; it adds full-page images after checkpoints for pages that are only read and hinted.

Key takeaways

• wal_level controls how much information the WAL carries: minimal < replica < logical. Changing it needs a restart.
• replica (the default) is required for archiving, PITR, pg_basebackup, and streaming replication.
• logical adds what is needed to decode row changes; required for logical replication, CDC, and logical upgrades.
• minimal speeds up bulk loads into tables created or truncated in the same transaction, but disables archiving and replicas.
• Plan wal_level = logical before you need it, since switching requires a restart.

🧪 Practice

1. (Beginner) Check your server's wal_level, max_wal_senders, and archive_mode. Explain which backup and replication features are available with the current combination.
2. (Intermediate) On a test server, load 1 million rows with CREATE TABLE ... ; COPY ... in one transaction, once with wal_level = minimal and once with replica. Compare the WAL generated and the elapsed time.
3. (Interview) "The data team wants to stream changes from production into Kafka with Debezium. What has to change on the database, and what are the operational risks?" Hint: Consider wal_level, the restart, replication slots and what happens to pg_wal when the consumer stops, replica identity, and monitoring.

Synchronous Commit Trade-offs

By default, COMMIT does not return until the transaction's WAL records are flushed to durable storage. That flush is the dominant cost of small write transactions: a few hundred microseconds on good SSDs, several milliseconds on network storage. For some data, losing the last fraction of a second of commits after a crash is acceptable in exchange for much higher throughput. synchronous_commit lets you make that choice, per server, per role, or even per transaction.

Analogy. Sending a letter by registered mail versus dropping it in a mailbox. Registered mail (synchronous commit) waits for a receipt before you walk away. The mailbox (asynchronous commit) lets you leave immediately; the letter almost always arrives, but if the mail truck crashes in the next minute, your letter is lost. Either way, letters never arrive damaged.

The settings

synchronous_commit	Commit waits for	Data lost if the primary crashes	Data lost if the primary's disk is destroyed
off	Nothing; WAL is flushed shortly after	Up to ~3 x wal_writer_delay (default 200 ms) of commits	Since last replication
local	Local WAL flush	None	Since last replication
on (default)	Local flush, plus flush on synchronous standbys if configured	None	None, if a synchronous standby is configured
remote_write	Local flush, plus standby received and wrote (not flushed)	None	Only if the standby's OS also crashes
remote_apply	Local flush, plus standby replayed it (visible to queries there)	None	None, and reads on the standby see the commit

Without synchronous standbys (synchronous_standby_names empty), on, remote_write, and remote_apply all behave like local. Replica-related options are covered in Synchronous vs. Asynchronous Replication.

Asynchronous commit is safe, just not durable. With synchronous_commit = off, the database can never become inconsistent or corrupt. After a crash, recovery replays the WAL that did reach disk; transactions whose commit records were still in memory simply never happened, as if they had been rolled back. The client, however, was told they committed. This differs from fsync = off, which can corrupt the whole cluster:

Setting	Effect of a crash	Use
synchronous_commit = off	The last few hundred milliseconds of commits are lost; database is consistent	Acceptable for low-value, high-volume data
fsync = off	Database may be corrupt and unrecoverable	Only for throwaway data you can rebuild entirely (for example, a test load)
Unlogged tables	Table is truncated after a crash; not replicated	Caches, staging, session data

Choosing per transaction. The real power is that synchronous_commit is a user-context parameter. Keep durable commits as the default and relax them only where it makes sense:

-- Payments: keep the default
BEGIN;
INSERT INTO payments (order_id, amount) VALUES (9182, 49.90);
COMMIT;                                           -- waits for the WAL flush

-- Click tracking: losing a fraction of a second of events is fine
BEGIN;
SET LOCAL synchronous_commit = off;
INSERT INTO page_views (session_id, path, viewed_at) VALUES ('a91f', '/products/42', now());
COMMIT;                                           -- returns immediately

-- Or for a whole role
ALTER ROLE telemetry_writer SET synchronous_commit = off;

Measuring the difference

# Single-row insert benchmark on the same server
cat > insert.sql <<'EOF'
INSERT INTO bench_events (payload) VALUES ('x');
EOF

PGOPTIONS='-c synchronous_commit=on'  pgbench -n -c 8 -T 30 -f insert.sql shop   # e.g. tps = 6,800
PGOPTIONS='-c synchronous_commit=off' pgbench -n -c 8 -T 30 -f insert.sql shop   # e.g. tps = 31,500

The gain is largest for many small transactions on storage with slow fsync. It is negligible for large transactions, where the flush is a small part of the total time.

Group commit. When many sessions commit at the same moment, one fsync can cover all of their commit records. PostgreSQL does this naturally; commit_delay (microseconds) and commit_siblings can make a committing session wait briefly so more commits join the same flush. They help only on very busy servers with slow fsync, and should be tuned with measurements (pg_test_fsync reports how fast your storage flushes).

Key takeaways

• synchronous_commit decides what COMMIT waits for: nothing (off), the local flush (local/on), or a standby's write, flush, or replay.
• off can lose the last ~600 ms of commits after a crash but never corrupts the database; fsync = off can corrupt it.
• Keep on as the default and use SET LOCAL or per-role settings for low-value, high-volume writes.
• Without synchronous standbys, on, remote_write, and remote_apply behave like local.
• Measure with pgbench and pg_test_fsync; the benefit is largest for small transactions on slow-flushing storage.

🧪 Practice

1. (Beginner) Run pg_test_fsync on your machine and note the fsync rate. Then run the pgbench insert benchmark with synchronous_commit on and off and compare the throughput to the fsync rate.
2. (Intermediate) For an e-commerce application with tables orders, payments, cart_items, product_views, and search_logs, decide which tables (or code paths) could use asynchronous commit and justify each choice in terms of business impact.
3. (Interview) "What is the difference between synchronous_commit = off and fsync = off? Would you ever use either in production?" Hint: Think about what can be lost versus what can be corrupted, and whether the database is still consistent after a crash in each case.

WAL Archiving

Crash recovery only needs WAL since the last checkpoint, so PostgreSQL recycles older segments. For backups, that is not enough. To restore a backup taken last night and roll it forward to 14:29 today, you need every WAL segment generated between the backup and 14:29. WAL archiving copies each completed segment to safe, separate storage before PostgreSQL is allowed to recycle it. Together with a base backup, the archive allows point-in-time recovery (Continuous Archiving and PITR), and it can also feed standbys that fall behind streaming replication.

Analogy. A shop that keeps its daily receipts. The till (pg_wal) only holds today's receipts and is emptied regularly. Archiving moves every full roll of receipts to a vault off-site. With last month's inventory count (the base backup) and every receipt since, you can reconstruct the shop's stock at any moment.

How archiving works

                     pg_wal/
  WAL segment fills  000000010000002B0000007F   --> pg_wal/archive_status/000000010000002B0000007F.ready
                                                         |
                       archiver process runs archive_command (or archive_library)
                                                         |
                          exit code 0? ----- no -----> retry later; segment stays in pg_wal
                                |
                               yes
                                |
                     .ready renamed to .done  --> segment may now be recycled at the next checkpoint

Configuration

wal_level = replica                 # or logical; minimal does not allow archiving
archive_mode = on                   # restart required; 'always' also archives on standbys
archive_command = 'pgbackrest --stanza=main archive-push %p'   # %p = path of the segment, %f = file name only
archive_timeout = 60s               # force a segment switch at least this often on a quiet server

archive_timeout bounds how old the newest archived data can be. Without it, a server with little write activity could take hours to fill a 16 MB segment, and that last partial segment would not be in the archive. Each forced switch still produces a full-size file (mostly empty, compressible), so do not set it to a few seconds.

The archive_command contract. The command is any shell command. PostgreSQL trusts its exit code completely, so it must follow strict rules:

• Return 0 only when the file is durably stored (fsynced, uploaded and acknowledged), never before.
• Return non-zero on any failure; PostgreSQL will retry the same segment indefinitely and keep it in pg_wal until it succeeds.
• Never overwrite an existing archived file with different content; refuse or verify instead.
• Be safe to run again for the same file after a crash (idempotent for identical content).

The documentation's classic example illustrates the logic but is not production-grade (cp does not fsync):

archive_command = 'test ! -f /mnt/archive/%f && cp %p /mnt/archive/%f'
#                  \___ fail if already archived ___/ \___ copy ___/

A slightly safer script that fsyncs and refuses to overwrite:

#!/usr/bin/env bash
# /usr/local/bin/archive_wal.sh  -- archive_command = '/usr/local/bin/archive_wal.sh %p %f'
set -euo pipefail
src="$1"; name="$2"; dest="/mnt/archive/$name"

if [[ -f "$dest" ]]; then
  cmp -s "$src" "$dest" && exit 0      # same content already archived: success (idempotent)
  echo "archive file $dest exists with different content" >&2
  exit 1                               # never overwrite different content
fi

cp "$src" "$dest.tmp"
sync "$dest.tmp"                       # flush file data to disk (coreutils 8.24+)
mv "$dest.tmp" "$dest"                 # atomic rename: readers never see a partial file
sync "$(dirname "$dest")"              # flush the directory entry

In production, prefer a dedicated tool (pgBackRest, Barman, WAL-G) that compresses, encrypts, uploads to object storage, and checks for errors (Backup Tools: pgBackRest and Barman). PostgreSQL 15 added archive_library, which loads archiving code as a module instead of spawning a shell per segment, which is faster at high WAL rates.

Streaming archive. An alternative to archive_command is pg_receivewal, which connects like a standby and writes WAL as it is generated, including the current partial segment. With a replication slot and --synchronous, it can reduce the archive's data loss window to near zero. Barman uses this approach.

Monitoring. A failing archive command is one of the most dangerous silent failures in PostgreSQL. Segments pile up in pg_wal until the disk fills and the server stops.

SELECT archived_count, last_archived_wal, last_archived_time,
       failed_count, last_failed_wal, last_failed_time,
       now() - last_archived_time AS since_last_success
FROM pg_stat_archiver;
--  archived_count |    last_archived_wal     |   last_archived_time   | failed_count |     last_failed_wal      |   last_failed_time     | since_last_success
-- ----------------+--------------------------+------------------------+--------------+--------------------------+------------------------+--------------------
--          189212 | 000000010000002B0000007E | 2026-10-06 09:31:12+00 |           14 | 000000010000002B0000007F | 2026-10-06 09:58:40+00 | 00:27:41
-- last_failed_time newer than last_archived_time = archiving is currently broken

-- How many segments are waiting? (needs superuser or pg_monitor)
SELECT count(*) FROM pg_ls_archive_statusdir() WHERE name LIKE '%.ready';

Alert when the time since the last successful archive exceeds a few multiples of archive_timeout, or when .ready files keep growing. The failure reason is in the server log:

LOG:  archive command failed with exit code 1
DETAIL:  The failed archive command was: pgbackrest --stanza=main archive-push pg_wal/000000010000002B0000007F
WARNING:  archiving write-ahead log file "000000010000002B0000007F" failed too many times, will try again later

Key takeaways

• Archiving copies every completed WAL segment to separate storage; with a base backup, it enables point-in-time recovery.
• Configure archive_mode = on (restart), an archive_command or archive_library, and an archive_timeout to bound staleness.
• The command must return 0 only after durable storage, never overwrite different content, and be safe to repeat.
• Failed archiving keeps segments in pg_wal until the disk fills; monitor pg_stat_archiver and alert on it.
• Use a dedicated backup tool or pg_receivewal rather than ad-hoc cp commands in production.

🧪 Practice

1. (Beginner) Enable archiving to a local directory with the documentation's test ! -f ... && cp ... command. Run SELECT pg_switch_wal(); a few times and watch files appear in the archive and in pg_stat_archiver.
2. (Intermediate) Break the archive (for example, make the directory read-only). Generate WAL with pgbench and observe pg_stat_archiver, the .ready files, and the size of pg_wal. Fix it and confirm that the backlog drains. Write the alert condition you would use.
3. (Interview) "Why must archive_command never return success before the file is durable, and what goes wrong if it overwrites an existing archive file?" Hint: Consider what PostgreSQL does with a segment after success, and what happens when two servers (for example, an old primary and a newly promoted one) archive to the same location.

Crash Recovery Process

A crash can happen at any moment: power loss, a kernel panic, the OOM killer terminating a backend, or someone running kill -9 on the postmaster. At that instant, some committed changes exist only in the WAL (their data pages were still dirty in memory), and some data pages on disk may contain changes from transactions that never committed. Crash recovery brings the data files back to a consistent state that includes every committed transaction, automatically, the next time the server starts.

Analogy. A chef keeps a recipe log of every step taken, in order. After a power cut, they look at the last time they confirmed the kitchen was in a known state (the checkpoint) and redo every logged step from there. Dishes that were half-prepared for orders nobody confirmed are simply never served.

The steps

 Server start after a crash
        |
        v
 1. Read global/pg_control: "state: in production" -> the server did not shut down cleanly
        |
        v
 2. Find the latest checkpoint record and its REDO point
        |
        v
 3. Replay WAL records from the REDO point forward, in order
        - for each record, read the affected page
        - if page LSN >= record LSN: change already on disk, skip
        - otherwise apply the change and set page LSN = record LSN
        |
        v
 4. Stop at the end of valid WAL (a record with a bad CRC or zero length)
        |
        v
 5. Write an end-of-recovery checkpoint
        |
        v
 6. Accept connections

The page LSN comparison makes replay idempotent: applying a record to a page that already contains it is skipped, so it does not matter which dirty pages reached disk before the crash.

Why there is no undo phase. Classic recovery algorithms (such as ARIES, used by many databases) have a redo phase followed by an undo phase that reverses changes of uncommitted transactions. PostgreSQL needs only redo. Thanks to MVCC, an uncommitted transaction's row versions are stamped with its transaction ID, and visibility checks treat that ID as aborted because the commit log (pg_xact) has no commit record for it. Its rows are invisible and are later removed by vacuum (Tuple Visibility Rules). This is why crash recovery time in PostgreSQL depends on the amount of WAL since the checkpoint, not on the number of in-flight transactions.

What it looks like

LOG:  database system was interrupted; last known up at 2026-10-06 09:47:12 UTC
LOG:  database system was not properly shut down; automatic recovery in progress
LOG:  redo starts at 2B/7F1A9C28
LOG:  redo in progress, elapsed time: 10.01 s, current LSN: 2B/B3D21F00
LOG:  invalid record length at 2B/C0A11E58: expected at least 24, got 0
LOG:  redo done at 2B/C0A11E20 system usage: CPU: user: 11.42 s, system: 2.87 s, elapsed: 14.93 s
LOG:  checkpoint starting: end-of-recovery immediate wait
LOG:  checkpoint complete: wrote 61822 buffers (5.9%); ...
LOG:  database system is ready to accept connections

"invalid record length ... got 0" is normal: it is how recovery finds the end of the WAL that was written before the crash. Progress messages are logged every log_startup_progress_interval (default 10 s).

Inspecting control data

pg_controldata /var/lib/postgresql/17/main

pg_control version number:            1700
Database cluster state:               in production
Latest checkpoint location:           2B/A3F00218
Latest checkpoint's REDO location:    2B/7F1A9C28
Latest checkpoint's TimeLineID:       1
Time of latest checkpoint:            Mon 06 Oct 2026 09:45:01 AM UTC
wal_level setting:                    replica
Data page checksum version:           1

What determines recovery time. Recovery replays all WAL from the last checkpoint's REDO point, so its duration is roughly (WAL generated since the last checkpoint) / (replay speed). Replay is mostly single-threaded and often limited by random reads of the pages being modified. A server with checkpoint_timeout = 30min and a heavy write load can need many minutes to recover. Bound it by the checkpoint interval and max_wal_size (Checkpoints and Tuning), and test it, since it is part of your recovery time objective.

Crashes that are not full crashes. If a single backend process dies abnormally (segfault, OOM kill), the postmaster cannot know whether it corrupted shared memory. It terminates all other backends, reinitializes shared memory, and runs crash recovery, without restarting the postmaster itself:

LOG:  server process (PID 48211) was terminated by signal 9: Killed
DETAIL:  Failed process was running: SELECT ... FROM huge_report ...
LOG:  terminating any other active server processes
LOG:  all server processes terminated; reinitializing
LOG:  database system was not properly shut down; automatic recovery in progress

For a few seconds, every client loses its connection. This is why the Linux OOM killer is so disruptive (Resource Limit Parameters).

Where crash recovery cannot help. Recovery repairs the effects of a crash on correctly working hardware. It does not repair lost writes from a disk that lies about fsync, corrupted pages on bad storage, a deleted pg_wal directory, or a DROP TABLE that was committed on purpose. Those need backups (11.3).

Never do this: delete files from pg_wal to free space, run pg_resetwal to "fix" a server that will not start, or remove postmaster.pid while a postmaster may still be running. Each of these can turn a recoverable situation into data loss.

Key takeaways

• After an unclean shutdown, the server replays WAL from the last checkpoint's REDO point to the end of valid WAL.
• Page LSNs make replay idempotent; records already reflected in a page are skipped.
• PostgreSQL needs no undo phase: MVCC makes uncommitted rows invisible, and vacuum removes them later.
• Recovery time grows with the amount of WAL since the last checkpoint; checkpoint settings bound it.
• A crashed backend triggers a cluster-wide restart of sessions; recovery does not protect against bad storage or human error.

🧪 Practice

1. (Beginner) On a test server, start a pgbench load, then stop the server with pg_ctl stop -m immediate. Start it again and read the recovery messages in the log. Run pg_controldata before and after.
2. (Intermediate) Repeat the experiment with checkpoint_timeout = 30s and then 30min (with max_wal_size large). Compare the "redo done" elapsed times and explain the relationship to WAL volume.
3. (Interview) "Why does PostgreSQL crash recovery not need to roll back uncommitted transactions, while many other databases do?" Hint: Think about where row versions live in PostgreSQL's MVCC, how visibility is decided, and what pg_xact records.

Full Page Writes

PostgreSQL writes data in 8 kB pages, but most storage only guarantees atomic writes of 4 kB or even 512-byte sectors. If power fails while an 8 kB page is being written, the page on disk can end up half old and half new: a torn page. Crash recovery cannot fix a torn page by replaying a small WAL record such as "insert this tuple at offset 7", because that record assumes the rest of the page is intact. Full page writes solve this by logging a complete copy of a page the first time it is modified after each checkpoint.

Analogy. Before renovating a room, you photograph it the first time you touch it after each inspection. If the renovation is interrupted at a bad moment and the room ends up half-demolished, you rebuild it exactly from the photo and then redo the logged changes. Later changes to the same room before the next inspection need only short notes, because the photo already exists.

The torn page problem

 8 kB PostgreSQL page = two 4 kB disk sectors

 Before:   [ sector 1: old A | sector 2: old B ]
 Writing:  [ sector 1: new A | sector 2: old B ]    <-- power fails here
 On disk after reboot: a page mixing old and new halves; its contents are inconsistent

 WAL record "insert tuple at offset 7" cannot be safely applied to this mixed page.

How full page writes work

 Checkpoint (REDO point)
     |
     |  first change to page P after the checkpoint:
     |      WAL record carries the FULL 8 kB image of P (a full-page image, FPI)
     |
     |  further changes to P before the next checkpoint:
     |      normal, small WAL records
     |
 Crash, page P torn on disk
     |
 Recovery: restore P from the FPI (ignoring the torn copy), then replay the small records

Because recovery always starts at a checkpoint's REDO point, and every page modified after that point has a full image in the WAL, any torn page can be rebuilt.

The cost. Full-page images are the biggest contributor to WAL volume on many servers, and they spike right after each checkpoint, when every page touched for the first time produces a full 8 kB image instead of a record of a few dozen bytes.

SELECT wal_records, wal_fpi,
       round(100.0 * wal_fpi / nullif(wal_records, 0), 1) AS fpi_pct_of_records,
       pg_size_pretty(wal_bytes) AS wal_bytes
FROM pg_stat_wal;
--  wal_records | wal_fpi | fpi_pct_of_records | wal_bytes
-- -------------+---------+--------------------+-----------
--    918273645 | 9182736 |                1.0 | 412 GB      -- 1% of records, but often a large share of the bytes

pg_waldump --stats=record breaks the volume down by record type and shows how much of it is full-page images.

Reducing the cost

Technique	Effect
Longer checkpoint_timeout and larger max_wal_size	Fewer checkpoints, so fewer "first change after a checkpoint" events
wal_compression = lz4 or zstd (PG 15+; pglz earlier)	Compresses full-page images in the WAL; costs some CPU, often cuts WAL volume substantially
Lower fillfactor and HOT updates	Concentrate updates on fewer pages (HOT Updates and Fillfactor)
UUIDv7 or sequential keys instead of random UUIDs	Random keys touch many different index pages, each requiring an FPI after a checkpoint

wal_compression = zstd     # changeable with a reload (superuser)

Can it be turned off? full_page_writes = off is safe only if the storage stack guarantees that 8 kB writes are atomic, for example ZFS with an appropriate record size, or some storage arrays that document atomic writes of at least the page size. On ordinary ext4 or XFS on SSDs or cloud volumes, turning it off risks silent corruption after a power failure. Base backups temporarily force full-page writes on regardless of the setting, because a backup copies pages while they change.

Full page writes versus checksums. Data checksums (initdb --data-checksums, default since PostgreSQL 18; pg_checksums --enable on a stopped cluster for older ones) detect corruption when a page is read, from any cause. They do not repair anything. Full page writes prevent one specific kind of corruption (torn pages after a crash). Use both.

SHOW data_checksums;      -- on
SHOW full_page_writes;    -- on (leave it on)
SHOW wal_compression;     -- zstd

Key takeaways

• Torn pages happen because PostgreSQL's 8 kB page is larger than the atomic write unit of most storage.
• After each checkpoint, the first change to a page writes its full image to the WAL; recovery restores that image before replaying later records.
• Full-page images are a major share of WAL volume and spike after checkpoints; longer checkpoint intervals and wal_compression reduce them.
• Keep full_page_writes = on unless the storage guarantees atomic 8 kB writes.
• Checksums detect corruption; full page writes prevent torn pages. They complement each other.

🧪 Practice

1. (Beginner) Run CHECKPOINT;, then update one row and look at the generated WAL with pg_waldump. Find the record marked as a full-page image (FPW). Update the same row again and compare the record sizes.
2. (Intermediate) Run the same pgbench workload with wal_compression = off and wal_compression = zstd, and compare wal_bytes and wal_fpi in pg_stat_wal (reset with SELECT pg_stat_reset_shared('wal')). Then repeat with a longer checkpoint interval.
3. (Interview) "WAL volume doubled after the team switched primary keys from bigint sequences to random UUIDv4. Why?" Hint: Think about which index pages each insert touches, how many of them are touched for the first time after a checkpoint, and what that means for full-page images.

<a id="113-backup-and-recovery"></a>

11.3 Backup and Recovery

A backup is only as good as the restore it makes possible; this subchapter covers the two families of PostgreSQL backups (logical and physical), point-in-time recovery, the tools that automate them, and the planning that turns backups into a dependable recovery capability.

Logical Backups with pg_dump and pg_dumpall

Sometimes you need a copy of a database that is independent of the server it came from: to move one database to another server, to load it into a newer PostgreSQL version, to restore a single table someone dropped, or to hand a developer a copy of the schema. A logical backup captures the database as SQL definitions and data rows rather than as raw files. pg_dump makes a logical backup of one database; pg_dumpall makes one of the whole cluster.

Analogy. A logical backup is like the recipe and ingredient list for a dish, while a physical backup is a frozen copy of the dish itself. The recipe works in any kitchen and you can cook just one part of it, but recreating the dish takes time. The frozen copy is ready almost instantly, but only fits the same kind of oven.

How pg_dump works. pg_dump is an ordinary client. It connects, starts a REPEATABLE READ transaction to get a consistent snapshot, reads the catalogs to generate CREATE statements, and copies each table's rows with COPY. Other sessions keep working; the dump sees the database as it was at the moment the snapshot was taken.

 pg_dump                                   PostgreSQL
   | BEGIN ISOLATION LEVEL REPEATABLE READ   |
   | LOCK TABLE ... IN ACCESS SHARE MODE  -->|  blocks DDL (ALTER/DROP) on dumped tables, not reads or writes
   | read catalogs -> CREATE TABLE ...       |
   | COPY orders TO STDOUT                -->|  one consistent snapshot for every table
   | COPY customers TO STDOUT             -->|
   | COMMIT                                  |

Output formats

Format	Flag	Output	Restore with	Parallel dump	Selective restore
Plain SQL	-Fp (default)	One .sql script	psql	No	No (edit the file)
Custom	-Fc	One compressed archive file	pg_restore	No	Yes
Directory	-Fd	A directory, one file per table	pg_restore	Yes (-j)	Yes
Tar	-Ft	A tar archive	pg_restore	No	Yes

The custom and directory formats are the usual choice: compressed, restorable with pg_restore in parallel, and selective.

Common commands

# Whole database, custom format, compressed with zstd (PG 16+ supports lz4/zstd)
pg_dump -h db1 -U backup -d shop -Fc -Z zstd:5 -f shop_2026-10-06.dump

# Directory format, dumped with 8 parallel jobs (each job uses its own connection)
pg_dump -h db1 -U backup -d shop -Fd -j 8 -f /backups/shop_2026-10-06/

# Schema only (no data): for reviews, diffs, and new environments
pg_dump -d shop --schema-only -f shop_schema.sql

# Only some objects
pg_dump -d shop -n sales -t 'sales.orders*' -Fc -f orders.dump          # schema and table patterns
pg_dump -d shop --exclude-table-data='audit.*' -Fc -f shop_noaudit.dump # keep definitions, skip big data

# Plain SQL straight into another server (no intermediate file)
pg_dump -h old -d shop | psql -h new -d shop -v ON_ERROR_STOP=1

What pg_dump does not include. Roles, tablespaces, and other cluster-wide ("global") objects do not belong to a single database, so pg_dump does not dump them. It dumps GRANT statements that refer to roles, which fail on restore if the roles do not exist. pg_dumpall fills the gap:

pg_dumpall -h db1 -U postgres --globals-only -f globals.sql   # roles (with password hashes), tablespaces, role settings
pg_dumpall -h db1 -U postgres --roles-only    -f roles.sql
pg_dumpall -h db1 -U postgres -f cluster.sql                  # everything, as one plain SQL script

pg_dumpall for the entire cluster produces a single plain-SQL file and cannot dump in parallel. A common pattern is pg_dumpall --globals-only plus one pg_dump -Fd -j N per database.

Consistency and impact

• Each pg_dump is consistent within one database. Dumps of different databases are taken at different moments.
• The long-running snapshot holds back vacuum for the whole cluster while the dump runs, which can cause bloat on busy servers during multi-hour dumps.
• ACCESS SHARE locks block DDL on dumped tables; a migration that runs ALTER TABLE during the dump will wait (or wait in a queue that blocks other queries, see Lock-Free Migration Patterns).
• Dumping a busy production primary adds I/O and CPU load. Dumping from a standby avoids that; long dumps on a standby may need hot_standby_feedback or max_standby_streaming_delay adjustments to avoid being cancelled (Standby Conflicts and Feedback).

Logical versus physical backups

Aspect	Logical (pg_dump)	Physical (pg_basebackup, pgBackRest)
Granularity	Database, schema, or table	Whole cluster
Portability	Across major versions, OS, CPU architecture	Same major version and platform
Point-in-time recovery	No: only the moment of the dump	Yes, with WAL archive
Backup speed	Slow for large databases	Fast (file copy)
Restore speed	Slow: reloads data and rebuilds every index	Fast: copy files, replay WAL
Size	Small (no indexes, no bloat, compressed)	Size of the data directory
Typical use	Migrations, upgrades, partial restores, dev copies	Production disaster recovery

For production databases beyond a few tens of gigabytes, logical dumps complement physical backups; they do not replace them.

Key takeaways

• pg_dump produces a consistent logical snapshot of one database without blocking reads or writes; it blocks DDL on dumped tables.
• Prefer the custom (-Fc) or directory (-Fd, parallel with -j) formats; plain SQL is restored with psql.
• Roles and tablespaces are global objects: dump them with pg_dumpall --globals-only.
• Logical backups are portable and selective but slow to restore and provide no point-in-time recovery.
• Long dumps hold a snapshot that delays vacuum; consider dumping from a standby.

🧪 Practice

1. (Beginner) Dump a sample database in plain, custom, and directory formats. Compare the sizes and look inside the plain file to find the CREATE TABLE, COPY, and CREATE INDEX sections and where the indexes appear relative to the data.
2. (Intermediate) Time a directory-format dump of a database of at least 5 GB with -j 1, -j 4, and -j 8. Then, in another session, run ALTER TABLE on a large table during the dump and observe what happens in pg_stat_activity and pg_locks.
3. (Interview) "A developer restored a pg_dump file on a new server and got dozens of role ... does not exist errors. What went wrong, and how do you design a backup procedure that avoids it?" Hint: Think about which objects live at the cluster level, what pg_dump includes, and the --no-owner/--no-privileges options as an alternative.

Restoring with pg_restore

A logical backup is only half the job; the restore is where time pressure and surprises happen. pg_restore reads archives created by pg_dump in custom, directory, or tar format and recreates the objects in a target database. Because these archives are structured (a table of contents plus data per object), pg_restore can restore in parallel, restore only selected objects, reorder steps, or simply print the SQL that would be run.

Analogy. A custom-format dump is a flat-pack furniture kit with a numbered parts list. pg_restore is the assembler: it can build the whole wardrobe, only the drawers, several parts at once with helpers, or first show you the instructions without building anything.

Basic restores

# Restore into an existing, empty database
createdb -h db2 -U postgres shop_restored
pg_restore -h db2 -U postgres -d shop_restored -j 8 shop_2026-10-06.dump

# Create the database from the archive (-C), connecting first to an existing db such as 'postgres'
pg_restore -h db2 -U postgres -d postgres -C -j 8 shop_2026-10-06.dump

# Restore a plain SQL dump: stop at the first error, inside one transaction
psql -h db2 -U postgres -d shop_restored -v ON_ERROR_STOP=1 --single-transaction -f shop.sql

Restore order. pg_restore splits the work into three sections, which explains why restores are slow for large databases:

 pre-data    CREATE SCHEMA, CREATE TABLE, CREATE TYPE, CREATE FUNCTION, ...
     |
 data        COPY rows into every table               (parallel across tables with -j)
     |
 post-data   CREATE INDEX, ADD CONSTRAINT (PK, FK, UNIQUE), CREATE TRIGGER, ...  (parallel with -j)

Loading data before indexes and constraints is much faster than maintaining indexes row by row, but rebuilding every index and validating every foreign key can take as long as loading the data itself.

Selective restore with a table of contents

# 1. List the archive's contents
pg_restore -l shop_2026-10-06.dump > toc.list

# 2. Edit toc.list: comment out lines with ';' to skip them
#    ; 3412; 0 16391 TABLE DATA public audit_log app_owner      <- skipped
#    3413; 0 16402 TABLE DATA public orders app_owner            <- kept

# 3. Restore using the edited list
pg_restore -d shop_restored -L toc.list -j 8 shop_2026-10-06.dump

# Or simpler filters for common cases
pg_restore -d shop_restored -n sales -t orders shop_2026-10-06.dump      # one table (definition + data)
pg_restore -d shop_restored --section=pre-data shop_2026-10-06.dump      # schema without indexes and data
pg_restore -f orders.sql -t orders shop_2026-10-06.dump                  # write SQL to a file instead of a db

Recovering a single dropped table. A frequent real-world task: someone ran DROP TABLE or a DELETE without WHERE, and you have last night's dump.

# Restore the table into a scratch database, never directly over production
createdb recovery_scratch
pg_restore -d recovery_scratch -t customers --no-owner shop_2026-10-06.dump

-- Then copy back only the missing rows, under your control
-- (from the scratch database, using postgres_fdw or \copy through a file)
INSERT INTO customers
SELECT * FROM recovery.customers r                 -- e.g. a postgres_fdw foreign table
WHERE NOT EXISTS (SELECT 1 FROM customers c WHERE c.id = r.id);

Useful options

Option	Purpose
-j N	Parallel jobs for data and post-data (custom and directory formats only)
-C	Create the database first (connect with -d to another database)
--clean --if-exists	Drop objects before recreating them; for refreshing an existing copy
--no-owner, --no-privileges (-O, -x)	Skip ALTER OWNER and GRANT; objects are owned by the restoring user
--role=name	Run the restore as this role
--exit-on-error (-e)	Stop at the first error instead of continuing
--single-transaction (-1)	All or nothing; cannot be combined with -j
--disable-triggers	For data-only restores, skip triggers and FK checks during load (superuser)
--section=...	Restore only pre-data, data, or post-data

Errors are not fatal by default. Without --exit-on-error, pg_restore logs each failed statement and continues, then reports a count at the end:

pg_restore: error: could not execute query: ERROR:  role "legacy_report" does not exist
Command was: GRANT SELECT ON TABLE public.orders TO legacy_report;
pg_restore: warning: errors ignored on restore: 3

Check the exit code and the error count in scripts. Three ignored GRANT errors may be harmless; one ignored CREATE TABLE error means missing data.

After the restore

vacuumdb -d shop_restored --analyze-in-stages -j 4   # build planner statistics quickly

Unless the dump included planner statistics (an option added in PostgreSQL 18), the restored database has none, and queries will be badly planned until ANALYZE runs. Also check that sequences, row counts, and extension versions match expectations.

Speeding up large restores. Use -j equal to the CPU cores of the target, raise maintenance_work_mem (faster index builds), and on a target that is not yet in production, temporarily relax durability (synchronous_commit = off, longer checkpoint_timeout, larger max_wal_size; wal_level = minimal with max_wal_senders = 0 if no replicas or archive are attached yet). Revert the settings and take a fresh physical backup afterwards.

Key takeaways

• pg_restore restores custom, directory, and tar archives; plain SQL dumps are restored with psql -v ON_ERROR_STOP=1.
• Restores run in three sections (pre-data, data, post-data); index and constraint creation often dominates the time.
• Use -j for parallelism and -l/-L or -t/-n for selective restores.
• Restore accidentally lost data into a scratch database, then copy rows back deliberately.
• Errors are ignored by default; use --exit-on-error or check the error count, and run ANALYZE afterwards.

🧪 Practice

1. (Beginner) Restore a custom-format dump into a new database with and without -j 4 and compare the elapsed times. Run vacuumdb --analyze-in-stages afterwards and compare a query plan before and after.
2. (Intermediate) Using pg_restore -l and an edited list, restore a database without the data of its two largest tables but with all their definitions and indexes. Verify the result with \dt+.
3. (Interview) "A table was truncated in production by mistake at 11:00. You have a pg_dump from 02:00 and no other backups. Walk through the recovery and explain what data will be lost." Hint: Restore into a scratch database, think about rows inserted or changed between 02:00 and 11:00, foreign keys pointing to the table, and why physical backups with WAL would have helped.

Physical Backups with pg_basebackup

For a 2 TB database, a logical dump can take many hours and a restore even longer, because every index must be rebuilt. A physical backup copies the data directory's files as they are on disk, which is as fast as copying files can be, and restoring it means copying them back. pg_basebackup is PostgreSQL's built-in tool for taking a physical backup of a running cluster over a replication connection, without stopping the server.

Analogy. Photographing every page of a book while someone keeps editing it. The pages you photograph early may be outdated by the time you finish. To make the photo set usable, you also record every edit made while you were photographing (the WAL), and apply those edits when you put the book back together.

Why a file copy of a running server works. Files change while they are being copied, so the copy itself is inconsistent ("fuzzy"). PostgreSQL makes it consistent with WAL:

 1. pg_backup_start: force a checkpoint, remember its REDO LSN, force full_page_writes on
 2. copy every file in the data directory (pages may be torn or mixed-age)
 3. pg_backup_stop: record the end LSN
 4. keep all WAL between the start REDO LSN and the end LSN
 ---------------------------------------------------------------
 Restore: copy the files back, replay WAL from start to at least end LSN
          -> full-page images repair torn pages, replay makes everything consistent

pg_basebackup does all of this for you over the replication protocol. The low-level functions pg_backup_start() and pg_backup_stop() (named pg_start_backup/pg_stop_backup before PostgreSQL 15) let external tools such as storage snapshots do the copy themselves.

Prerequisites

# postgresql.conf on the source server
wal_level = replica
max_wal_senders = 10            # pg_basebackup uses up to two connections with -X stream

CREATE ROLE backup_user WITH LOGIN REPLICATION PASSWORD '...';

# pg_hba.conf: replication connections are matched only by the 'replication' keyword
hostssl  replication  backup_user  10.0.9.20/32  scram-sha-256

Taking a backup

pg_basebackup \
  -h db1 -U backup_user \
  -D /backups/base_2026-10-06 \
  -Ft \                       # tar format: base.tar, one tar per tablespace, pg_wal.tar
  --compress=server-zstd:5 \  # compress on the server (PG 15+); or -z / client-gzip for client side
  -X stream \                 # stream the needed WAL in parallel, so the backup is self-contained
  -c fast \                   # start with an immediate checkpoint instead of waiting for the next one
  -P                          # show progress

waiting for checkpoint
48211932/48211932 kB (100%), 1/1 tablespace

Option	Meaning
-D dir	Target directory (must be empty or not exist)
-Fp / -Ft	Plain (a ready-to-start data directory) or tar files
-X stream / -X fetch / -X none	How to include the WAL needed to make the backup consistent; stream is the safe default
-c fast	Immediate checkpoint at start (more I/O now, faster backup start)
-R	Write standby.signal and primary_conninfo, for building a standby (12.1)
-S slot / -C	Use (or create) a replication slot so the needed WAL cannot be removed
-T old=new	Relocate a tablespace in plain format
-r rate	Throttle the transfer, e.g. -r 100M
--incremental=manifest	Incremental backup relative to an earlier backup (PG 17+)

The backup manifest. Since PostgreSQL 13, pg_basebackup writes a backup_manifest file listing every file with its size and checksum, plus the WAL range needed. pg_verifybackup checks a backup against it:

pg_verifybackup /backups/base_2026-10-06         # plain format, or tar format since PG 18
# backup successfully verified

Incremental backups (PostgreSQL 17+). Full backups of large clusters are expensive to take every day. With WAL summarization enabled, pg_basebackup can copy only blocks that changed since a previous backup, and pg_combinebackup reconstructs a full data directory for restore:

summarize_wal = on               # the walsummarizer process records which blocks changed

pg_basebackup -h db1 -U backup_user -D /backups/full_sun -c fast                     # Sunday: full
pg_basebackup -h db1 -U backup_user -D /backups/incr_mon \
  --incremental=/backups/full_sun/backup_manifest                                    # Monday: changes since Sunday
pg_basebackup -h db1 -U backup_user -D /backups/incr_tue \
  --incremental=/backups/incr_mon/backup_manifest                                    # Tuesday: changes since Monday

# Restore: combine the chain into one ordinary data directory
pg_combinebackup /backups/full_sun /backups/incr_mon /backups/incr_tue -o /var/lib/postgresql/17/restore

Restoring a base backup

sudo systemctl stop postgresql@17-main
mv /var/lib/postgresql/17/main /var/lib/postgresql/17/main.broken   # keep it until the restore is verified
mkdir -m 700 /var/lib/postgresql/17/main
tar -C /var/lib/postgresql/17/main -xf /backups/base_2026-10-06/base.tar
tar -C /var/lib/postgresql/17/main/pg_wal -xf /backups/base_2026-10-06/pg_wal.tar
chown -R postgres:postgres /var/lib/postgresql/17/main
sudo systemctl start postgresql@17-main       # replays the included WAL, then accepts connections

This brings the cluster back to the moment the backup ended. To go further forward in time, you need the WAL archive (Continuous Archiving and PITR).

Limitations of plain pg_basebackup. It has no retention management, no catalog of backups, no parallel or resumable transfer, and no built-in encryption. For production, dedicated tools build on the same mechanisms and add those features (Backup Tools: pgBackRest and Barman).

Key takeaways

• A physical backup copies the data directory of a running cluster; WAL replay makes the inconsistent copy consistent.
• pg_basebackup needs a role with REPLICATION, wal_level = replica, and a replication line in pg_hba.conf.
• Use -X stream so the backup includes all WAL needed to be consistent; -c fast to start immediately.
• Check backups with backup_manifest and pg_verifybackup; PostgreSQL 17 adds incremental backups with pg_combinebackup.
• Physical backups restore much faster than dumps but only to the same major version and platform.

🧪 Practice

1. (Beginner) Take a plain-format base backup of a test cluster with -X stream, verify it with pg_verifybackup, and start a second server on another port directly from the backup directory.
2. (Intermediate) On PostgreSQL 17+, enable summarize_wal, take a full backup, run pgbench for a few minutes, and take an incremental backup. Compare their sizes, combine them with pg_combinebackup, start the result, and check that the latest pgbench changes are present.
3. (Interview) "Why can you not take a backup of a running PostgreSQL server by simply copying the data directory with cp -r or rsync?" Hint: Think about pages changing during the copy, torn pages, and what information you need to make the copy consistent; then explain what pg_backup_start/pg_backup_stop provide.

Continuous Archiving and PITR

Most data loss is not caused by hardware. It is caused by people and software: a migration that drops the wrong column, an UPDATE without a WHERE, an application bug that corrupts records for an hour. A replica copies the mistake within milliseconds, and last night's backup loses a day of work. Point-in-time recovery (PITR) combines a base backup with the continuous archive of WAL so you can restore the database to any moment, for example to 14:29:59, one second before the bad DELETE.

Analogy. A video recording of a whiteboard. The base backup is a photo of the board taken at midnight. The WAL archive is the video of every stroke since. To see the board at 14:29, you start from the midnight photo and play the video until 14:29, then press pause.

The building blocks

 base backup (Sunday 01:00)        WAL archive (every segment since)
 +----------------------+     +---+---+---+---+---+---+---+---+---+---+
 | copy of data files   | --> | 7E| 7F| 80| 81| 82| 83| 84| 85| 86| ..|  -->
 +----------------------+     +---+---+---+---+---+---+---+---+---+---+
            |                                          ^
            |  replay WAL from the backup's start ---- stop at recovery target
            v                                          (time, LSN, xid, or named point)
       restored cluster as of 14:29:59

The prerequisites are a base backup taken before the target time and an unbroken archive of every WAL segment from that backup's start up to the target (WAL Archiving). A single missing segment stops replay at the gap.

Performing a PITR (PostgreSQL 12+, with plain tools; backup tools wrap the same steps)

# 1. Stop the server and keep the damaged data directory for later investigation
sudo systemctl stop postgresql@17-main
mv /var/lib/postgresql/17/main /var/lib/postgresql/17/main.damaged

# 2. Restore the most recent base backup taken BEFORE the target time
mkdir -m 700 /var/lib/postgresql/17/main
tar -C /var/lib/postgresql/17/main -xf /backups/base_2026-10-05/base.tar
rm -rf /var/lib/postgresql/17/main/pg_wal/*      # stale WAL from the backup; the archive is the source of truth

# 3. Recovery settings (postgresql.conf or postgresql.auto.conf)
restore_command = 'cp /mnt/archive/%f %p'        # how to fetch an archived segment; must fail for missing files
recovery_target_time = '2026-10-06 14:29:59+00'  # stop just before the mistake
recovery_target_action = 'pause'                 # pause so you can inspect before ending recovery
recovery_target_inclusive = false                # stop before (not after) a transaction committing exactly at the target

# 4. Tell PostgreSQL to start in targeted recovery mode, then start it
touch /var/lib/postgresql/17/main/recovery.signal
chown -R postgres:postgres /var/lib/postgresql/17/main
sudo systemctl start postgresql@17-main

LOG:  starting point-in-time recovery to 2026-10-06 14:29:59+00
LOG:  restored log file "000000010000002B0000007E" from archive
LOG:  redo starts at 2B/7E000028
LOG:  restored log file "000000010000002B0000007F" from archive
...
LOG:  recovery stopping before commit of transaction 918944, time 2026-10-06 14:30:02.118+00
LOG:  pausing at the end of recovery
HINT:  Execute pg_wal_replay_resume() to promote.

-- 5. While paused, the server accepts read-only queries: verify the data
SELECT count(*) FROM orders WHERE created_at > '2026-10-06 14:00';
-- 6. Satisfied? End recovery and open for writes
SELECT pg_wal_replay_resume();

If the target was wrong, stop the server, restore the base backup again, and choose a different target. Recovery can only move forward; once you end it, you cannot replay further on the same copy.

Recovery targets

Setting	Stops at	Typical use
recovery_target_time	A timestamp	"Just before the incident at 14:30"
recovery_target_lsn	A WAL position	Precise targets found with pg_waldump
recovery_target_xid	A transaction ID	Exclude one known bad transaction
recovery_target_name	A named restore point	Points created deliberately before risky changes
recovery_target = 'immediate'	As soon as the backup is consistent	Restore the backup itself, no roll-forward
(none)	End of available WAL	Full recovery after losing the server

recovery_target_action decides what happens at the target: pause (default when hot standby is on), promote (open for writes immediately), or shutdown (stop, for scripted workflows).

Named restore points. Before a risky operation, create a marker you can recover to precisely:

SELECT pg_create_restore_point('before_release_2026_10_06');
-- later, if needed: recovery_target_name = 'before_release_2026_10_06'

Finding the exact moment of the mistake. Timestamps from users are imprecise. The WAL contains the commit time of every transaction, so you can search for the offending one:

pg_waldump --path=/mnt/archive --start=2B/7E000028 --rmgr=Transaction | grep COMMIT | tail
# rmgr: Transaction ... tx: 918944, lsn: 2B/84A1C3B0, ... desc: COMMIT 2026-10-06 14:30:02.118203 UTC

Combined with --rmgr=Heap and the table's relfilenode (SELECT pg_relation_filenode('orders')), you can identify the transaction that deleted rows from a specific table, and use recovery_target_xid with recovery_target_inclusive = false to stop just before it.

Timelines. When recovery ends and the server is promoted, it starts a new timeline (1 becomes 2). New WAL segments carry the new timeline ID in their file names, and a history file (00000002.history) in the archive records where timeline 2 branched off. This prevents WAL from the new history from overwriting WAL from the old one in the shared archive, so you can still recover to a point on the original timeline later if needed. recovery_target_timeline (default latest) chooses which history to follow.

 timeline 1:  ----A----B----C----X (bad DELETE)----D----E   (archived, still available)
                             \
 timeline 2:                  C'----F----G   (after PITR to just before X)

Partial recovery. PITR restores the whole cluster. To recover a single table without rolling everything back, run PITR on a separate server, then copy the needed rows back to production (with pg_dump -t, \copy, or postgres_fdw). This is the most common real-world use of PITR.

Key takeaways

• PITR = base backup + unbroken WAL archive + a recovery target; it can restore the cluster to any moment after the backup.
• Configure restore_command and a target, create recovery.signal, and start the server; use pause to inspect before promoting.
• Use timestamps, LSNs, transaction IDs, or named restore points (pg_create_restore_point) as targets; pg_waldump finds precise ones.
• Promotion creates a new timeline so new WAL never overwrites the old history.
• To undo damage to one table, run PITR on a separate server and copy rows back.

🧪 Practice

1. (Beginner) With archiving enabled on a test server, take a base backup, insert rows for a few minutes while noting the time, then delete all rows. Recover a copy of the cluster to just before the delete using recovery_target_time and pause, and check the row count before promoting.
2. (Intermediate) Repeat the exercise but use pg_waldump to find the transaction ID of the DELETE, and recover with recovery_target_xid and recovery_target_inclusive = false. Then promote, and list the files in the archive to find the new timeline's history file.
3. (Interview) "At 16:00 you learn that a deployment at 13:00 has been silently corrupting the invoices table, while all other tables received valid writes since then. How do you recover?" Hint: Rolling back the whole cluster to 12:59 loses three hours of valid data everywhere. Think about PITR on a separate server, identifying affected rows, and merging them back.

Backup Tools: pgBackRest and Barman

The built-in tools provide the mechanisms (base backups, WAL archiving, recovery targets), but production backups need much more: scheduling, retention ("keep 4 weekly full backups and 30 days of WAL"), compression, encryption, parallel transfer, object storage, verification, and a single command to restore to a point in time. Writing that yourself with shell scripts is error-prone, and errors in backup scripts tend to be discovered at the worst possible moment. Two mature open-source tools dominate: pgBackRest and Barman. WAL-G is a popular lightweight alternative for cloud object storage.

Analogy. pg_basebackup and archive_command are a camera and a film roll. pgBackRest and Barman are a full photo archive service: they take the pictures on schedule, label them, store copies in a vault, throw away the old ones by policy, and can produce any photo on request.

Comparison

Feature	pgBackRest	Barman	WAL-G
Architecture	Runs on the database host; optional dedicated repository host	Central backup server managing many PostgreSQL servers	Agent on the database host
Full / differential / incremental	Yes / yes / yes (block-level incremental)	Full; incremental through rsync with hard links or PostgreSQL 17 incremental	Full and delta
WAL archiving	archive-push / archive-get with async, parallel mode	archive_command or streaming with pg_receivewal	wal-push / wal-fetch
Storage	POSIX, S3, Azure, GCS, SFTP; multiple repositories	Local disk; cloud through barman-cloud-* utilities	S3, GCS, Azure, Swift, file systems
Encryption	Built-in (AES-256)	Via storage or GPG	Built-in (libsodium, OpenPGP)
Parallelism	Parallel backup, restore, and archiving	Parallel copy with rsync method	Parallel upload/download
Retention	Count of full/differential backups, time-based	Recovery window or redundancy count	Count or time-based delete commands
Strengths	Speed for very large clusters, delta restore, verification	Centralized management of many servers, near-zero RPO with streaming	Simple, cloud-native, used by many operators

pgBackRest in practice

# /etc/pgbackrest/pgbackrest.conf on the database host
[global]
repo1-type=s3
repo1-s3-bucket=acme-pg-backups
repo1-s3-region=eu-west-1
repo1-s3-endpoint=s3.eu-west-1.amazonaws.com
repo1-path=/prod-shop
repo1-retention-full=4                 # keep the 4 most recent full backups (and the WAL they need)
repo1-cipher-type=aes-256-cbc          # client-side encryption
repo1-cipher-pass=<from secret store>
compress-type=zst
process-max=8                          # parallel processes for backup/restore
start-fast=y                           # immediate checkpoint at backup start
archive-async=y                        # push WAL asynchronously in batches

[main]                                 # a "stanza" = one PostgreSQL cluster
pg1-path=/var/lib/postgresql/17/main

# postgresql.conf
archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'

pgbackrest --stanza=main stanza-create            # initialize the repository for this cluster
pgbackrest --stanza=main check                    # verifies config, archiving, and repository access

pgbackrest --stanza=main --type=full backup       # e.g. weekly
pgbackrest --stanza=main --type=diff backup       # changes since the last full, e.g. daily
pgbackrest --stanza=main --type=incr backup       # changes since the last backup of any type, e.g. hourly

pgbackrest --stanza=main info                     # list backups, sizes, and WAL ranges

# Point-in-time restore; --delta reuses files already present and only fetches what differs
sudo systemctl stop postgresql@17-main
pgbackrest --stanza=main --delta \
  --type=time --target='2026-10-06 14:29:59+00' --target-action=promote restore
sudo systemctl start postgresql@17-main

Barman in practice. Barman runs on a separate backup server and pulls from PostgreSQL:

# /etc/barman.d/shop.conf on the Barman server
[shop]
description = "Production shop cluster"
conninfo = host=db1 user=barman dbname=postgres
streaming_conninfo = host=db1 user=streaming_barman
backup_method = postgres                  # uses pg_basebackup under the hood
streaming_archiver = on                   # pg_receivewal: WAL streamed as it is written
slot_name = barman
create_slot = auto
retention_policy = RECOVERY WINDOW OF 14 DAYS

barman check shop                         # every check should report OK
barman backup shop
barman list-backups shop
barman recover shop latest /var/lib/postgresql/17/main \
  --target-time '2026-10-06 14:29:59+00' \
  --remote-ssh-command 'ssh postgres@db-restore'

Choosing

• One or a few large clusters, maximum speed, object storage: pgBackRest.
• Many clusters managed centrally, a dedicated backup server, near-zero data loss through streaming: Barman.
• Kubernetes or cloud-first setups: WAL-G, or whatever the operator integrates (CloudNativePG uses Barman's cloud tools; Crunchy's operator uses pgBackRest). See Kubernetes Operators for Postgres.
• Managed services (RDS, Cloud SQL, Azure) provide their own automated backups and PITR. You still own retention choices, cross-region copies, and restore testing.

Whichever tool you pick, check its release activity and supported PostgreSQL versions before adopting it, and pin it in your infrastructure code like any other critical dependency.

Key takeaways

• Production backups need scheduling, retention, compression, encryption, parallelism, and verification; dedicated tools provide them.
• pgBackRest: host-based, very fast, full/differential/incremental, delta restore, multiple repositories, built-in encryption.
• Barman: central backup server for many clusters, streaming WAL for near-zero RPO, recovery-window retention.
• All of them build on base backups, WAL archiving, and recovery targets; the concepts transfer.
• Run the tool's check/verify commands continuously and practice restores regularly.

🧪 Practice

1. (Beginner) Install pgBackRest on a test server with a local repository, create a stanza, run check, take a full and an incremental backup, and read the output of info.
2. (Intermediate) Design a backup schedule and retention configuration for a 1.5 TB database that must support PITR for the last 14 days and keep monthly backups for one year. Specify full/differential/incremental frequency, repositories, and estimated storage.
3. (Interview) "Compare pgBackRest and Barman. Which would you choose for an organization running 40 PostgreSQL clusters, and why?" Hint: Consider where the tool runs, how WAL reaches the repository, retention policies, central visibility, and the operational cost of each architecture.

Backup Verification and Testing

A backup that has never been restored is a hope, not a backup. Backups fail silently in many ways: an archive command that stopped working three weeks ago, an encryption key nobody can find, a missing WAL segment, a tablespace not included, an object storage lifecycle rule that deleted files, or a restore that works but takes twelve hours when the business expects one. The only way to know that recovery works is to perform it regularly, automatically, and measure it.

Analogy. A fire drill. Having extinguishers on every floor is good, but only an actual drill reveals that one exit is locked, nobody knows the assembly point, and evacuation takes twice as long as planned.

Levels of verification

Level	What it proves	How
1. Backup job succeeded	The tool exited with 0	Exit codes, tool logs
2. Backup is complete and intact	All files present, checksums match	pg_verifybackup, pgbackrest verify, barman check-backup
3. WAL archive is continuous	No gaps between backups and now	pgbackrest check, barman check, pg_stat_archiver
4. Backup restores and starts	Files form a valid cluster that reaches consistency	Automated restore to a scratch server
5. Data is correct	Restored data matches expectations, no corruption	Row counts, application checks, pg_amcheck
6. Recovery meets objectives	Restore time and data loss within RTO/RPO	Timed restore drills, including PITR

Most teams stop at level 1 or 2. Levels 4 to 6 are what actually protect you.

An automated restore test. Run this daily or weekly on a dedicated host, alert on failure, and record the duration.

#!/usr/bin/env bash
# restore_test.sh - restore the latest backup to a scratch host and validate it
set -euo pipefail
STANZA=main
PGDATA=/var/lib/postgresql/17/restore_test
TARGET_TIME=$(date -u -d '-15 minutes' '+%Y-%m-%d %H:%M:%S+00')   # also exercises WAL replay (PITR)
start=$(date +%s)

pg_ctl -D "$PGDATA" stop -m fast 2>/dev/null || true
rm -rf "$PGDATA" && mkdir -m 700 "$PGDATA"

pgbackrest --stanza=$STANZA --pg1-path="$PGDATA" \
  --type=time --target="$TARGET_TIME" --target-action=promote restore

# Avoid clashing with production: different port, no archiving from the test copy
cat >> "$PGDATA/postgresql.auto.conf" <<EOF
port = 5499
archive_mode = off
EOF

pg_ctl -D "$PGDATA" -w -t 3600 start          # wait up to 1 hour for recovery to finish

# 1. Recovery reached the target and promoted
psql -p 5499 -d shop -Atc "SELECT pg_is_in_recovery();" | grep -q f

# 2. Business sanity checks: recent data exists
recent=$(psql -p 5499 -d shop -Atc \
  "SELECT count(*) FROM orders WHERE created_at > now() - interval '1 day';")
[[ "$recent" -gt 0 ]] || { echo "no recent orders in restored copy" >&2; exit 1; }

# 3. Structural integrity of heap and B-tree indexes (PG 14+ client tool)
pg_amcheck -p 5499 -d shop --heapallindexed --jobs 4

elapsed=$(( $(date +%s) - start ))
echo "restore_test OK in ${elapsed}s"           # ship this metric to monitoring and compare with the RTO
pg_ctl -D "$PGDATA" stop -m fast

What to check in the restored copy

• The server reaches consistency and promotes (no missing WAL, no missing files).
• Row counts of key tables are within an expected range compared with production.
• The most recent data matches the recovery target (for example, the newest order timestamp).
• pg_amcheck (or the amcheck extension functions verify_heapam and bt_index_check) finds no corruption.
• Extensions load, and the application's own health checks pass when pointed at the copy.

Monitoring backups continuously

-- Archive health on the primary
SELECT now() - last_archived_time AS archive_age,
       failed_count,
       last_failed_time > coalesce(last_archived_time, '-infinity') AS currently_failing
FROM pg_stat_archiver;

Alert on:

• Age of the latest successful backup exceeding the schedule plus a margin (for example, more than 26 hours for daily backups).
• Age of the latest archived WAL exceeding a few times archive_timeout.
• Any failed verify or restore test, and restore duration trending toward the RTO.
• Backup size changing abruptly (a sudden drop often means something was excluded).

Testing the human side. Automated restores prove the mechanics; drills prove the people. At least a few times a year, have someone who did not write the runbook perform a PITR from the runbook alone, under time pressure, including retrieving encryption keys and credentials from the secret store (Runbooks and Incident Response).

Key takeaways

• Verification has levels: job success, integrity, archive continuity, restorability, data correctness, and meeting RTO/RPO.
• Automate regular restores (including PITR) to a scratch server and validate data with queries and pg_amcheck.
• Use pg_verifybackup, pgbackrest verify, or barman check for integrity, but do not stop there.
• Monitor backup age, archive age, verification results, and restore duration; alert on all of them.
• Run human drills from the runbook, including access to keys and credentials.

🧪 Practice

1. (Beginner) Take a base backup of a test cluster, restore it to a new directory, start it on a different port, and run pg_amcheck against it. Record how long the whole process took.
2. (Intermediate) Adapt the restore test script to your backup tool, schedule it with cron or a CI pipeline, and send the elapsed time and result to a monitoring system. Deliberately break the archive to confirm that the test fails.
3. (Interview) "How do you know your backups work?" Hint: A strong answer goes beyond "the job is green": regular automated restores, PITR to a recent point, data validation, corruption checks, measured restore time against the RTO, alerting, and periodic human drills.

Recovery Objectives RPO and RTO

"We need backups" is not a requirement anyone can design for. The useful questions are: how much data can we afford to lose, and how long can we afford to be down? These two numbers, the recovery point objective (RPO) and the recovery time objective (RTO), turn vague expectations into concrete engineering choices: archiving frequency, replication mode, backup type, hardware, and staffing.

Analogy. Writing a thesis. RPO is how much text you are willing to retype after the laptop dies: save every minute and you lose at most a minute. RTO is how long it takes to be writing again: a spare laptop with everything synced means minutes; reinstalling from a backup drive means hours.

Definitions on a timeline

          last recoverable point              incident             service restored
                    |                             |                         |
 -------------------+-----------------------------+-------------------------+----------->
                    |<----- data loss (RPO) ----->|<-------- downtime (RTO) ------->|

• RPO: the maximum acceptable amount of data loss, measured in time. "RPO = 5 minutes" means at most the last 5 minutes of committed transactions may be lost.
• RTO: the maximum acceptable time from the incident until service is restored.

Both are business decisions informed by cost. Lower values cost more in infrastructure and complexity, and the cost curve gets steep near zero.

What drives RPO in PostgreSQL

Setup	Typical RPO
Nightly pg_dump only	Up to 24 hours
Base backups + archive_command with archive_timeout = 60s	Up to about 1 minute (the unarchived current segment)
Streaming WAL to a backup server (pg_receivewal, Barman streaming)	Seconds
Asynchronous streaming replica	Usually under a second, unbounded during lag spikes
Synchronous replication (synchronous_commit = on with a synchronous standby)	Zero for committed transactions

What drives RTO. The total recovery time is the sum of several phases, and teams often estimate only the restore step:

 RTO = detect  + decide + provision + restore files + replay WAL + validate + reconnect apps
       (alerts)  (people) (hardware)  (size/bandwidth) (WAL volume) (checks)   (DNS, pools)

Estimating a restore. Suppose a 2 TB cluster, daily full backups at 01:00, and a failure at 23:00:

 restore files:  2 TB at 500 MB/s (parallel restore from object storage)      ~ 70 min
 replay WAL:     22 h of WAL, 300 GB total, replay at ~100 MB/s                ~ 50 min
 detect/decide:  alerting + on-call response + decision to restore            ~ 20 min
 validate/apps:  checks, DNS/pooler switch, cache warm-up                     ~ 20 min
                                                                    total     ~ 2 h 40 min

If the business needs an RTO of 30 minutes, no amount of backup tuning will get there; the design must change to failover to a standby, which turns recovery from "restore" into "promote" (12.3).

Strategies by objective

Target	RPO	RTO	Typical design
Internal tools, dev	Hours	Hours to a day	Daily dumps or base backups
Standard business app	Minutes	1 to 4 hours	Base backups + WAL archive (pgBackRest/Barman), tested PITR
Customer-facing service	Seconds	Minutes	Above + async streaming replica with automated failover (Patroni)
Payments, ledgers	Zero	Minutes	Synchronous replication to a second availability zone + automated failover + backups
Region-level disaster	Minutes	Hours	Above + cross-region replica or backup repository

Backups and replicas are not interchangeable. Replication gives a low RTO for hardware failure but faithfully copies DROP TABLE and bad updates. Backups with PITR protect against logical errors but restore slowly. Most production systems need both, and their RPO/RTO should be stated per scenario: "hardware failure: RPO 0, RTO 2 min; accidental deletion: RPO 0 (PITR), RTO 2 h; region loss: RPO 5 min, RTO 4 h".

Delayed replicas. A standby with recovery_min_apply_delay = '1h' replays WAL one hour late. When someone drops a table, you have an hour to pause replay on it (pg_wal_replay_pause()) and recover the data from it, which can be far faster than a full PITR on a large database.

Measuring, not assuming. Record actual numbers from restore tests and failover drills (Backup Verification and Testing), and alert when they drift toward the objective: database growth increases restore time every month even when nothing else changes.

Key takeaways

• RPO is the maximum acceptable data loss; RTO is the maximum acceptable downtime. Both are business decisions with costs.
• RPO depends on how quickly WAL leaves the server: dumps (hours), archiving (minutes), streaming (seconds), synchronous replication (zero).
• RTO includes detection, decisions, provisioning, restore, WAL replay, validation, and reconnection, not just copying files.
• Large databases cannot meet short RTOs by restoring; they need failover to standbys, while backups cover logical errors.
• State objectives per failure scenario, and verify them with measured drills as data grows.

🧪 Practice

1. (Beginner) For your current or a hypothetical system, write down the RPO and RTO implied by its present backup setup, phase by phase. Compare them with what stakeholders believe they are.
2. (Intermediate) A 5 TB database generates 400 GB of WAL per day and must meet RPO 1 minute and RTO 1 hour for hardware failure, and RPO 0, RTO 6 hours for accidental data deletion. Design a solution and estimate each recovery path's time.
3. (Interview) "The business asks for zero data loss and zero downtime. How do you respond?" Hint: Translate the request into concrete RPO/RTO per scenario, explain the cost and latency of synchronous replication across zones, and the residual risks (logical errors, region loss) that no replication setup removes.

Disaster Recovery Planning

Backups, replicas, and PITR are tools. Disaster recovery (DR) planning decides which tools you use for which failures, who runs them, how fast, and how you know they work. Without a plan, a disaster becomes a sequence of improvised decisions made by tired people, under pressure, often without access to the credentials or documentation they need.

Analogy. A ship's emergency plan. Lifeboats (backups) and a second engine (replicas) help only if the crew knows which to use for a fire versus a leak, who launches the boats, where the keys are, and has practiced it in calm weather.

Start from scenarios, not tools

Scenario	Example	Primary defense	Secondary defense
Server or disk failure	Disk dies, instance terminated	Failover to a streaming standby (12.3)	Restore from backup
Availability zone outage	Cloud zone loses power	Standby in another zone	Backup repository in another zone
Region or site loss	Data center fire, provider-wide outage	Cross-region replica or backups in another region	Backups in another provider or account
Human error	DROP TABLE, UPDATE without WHERE	PITR to a separate server, delayed replica	Logical dumps
Application bug corrupting data	Wrong values written for hours	PITR plus targeted repair of affected rows	Audit tables, event logs
Data corruption	Storage or firmware bug, checksum failures	Rebuild from a clean standby or backup	amcheck to scope the damage
Malicious action	Ransomware, compromised superuser deletes backups	Immutable, separately-credentialed backups	Offline copies
Bad upgrade or migration	Major upgrade breaks queries	Rollback plan (old cluster kept, reverse replication)	PITR from before the change

The 3-2-1 rule (and its modern extension). Keep at least 3 copies of the data, on 2 different types of storage, with 1 copy off-site. Many teams add 1 immutable or air-gapped copy and 0 unverified backups (3-2-1-1-0). For PostgreSQL in the cloud, that typically means:

• Production cluster plus streaming standby (copies 1 and 2, in different zones).
• Backup repository in object storage in another region (copy 3), with versioning and object lock (write-once retention) so a compromised account cannot delete it.
• Credentials for the backup repository separate from production credentials; the database host can write backups but not delete them.

Contents of a DR plan

 1. Scope and objectives
    - systems covered, owners, RPO/RTO per scenario
 2. Architecture
    - primary, standbys, backup repositories, regions, diagrams
 3. Dependencies
    - DNS, load balancers, poolers, secrets store, configuration repo, monitoring,
      encryption keys, cloud accounts, people with access
 4. Decision guide
    - which scenario is this? who declares a disaster? failover or restore?
 5. Procedures (runbooks)
    - failover, PITR to a new server, single-table recovery, region evacuation,
      rebuilding standbys afterwards
 6. Communication
    - who is informed, in what order, status page, customer messaging
 7. Validation
    - data checks after recovery, application smoke tests
 8. Testing schedule and results
    - last drill date, measured RPO/RTO, issues found and their fixes

Dependencies are where plans fail. Restoring the database is often the easy part. Typical blockers during real incidents:

• The encryption passphrase for the backup repository is stored only on the server that died.
• The restore server's PostgreSQL version or extensions do not match the backup.
• DNS TTLs are 24 hours, so clients keep connecting to the dead server.
• The runbook references a monitoring dashboard hosted in the failed region.
• Only one person has the cloud permissions needed, and they are on vacation.

DR drills. Schedule them, with increasing realism:

Drill	Frequency	What it exercises
Automated restore test	Daily or weekly	Backup integrity, restore mechanics, timing
Planned switchover to a standby	Monthly or quarterly	Failover procedure, client reconnection
PITR of a single table by a non-author	Quarterly	Runbook clarity, tooling, human skills
Full region evacuation (game day)	Yearly	Dependencies, communication, true RTO

Each drill produces a short report: what was done, measured times, what went wrong, and tickets for fixes. A drill that finds problems is a success.

Key takeaways

• Plan per failure scenario: hardware, zone, region, human error, bugs, corruption, malicious action, bad changes.
• Replicas cover hardware and site failures; PITR and immutable backups cover logical errors and attacks. You need both.
• Follow 3-2-1 (plus an immutable copy), with backup credentials separate from production.
• Document dependencies (keys, DNS, secrets, permissions, people); they cause more DR failures than the database.
• Drill regularly with increasing realism, measure RPO/RTO, and fix what drills reveal.

🧪 Practice

1. (Beginner) For a system you know, fill in the scenario table above with its current primary and secondary defenses. Mark any scenario without a defense.
2. (Intermediate) Write a one-page DR plan for a PostgreSQL-backed web application running in one cloud region with one standby, covering objectives, dependencies, the decision guide, and a drill schedule.
3. (Interview) "An attacker gains superuser access to your production database and the cloud account where backups are stored. What would have protected you, and how would you recover?" Hint: Think about separate credentials and accounts for backups, object lock or immutable storage, off-site copies, PITR to a point before the compromise, and rotating every credential afterwards.

<a id="114-upgrades-and-migration"></a>

11.4 Upgrades and Migration

Every PostgreSQL deployment eventually has to move: to a newer minor release for security fixes, to a new major version for features and support, to new hardware, or from another database entirely; this subchapter covers the upgrade methods, their downtime and risk profiles, migrations from other systems, and the tooling that keeps schema changes under version control.

Minor vs. Major Version Upgrades

PostgreSQL releases come in two kinds that carry very different risk. A minor release fixes bugs and security vulnerabilities and never changes the on-disk format; a major release adds features and may change the internal catalogs and storage format. Treating them the same leads either to skipping critical security patches out of fear, or to an unplanned outage when a "routine" major upgrade breaks something.

Analogy. A minor release is like replacing worn tires on your car: same car, quick, and you should do it as soon as possible. A major release is like moving to a new model year: better in many ways, but the controls moved, and you need to test drive it before handing it to the family.

Version numbering (PostgreSQL 10 and later)

  17.6
  |  |
  |  +-- minor version: bug and security fixes only, released roughly quarterly
  +----- major version: new features, released once a year (usually September/October)

Before version 10, the major version had two parts (9.6), and 9.6.24 was a minor release of major version 9.6.

SELECT version();
--  PostgreSQL 17.6 (Debian 17.6-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc ...
SHOW server_version_num;     -- 170006: convenient for scripts and comparisons

Comparison

Aspect	Minor upgrade (17.5 to 17.6)	Major upgrade (16 to 17)
Content	Bug fixes, security fixes; no new features	New features, behavior changes, removed features
On-disk format	Unchanged	System catalogs change; data files usually compatible
Method	Install new binaries, restart	pg_upgrade, dump and restore, or logical replication
Downtime	A restart (seconds), or a switchover	Minutes to hours depending on method
Application risk	Very low	Real: incompatibilities, planner changes, extension versions
Frequency	Every quarter, as soon as practical	Every 1 to 3 years, before end of support
Rollback	Reinstall previous binaries	Depends on method; must be planned

Support policy. Each major version receives minor releases for five years after its initial release, then reaches end of life (EOL) and receives no more security fixes. Running an EOL version is a security and compliance risk (Release Cycle and Version Support). Planning a major upgrade every one or two years keeps you comfortably within support and avoids huge jumps across many versions.

Doing a minor upgrade

# 1. Read the release notes for every minor version you skip: some require extra steps,
#    such as REINDEX of certain index types after a bug fix
# 2. Install the new packages (the running server keeps the old binaries in memory)
sudo apt-get update && sudo apt-get install --only-upgrade postgresql-17 postgresql-client-17

# 3. Restart to load the new binaries (a reload is not enough)
sudo systemctl restart postgresql@17-main

# 4. Verify
psql -Atc "SHOW server_version;"     # 17.6

With replicas, upgrade the standbys first, then switch over to an upgraded standby, then upgrade the old primary. Clients see only the brief switchover rather than a restart of the primary. Minor releases are compatible across primary and standby in both directions within the same major version, though the docs recommend upgrading standbys first.

Preparing for a major upgrade

 1. Read the release notes of EVERY major version you cross, especially "Migration" sections
    (removed parameters, changed defaults, incompatible behavior)
 2. Inventory extensions: are versions available for the new major version on your platform?
 3. Upgrade a copy of production (from a backup) and run the application test suite against it
 4. Compare query plans and timings for the top queries from pg_stat_statements
 5. Choose a method: pg_upgrade, dump/restore, or logical replication (next topics)
 6. Write a rollback plan and rehearse the whole procedure with timings

Operating system upgrades and collations. An OS upgrade can change PostgreSQL's behavior even when PostgreSQL itself does not change. When the C library (glibc) changes its collation rules (glibc 2.28 was a notorious example), text is sorted differently, and existing B-tree indexes on text columns are suddenly in the "wrong" order. Lookups can miss rows and unique indexes can accept duplicates. The same applies to physical replicas or restores onto a host with a different glibc version.

-- PostgreSQL records collation versions and warns on mismatch at connection time:
-- WARNING:  database "shop" has a collation version mismatch
-- DETAIL:  The database was created using collation version 2.31, but the operating system provides version 2.36.

-- Fix: rebuild affected indexes, then record the new version
REINDEX DATABASE CONCURRENTLY shop;          -- run while connected to 'shop'; rebuilds every index (PG 12+)
ALTER DATABASE shop REFRESH COLLATION VERSION;

REINDEX DATABASE rebuilds every index; on large databases, rebuild only indexes on collatable columns (text, varchar, citext) with REINDEX INDEX CONCURRENTLY. Using ICU collations or the builtin collation provider (PostgreSQL 17+) decouples sort order from glibc and makes OS upgrades safer.

Key takeaways

• Minor releases contain only fixes and keep the on-disk format; apply them promptly by installing binaries and restarting.
• Major releases change catalogs and behavior; they need pg_upgrade, dump/restore, or logical replication, plus testing.
• Each major version is supported for five years; plan major upgrades every one or two years.
• Read release notes for every version crossed, check extension availability, and test on a copy of production.
• OS upgrades can change collation behavior and silently corrupt text indexes; reindex and refresh collation versions.

🧪 Practice

1. (Beginner) Find your server's major and minor version, look up its end-of-life date, and list the minor releases since yours along with any that require extra steps.
2. (Intermediate) Write a checklist for a minor upgrade of a primary with two streaming standbys that minimizes client-visible downtime, including verification steps after each server.
3. (Interview) "After an OS upgrade on a replica, queries using WHERE email = ... sometimes return no rows, even though the row exists. What is happening, and how do you fix it?" Hint: Think about what a B-tree index depends on for ordering text, what changed in the OS, and how PostgreSQL tracks collation versions.

pg_upgrade Workflow

Dumping and restoring a multi-terabyte database for a major upgrade can take a day. Yet between most major versions, the format of data files (the table and index pages) does not change; only the system catalogs do. pg_upgrade exploits this: it creates a fresh cluster with the new version, transfers the schema with pg_dump/pg_restore, and then moves the data files over unchanged, by copying, hard-linking, cloning, or swapping them. With link mode, a multi-terabyte upgrade can take minutes.

Analogy. Moving into a new house where the furniture fits exactly. Instead of disassembling every piece and reassembling it (dump and restore), you only rewrite the address labels (catalogs) and either carry the furniture over as-is (copy) or simply declare that the old rooms now belong to the new house (link).

How it works

 old cluster (v16)                         new cluster (v17, freshly initdb'ed)
 +---------------------+                   +---------------------+
 | catalogs (v16 fmt)  | -- pg_dump ------>| catalogs (v17 fmt)  |   schema only, keeps OIDs/relfilenodes
 |                     |    --schema-only  |                     |
 | data files          | -- copy / link -->| data files          |   unchanged bytes
 | (tables, indexes)   |    / clone / swap |                     |
 +---------------------+                   +---------------------+
       also: transaction ID counters, multixact data, pg_xact, (since 17) logical replication slots

Transfer modes

Mode	Option	Speed	Disk space	Old cluster after upgrade
Copy	--copy (default)	Slow (copies every byte)	Doubles	Intact; usable for rollback
Link	--link	Very fast (hard links)	Almost none	Unusable once the new cluster has started
Clone	--clone	Very fast (reflinks on XFS, Btrfs, APFS)	Grows only as files change	Intact
Copy file range	--copy-file-range (PG 17+)	Fast where supported	Like copy or clone	Intact
Swap	--swap (PG 18+)	Fastest (moves directories)	None	Destroyed

Link mode is the most common choice for large databases, combined with a standby or backup as the rollback path.

Step by step (Debian/Ubuntu layout, 16 to 17)

# 0. Take a fresh backup and confirm it restores. Note extensions and settings.

# 1. Install the new major version alongside the old one
sudo apt-get install postgresql-17 postgresql-17-postgis-3   # same extensions as on 16

# 2. Create the new cluster with matching settings: encoding, locale, and checksums must match
sudo -u postgres /usr/lib/postgresql/17/bin/initdb \
  -D /var/lib/postgresql/17/main \
  --encoding=UTF8 --locale=en_US.UTF-8 --data-checksums
# Note: initdb in PG 18 enables checksums by default; when upgrading a cluster WITHOUT checksums
# to 18, pass --no-data-checksums, because pg_upgrade requires matching checksum settings.

# 3. Run the compatibility check while the old cluster is still running
sudo -u postgres /usr/lib/postgresql/17/bin/pg_upgrade \
  --old-bindir=/usr/lib/postgresql/16/bin  --new-bindir=/usr/lib/postgresql/17/bin \
  --old-datadir=/var/lib/postgresql/16/main --new-datadir=/var/lib/postgresql/17/main \
  --old-options='-c config_file=/etc/postgresql/16/main/postgresql.conf' \
  --new-options='-c config_file=/etc/postgresql/17/main/postgresql.conf' \
  --link --jobs=8 --check

Performing Consistency Checks on Old Live Server
------------------------------------------------
Checking cluster versions                                     ok
Checking database user is the install user                    ok
Checking for prepared transactions                            ok
Checking for contrib/isn with bigint-passing mismatch         ok
Checking for incompatible "aclitem" data type                 ok
Checking for presence of required libraries                   fatal

Your installation references loadable libraries that are missing from the new installation.
A list of problem libraries is in the file: .../loadable_libraries.txt

# 4. Fix every reported problem (here: install the missing extension package), re-run --check until clean

# 5. Downtime starts: stop the application and the old cluster
sudo systemctl stop postgresql@16-main

# 6. Run the upgrade for real (same command without --check)
sudo -u postgres /usr/lib/postgresql/17/bin/pg_upgrade ... --link --jobs=8

# 7. Bring over configuration changes (postgresql.conf, pg_hba.conf) and start the new cluster
sudo systemctl start postgresql@17-main

# 8. Rebuild planner statistics: PG 18 keeps most statistics across pg_upgrade;
#    earlier versions start with none, so queries are badly planned until this finishes
sudo -u postgres /usr/lib/postgresql/17/bin/vacuumdb --all --analyze-in-stages --jobs=8
#    (on PG 18+, --missing-stats-only limits this to what was not carried over)

# 9. Update extensions if pg_upgrade generated update_extensions.sql
sudo -u postgres psql -f update_extensions.sql

# 10. After verification, remove the old cluster (script generated by pg_upgrade)
./delete_old_cluster.sh

Debian's wrapper pg_upgradecluster -m upgrade --link 16 main performs most of these steps, including configuration migration.

Downtime. With --link, the downtime is dominated by the schema dump and restore (proportional to the number of objects, not data size) plus the restart. A 3 TB database with a few thousand tables typically takes a few minutes; databases with hundreds of thousands of objects (many partitions or schemas) take longer, which --jobs helps with.

Standbys. Physical standbys cannot follow a major upgrade by streaming, because the WAL format differs. Options: rebuild them from the upgraded primary with pg_basebackup (simple, takes time and bandwidth), or follow the documented rsync --hard-links procedure that upgrades standbys in place using the old and new directories (fast, but requires care).

Rollback. Plan it before you start:

• Copy or clone mode: the old cluster is untouched; stop the new one and start the old one. Writes made to the new cluster are lost.
• Link mode: once the new cluster has started, the old one is no longer safe to start. Rollback means failing over to a standby that was not upgraded, or restoring from backup.
• In every mode, after the application writes to the new version, going back requires logical replication or data reconciliation.

Key takeaways

• pg_upgrade recreates the catalogs in a new cluster and reuses the data files, making major upgrades fast.
• Copy mode keeps the old cluster as a rollback path; link mode is fast but makes the old cluster unusable once the new one starts.
• Run --check first; fix missing extensions, settings mismatches (locale, encoding, checksums), and reported incompatibilities.
• After upgrading, regenerate statistics (vacuumdb --analyze-in-stages), update extensions, and rebuild or re-sync standbys.
• Downtime depends mostly on the number of objects, not on data size.

🧪 Practice

1. (Beginner) Install two major versions on a test machine, create a database with pgbench -i -s 100 on the older one, and upgrade it with pg_upgrade --copy. Measure the time and compare pgbench results before and after.
2. (Intermediate) Repeat with --link and then with --clone (on a file system that supports it). Compare elapsed time and disk usage, and explain what would happen if you tried to start the old cluster after each.
3. (Interview) "You must upgrade a 4 TB PostgreSQL 13 cluster with two standbys to 17 with less than 15 minutes of downtime. Walk through your plan, including rollback." Hint: Consider --link with --check rehearsals, extension availability, the standby strategy, statistics regeneration, and keeping a non-upgraded standby or backup as the rollback path.

Dump and Restore Upgrades

The oldest and simplest way to move to a new major version is to dump the data from the old server and load it into a new one. It requires no special tooling, works across any versions, operating systems, and CPU architectures, and produces a clean, freshly built cluster. Its cost is time: every row is exported, imported, and every index rebuilt, so downtime grows with the size of the data.

Analogy. Moving house by packing everything into boxes and unpacking it in the new place. It always works, even across countries with different plugs (architectures), and you get to throw away junk on the way (bloat). But the bigger the house, the longer the move.

When to choose it

Situation	Why dump/restore fits
Small databases (tens of GB) with an acceptable maintenance window	Simple and reliable
Changing CPU architecture or OS (x86 to ARM, Linux to another platform)	Physical files are not portable; logical dumps are
Changing encoding, locale, collation provider, or enabling checksums	pg_upgrade requires these to match
Very old source versions	pg_dump from the new version can read servers going back many major versions
Heavy bloat	The restored database is compact, with freshly built indexes
Consolidating or splitting clusters	Dump selected databases or schemas into different targets

Use the new version's tools. Always run pg_dump and pg_dumpall from the newer PostgreSQL version against the old server. Newer dump tools know how to export older servers in a form the new server accepts; the reverse is not supported.

The procedure

OLD="host=old-db port=5432 user=postgres"
NEW="host=new-db port=5432 user=postgres"
BIN=/usr/lib/postgresql/17/bin        # new version's client tools

# 0. Stop application writes (maintenance mode) so the dump is final

# 1. Globals: roles, tablespaces, role settings
$BIN/pg_dumpall -d "$OLD" --globals-only -f globals.sql
psql -d "$NEW dbname=postgres" -v ON_ERROR_STOP=1 -f globals.sql

# 2. Each database: parallel dump in directory format, parallel restore
for db in shop billing; do
  $BIN/pg_dump    -d "$OLD dbname=$db" -Fd -j 8 -f "/dump/$db"
  $BIN/createdb   -h new-db -U postgres -T template0 "$db"
  $BIN/pg_restore -d "$NEW dbname=$db" -j 8 --exit-on-error "/dump/$db"
  $BIN/vacuumdb   -d "$NEW dbname=$db" --analyze-in-stages -j 8
done

# 3. Validate, then point the application at the new server

For smaller databases on the same host, a pipe avoids intermediate files:

$BIN/pg_dumpall -p 5432 | $BIN/psql -p 5433 -d postgres -v ON_ERROR_STOP=1

Estimating downtime. Measure on a copy of production; do not guess. A rough model:

 downtime ~ dump time + restore data time + index/constraint build time + ANALYZE time

 Example: 300 GB of table data, 150 GB of indexes, 16 cores, fast SSDs
   pg_dump -j 8        ~ 40 min
   pg_restore data -j 8 ~ 50 min
   indexes + FKs -j 8  ~ 60 min
   analyze-in-stages   ~ 10 min (first usable stage much sooner)
   total               ~ 2.5-3 hours of write downtime

Dumping and restoring at the same time over a pipe saves the intermediate storage but not much time, and it cannot use parallel jobs.

Speeding it up

• Run dump and restore on separate machines with fast networking, each with enough CPU for -j.
• On the target (not yet in production): raise maintenance_work_mem and max_wal_size, set synchronous_commit = off and wal_level = minimal (with max_wal_senders = 0, archive_mode = off), then revert and take a base backup before going live.
• Restore the largest tables first in their own jobs (they determine the critical path), or exclude cold archive data and copy it separately later.
• If the window is still too long, use logical replication instead (Logical Replication Upgrades).

Validation before go-live

-- Compare row counts per table on both sides (run on each server, diff the output)
SELECT schemaname, relname, n_live_tup FROM pg_stat_user_tables ORDER BY 1, 2;   -- approximate
-- For exact counts on critical tables:
SELECT count(*) FROM orders;

-- Sequences must be at least as high as on the old server
SELECT schemaname, sequencename, last_value FROM pg_sequences ORDER BY 1, 2;

-- Extensions and versions
SELECT extname, extversion FROM pg_extension ORDER BY 1;

Rollback is simple: the old server is unchanged. As long as no application writes reached the new server, you switch back. After go-live, rolling back requires copying new writes back, so decide on a "point of no return" in advance.

Key takeaways

• Dump and restore works across any versions, platforms, and architectures, and lets you change encoding, locale, and checksums.
• Downtime grows with data size: dump, load, index build, and analyze all happen inside the window.
• Use the new version's pg_dump/pg_dumpall; restore globals first, then each database with parallel jobs.
• Validate row counts, sequences, and extensions before switching traffic, and run ANALYZE.
• The old server remains a clean rollback until the application writes to the new one.

🧪 Practice

1. (Beginner) Upgrade a sample database from an older to a newer major version with pg_dumpall piped into psql. List the steps you would add for a production database.
2. (Intermediate) Time a parallel directory-format dump and restore of a 20 GB database with -j 1 and -j 8. Break the restore time into data load and index build by restoring with --section=data and --section=post-data separately.
3. (Interview) "When would you choose dump and restore over pg_upgrade for a major upgrade, even though it is slower?" Hint: Think about architecture changes, encoding and locale changes, checksums, very old versions, bloat, and consolidation.

Logical Replication Upgrades

pg_upgrade still requires the database to be down for minutes, and dump/restore for hours. For systems where even that is too long, logical replication allows a major-version upgrade with only seconds of write downtime. The new-version cluster is built alongside the old one, kept continuously in sync by replicating row changes, and then traffic is switched over at a moment of your choosing. Because logical replication works between different major versions, the old and new clusters can run side by side for days while you test.

Analogy. Building a new bridge next to the old one while traffic keeps flowing. Every change made to the old bridge's signage is mirrored on the new one. When the new bridge is ready and inspected, you close the old one for a minute, redirect traffic, and open the new one.

Architecture

  application ---> old primary (v13)                      new primary (v17)
                   wal_level = logical                    schema loaded with pg_dump --schema-only
                   PUBLICATION upgrade_pub  ===== row changes =====> SUBSCRIPTION upgrade_sub
                   (FOR ALL TABLES)          initial table copy, then continuous streaming

  cutover: stop writes -> wait until in sync -> sync sequences -> point application to v17

Step by step

-- On the OLD cluster (requires restart if wal_level was not already logical)
ALTER SYSTEM SET wal_level = 'logical';
-- restart, then:
CREATE ROLE repl_upgrade WITH LOGIN REPLICATION PASSWORD '...';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO repl_upgrade;   -- initial copy reads tables

-- Every table needs a replica identity so UPDATE/DELETE can be replicated
SELECT c.oid::regclass AS table_without_pk
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r' AND n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND NOT EXISTS (SELECT 1 FROM pg_index i WHERE i.indrelid = c.oid AND i.indisprimary);
-- Add primary keys, or: ALTER TABLE legacy_log REPLICA IDENTITY FULL;  (slower, more WAL)

CREATE PUBLICATION upgrade_pub FOR ALL TABLES;

# Copy roles and schema (no data) to the NEW cluster, using the new version's tools
pg_dumpall -h old-db --globals-only | psql -h new-db -d postgres
pg_dump    -h old-db -d shop --schema-only | psql -h new-db -d shop -v ON_ERROR_STOP=1

-- On the NEW cluster: start the initial copy and streaming
CREATE SUBSCRIPTION upgrade_sub
  CONNECTION 'host=old-db dbname=shop user=repl_upgrade password=... sslmode=require'
  PUBLICATION upgrade_pub;

-- Watch the initial copy: every table should reach state 'r' (ready)
SELECT srrelid::regclass, srsubstate
FROM pg_subscription_rel
WHERE srsubstate <> 'r';          -- i = init, d = copying data, f = finished copy, s = synchronized

-- On the OLD cluster: how far behind is the subscriber?
SELECT slot_name,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS lag
FROM pg_replication_slots
WHERE slot_name = 'upgrade_sub';

While the clusters run in parallel, point read-only traffic, test suites, and query-plan comparisons at the new cluster.

Cutover

 T-0  Stop writes: application maintenance mode, or PgBouncer PAUSE, or revoke write grants
 T+5s Verify lag is zero (confirmed_flush_lsn = pg_current_wal_lsn on old)
 T+10s Copy sequence values (sequences are not replicated, see below)
 T+15s Disable the subscription on the new cluster (ALTER SUBSCRIPTION upgrade_sub DISABLE)
 T+20s Point the application (DNS, pooler config, service discovery) at the new cluster
 T+30s Resume traffic; monitor errors and latency

What logical replication does not copy. Plan for each of these explicitly:

Not replicated	Consequence	Mitigation
DDL (schema changes)	Schema drift if anyone runs migrations during the upgrade	Freeze schema changes; apply DDL to both sides
Sequence values (up to PostgreSQL 18)	New inserts on the new cluster collide with existing IDs	Copy setval at cutover, with a safety margin
Large objects (pg_largeobject)	Missing data	Copy separately, or migrate to bytea beforehand
Tables without a primary key or replica identity	UPDATE/DELETE fail on the publisher	Add primary keys or REPLICA IDENTITY FULL
Materialized views	Empty on the subscriber	REFRESH MATERIALIZED VIEW before cutover
Roles and global objects	Missing users	pg_dumpall --globals-only

Synchronizing sequences at cutover

-- On the OLD cluster, after writes stopped: generate setval statements
SELECT format('SELECT setval(%L, %s, true);',
              format('%I.%I', schemaname, sequencename),
              last_value + 1000)                      -- margin, in case of in-flight values
FROM pg_sequences
WHERE last_value IS NOT NULL;
-- Run the generated statements on the NEW cluster (psql: \gexec on a connection to the new cluster,
-- or save to a file and execute it there)

Speeding up the initial copy. For very large databases, the initial table copy can take days. PostgreSQL 17 added pg_createsubscriber, which converts a physical standby into a logical subscriber, avoiding the copy entirely: you build a streaming standby, convert it, and then upgrade it with pg_upgrade (which, from 17 onward, preserves logical replication slots and subscriptions).

Rollback. Before cutover, rollback is trivial: keep using the old cluster. After cutover, you can keep a reverse path by creating a publication on the new cluster and a subscription on the old one (with copy_data = false) so writes on the new version flow back, until you are confident.

Key takeaways

• Logical replication keeps a new-version cluster in sync with the old one, reducing upgrade downtime to seconds.
• Requirements: wal_level = logical on the old cluster, primary keys or replica identity on every table, schema copied with pg_dump --schema-only.
• DDL, sequences, large objects, and materialized views are not replicated; handle each explicitly.
• Cutover: stop writes, confirm zero lag, copy sequences, switch the application, resume.
• pg_createsubscriber (PG 17+) avoids the initial copy; reverse replication provides a rollback path.

🧪 Practice

1. (Beginner) Run two PostgreSQL versions on different ports. Replicate a pgbench database from the older to the newer with a publication and subscription, and watch pg_subscription_rel until all tables are ready.
2. (Intermediate) While pgbench runs against the old cluster, perform a full cutover: stop the load, confirm zero lag, sync sequences, and restart pgbench against the new cluster. Verify that no inserts fail with duplicate keys.
3. (Interview) "During a logical replication upgrade, the old cluster's disk usage keeps growing even though the subscription is active. What is going on?" Hint: Think about the replication slot, what holds WAL on the publisher, initial table synchronization, a subscriber that is erroring on a conflict, and what pg_replication_slots and pg_stat_subscription show.

Zero-Downtime Migration Strategies

Many changes besides upgrades threaten availability: renaming a column used by a running application, splitting a table, changing a type, moving to new hardware or a new cloud provider. Doing them "big bang", with everything changed at once during a maintenance window, causes downtime and makes rollback hard. Zero-downtime strategies break a change into small, backward-compatible steps, each of which can be deployed and, if needed, rolled back while the system keeps serving traffic.

Analogy. Replacing the wheels of a moving train is impossible; adding a second set of tracks, moving trains over one by one, and removing the old tracks later is merely slow. Zero-downtime migration trades speed and simplicity for continuous service.

The core idea: never break the running version. At any moment during a rolling deployment, two versions of the application may run at once (old and new instances). Every schema state must therefore work with both the current and the next application version. Lock-level details of individual DDL statements are covered in Lock-Free Migration Patterns; this topic is about sequencing.

Expand and contract (parallel change)

 Goal: rename customers.phone to customers.phone_number

 Phase 1 EXPAND     add the new column; old app still uses 'phone'
                    ALTER TABLE customers ADD COLUMN phone_number text;
 Phase 2 SYNC       keep both in sync for writes from either version (trigger or dual write)
 Phase 3 BACKFILL   copy existing values in small batches
 Phase 4 MIGRATE    deploy app version that reads/writes 'phone_number'
 Phase 5 CONTRACT   after all instances use the new column and a safe period has passed:
                    drop the trigger, then ALTER TABLE customers DROP COLUMN phone;

 Each phase is a separate deployment that can be rolled back on its own.

Phase 2: keeping old and new in sync

CREATE FUNCTION customers_sync_phone() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    NEW.phone_number := coalesce(NEW.phone_number, NEW.phone);   -- old app writes 'phone'
    NEW.phone        := coalesce(NEW.phone, NEW.phone_number);   -- new app writes 'phone_number'
  ELSIF NEW.phone IS DISTINCT FROM OLD.phone THEN
    NEW.phone_number := NEW.phone;
  ELSIF NEW.phone_number IS DISTINCT FROM OLD.phone_number THEN
    NEW.phone := NEW.phone_number;
  END IF;
  RETURN NEW;
END $$;

CREATE TRIGGER customers_sync_phone
  BEFORE INSERT OR UPDATE ON customers
  FOR EACH ROW EXECUTE FUNCTION customers_sync_phone();

Phase 3: backfilling in batches. A single UPDATE customers SET phone_number = phone on 50 million rows holds row locks for the whole run, creates massive WAL bursts and replica lag, and doubles the table size in dead tuples at once. Batches keep each transaction short:

-- PostgreSQL 11+: a procedure can COMMIT between batches
CREATE PROCEDURE backfill_phone_number(batch_size int DEFAULT 10000)
LANGUAGE plpgsql AS $$
DECLARE
  rows_updated int;
BEGIN
  LOOP
    UPDATE customers
    SET phone_number = phone
    WHERE id IN (
      SELECT id FROM customers
      WHERE phone_number IS NULL AND phone IS NOT NULL
      LIMIT batch_size
      FOR UPDATE SKIP LOCKED                  -- do not wait on rows the application is updating
    );
    GET DIAGNOSTICS rows_updated = ROW_COUNT;
    EXIT WHEN rows_updated = 0;
    COMMIT;                                   -- release locks, let vacuum and replicas keep up
    PERFORM pg_sleep(0.1);                    -- throttle: leave capacity for production traffic
  END LOOP;
END $$;

CALL backfill_phone_number();

For very large tables, iterate over primary key ranges (WHERE id BETWEEN x AND x + 9999) instead of repeatedly searching for unprocessed rows, and watch replication lag while it runs.

Other strategies

Strategy	How it works	Good for
Expand and contract	Additive schema change, dual support, later cleanup	Renames, type changes, table splits
Feature flags	New code paths enabled gradually, independent of deploys	Switching reads/writes to new structures safely
Dual writes with verification	Application writes to old and new stores; a job compares them	Moving data to a new table or service
Shadow reads	New path runs in parallel, results compared but not returned	Validating a new query or data model
Blue-green database	A second cluster kept in sync by logical replication; switch over	Upgrades, hardware moves, big rewrites (Logical Replication Upgrades)
Online schema change via copy	New table with target schema, sync by trigger, swap names in one short transaction	Large rewrites that would otherwise lock the table

Switching connections without dropping requests. For a blue-green cutover, PgBouncer can hold client queries for a few seconds while the backend changes:

-- In the PgBouncer admin console (psql -p 6432 pgbouncer)
PAUSE shop;          -- waits for running transactions to finish, then holds new queries
-- ... update pgbouncer.ini to point 'shop' at the new server, confirm replication caught up ...
RELOAD;
RESUME shop;         -- queued queries proceed against the new server

Clients see a short latency spike instead of connection errors.

Rules of thumb

• Separate schema changes from code changes; deploy schema first when expanding, code first when contracting.
• Make each step reversible, and know the rollback for each before starting.
• Use lock_timeout on every DDL statement so a migration never queues behind a long query and blocks everyone.
• Watch replication lag, WAL rate, and locks during backfills; throttle accordingly.
• Wait long enough before contracting: old app instances, background jobs, caches, and reports may still read the old column.

Key takeaways

• Every intermediate schema state must work with both the old and the new application version.
• Expand and contract: add, sync, backfill, switch, then remove, as separately deployable steps.
• Backfill in small committed batches with throttling to avoid lock contention, WAL spikes, and replica lag.
• Blue-green clusters with logical replication and pooler pause/resume enable near-instant cutovers.
• Plan rollback for every step and protect every DDL statement with lock_timeout.

🧪 Practice

1. (Beginner) Write the sequence of deployments (schema and application) needed to rename a column users.fullname to users.display_name without downtime. State what is deployed in each step and what the rollback is.
2. (Intermediate) Implement the batch backfill procedure for a 10-million-row test table and compare its effect on concurrent pgbench latency and on WAL rate with a single-statement UPDATE.
3. (Interview) "You need to change orders.amount from integer (cents) to numeric(12,2) (currency units) on a 2-billion-row table, with no downtime. Describe your approach." Hint: A direct ALTER COLUMN TYPE rewrites the table under an exclusive lock. Think about a new column, sync triggers, batched backfill, application changes with feature flags, verification, and cleanup.

Migrating from Other Databases

Organizations move to PostgreSQL from MySQL, Oracle, SQL Server, and others to cut licensing costs, use its extension ecosystem, or standardize their stack. Copying the data is the easy part. The hard parts are the differences: data types that do not map one to one, SQL dialects, procedural code, implicit behaviors applications silently depend on (case-insensitive comparisons, empty strings treated as NULL), and performance characteristics. A successful migration treats these as a project, not as a data transfer.

Analogy. Translating a book into another language. Converting words one by one (types and syntax) is mechanical and tools do it well. Idioms, jokes, and cultural references (implicit behaviors) need a human translator who understands both languages, and a native reader to proofread the result (testing).

The phases

 1. Assess      inventory schemas, data volume, procedural code, features used; estimate effort
 2. Convert     schema (types, constraints, indexes), then code (procedures, triggers, queries)
 3. Migrate     initial bulk load, then change data capture to keep the copy current
 4. Validate    row counts, checksums, business reports compared side by side
 5. Test        application test suites, performance tests with production-like load
 6. Cut over    stop writes, final sync, switch, keep the old system for rollback

Tools

Source	Tool	What it does
MySQL, MariaDB, SQLite, SQL Server	pgloader	Schema conversion and fast parallel data load in one command, with configurable type casting
Oracle	ora2pg	Assessment report with effort estimate, schema and PL/SQL conversion, data export
SQL Server	Babelfish for PostgreSQL	T-SQL compatibility layer (available on Aurora and as open source)
Many sources	AWS DMS, Google Database Migration Service, Debezium	Full load plus change data capture for minimal downtime
Any	Foreign data wrappers (mysql_fdw, oracle_fdw, tds_fdw)	Query the source from PostgreSQL; INSERT ... SELECT migrations

pgloader example (MySQL to PostgreSQL)

-- migrate.load
LOAD DATABASE
     FROM      mysql://app:secret@mysql-host/shop
     INTO      postgresql://app:secret@pg-host/shop

WITH include drop, create tables, create indexes, foreign keys,
     reset sequences, workers = 8, concurrency = 2

CAST type datetime to timestamptz drop default drop not null using zero-dates-to-null,
     type tinyint   to boolean drop typemod using tinyint-to-boolean

ALTER SCHEMA 'shop' RENAME TO 'public';

pgloader migrate.load

Oracle assessment with ora2pg

ora2pg -c ora2pg.conf -t SHOW_REPORT --estimate_cost --dump_as_html > assessment.html
ora2pg -c ora2pg.conf -t TABLE     -o tables.sql
ora2pg -c ora2pg.conf -t PROCEDURE -o procedures.sql      # PL/SQL converted to PL/pgSQL (needs review)

Type mapping highlights

Source type	PostgreSQL type	Watch out for
MySQL INT AUTO_INCREMENT	integer GENERATED ALWAYS AS IDENTITY	Reset identity values after the load
MySQL TINYINT(1)	boolean	Only if it really stores booleans
MySQL DATETIME	timestamp or timestamptz	Zero dates 0000-00-00 do not exist in PostgreSQL
MySQL UNSIGNED BIGINT	numeric(20) or bigint with a check	No unsigned integers in PostgreSQL
MySQL ENUM('a','b')	CREATE TYPE ... AS ENUM or text with CHECK	Ordering and changes to the list
Oracle NUMBER(p,s)	numeric(p,s); NUMBER(10,0) to bigint	Plain NUMBER is arbitrary precision; integer types are faster
Oracle VARCHAR2(n)	varchar(n) or text	Oracle n may be bytes, PostgreSQL counts characters
Oracle DATE	timestamp(0)	Oracle DATE includes a time of day
Oracle CLOB / BLOB	text / bytea	Large values are stored with TOAST automatically
SQL Server NVARCHAR	text or varchar	PostgreSQL databases are typically UTF-8 throughout
SQL Server DATETIME2	timestamp	Precision is microseconds in PostgreSQL
SQL Server UNIQUEIDENTIFIER	uuid
SQL Server BIT	boolean

Behavioral differences that break applications

Behavior	Source	PostgreSQL	Fix
Empty string vs. NULL	Oracle treats '' as NULL	'' and NULL are different	Review IS NULL checks; normalize data
Case-insensitive comparison	MySQL and SQL Server default collations	Case-sensitive by default	citext, nondeterministic ICU collations, or lower() with expression indexes
Identifier case	MySQL preserves case; Oracle uppercases	Unquoted identifiers fold to lowercase	Avoid quoted mixed-case identifiers; convert names to snake_case
Implicit type conversion	MySQL compares '42abc' = 42	Strict typing, errors on mismatches	Fix queries; add explicit casts
GROUP BY leniency	MySQL without ONLY_FULL_GROUP_BY	Every non-aggregated column must be grouped (or functionally dependent)	Rewrite queries
Transactions and DDL	Oracle/MySQL commit DDL implicitly	DDL is transactional	Usually a benefit; review migration scripts
Syntax	NVL, ROWNUM, CONNECT BY, DUAL, TOP n, ISNULL	COALESCE, LIMIT/ROW_NUMBER(), recursive CTE, no DUAL, LIMIT, COALESCE	Rewrite, or use orafce for Oracle functions
Packages	Oracle packages with state	No packages	Schemas for namespacing; session variables via set_config or temp tables

Validation. Never trust a migration because the tool reported success:

-- On both sides: row counts and an aggregate fingerprint per table
SELECT count(*),
       sum(amount_cents),
       md5(string_agg(id::text, ',' ORDER BY id))   -- for moderately sized tables or ranges
FROM orders
WHERE created_at < '2026-10-01';

Compare business-level results too: last month's revenue report, user counts by country, open orders. Equal row counts with different totals mean a conversion problem (truncated decimals, time zone shifts, character encoding).

Performance. Queries tuned for the source database's optimizer may perform differently. Capture the source's top queries, run them against PostgreSQL with EXPLAIN (ANALYZE, BUFFERS), and add missing indexes (PostgreSQL does not automatically index foreign key columns, unlike MySQL's InnoDB).

Key takeaways

• Migrations are projects: assess, convert schema and code, migrate data, validate, test, and cut over.
• Use pgloader (MySQL, SQL Server, SQLite), ora2pg (Oracle), and CDC tools (DMS, Debezium) for minimal downtime.
• Type mapping is mechanical; behavioral differences (empty string vs. NULL, case sensitivity, implicit casts) need human review.
• Validate with row counts, aggregates, checksums, and business reports compared side by side.
• Re-test performance: different optimizers, different indexing defaults, different query patterns.

🧪 Practice

1. (Beginner) Migrate a small MySQL sample database (such as Sakila) to PostgreSQL with pgloader. List the type conversions pgloader applied and check one table's row count on both sides.
2. (Intermediate) Write a validation script that, for every table, compares row counts and a numeric aggregate between source and target, and prints mismatches. Introduce a deliberate conversion error (for example, rounding a decimal) and check that the script catches it.
3. (Interview) "You are migrating an Oracle application to PostgreSQL. After the cutover, users report that searches for customers with no middle name return nothing. What is the likely cause?" Hint: Think about how Oracle stores empty strings and how the application's WHERE middle_name IS NULL behaves on each side.

Schema Migration Tooling and Versioning

Application code lives in version control, is reviewed, tested, and deployed the same way to every environment. Schema changes deserve the same treatment, but historically they were run by hand: someone pastes an ALTER TABLE into production, forgets staging, and three months later the environments no longer match. Schema migration tools store every schema change as a versioned script in the repository, record in each database which scripts have been applied, and apply missing ones in order, the same way everywhere.

Analogy. A ship's logbook for the schema. Every change is a numbered entry. Any database can tell you the last entry it applied, and bringing it up to date means applying the following entries, in order, exactly once.

How migration tools work

 repository                               database
 migrations/                              flyway_schema_history (or schema_migrations, ...)
   V1__create_customers.sql     -----+    | version | description       | checksum   | success |
   V2__create_orders.sql             +--> | 1       | create customers  | 1834912... | t       |
   V3__add_orders_status.sql         |    | 2       | create orders     | -912374... | t       |
   V4__index_orders_customer.sql  (pending: will be applied next, in order)

 "migrate": compare the files with the history table, apply the missing ones in order,
            record each one; refuse to run if an applied file's checksum changed

Common tools

Tool	Style	Language	Notes
Flyway	Versioned SQL files (V1__name.sql)	Any (JVM CLI)	Simple, SQL-first; repeatable migrations (R__) for views and functions
Liquibase	Changelogs in SQL, XML, YAML, or JSON	Any (JVM CLI)	Changesets with preconditions, rollback blocks, contexts
Sqitch	deploy/revert/verify scripts per change, dependency plan	Any (Perl CLI)	Explicit dependencies instead of linear numbering
Alembic	Python migration scripts, autogenerate from SQLAlchemy models	Python	Common with SQLAlchemy
Django, Rails, Laravel, EF Core, Prisma	Framework-native migrations generated from models	Framework language	Convenient; review the generated SQL
golang-migrate, dbmate	Numbered up/down SQL files	Any (Go CLI)	Lightweight
Atlas	Declarative desired schema, tool computes the diff	Any	"What it should look like" instead of "how to change it"

A versioned migration (Flyway)

db/migration/
  V1__create_customers.sql
  V2__create_orders.sql
  V3__add_orders_status.sql
  V4__index_orders_customer.sql
  V4__index_orders_customer.sql.conf      # executeInTransaction=false (needed for CONCURRENTLY)
  R__view_order_summary.sql               # repeatable: re-applied whenever its checksum changes

-- V3__add_orders_status.sql
SET lock_timeout = '3s';                        -- fail fast instead of blocking traffic

ALTER TABLE orders
  ADD COLUMN status text NOT NULL DEFAULT 'pending';   -- constant default: no table rewrite (PG 11+)

ALTER TABLE orders
  ADD CONSTRAINT orders_status_check
  CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')) NOT VALID;   -- no full scan under lock

ALTER TABLE orders VALIDATE CONSTRAINT orders_status_check;                  -- scans with a weaker lock

-- V4__index_orders_customer.sql  (runs outside a transaction, see the .conf file)
CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_customer_id_idx ON orders (customer_id);

flyway -url=jdbc:postgresql://db1/shop -user=migrator info      # show applied and pending
flyway -url=jdbc:postgresql://db1/shop -user=migrator migrate   # apply pending, in order

PostgreSQL's transactional DDL. Unlike MySQL and Oracle, PostgreSQL runs most DDL inside transactions, so a migration that fails halfway rolls back completely and leaves no partial schema. Migration tools wrap each script in a transaction by default. Exceptions that cannot run inside a transaction block need special handling: CREATE INDEX CONCURRENTLY, DROP INDEX CONCURRENTLY, REINDEX ... CONCURRENTLY, VACUUM, CREATE DATABASE, and ALTER SYSTEM. Put each in its own non-transactional migration, and make it idempotent (IF NOT EXISTS) because a failure leaves no automatic rollback; a failed CREATE INDEX CONCURRENTLY leaves an INVALID index that must be dropped before retrying.

Rules that keep migrations healthy

• Never edit an applied migration. The checksum check will fail, and other environments already ran the old version. Write a new migration instead.
• One logical change per migration, small and reviewable.
• Forward-only in production. Down migrations are useful in development, but rolling back a migration that dropped a column cannot bring the data back. Prefer fixing forward with a new migration and designing changes to be backward compatible (Zero-Downtime Migration Strategies).
• Separate data migrations (large backfills) from schema migrations; run them as batched jobs, not inside a deploy-blocking transaction.
• Run migrations with a dedicated role that owns the schema objects, not the application's runtime role (10.2).
• Test migrations against a production-sized copy to see lock durations and runtimes; a migration that takes 50 ms on an empty dev database can take an hour in production.
• Run migrations once per deployment, from a single job, not from every application instance at startup (or use the tool's locking, which Flyway and Liquibase provide via advisory locks or lock tables).

Squashing and baselines. After hundreds of migrations, a fresh environment replays years of history. Tools support a baseline: a single script with the current schema (generated with pg_dump --schema-only) marked as a version, from which new environments start. Existing environments keep their history.

Key takeaways

• Migration tools store versioned change scripts in the repository and record applied versions in a history table in each database.
• Flyway and Liquibase are general-purpose; Sqitch uses explicit dependencies; ORMs have built-in migration systems; Atlas is declarative.
• PostgreSQL's transactional DDL makes failed migrations roll back cleanly, except for CONCURRENTLY operations and a few others.
• Never edit applied migrations, keep changes small, fix forward, and test against production-sized data.
• Separate long data backfills from schema changes, and use lock_timeout in every migration.

🧪 Practice

1. (Beginner) Set up Flyway (or your framework's migration tool) for a sample project with three migrations: create a table, add a column, add an index concurrently. Run info and migrate, and inspect the history table.
2. (Intermediate) Change the content of an already-applied migration and run migrate again. Explain the error, how to recover properly, and why the tool refuses. Then write a migration that fails halfway and verify that PostgreSQL rolled back all of its statements.
3. (Interview) "How do you handle database schema changes in a team of 20 developers deploying several times a day?" Hint: Cover versioned migrations in the repository, code review with a checklist, CI that applies migrations to a fresh and a production-like database, backward-compatible changes, lock_timeout, and who runs migrations in production.

<a id="115-routine-operations"></a>

11.5 Routine Operations

Most production incidents are preceded by signals that were visible days in advance: a disk slowly filling, a replica drifting behind, a table bloating, an error that started appearing in the logs; this subchapter covers the routine practices that catch those signals and the processes that turn surprises into well-rehearsed responses.

Health Checks and Alerting

A database can be "up" and still unhealthy: accepting connections while a forgotten transaction blocks vacuum, while the WAL archive has been failing for hours, or while 95% of connection slots are used. Health checks answer two different questions: is the database able to serve requests right now? (liveness and readiness) and is it drifting toward a failure? (trends and saturation). Alerting turns the answers into notifications that reach a person only when a person needs to act.

Analogy. A car dashboard. The engine-failure light (an outage alert) demands that you stop immediately. The fuel gauge (a trend) tells you to plan a stop within the next hour. A dashboard where every light blinks all the time is ignored, which is worse than having no dashboard.

Liveness and readiness

pg_isready -h db1 -p 5432 -d shop -t 3
# db1:5432 - accepting connections            exit code 0
# db1:5432 - rejecting connections            exit code 1 (e.g. starting up or shutting down)
# db1:5432 - no response                      exit code 2

pg_isready checks that the server accepts connections, without authenticating. A stronger readiness check runs a trivial query as the application role and confirms the expected role (primary or standby):

SELECT 1 AS ok, pg_is_in_recovery() AS is_standby;

What to watch. Build checks from the four "golden signals" (latency, traffic, errors, saturation) plus PostgreSQL-specific risks:

Area	Check	Example warning / critical threshold
Availability	Server accepts connections; correct primary/standby role	Any failure for 1 minute
Connections	count(*) from pg_stat_activity vs. max_connections	80% / 95%
Long transactions	Oldest xact_start among active or idle-in-transaction sessions	15 min / 1 h
Transaction ID age	age(datfrozenxid) per database	500 million / 1 billion (Transaction ID Wraparound and Freezing)
Replication	Lag in bytes and seconds; standby connected; slots active	1 min / 5 min lag, inactive slot retaining WAL
WAL archiving	Time since last successful archive; failed_count growth	5 min / 15 min
Backups	Age of last successful backup and restore test	Schedule + 2 h / + 1 day
Disk	Free space on data, WAL, and log volumes; growth forecast	20% / 10% free, or full within 7 days
Errors	Rate of ERROR/FATAL/PANIC, deadlocks, checksum failures	Any PANIC or checksum failure is critical
Performance	Query latency percentiles, pg_stat_statements mean time changes	Relative to baseline
Maintenance	Tables with many dead tuples; time since last autovacuum	Workload dependent

Health check queries

-- Connection saturation
SELECT count(*) AS connections,
       current_setting('max_connections')::int AS max_connections,
       round(100.0 * count(*) / current_setting('max_connections')::int, 1) AS pct_used
FROM pg_stat_activity;

-- Oldest open transaction (holds back vacuum everywhere)
SELECT pid, usename, application_name, state,
       now() - xact_start AS xact_age, left(query, 60) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 5;

-- Wraparound distance
SELECT datname, age(datfrozenxid) AS xid_age,
       round(100.0 * age(datfrozenxid) / 2147483647, 1) AS pct_to_wraparound
FROM pg_database
ORDER BY xid_age DESC;

-- Replication from the primary's point of view
SELECT application_name, state, sync_state,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS replay_lag_bytes,
       replay_lag
FROM pg_stat_replication;

-- Replication slots that keep WAL around
SELECT slot_name, active, wal_status,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;

-- Data checksum failures (PG 12+)
SELECT datname, checksum_failures, checksum_last_failure
FROM pg_stat_database
WHERE checksum_failures > 0;

Run monitoring with a dedicated role that has pg_monitor, which grants read access to all statistics views without superuser.

Tooling. A typical open-source stack is postgres_exporter (exposes metrics) + Prometheus (stores and evaluates) + Alertmanager (routes notifications) + Grafana (dashboards). Alternatives include Datadog, pganalyze, Percona Monitoring and Management, Zabbix, and check_postgres for Nagios-style systems. Metric names depend on the exporter version and any custom queries you configure.

# prometheus-rules.yml (metric names as exposed by postgres_exporter)
groups:
  - name: postgresql
    rules:
      - alert: PostgresDown
        expr: pg_up == 0
        for: 1m
        labels: { severity: critical }
        annotations:
          summary: "PostgreSQL {{ $labels.instance }} is not reachable"
          runbook: "https://wiki.example.com/runbooks/postgres-down"

      - alert: PostgresConnectionsHigh
        expr: sum by (instance) (pg_stat_activity_count) / on (instance) pg_settings_max_connections > 0.8
        for: 10m
        labels: { severity: warning }
        annotations:
          summary: "{{ $labels.instance }} uses more than 80% of max_connections"
          runbook: "https://wiki.example.com/runbooks/postgres-connections"

Alerting principles

• Alert on symptoms that need action; graph everything else. A cache hit ratio drop is a dashboard item; "archive failing for 15 minutes" is a page.
• Two severities are usually enough. Critical wakes someone up (outage or imminent data loss). Warning creates a ticket for business hours.
• Every alert links to a runbook that says what it means and what to do (Runbooks and Incident Response).
• Use for: durations to ignore momentary spikes, and forecasts (predict_linear in Prometheus) for slowly filling resources.
• Review alerts regularly. An alert that fired ten times last month without anyone acting should be fixed, retuned, or deleted.
• Monitor the monitoring. Alert when metrics stop arriving; a silent exporter looks exactly like a healthy database.

Key takeaways

• Distinguish liveness (accepting connections), readiness (can serve the application, correct role), and trend checks (drifting toward failure).
• Watch connections, long transactions, XID age, replication, archiving, backups, disk, errors, and performance.
• Use a pg_monitor role and an exporter-based stack or equivalent; dashboards for everything, alerts only for actionable conditions.
• Two severity levels, for: durations, forecasts, and a runbook link on every alert keep alerting useful.
• Monitor the monitoring system itself.

🧪 Practice

1. (Beginner) Run each health check query above on a test server. Then open a transaction in another session, leave it idle, and watch it appear in the "oldest open transaction" query.
2. (Intermediate) Deploy postgres_exporter, Prometheus, and Grafana with Docker Compose against a test database. Create the two example alerts plus one for WAL archiving age, and trigger each one deliberately.
3. (Interview) "What are the five most important alerts for a production PostgreSQL primary, and why each?" Hint: Prioritize conditions that lead to outages or data loss: availability, disk space (data and WAL), replication/archiving failures, XID wraparound, and connection saturation or long-running transactions.

Log Analysis with pgBadger

PostgreSQL logs contain a wealth of information: every slow query, every lock wait, every checkpoint, every connection, every error. But a busy server writes gigabytes of log lines a day, and nobody can read them. pgBadger is a fast log analyzer that parses PostgreSQL logs and produces an HTML report with charts and rankings: the slowest and most frequent queries (normalized so that WHERE id = 7 and WHERE id = 9 count as the same query), error summaries, lock waits, temporary files, checkpoint behavior, connection patterns, and more.

Analogy. Bank statements versus a budgeting app. The statement lists every transaction, which is accurate but unreadable after a few pages. The budgeting app groups them into categories and shows where the money really goes. pgBadger is the budgeting app for your database's time.

pgBadger versus pg_stat_statements

Aspect	pgBadger	pg_stat_statements (pg_stat_statements)
Source	Log files	Shared memory counters
Coverage	Only what is logged (e.g. queries above the threshold)	Every statement, aggregated
History	As long as you keep logs; per-hour charts	Cumulative since last reset
Extra information	Errors, locks, temp files, checkpoints, connections, sessions, autovacuum	Query statistics only
Example values	Actual parameter values of slow executions	Normalized query text only
Overhead	Logging cost on the server, analysis offline	Small, always on

They complement each other: pg_stat_statements for continuous totals, pgBadger for history, examples, and everything that is not a query statistic.

Configuring logs for pgBadger. pgBadger can only report what the server logged, and it needs to parse each line's prefix:

# Minimum useful configuration
log_line_prefix = '%t [%p]: user=%u,db=%d,app=%a,client=%h '   # a format pgBadger recognizes by default
log_min_duration_statement = 250ms     # 0 logs everything: best report, highest log volume
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0
log_autovacuum_min_duration = 0
log_error_verbosity = default
lc_messages = 'C'                       # pgBadger parses English messages

Do not use log_statement = 'all' together with log_min_duration_statement, since statements and durations would be logged on separate lines and the report becomes less accurate. Structured csvlog or jsonlog output is also supported and avoids prefix parsing entirely (-f csv, -f jsonlog).

Running pgBadger

# One-off report from a day of logs, using 4 CPU cores
pgbadger -j 4 /var/log/postgresql/postgresql-17-main.log -o /var/www/reports/pg-2026-10-06.html

# Custom prefix (if you did not use the default-compatible one above)
pgbadger --prefix '%m [%p] %q%u@%d app=%a client=%h ' postgresql.log -o report.html

# Incremental mode: run hourly from cron, builds daily and weekly reports, parses only new data
pgbadger -I -O /var/www/pgbadger -j 4 /var/log/postgresql/postgresql-17-main.log*

# Logs from a remote server over SSH, or from a cloud provider's downloaded log files
pgbadger -r db1.example.com /var/log/postgresql/postgresql-17-main.log -o report.html

Reading the report. The most useful sections:

Section	Question it answers	Typical action
Time-consuming queries	Which normalized queries consume the most total time?	Optimize the top few; often the best return on effort
Most frequent queries	What runs millions of times?	Caching, batching, N+1 query fixes
Slowest individual queries	Which single executions were worst, with real parameters?	Reproduce with EXPLAIN ANALYZE using the logged values
Temporary files	Which queries spill to disk, and how much?	Raise work_mem for that role, add indexes, rewrite
Locks	Which queries waited for locks, and for how long?	Shorter transactions, better migration practice (Lock Monitoring and Blocking Queries)
Checkpoints	Frequency, duration, WAL-triggered checkpoints	Tune max_wal_size, checkpoint_timeout
Errors and events	Most common errors, FATAL connection failures	Fix application bugs, authentication issues
Connections and sessions	Connections per second, session durations, by user/app/host	Introduce or tune connection pooling
Autovacuum	Which tables are vacuumed most, and how long it takes	Per-table autovacuum settings

A weekly review routine

 1. Open the weekly pgBadger report
 2. Top 5 time-consuming queries: anything new or growing compared with last week?
 3. Errors: new error messages or spikes?
 4. Temp files and lock waits: new offenders?
 5. Checkpoints: any WAL-triggered checkpoints at peak?
 6. File tickets for findings, with the report link and the normalized query

A short, regular review catches regressions from releases within days instead of when users complain.

Key takeaways

• pgBadger turns raw PostgreSQL logs into an HTML report of slow and frequent queries, errors, locks, temp files, checkpoints, and connections.
• It complements pg_stat_statements: logs give history, real parameter values, and non-query events.
• Configure log_line_prefix, log_min_duration_statement, and the event logging parameters deliberately, with lc_messages = 'C'.
• Use incremental mode (-I -O) on a schedule to build daily and weekly reports automatically.
• Review the report regularly; the "time-consuming queries" section usually offers the biggest wins.

🧪 Practice

1. (Beginner) Configure the logging parameters above on a test server, run pgbench for 10 minutes with log_min_duration_statement = 0, and generate a pgBadger report. Identify the most time-consuming query.
2. (Intermediate) Set up incremental pgBadger reports from cron every hour. Add a deliberately slow query and a query that creates temp files to the workload, and find both in the next report.
3. (Interview) "Your team has pg_stat_statements. Why would you still set up pgBadger?" Hint: Compare what each source captures: history over time, actual parameter values, errors, locks, temp files, checkpoints, connection patterns.

Disk Space Management

Running out of disk space is one of the most common and most damaging PostgreSQL incidents. When the WAL volume fills, PostgreSQL cannot write WAL, panics, and shuts down; when the data volume fills, inserts and updates fail. Recovery can be slow and risky, especially if someone panics and deletes "old log files" from pg_wal. Disk space management means knowing what uses the space, forecasting growth, alerting early, and knowing the safe emergency moves.

Analogy. A warehouse. Goods (table data) arrive every day, packaging (bloat) piles up, and the loading dock (WAL) must always have room for incoming trucks. If the dock is blocked, the whole warehouse stops, even if the shelves have space.

What consumes disk space

 $PGDATA
 |-- base/            table and index files (per database), including TOAST and bloat
 |-- pg_wal/          write-ahead log: normally ~max_wal_size, but grows when WAL cannot be removed
 |-- pg_xact/, pg_multixact/   transaction status (small unless wraparound is near)
 |-- log/             server logs (if logging_collector writes here)
 |-- base/pgsql_tmp/  temporary files from sorts and hashes (can grow fast)
 tablespaces/          other volumes, if used

Measuring

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

-- Largest tables, split into table, indexes, and TOAST
SELECT c.oid::regclass AS table_name,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS total,
       pg_size_pretty(pg_relation_size(c.oid))       AS heap,
       pg_size_pretty(pg_indexes_size(c.oid))        AS indexes,
       pg_size_pretty(pg_total_relation_size(c.oid) - pg_relation_size(c.oid) - pg_indexes_size(c.oid)) AS toast_and_other
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'm') AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(c.oid) DESC
LIMIT 10;

-- WAL directory size and temporary files in use right now
SELECT pg_size_pretty(sum(size)) AS pg_wal_size FROM pg_ls_waldir();
SELECT pg_size_pretty(coalesce(sum(size), 0)) AS temp_in_use FROM pg_ls_tmpdir();

At the operating-system level, df -h for each volume and du -sh $PGDATA/* show the same picture from outside.

Why pg_wal grows beyond normal. WAL segments are removed or recycled after a checkpoint, unless something still needs them:

Cause	How to see it	Fix
Failing archive_command	pg_stat_archiver.failed_count rising, .ready files piling up	Fix the archive destination or command (WAL Archiving)
Inactive or lagging replication slot	pg_replication_slots with active = false or large retained WAL	Reconnect the consumer, or drop the slot if it is abandoned
Large wal_keep_size	SHOW wal_keep_size	Reduce to what standbys actually need
Very high write rate	pg_stat_wal, WAL-triggered checkpoints	Size the volume for peaks; tune checkpoints

Since PostgreSQL 13, max_slot_wal_keep_size caps how much WAL a slot may retain. When exceeded, the slot is invalidated (its consumer must be rebuilt) instead of filling the disk. For most primaries, a cap is the safer failure mode:

max_slot_wal_keep_size = 100GB

Emergency: the disk is full. Work through these steps in order and stay calm:

 1. DO NOT delete files in pg_wal, pg_xact, or base/ by hand. DO NOT run pg_resetwal.
    These turn an outage into data loss.
 2. Find what is consuming space (df, du; pg_ls_waldir if the server still runs).
 3. Free space that is safe to free:
      - old server logs, core dumps, unrelated files on the same volume
      - a reserved "ballast" file created in advance for this purpose (e.g. 10 GB)
 4. Remove the cause:
      - fix the archive_command so the backlog drains
      - drop an abandoned replication slot: SELECT pg_drop_replication_slot('old_slot');
      - cancel the query producing huge temp files
 5. If the server stopped, extend the volume (cloud volumes can usually grow online)
    and start it; crash recovery runs normally.
 6. After recovery: CHECKPOINT; confirm pg_wal shrinks; write the incident report.

Reclaiming space from tables. DELETE and regular VACUUM make space reusable inside the table but rarely return it to the operating system. To shrink files:

Method	Lock	Notes
VACUUM FULL	ACCESS EXCLUSIVE for the whole run	Rewrites the table; needs free space for a full copy (VACUUM vs. VACUUM FULL)
pg_repack extension	Brief exclusive locks at start and end	Online rewrite; also needs space for a copy
REINDEX CONCURRENTLY	Weak lock	Shrinks bloated indexes
Partition drop or detach	Brief lock	Removing old data by dropping partitions is instant and frees space immediately (6.4)
TRUNCATE	ACCESS EXCLUSIVE	Instant, for tables you can empty completely

Forecasting. Record database and volume sizes daily and project forward. Alert on "full within N days" rather than only on a fixed percentage, which can fire too late on a fast-growing volume and too early on a stable one. See Capacity and Growth Monitoring for growth queries.

Preventive design

• Separate volumes for data, WAL, and logs, so a log flood cannot stop WAL writes.
• temp_file_limit to cap runaway queries, max_slot_wal_keep_size to cap abandoned slots.
• Partition large time-series tables so retention is a DROP instead of a massive DELETE.
• Keep at least 20 to 30% free space: VACUUM FULL, pg_repack, index rebuilds, and major upgrades in copy mode all need temporary room.

Key takeaways

• Space goes to tables, indexes, TOAST, bloat, WAL, temporary files, and logs; measure each with pg_*_size functions and pg_ls_waldir().
• pg_wal grows when WAL cannot be removed: failing archiving, inactive replication slots, or large wal_keep_size.
• Never delete files from pg_wal or other internal directories; fix the cause, free unrelated space, or grow the volume.
• VACUUM reuses space inside tables; returning it to the OS needs VACUUM FULL, pg_repack, REINDEX, or dropping partitions.
• Forecast growth, alert on time-to-full, separate volumes, and cap temp files and slot retention.

🧪 Practice

1. (Beginner) List the ten largest tables in a database with heap, index, and TOAST sizes. Then compare the total with pg_database_size() and du -sh of the data directory, and explain the differences.
2. (Intermediate) On a test server, create a logical replication slot with pg_create_logical_replication_slot('test_slot', 'pgoutput') without consuming it, generate WAL with pgbench, and watch pg_wal grow. Set max_slot_wal_keep_size to a small value and observe the slot's wal_status change.
3. (Interview) "At 2 a.m. you are paged: the database volume is 100% full and PostgreSQL has shut down. What do you do, and what must you never do?" Hint: Identify what grew, free safe space or extend the volume, fix the cause (archiving, slots, temp files), start the server and let recovery run; never delete WAL files or run pg_resetwal.

Index and Table Maintenance Schedules

Autovacuum handles most routine maintenance automatically, and for many databases it is enough. But some tasks fall outside what autovacuum does or does well: indexes slowly bloat, unused indexes waste space and slow down writes, very large append-only tables are analyzed too rarely, partitions need creating and dropping, and statistics after bulk loads lag behind. A maintenance schedule lists these tasks, how often each runs, how it runs, and who checks the results, so maintenance is planned rather than done reactively during incidents.

Analogy. Car maintenance. The car checks some things itself while driving (autovacuum), but you still schedule oil changes, tire rotations, and annual inspections. Skipping them does not cause a problem today; it causes a breakdown in a few months.

What autovacuum does and does not do

Autovacuum does	Autovacuum does not
Removes dead tuples so space is reused	Shrink tables or indexes on disk
Updates planner statistics (autoanalyze)	Analyze parent partitioned tables (inheritance statistics)
Freezes old tuples to prevent wraparound	Rebuild bloated indexes
Updates the visibility map for index-only scans	Remove unused or duplicate indexes
	Create or drop partitions
	Run at a time you choose (it runs whenever thresholds are crossed)

Autovacuum tuning itself is covered in Autovacuum Configuration and Tuning.

A typical schedule

Task	Frequency	How	Why
Review autovacuum activity and dead tuples	Weekly	pg_stat_user_tables query, pgBadger autovacuum section	Find tables that need per-table settings
VACUUM (ANALYZE) of large, busy tables at off-peak	Nightly (optional)	pg_cron or cron with vacuumdb	Reduce daytime autovacuum work; keep visibility map fresh
ANALYZE of partitioned parents	Daily or after bulk loads	ANALYZE parent_table;	Autovacuum does not analyze partitioned parents
Index bloat check	Weekly or monthly	Bloat estimation queries, pgstattuple	Find indexes worth rebuilding (Table and Index Bloat Measurement)
REINDEX CONCURRENTLY of bloated indexes	As found, off-peak	reindexdb --concurrently or SQL	Restore index size and performance
Unused and duplicate index review	Quarterly	pg_stat_user_indexes on primary and replicas	Save space and write overhead
Partition creation and retention	Daily	pg_partman or a scheduled procedure	Future partitions exist; old ones are dropped
XID age review	Weekly	age(datfrozenxid), age(relfrozenxid)	Catch tables that autovacuum cannot freeze in time
pg_stat_statements snapshot and reset	Weekly or monthly	Copy to a history table, then reset	Compare periods
Integrity check	Monthly	pg_amcheck on a restored backup or standby	Detect corruption early

Scheduling inside the database with pg_cron. The pg_cron extension runs SQL on a cron schedule inside PostgreSQL. It is available on most managed services, which makes it the portable choice:

-- postgresql.conf: shared_preload_libraries = 'pg_cron'; cron.database_name = 'shop'
CREATE EXTENSION pg_cron;

-- Nightly vacuum and analyze of the busiest table at 03:15 UTC
SELECT cron.schedule('nightly-vacuum-events', '15 3 * * *',
                     $$VACUUM (ANALYZE) public.events$$);

-- Analyze partitioned parents every morning
SELECT cron.schedule('analyze-partitioned-parents', '0 5 * * *',
                     $$ANALYZE public.measurements; ANALYZE public.audit_log$$);

-- Partition maintenance with pg_partman every hour
SELECT cron.schedule('partman-maintenance', '0 * * * *',
                     $$CALL partman.run_maintenance_proc()$$);

-- Check results
SELECT jobid, jobname, schedule, active FROM cron.job;
SELECT jobid, status, return_message, start_time, end_time
FROM cron.job_run_details
ORDER BY start_time DESC
LIMIT 10;

Scheduling from the operating system. The client tools vacuumdb and reindexdb are convenient for cron or systemd timers:

# /etc/cron.d/postgres-maintenance
# Analyze all databases in parallel every night at 02:30
30 2 * * *  postgres  vacuumdb --all --analyze-only --jobs=4 --quiet
# Rebuild one known-bloating index every Sunday at 04:00 without blocking writes
0 4 * * 0   postgres  reindexdb --dbname=shop --index=public.events_created_at_idx --concurrently

Finding unused indexes. Every index slows down writes and uses space and cache. Indexes with no scans are candidates for removal:

SELECT s.schemaname, s.relname AS table_name, s.indexrelname AS index_name,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size,
       s.idx_scan
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique              -- unique indexes enforce constraints even if never scanned
  AND NOT i.indisprimary
ORDER BY pg_relation_size(s.indexrelid) DESC;

Before dropping, check the same statistics on every replica (index usage is tracked per server, and reporting queries often run only on replicas), check when statistics were last reset (pg_stat_database.stats_reset), and drop with DROP INDEX CONCURRENTLY. Keep the definition (pg_get_indexdef(indexrelid)) so you can recreate it quickly if needed.

Guidelines

• Schedule heavy tasks off-peak, one at a time, and with lock_timeout where they take locks.
• Every scheduled job must report failures: check cron.job_run_details or cron mail, and alert on failures or missing runs.
• Prefer CONCURRENTLY variants on production (REINDEX, CREATE INDEX, DROP INDEX).
• Treat the schedule as code: store it in the repository and deploy it with the rest of the configuration.
• Revisit the schedule when the workload changes; a table that grew tenfold needs different treatment.

Key takeaways

• Autovacuum covers dead tuples, statistics, and freezing, but not index bloat, unused indexes, partitioned parent statistics, or partition lifecycle.
• A written schedule lists each task, its frequency, method, and owner.
• pg_cron schedules SQL inside the database (portable to managed services); vacuumdb and reindexdb suit OS-level schedulers.
• Find unused indexes with pg_stat_user_indexes, but check replicas and the stats reset time before dropping.
• Monitor scheduled jobs for failures; a silently failing maintenance job is worse than none.

🧪 Practice

1. (Beginner) Install pg_cron on a test server, schedule a nightly VACUUM (ANALYZE) of one table, temporarily change it to run every minute, and inspect cron.job_run_details.
2. (Intermediate) Write a maintenance schedule for a database with a 2 TB append-only events table partitioned by day (90-day retention), a heavily updated sessions table, and a 50 GB orders table. Justify each task and frequency.
3. (Interview) "Autovacuum is enabled and working. Why would you still need scheduled maintenance?" Hint: List what autovacuum cannot do: shrink files, rebuild bloated indexes, analyze partitioned parents, manage partitions, remove unused indexes, and run at times you control.

Runbooks and Incident Response

When an alert fires at 3 a.m., the person on call may be tired, new to the team, or not the database expert. Under that pressure, people skip steps, guess, and make things worse. A runbook is a written, tested procedure for a specific situation: how to recognize it, how to assess impact, what to do, and when to escalate. Incident response is the broader process around it: who leads, how the team communicates, and how the organization learns afterwards.

Analogy. Airline pilots use checklists for engine failures, not because they do not know how to fly, but because under stress even experts forget steps. The checklist is short, ordered, and practiced in simulators.

Anatomy of a runbook

 Title:       PostgreSQL: connections exhausted
 Alert:       PostgresConnectionsHigh / "too many clients already" errors
 Severity:    Critical when > 95% or connection errors observed
 Impact:      New requests fail; existing sessions continue
 Owner:       Database team; escalate to @db-oncall after 15 min

 1. Confirm
 2. Assess impact
 3. Mitigate (stop the bleeding)
 4. Find the cause
 5. Fix, or hand over to the owning team
 6. Verify recovery
 7. Follow-up: incident ticket, postmortem if critical

Example runbook: connections exhausted

-- 1. Confirm: how many connections, and who holds them?
SELECT usename, application_name, client_addr, state, count(*)
FROM pg_stat_activity
GROUP BY 1, 2, 3, 4
ORDER BY count(*) DESC
LIMIT 15;

-- 2. Assess: many 'idle' connections suggest a pool size or leak problem;
--    many 'active' suggests slow queries; many 'idle in transaction' suggests an application bug
SELECT state, count(*), max(now() - state_change) AS longest_in_state
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state;

-- 3a. Mitigate: terminate sessions idle in transaction for more than 10 minutes
SELECT pid, usename, application_name, pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - state_change > interval '10 minutes';

-- 3b. Mitigate: cancel (not terminate) long-running queries from one misbehaving service
SELECT pid, pg_cancel_backend(pid)
FROM pg_stat_activity
WHERE application_name = 'report-exporter' AND state = 'active'
  AND now() - query_start > interval '5 minutes';

pg_cancel_backend stops the current query and keeps the session; pg_terminate_backend closes the session and rolls back its transaction. Prefer the gentler option first. Reserved connection slots (superuser_reserved_connections, reserved_connections) are what allow you to log in at all during this incident (Resource Limit Parameters).

Runbooks every PostgreSQL team should have

Runbook	Key first steps
Primary down / failover	Confirm from several vantage points, check the HA tool's state, promote or let automation act, repoint clients (12.3)
Connections exhausted	Group sessions by state and application; terminate idle-in-transaction; fix pool sizes
Replication lag or broken standby	Check pg_stat_replication, standby logs, network; decide rebuild vs. wait
Disk filling or full	Identify the consumer; never delete WAL; fix archiving or slots; extend volume (Disk Space Management)
WAL archiving failing	Read the archive error in the log; check destination and credentials; watch the backlog drain
Blocking locks / lock pile-up	Find the root blocker with pg_blocking_pids; cancel or terminate it (Detecting Lock Contention)
Sudden slowness	Compare pg_stat_statements with baseline; check plans, waits, I/O, recent deploys
XID wraparound warning	Find the oldest relfrozenxid; remove blockers (old transactions, slots, prepared transactions); run VACUUM (FREEZE)
Accidental data deletion	Stop further damage; PITR to a separate server; copy back rows (Continuous Archiving and PITR)
Corruption / checksum failure	Stop writes if spreading; identify objects; fail over to a clean standby or restore

Incident response roles. For anything beyond a quick fix, separate the roles so that one person is not simultaneously debugging, deciding, and answering questions:

Role	Responsibility
Incident commander	Coordinates, makes decisions, sets priorities; does not debug
Operations lead	Investigates and executes changes, following runbooks
Communications lead	Updates stakeholders and the status page at a fixed cadence
Scribe	Records a timeline of observations, decisions, and actions

Severity levels

Level	Definition	Response
SEV1	Outage or data loss affecting customers	Page immediately, all hands, updates every 15–30 min, postmortem required
SEV2	Significant degradation or high risk of SEV1	Page on-call, updates hourly, postmortem usually
SEV3	Minor impact, workaround exists	Business hours, ticket

During the incident

• Mitigate first, find the root cause later. Restoring service (failover, cancel a query, roll back a deploy) comes before understanding.
• Change one thing at a time and write it in the timeline with the exact time.
• Preserve evidence: save pg_stat_activity, pg_locks, and log excerpts before terminating sessions or restarting.
• Prefer reversible actions; announce irreversible ones (dropping a slot, promoting a standby) before doing them.

After the incident: blameless postmortem

 Summary          what happened, impact, duration
 Timeline         detection, key decisions, mitigation, resolution (UTC)
 Root cause(s)    technical and process causes; use "5 whys"
 What went well   detection, runbooks, tools that helped
 What went badly  missing alerts, unclear runbooks, slow access
 Action items     each with an owner and a due date: alerts, runbook updates, design fixes

"Blameless" means the analysis assumes people acted reasonably with the information they had, and asks why the system allowed the failure. People report problems honestly only when doing so is safe.

Key takeaways

• A runbook gives a specific, ordered procedure: confirm, assess, mitigate, diagnose, fix, verify, follow up.
• Cover the common PostgreSQL emergencies: failover, connections, replication, disk, archiving, locks, slowness, wraparound, deletion, corruption.
• Prefer pg_cancel_backend before pg_terminate_backend; keep reserved connections for administrators.
• Separate incident roles, mitigate before root-causing, log every action with a timestamp, and preserve evidence.
• Hold blameless postmortems with owned action items, and update runbooks after every use.

🧪 Practice

1. (Beginner) Write a runbook for "WAL archiving is failing" using the anatomy above, including the exact queries and log messages to check.
2. (Intermediate) Run a tabletop exercise with a colleague: one person plays the incident ("disk at 97% and rising 1% per minute"), the other follows only your runbook. Record where the runbook was unclear and revise it.
3. (Interview) "Tell me about how you would handle a production database incident where queries suddenly became ten times slower." Hint: Structure the answer: confirm and assess impact, check recent changes (deploys, migrations, statistics), compare pg_stat_statements with baseline, look at waits and locks, mitigate (roll back, cancel, add capacity), then root-cause and follow up.

Change Management for Schemas

Schema changes are among the riskiest changes in a software system. A single ALTER TABLE can lock a busy table for minutes, rewrite a terabyte, break every running instance of an application that still expects the old column, or silently change query plans. Unlike application code, schema changes are hard to roll back once data has been written in the new shape. Change management is the set of practices that decides how schema changes are proposed, reviewed, tested, scheduled, executed, and audited, so that risk is matched with the right amount of care.

Analogy. Renovations in an occupied apartment building. Painting a wall inside your own flat needs no permit. Moving a load-bearing wall needs plans reviewed by an engineer, a scheduled date, notices to the neighbors, and an inspector afterwards. A good building manager knows which is which and does not require a permit to hang a picture.

The change lifecycle

 propose  --> migration file in a pull request, with a description of intent and risk
 review   --> peer review + automated checks (lint, apply to a test database)
 test     --> apply to staging with production-like data volume; measure duration and locks
 schedule --> low-risk: any deploy; high-risk: agreed window, people available, rollback ready
 execute  --> via the migration tool, never by hand; lock_timeout set; monitoring watched
 verify   --> schema matches the repository; application healthy; no lock or lag alarms
 record   --> history table in the database, PR link, change log entry

Classifying risk. Most of the value of change management comes from treating risky changes differently from routine ones. A classification guide for reviewers:

Operation	Lock taken	Duration	Risk	Safer alternative
CREATE TABLE, CREATE VIEW	None on existing tables	Instant	Low
ADD COLUMN (nullable, or constant default)	ACCESS EXCLUSIVE	Instant (PG 11+)	Low*
ADD COLUMN with volatile default (DEFAULT random())	ACCESS EXCLUSIVE	Full rewrite	High	Add nullable, backfill in batches
CREATE INDEX	SHARE (blocks writes)	Proportional to size	High	CREATE INDEX CONCURRENTLY
ADD FOREIGN KEY	SHARE ROW EXCLUSIVE on both tables	Full validation scan	High	NOT VALID, then VALIDATE CONSTRAINT
ADD CHECK constraint	ACCESS EXCLUSIVE	Full scan	High	NOT VALID, then VALIDATE CONSTRAINT
SET NOT NULL	ACCESS EXCLUSIVE	Full scan	High	Valid CHECK (col IS NOT NULL) first (PG 12+ then skips the scan)
ALTER COLUMN TYPE (most changes)	ACCESS EXCLUSIVE	Full rewrite + index rebuilds	Very high	New column, sync, backfill, switch
DROP COLUMN	ACCESS EXCLUSIVE	Instant	Medium (app compatibility)	Stop using it in code first, then drop
RENAME COLUMN / RENAME TABLE	ACCESS EXCLUSIVE	Instant	High (app compatibility)	Expand and contract, or a view
DROP TABLE	ACCESS EXCLUSIVE	Instant	Very high (irreversible)	Rename first, drop after a waiting period; confirm backups

* "Instant" operations that need ACCESS EXCLUSIVE still wait for every running query on the table to finish, and every new query queues behind them while they wait. Without lock_timeout, a one-millisecond ADD COLUMN can block all traffic for as long as the longest running query. Mechanics and patterns are covered in Lock-Free Migration Patterns and Zero-Downtime Migration Strategies.

Automated review. Linters catch dangerous patterns before a human reviewer sees them. squawk is a PostgreSQL migration linter that flags, for example, non-concurrent index creation, adding constraints without NOT VALID, and type changes:

squawk migrations/V42__add_orders_index.sql

migrations/V42__add_orders_index.sql:1:0: warning: require-concurrent-index-creation

   1 | CREATE INDEX orders_status_idx ON orders (status);

  note: Creating an index blocks writes.
  help: Create the index CONCURRENTLY.

A review checklist for migration pull requests

 [ ] One logical change; clear description of why
 [ ] Risk class identified (low / high / very high) from the table above
 [ ] lock_timeout set; CONCURRENTLY / NOT VALID used where applicable
 [ ] Backward compatible with the currently deployed application version
 [ ] Duration measured on production-sized data (attach numbers)
 [ ] Large data changes split into a separate, batched job
 [ ] Rollback or fix-forward plan written down
 [ ] Grants, RLS policies, and ownership correct for new objects (10.2, 10.3)
 [ ] Indexes for new foreign keys and new query patterns
 [ ] Linter passes; migration applied cleanly in CI from an empty database and from a production snapshot

Detecting drift. Even with good tooling, someone eventually changes production by hand during an incident. Drift detection compares the real schema with what the repository says it should be:

# In CI or nightly: build the expected schema from migrations, dump both, and compare
pg_dump --schema-only --no-owner --no-privileges -d "$PROD_READONLY_URL" > actual.sql
pg_dump --schema-only --no-owner --no-privileges -d "$CI_FRESH_DB_URL"   > expected.sql
diff -u expected.sql actual.sql && echo "no drift"

Any difference becomes either a new migration (to make the repository match a justified emergency change) or a fix to production.

Auditing schema changes. Keep a trail of who changed what, independent of the migration tool:

• log_statement = 'ddl' records every DDL statement with user and time in the server log (Logging Configuration).
• An event trigger on ddl_command_end can write DDL events to an audit table (Triggers and Event Handling).
• pgaudit's ddl class provides structured audit records for compliance (Auditing with pgaudit).
• Only the migration role should own schema objects; developers and applications should not have DDL rights in production (10.2).

Scheduling and communication. Low-risk changes go out with normal deployments. High-risk changes get an agreed window at low traffic, a named executor and a reviewer present, monitoring on screen (locks, replication lag, error rates), and a notice to teams that depend on the database (analytics, CDC consumers, which can break on renamed columns).

Key takeaways

• Schema changes follow a lifecycle: propose, review, test, schedule, execute, verify, record.
• Classify risk by lock type, duration, and application compatibility; most changes are low risk, a few deserve real ceremony.
• Use linters (squawk) and a review checklist; always set lock_timeout and test on production-sized data.
• Detect drift by comparing the production schema with one built from the repository; audit DDL with logs, event triggers, or pgaudit.
• Restrict DDL rights to the migration role, and communicate high-risk changes to downstream consumers.

🧪 Practice

1. (Beginner) Classify these changes by risk using the table: adding a nullable column, creating an index on a 200 GB table, renaming a column used by three services, adding a foreign key between two large tables. Rewrite the risky ones in their safer form.
2. (Intermediate) Set up a CI job that applies all migrations to an empty database, runs squawk on new migration files, and compares the resulting schema with a schema-only dump of a staging database, failing on any difference.
3. (Interview) "A one-line migration that added a column caused a 4-minute outage. The column add itself is instant. How is that possible, and what process changes would you introduce?" Hint: Think about lock queues behind a long-running query, lock_timeout, retries, testing under realistic load, and review checklists.

<a id="12-replication-and-high-availability"></a>