Australian cloud for business software — built and run in Sydney since 2002

NetLeverage
NetLeverage

PostgreSQL "FATAL: the database system is in recovery mode": what it means and what to check

Technical article
Troubleshooting

About this article: This article is reviewed against the cited sources. This is source research, not lab validation. Check the stated versions and your own requirements before making changes.

FATAL: the database system is in recovery mode is what a client sees when a PostgreSQL server process has died and the server is reinitialising itself, from the crash until the restarted server starts replaying its write-ahead log (WAL). After that, in the source code of release 17.12, clients get the database system is not yet accepting connections instead, until recovery finishes. While the server log shows recovery progressing, the right move is to wait; then work out which server process died and why. One documented cause on Linux is the out-of-memory killer ending a PostgreSQL process.

What the message means

The server rejects the connection before any login. The message texts below come from the PostgreSQL source code, tag REL_17_12 (the PostgreSQL 17.12 release, matching the 17.12 documentation pages), not from a documentation page. In backend_startup.c, four states give five different messages, all with SQLSTATE 57P03 (cannot_connect_now in the error code appendix):

Message State
the database system is starting up Normal startup, before WAL redo has started
the database system is not yet accepting connections with the detail Consistent recovery state has not been yet reached. WAL redo has started (after a crash or on a standby) and no consistent state has been reached, with hot standby on
the database system is not accepting connections with the detail Hot standby mode is disabled. The same state, with hot standby off
the database system is shutting down A shutdown is pending
the database system is in recovery mode Anything else: a crash handled by the running server, from the crash until WAL redo starts; the check in postmaster.c carries the comment "else must be crash recovery"

