Skip to main content
RunBook Academy

← All break/fix scenarios in PostgreSQL

intermediatepg-startup~35 min

The database did not come back after the kernel patch, and the change that broke it was six weeks old

Reported symptoms

  • ●A scheduled kernel patch reboots db-prod-01 at 03:10 and the host comes back in ninety seconds with every service healthy except PostgreSQL
  • ●systemctl status postgresql@18-main reports failed, and the unit has attempted to start three times before giving up
  • ●The application tier reports connection refused rather than any authentication or timeout error, so the network path and the firewall are ruled out within two minutes
  • ●The on-call engineer restarts the service by hand and it fails identically, then reboots the host a second time on the theory that something did not initialise, and it fails identically again
  • ●The data directory is present, owned by postgres, and the filesystem is 41 per cent full, so neither permissions nor space are implicated
  • ●A second, identically built host in the same cluster restarted an hour earlier during the same patch window and came back cleanly
  • ●Nobody has deployed anything to this host in six weeks and the change calendar for the night lists only the kernel patch

Evidence

  • · The cluster log ends with FATAL: configuration file "/etc/postgresql/18/main/conf.d/30-tuning.conf" contains errors, preceded by LOG: invalid value for parameter "log_min_duration_statement": "never"
  • · The postmaster logged listening on IPv4 address "0.0.0.0", port 5432 and listening on Unix socket before the FATAL, so it got as far as binding its sockets and then shut down
  • · pg_ctlcluster reports exited with status 1 and pg_lsclusters shows the cluster as down, while the second host in the pair shows online
  • · The offending file is dated six weeks earlier and its modification time matches a change ticket for a logging adjustment that was reloaded, verified as applied, and closed
  • · The cluster log from six weeks ago contains LOG: invalid value for parameter "log_min_duration_statement": "never" and LOG: configuration file "..." contains errors; unaffected changes were applied, at severity LOG, on the same day the ticket was closed
  • · log_min_duration_statement on the running server at the time was 250, the value set by an earlier ALTER SYSTEM, and pg_settings reported source as configuration file pointing at postgresql.auto.conf
  • · The second host has no such file: the change was applied to one node of the pair and never propagated, which is why it restarted cleanly
  • · pg_file_settings on the second host returns no rows with a non-null error, and would have returned one row on db-prod-01 at any point in the preceding six weeks
Diagnosis and resolutionclick to reveal

Root cause

A configuration file containing an invalid value was written six weeks before the outage. PostgreSQL tolerated it on reload and refused it at startup, and the reboot was simply the first startup since the file appeared. The value was `log_min_duration_statement = 'never'`. That parameter takes a duration in milliseconds or the sentinel `-1`; `never` is not a value it accepts. When the engineer reloaded, the server parsed the file, rejected that one line, kept the value it already had, applied every other change in the reload, and logged the problem at severity `LOG`. The reload reported success because it had succeeded — a reload's contract is to apply what it can and continue. Nothing about the running server changed. `log_min_duration_statement` stayed at 250, which is what the engineer expected to see, so the verification step passed. The ticket was closed on a server that was carrying a configuration file it could never start with. The asymmetry is deliberate and correct in both directions. A running server that meets an unparseable value has a known-good previous value to fall back on, and refusing to serve traffic over a typo would be far worse than skipping one line. A starting server has no previous value for anything, so a parameter it cannot resolve leaves it with no defined behaviour, and it declines to start rather than run with a configuration it does not understand. The gap between those two behaviours is the whole incident. It is measured in weeks, it is silent, and it turns an unrelated maintenance activity into an outage whose cause is six weeks in the past and in nobody's mind.

Remediation

Read the log before changing anything. The FATAL names the file and the line, and the LOG immediately above it names the parameter and the value. That is a complete diagnosis and it is the first thing in the file. Remove or correct the offending line. `log_min_duration_statement` accepts a duration, so the intended value was almost certainly `'250ms'` or `-1`; if the intent is not recoverable from the change ticket, delete the line and restore the behaviour the server has actually been running with for six weeks, which is what everyone has been observing anyway. Start the cluster and confirm from the log that it reached "database system is ready to accept connections", not merely that the start command returned. Confirm with a connection. Then run the check that would have prevented it, on this host and on every other host in the estate, because a file like this can be sitting on any of them: ```sql SELECT sourcefile, sourceline, name, setting, error FROM pg_file_settings WHERE error IS NOT NULL; ``` An empty result means every assignment in every configuration file is valid and the server will start. A row means it will not, and you have found the next incident before it happened. Do the same for the other two files a startup can fail on: ```sql SELECT line_number, error FROM pg_hba_file_rules WHERE error IS NOT NULL; SELECT line_number, error FROM pg_ident_file_mappings WHERE error IS NOT NULL; ``` Do not delete `postgresql.auto.conf`, and do not start moving files aside to see what happens. The log has already told you which file and which line.

