How to fix FATAL: the database system is in recovery mode in PostgreSQL

First find out whether this is normal crash recovery, standby replay, or a stuck/failing recovery. The server log tells you everything.

# Debian/Ubuntu package logs
tail -f /var/log/postgresql/postgresql-*.log

# Or, if PostgreSQL runs under systemd
sudo journalctl -u postgresql -f

Normal crash recovery looks like this and ends on its own:

LOG:  database system was not properly shut down; automatic recovery in progress
LOG:  redo starts at 0/A000028
LOG:  redo done at 0/A3F1180
LOG:  database system is ready to accept connections

A standby looks like this and stays in recovery by design:

LOG:  entering standby mode
LOG:  consistent recovery state reached
LOG:  database system is ready to accept read-only connections

If instead you see repeated errors about a missing WAL segment, a checksum/redo failure, or no progress at all, jump to the disk-space and WAL steps below.

Crash recovery runs automatically on startup and you normally do nothing but wait. Do not restart the server repeatedly - each restart can force recovery to start over and lengthen the outage.

Watch progress in the log:

tail -f /var/log/postgresql/postgresql-*.log | grep -E "redo|recovery|ready"

Once the server accepts connections, confirm its state:

SELECT pg_is_in_recovery();
-- false  => recovery finished, this node is a normal read/write primary
-- true   => still recovering, OR this node is a standby (see next step)

Recovery time scales with the volume of WAL generated since the last checkpoint, not directly with database size, so it is usually short. Increasing the gap between checkpoints (max_wal_size, checkpoint_timeout) trades faster steady-state writes for longer crash recovery.

If pg_is_in_recovery() returns true even after startup completes, this node is almost certainly a streaming standby. Standbys serve read-only traffic only; sending writes there will always fail.

Use the correct, version-appropriate functions. The WAL/LSN functions were renamed in PostgreSQL 10 (the old xlog/location names were removed):

-- PostgreSQL 10 and newer
SELECT
  CASE WHEN pg_is_in_recovery() THEN 'STANDBY' ELSE 'PRIMARY' END AS role,
  pg_is_wal_replay_paused()        AS replay_paused,
  pg_last_wal_receive_lsn()        AS receive_lsn,
  pg_last_wal_replay_lsn()         AS replay_lsn;

-- PostgreSQL 9.6 and older only (these names were REMOVED in PG 10)
SELECT
  pg_is_in_recovery()              AS in_recovery,
  pg_last_xlog_receive_location()  AS receive_location,
  pg_last_xlog_replay_location()   AS replay_location;

If you are on a standby and need to write:

1. Point your application at the primary for writes, or
2. Promote this standby intentionally (planned failover). Promotion is a deliberate action - make sure the primary is truly gone or fenced first:

# From the shell (adjust the data directory to your version/install)
sudo -u postgres pg_ctl promote -D /var/lib/postgresql/16/main

-- Or from SQL on PostgreSQL 12+
SELECT pg_promote();

A full disk stalls checkpoints and recovery. Check free space on the filesystem holding the data directory:

df -h /var/lib/postgresql/
du -sh /var/lib/postgresql/16/main/

Never manually delete files from pg_wal/ (or the older pg_xlog/) to free space. Those segments may be needed for crash recovery or by a standby/replication slot, and removing one that has not been safely recycled will corrupt the cluster and cause data loss.

To free space safely:

# 1) Reclaim space elsewhere on the volume first (old server logs, temp files, etc.)
sudo find /var/log/postgresql/ -name '*.log.*' -mtime +7 -delete

# 2) Let PostgreSQL recycle WAL itself. Once it can start, a CHECKPOINT plus a
#    lower max_wal_size lets the server trim WAL on its own:
sudo -u postgres psql -c "CHECKPOINT;"

If WAL is growing without bound, the usual culprits are an inactive replication slot or a stuck archiver. Inspect and, if a slot is truly abandoned, drop it on the primary:

SELECT slot_name, active, wal_status,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots;

-- Only drop a slot you are certain is no longer used by any standby:
-- SELECT pg_drop_replication_slot('orphaned_slot');

If archiving is failing, check archive_command and the destination, and clear the backlog with the supported tool rather than rm:

# Removes only archived WAL no longer needed past a given segment
pg_archivecleanup /path/to/wal-archive 00000001000000000000000C

If the log shows errors like could not open file "pg_wal/...", invalid record length, or requested WAL segment ... has already been removed, recovery cannot complete on its own.

# Inspect the WAL directory (16MB segments by default). Do not modify anything here.
ls -lh /var/lib/postgresql/16/main/pg_wal/

# Search the server log for WAL/redo problems
grep -Ei "wal|redo|corrupt|invalid|already been removed" /var/log/postgresql/postgresql-*.log

Choose a recovery path based on your situation:

- Standby missing WAL from the primary: if the primary still has the needed WAL (e.g. retained by a replication slot or in the archive), the standby will catch up. If it does not, re-seed the standby from the primary with pg_basebackup (see Advanced notes).
- Primary that can still reach its WAL archive: configure restore/PITR so the archived segments are applied.
- WAL permanently lost or corrupted on a primary: this is a data-loss scenario. Take a physical, file-level copy of the data directory while the server is stopped, then recover from your most recent backup. pg_resetwal is a destructive last resort - see Advanced notes before considering it.

Only restart if the log shows no redo progress for a long time and you have ruled out disk/WAL issues above. A healthy crash recovery should be left alone.

# Preferred: clean shutdown via your service manager
sudo systemctl stop postgresql
sudo systemctl start postgresql

# Or with pg_ctl, using a clean shutdown mode
sudo -u postgres pg_ctl stop  -D /var/lib/postgresql/16/main -m fast
sudo -u postgres pg_ctl start -D /var/lib/postgresql/16/main

Shutdown modes, safest first:

-m smart      # wait for existing sessions to disconnect (default before PG 9.5)
-m fast       # disconnect clients, then shut down cleanly (default since PG 9.5)
-m immediate  # AVOID - aborts without a final checkpoint, FORCING crash recovery next start

Never use -m immediate or kill -9 for a routine stop: both leave the cluster needing crash recovery and reproduce exactly this error.

Wait for the server to come up and verify its role:

while ! sudo -u postgres psql -tAc "SELECT 1" >/dev/null 2>&1; do
  echo "Waiting for PostgreSQL to accept connections..."
  sleep 5
done
echo "PostgreSQL is accepting connections."

SELECT pg_is_in_recovery();  -- false on a primary, true on a standby
SELECT version();

Do not run blanket REINDEX or VACUUM as a reflexive "integrity check" - crash recovery already restores a consistent state, and those operations are heavy and unnecessary here. Only act on integrity if PostgreSQL actually reports an error. If you suspect corruption (e.g. you saw checksum failures), verify checksums on a stopped cluster instead:

# Cluster must be stopped; reports checksum mismatches without changing data
sudo -u postgres pg_checksums --check -D /var/lib/postgresql/16/main