The documentation says what leads to crash recovery. When a child process is terminated (the manual's example is the out-of-memory killer), PostgreSQL "will typically terminate all remaining connections and either recover automatically, if restart_after_crash is enabled, or shut down." restart_after_crash defaults to on. The log then shows lines such as these. The source code has them as format strings; the parts that vary are the %s and %d values (process name, PID, signal number and its name) and the %X/%X WAL positions:

  • %s (PID %d) was terminated by signal %d: %s, for example server process (PID 4127) was terminated by signal 9: Killed
  • all server processes terminated; reinitializing
  • database system was not properly shut down; automatic recovery in progress (two adjacent string literals in the source, joined in the log)
  • redo starts at %X/%X
  • redo done at %X/%X system usage: %s
  • database system is ready to accept connections

Other sessions are told terminating connection because of crash of another server process (text from postgres.c in the same source code). An immediate stop makes the next start run crash recovery on purpose: the pg_ctl manual says "Immediate" mode "will lead to a crash-recovery cycle during the next server start." That next start is a fresh start, not a crash restart inside a running server. In the source (postmaster.c), the plain-startup answer is given only while the flag FatalError ("T if recovering from backend crash") is false, so the "in recovery mode" message belongs to a crash handled by the running server. The same file clears the flag when the startup process signals that WAL redo has started (the source comment reads "WAL redo has started. We're out of reinitialization."), and xlogrecovery.c sends that signal just before the redo starts at log line. From then on the check returns the "not yet accepting connections" answer, so clients see "in recovery mode" only until redo starts at appears in the log, not while the log is replayed. At a fresh start after an immediate stop, power loss or host crash that flag is false, so clients are told the database system is starting up until redo starts, the first row of the table above.

Likely causes

Cause What you see How to confirm
A PostgreSQL process was killed by the operating system terminated by signal 9: Killed in the log The kernel log has a line like the one the manual gives: Out of Memory: Killed process 12345 (postgres).
A server process crashed terminated by signal with another signal number The log names the signal and, in the DETAIL line, the query it was running
A crash restart inside the running server terminated by signal or reinitializing lines in the log, then this message until redo starts The log shows all server processes terminated; reinitializing
Long but normal recovery The message has changed to not yet accepting connections (or not accepting connections) and redo lines still appear The log shows redo starts at but not yet redo done at
Crash loop The same sequence repeats Two or more reinitializing lines close together

To tell this message from a standby that has not yet reached a consistent state: that standby also answers not yet accepting connections, and its log lacks consistent recovery state reached.

Prove the cause before changing anything

All of these only read.

1. Is the server rejecting or not answering?

pg_isready

The manual says it returns "0 to the shell if the server is accepting connections normally, 1 if the server is rejecting connections (for example during startup), 2 if there was no response to the connection attempt". Exit 1 matches this fault; exit 2 means the server is not running or not reachable.

2. Read the server log. Where it is depends on the installation: the documentation says log_destination defaults to stderr, and the logging collector, when enabled, writes files. Use the path your installation uses:

grep -n -E 'terminated by signal|reinitializing|automatic recovery|redo starts|redo done|ready to accept' <path-to-server-log>

A good result ends with database system is ready to accept connections. A bad result shows the same crash sequence repeating, or redo starts with no redo done for a long time.

3. Read the kernel log for the process kill.

journalctl -k

The journalctl manual documents -k for kernel messages. Look for an out-of-memory kill of a postgres process. The PostgreSQL manual says to "consult your system documentation and configuration on where to look for such a message".

4. On a standby, find the progress lines. The hot standby page says to look for LOG: entering standby mode, then LOG: consistent recovery state reached, then LOG: database system is ready to accept read-only connections.

Fix

Which fix applies depends on the cause. None of them is a command that should be run while recovery is still progressing.

While recovery is progressing

Risk: stopping the server with pg_ctl stop -m immediate, or removing WAL files, while recovery runs makes things worse. The pg_ctl manual says immediate mode "will lead to a crash-recovery cycle during the next server start."

Wait instead. Retry the connection from the application (the SQLSTATE 57P03 identifies this condition) or loop on pg_isready. If the log shows redo starts at but not yet redo done at, the server is working. A manual restart is a changing step with its own risk and is not part of this diagnosis.

The kernel killed a PostgreSQL process

The PostgreSQL manual describes several remedies: run PostgreSQL where "other processes will not run the machine out of memory", add swap, lower shared_buffers, work_mem or hash_mem_multiplier, reduce max_connections and use connection pooling, or use strict overcommit mode with vm.overcommit_memory=2. It adds that strict mode "will not prevent the OOM killer from being invoked altogether". Each of these changes configuration or kernel settings, so each is a planned change that needs the old value saved first and a way back. "Linux OOM Killer on a VM: Find the Cause, Set Memory Limits" covers reading the kernel record and setting limits.

A server process crashed

A crash from a signal other than 9 points to a bug, a damaged file or hardware, not to memory. Keep the log and the DETAIL line with the query, and check the PostgreSQL version against the release notes of your major version before taking further steps.

A standby pauses recovery on lower parameter settings

The hot standby page says that if a standby processes WAL showing a higher value on the primary for parameters such as max_connections, max_prepared_transactions, max_locks_per_transaction, max_wal_senders or max_worker_processes, it "will log a warning and pause recovery", with WARNING: hot standby is not possible because of insufficient parameter settings. The page says the settings on the standby need to be updated and the instance restarted before recovery can continue, and that these values should be equal to or greater than the primary's on every standby.

Recovery fails with a checkpoint error

If recovery stops with a PANIC about a checkpoint record, see "PostgreSQL will not start: 'could not locate a valid checkpoint record'". Do not delete WAL files by hand.

Example (illustrative): at 02:14:10 app.example.com reports FATAL: the database system is in recovery mode from db01. pg_isready returns 1. The log shows server process (PID 4127) was terminated by signal 9: Killed, then all server processes terminated; reinitializing, automatic recovery in progress, redo starts at and, at 02:14:50, redo done at and database system is ready to accept connections. The application therefore saw refusals for at least 40 seconds (02:14:50 minus 02:14:10); recovery itself began earlier, when the process was killed. In the source code, the message the application sees changes from in recovery mode to not yet accepting connections (or not accepting connections with hot standby off) once redo starts at is logged. journalctl -k shows an out-of-memory kill of PID 4127 a moment earlier. The cause is memory pressure, not the recovery, so the engineer plans a change to max_connections and connection pooling.

Confirm the fix

  1. pg_isready returns 0.
  2. The log ends with database system is ready to accept connections and shows no new terminated by signal line over a full busy period.
  3. The kernel log shows no new out-of-memory kill of a postgres process.
  4. The application reconnects without manual action.

What to watch afterwards

  • Count reinitializing lines in the log each day. One is an event; a series is a fault.
  • Watch memory use against the settings listed above, since total use can exceed physical memory when many sessions each use work_mem.
  • Keep the log retention long enough to cover the crash and the lines before it.

Risks and limits

  • These commands have not been run or lab-tested.
  • Exact message text comes from the PostgreSQL source code at tag REL_17_12; other major versions can word messages differently.
  • Recovery duration depends on how much WAL has to be replayed. No figure can be given in advance.
  • Corruption and failed recovery are outside this scope; the guidance here is for the wait-and-diagnose case.

Sources