Verification

The cluster starts and the log's final line is "database system is ready to accept connections". `pg_lsclusters` reports online. A `psql` connection succeeds and `SELECT 1` returns. `SELECT count(*) FROM pg_file_settings WHERE error IS NOT NULL` returns zero, and the same on `pg_hba_file_rules` and `pg_ident_file_mappings`. `SELECT name, setting, source, sourcefile FROM pg_settings WHERE name = 'log_min_duration_statement'` reports the value you intended and names the file it came from, rather than reporting a value that happens to match by accident. A deliberate restart, performed while somebody is watching, succeeds. This is the only verification that actually tests the thing that failed, and it is worth doing once during the incident window rather than discovering it at the next reboot.

Prevention

**Make the pre-flight query part of every configuration change.** After any edit and reload, run `SELECT * FROM pg_file_settings WHERE error IS NOT NULL` and treat a row as a failed change. It takes one round trip and it closes the entire class. **Monitor it continuously.** The query is cheap enough to run every few minutes. An alert on it firing tells you a server will not restart, weeks before anything restarts it, and it is the only signal that exists during that window. **Verify a configuration change by reading `pg_settings.source`, not by reading the value.** The engineer here checked that `log_min_duration_statement` was 250 and it was — set by a different mechanism entirely. Checking `source` and `sourcefile` would have shown the value came from `postgresql.auto.conf` and not from the file just edited. **Alert on configuration drift between paired hosts.** The second node restarted cleanly because it never received the change. That divergence was itself a defect and had been present for six weeks. **Restart deliberately, on a schedule.** A cluster that has not been restarted in months has accumulated an unknown number of these. Restarting one node of a pair during a planned window, with somebody watching, converts an unplanned 03:10 outage into a controlled five-minute check.

Reported symptoms

A kernel patch reboots db-prod-01 at 03:10. The host is back in ninety seconds. Every service on it is healthy except PostgreSQL, which has tried to start three times and given up.

The application reports connection refused — not a timeout, not an authentication error — so within two minutes the network path and the firewall are eliminated. The on-call engineer starts the service by hand and watches it fail the same way, then reboots the host again on the theory that something did not initialise, and watches it fail the same way a third time.

The data directory is present and owned by postgres. The filesystem is 41% full. The paired host db-prod-02 restarted an hour earlier in the same patch window and came back cleanly. Nothing has been deployed to either host in six weeks, and the change calendar for the night contains one entry: the kernel patch.

Evidence provided

The end of the cluster log:

Read-only / Safethe last four lines before the server gave up
$ tail -4 /var/log/postgresql/postgresql-18-main.log
2026-08-28 03:11:44.201 UTC [2841] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2026-08-28 03:11:44.204 UTC [2841] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2026-08-28 03:11:44.209 UTC [2841] LOG:  invalid value for parameter "log_min_duration_statement": "never"
2026-08-28 03:11:44.209 UTC [2841] FATAL:  configuration file "/etc/postgresql/18/main/conf.d/30-tuning.conf" contains errors

Illustrative output

The cluster registry:

Read-only / Safeone node of the pair is down
$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
18  main    5432 down   postgres /var/lib/postgresql/18/main /var/log/postgresql/postgresql-18-main.log

Illustrative output

The offending file is dated six weeks earlier. Its modification time matches change ticket CHG-4471, “adjust slow query logging threshold”, which was reloaded, verified and closed on the same day.

And the log from that day:

Read-only / Safethe same problem, six weeks earlier, at severity LOG
$ grep -A1 'invalid value' /var/log/postgresql/postgresql-18-main.log.6.gz
2026-07-17 14:22:08.551 UTC [1204] LOG:  received SIGHUP, reloading configuration files
2026-07-17 14:22:08.552 UTC [1204] LOG:  invalid value for parameter "log_min_duration_statement": "never"
2026-07-17 14:22:08.552 UTC [1204] LOG:  configuration file "/etc/postgresql/18/main/conf.d/30-tuning.conf" contains errors; unaffected changes were applied

Illustrative output

Work the evidence before reading on

Four questions, and the fourth is the one that matters.

  1. The server bound its sockets and then shut down. What does that tell you about how far startup got, and what it rules out?
  2. The same message appears six weeks earlier at severity LOG and tonight at severity FATAL. Why is it different?
  3. The engineer who made the change verified that log_min_duration_statement was 250 afterwards, and it was. What did that verification actually prove?
  4. db-prod-02 restarted cleanly an hour earlier. Is that reassuring?

Root cause

The value was never valid, and the reload said so

log_min_duration_statement takes a duration — a number of milliseconds, or a value with a unit, or -1 to disable. The string never is not any of those.

When the reload met it, the server did three things: it rejected that one line, it applied every other change in the file set, and it kept the value the parameter already had. Then it logged the problem and carried on serving traffic, because that is what a reload is supposed to do.

The verification checked the right value for the wrong reason

The engineer checked log_min_duration_statement after the reload and found 250. That looked like confirmation. It was a coincidence.

The value 250 was already in force, set months earlier by an ALTER SYSTEM that wrote it into postgresql.auto.conf. The reload had changed nothing, so of course the value was still 250. Checking the value alone cannot distinguish “my change was applied” from “my change was rejected and the old value survived”.

The paired host was not reassuring, it was a second defect

db-prod-02 restarted cleanly because the file was never written to it. The change had been applied to one node of a pair and never propagated, and had been in that state for six weeks.

That is not a mitigation. It means the two nodes of the pair have had divergent configuration for six weeks, and it means the fleet-wide verification that should have caught the broken file also never ran.

Resolution

Correct the file. The intent was a slow-query threshold, so '250ms' is almost certainly what was meant; if the ticket does not say, delete the line, which restores exactly the behaviour the server has actually been running with since July.

sed -i "/log_min_duration_statement/d" /etc/postgresql/18/main/conf.d/30-tuning.conf
pg_ctlcluster 18 main start
tail -3 /var/log/postgresql/postgresql-18-main.log

Confirm from the log that the server reached database system is ready to accept connections, and confirm with an actual connection. A start command that returns is not evidence; the pg_ctl wrapper can report success while the startup process fails immediately afterwards.

Then, before closing the incident, run the pre-flight query on every server in the estate:

SELECT sourcefile, sourceline, name, setting, error
FROM pg_file_settings WHERE error IS NOT NULL;

Every row is a server that will not restart. Finding them now, while everyone is already awake and the context is fresh, is considerably cheaper than finding them one at a time over the following months.

Two more files can stop a startup the same way:

SELECT line_number, error FROM pg_hba_file_rules       WHERE error IS NOT NULL;
SELECT line_number, error FROM pg_ident_file_mappings  WHERE error IS NOT NULL;

Note that pg_hba.conf behaves differently again: a single bad line causes the entire file to be rejected on reload, with pg_hba.conf was not reloaded, rather than the good lines being applied.

Verification

The cluster is online, the log ends with database system is ready to accept connections, and a connection succeeds.

All three pre-flight queries return zero rows.

pg_settings reports log_min_duration_statement with a sourcefile that is the file you intended, at the value you intended.

And — the only verification that tests what actually failed — a deliberate restart, performed once while somebody is watching, succeeds.

Prevention

Add the pre-flight query to the configuration-change procedure. One query after every reload, and a row in the result means the change failed regardless of what the reload returned.

Monitor it. It is cheap enough to run every minute. An alert on it tells you a server cannot restart, during the entire window in which that is a latent problem rather than an outage.

Verify by source, never by value. This is the specific habit that would have caught this in July.

Alert on configuration divergence between paired nodes. Six weeks of drift is a defect in its own right, and here it was the only reason the outage was not twice as large.

Restart on a schedule. Every node, one at a time, in a planned window, often enough that no node accumulates months of unexercised configuration. A restart nobody is watching is how a six-week-old typo becomes a 03:10 page.