Skip to main content
RunBook Academy

← All break/fix scenarios in PostgreSQL

intermediatepg-backup~35 min

The restore finished, the row counts matched, and the application could not read a single table

Reported symptoms

  • ●A migration rehearsal restores the production database into a newly built cluster over four hours and the pipeline reports success
  • ●A verification script compares row counts for all 214 tables between source and target and every count matches exactly
  • ●The application is pointed at the new cluster at 22:10 and fails to start, logging permission denied for schema billing on its first query
  • ●The application role exists on the new cluster and can connect, so authentication is initially ruled out and the failure is attributed to a configuration error in the application
  • ●Twenty minutes are spent checking connection strings, search_path settings and the application deployment before anybody queries the database directly
  • ●A superuser can read every table without difficulty, which delays the diagnosis further because the data is demonstrably present and readable
  • ●The restore log, when finally read, contains 47 error lines that scrolled past four hours earlier

Evidence

  • · psql as the application role returns ERROR: permission denied for schema billing on every table in three of the eleven schemas
  • · The same query as postgres returns rows normally, confirming the data is present and the problem is access rather than content
  • · The Access privileges column in \\dp is empty for every table in the affected schemas, while tables in the other eight schemas show the expected grants
  • · The restore log contains 47 lines matching pg_restore: error, each of the form could not execute query: ERROR: role "billing_app" does not exist followed by the GRANT statement it was attempting
  • · pg_restore exited with status 1, which the pipeline recorded as 0 because the command was piped through tee restore.log and the pipeline checked the exit status of the pipeline rather than of pg_restore
  • · The backup procedure consists of a single pg_dump of the database and does not include pg_dumpall --globals-only
  • · The three affected schemas are the three whose roles were created after the last time this rehearsal was performed, eight months earlier
  • · The rehearsal checklist contains a row-count comparison and a connectivity check performed as the postgres superuser
Diagnosis and resolutionclick to reveal

Root cause

`pg_dump` dumps one database. Roles, tablespaces and other cluster-wide objects live outside any database and are not in it. The dump therefore contained `GRANT` statements referring to roles it had never created, and on a cluster that did not already have those roles, every one of those grants failed. The data restored perfectly. Every table, every row, every index, every constraint. What did not restore was the access to three schemas, because the `GRANT` statements naming `billing_app` and two other roles could not be executed against roles that did not exist. `pg_restore` reported this correctly. It printed 47 errors and exited with status 1. The pipeline did not see that, for a reason worth stating precisely. The command was written as `pg_restore ... | tee restore.log`, and the pipeline checked `$?` afterwards. In a shell pipeline `$?` is the exit status of the **last** command, which is `tee`, which succeeded. The status of `pg_restore` was discarded the moment the pipe was written. The verification then confirmed the wrong thing. Comparing row counts as a superuser tests whether the data arrived. It cannot detect a missing grant, a missing role, a failed constraint or an invalid index, because a superuser bypasses the permission system entirely and the counts are identical either way. The reason only three schemas were affected is the same reason the problem had never been seen: the roles for the other eight already existed on the target cluster, left over from a rehearsal eight months earlier. The three that failed were the three created since.

Remediation

Restore the globals, which is the half of the backup that was never taken: ```bash pg_dumpall --globals-only -h source-host -f globals.sql psql -h target-host -f globals.sql ``` Expect errors of the form `role "postgres" already exists` — the globals file describes every role in the source cluster, including ones the target already has, and those errors are harmless. That is also why the globals restore cannot be verified by checking for zero errors, and why it must run **before** the database restore rather than after. With the roles present, re-apply the grants. The cleanest route is to restore only the ACL entries from the existing dump rather than repeating the four-hour data load: ```bash pg_restore -l production.dump | grep ' ACL ' > acl.list pg_restore -h target-host -d appdb -L acl.list production.dump echo "pg_restore exit: $?" ``` Note the exit status check with no pipe in front of it. Then verify as the application role, which is the check that would have caught this: ```bash PGPASSWORD=... psql -h target-host -U billing_app -d appdb \ -c "SELECT count(*) FROM billing.invoices;" ``` If the ACL-only restore is not practical — a dump taken with `--no-acl`, for instance — the grants must be reconstructed from the source cluster, which is recoverable but tedious. That is an argument for fixing the backup procedure rather than for scripting the reconstruction.

Verification

The application role can execute a representative query in every schema. Not a superuser, and not `SELECT 1` — an actual query against an actual table, per schema: ```sql SET ROLE billing_app; SELECT count(*) FROM billing.invoices; RESET ROLE; ``` `\dp` shows a populated Access privileges column for every table that should have one, and a diff of the grants between source and target is empty. The restore log contains zero lines matching `pg_restore: error`, and the exit status of `pg_restore` itself — measured without a pipe — is zero. Object counts match by type, not only row counts: tables, indexes, constraints, sequences, functions and views. An index that failed to build leaves the data intact and the database subtly wrong, and a row count cannot see it. `SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid` returns nothing. The application starts and serves traffic.

Prevention

**A logical backup is two commands, not one.** `pg_dump` for the database and `pg_dumpall --globals-only` for the cluster. The globals file is a few kilobytes and there is no reason to ever skip it. Note that it contains role password verifiers, so it is a credential and needs protecting accordingly. **Measure the exit status of the right command.** `cmd | tee log` followed by `$?` checks `tee`. Use `set -o pipefail`, or `${PIPESTATUS[0]}`, or redirect instead of piping: ```bash pg_restore -d appdb production.dump > restore.log 2>&1 rc=$? [ "$rc" -eq 0 ] || { echo "RESTORE FAILED: $rc"; exit 1; } ``` **Fail the pipeline on `pg_restore: error` in the log**, independently of the exit status. Two checks that can each catch what the other misses cost nothing. **Verify as the application, never as a superuser.** This is the single check that would have caught this incident, and it catches most others: a query as the application role, against a real table, in every schema. A superuser bypasses the permission system, so superuser verification is structurally incapable of detecting a permissions failure. **Compare object counts by type**, not only row counts. **Rehearse often enough that the rehearsal environment is not stale.** The eight pre-existing roles masked this problem for eight months. A rehearsal into a genuinely empty cluster would have exposed it the first time.

Reported symptoms

A migration rehearsal restores the production database into a newly built cluster. Four hours. The pipeline reports success.

A verification script compares row counts for all 214 tables between source and target. Every count matches exactly.

The application is pointed at the new cluster at 22:10 and fails to start:

ERROR:  permission denied for schema billing

The application role exists and can connect, so authentication is ruled out and the failure is attributed to an application configuration problem. Twenty minutes go into connection strings, search_path and the deployment manifest.

A superuser reads every table without difficulty, which delays things further — the data is demonstrably there.

At 22:40 somebody reads the restore log and finds 47 errors that scrolled past four hours earlier.

Evidence provided

Read-only / Safethe same table, two roles, two outcomes
$ a SELECT as the application role, then as postgres
-- as billing_app:
ERROR:  permission denied for schema billing

-- as postgres:
count  
---------
4471029
(1 row)

Illustrative output

Read-only / Safethe privileges are simply absent
$ psql -c "\dp billing.invoices"
                               Access privileges
Schema  |   Name   | Type  | Access privileges | Column privileges | Policies 
---------+----------+-------+-------------------+-------------------+----------
billing | invoices | table |                   |                   | 
(1 row)

Illustrative output

Read-only / Safeforty-seven of these, four hours earlier
$ grep -A1 'pg_restore: error' restore.log | head -4
pg_restore: error: could not execute query: ERROR:  role "billing_app" does not exist
Command was: GRANT USAGE ON SCHEMA billing TO billing_app;

pg_restore: error: could not execute query: ERROR:  role "billing_app" does not exist
Command was: GRANT SELECT ON TABLE billing.invoices TO billing_app;

Illustrative output

pg_restore exited with status 1. The pipeline recorded 0.

The command was pg_restore ... | tee restore.log, and the pipeline checked $?.

The backup procedure is a single pg_dump. There is no pg_dumpall --globals-only.

The three affected schemas are the three whose roles were created after the last rehearsal, eight months earlier.

Work the evidence before reading on

  1. Every row count matched. What did that prove, and what did it not prove?
  2. pg_restore exited 1 and the pipeline recorded 0. What is the mechanism?
  3. Eight schemas restored correctly and three did not. What distinguishes them?
  4. The verification connected successfully as postgres. Why was that worse than useless?

Root cause

The dump was half a backup

pg_dump dumps one database. Roles, tablespaces and cluster-wide settings live outside any database and are not in it.

So the dump contained GRANT USAGE ON SCHEMA billing TO billing_app; and had never contained CREATE ROLE billing_app;. On a cluster that already had the role, the grant worked. On one that did not, it failed.

The data restored perfectly. What did not restore was access.

The pipe swallowed the failure

pg_restore --dbname=restore_check dump.pgdump | tee restore.log
if [ $? -ne 0 ]; then
  echo "restore failed" >&2
  exit 1
fi

In a shell pipeline, $? is the exit status of the last command. That is tee. tee succeeded. pg_restore’s status was discarded the moment the pipe was written.

Counting rows as a superuser cannot detect this

The verification compared row counts for 214 tables. They matched, because the data was fine.

A superuser bypasses the permission system entirely. The counts would have been identical whether the grants existed or not, so the check was structurally incapable of detecting the failure.

Eight months of luck

The eight schemas that restored correctly had roles left over from a rehearsal eight months earlier. The three that failed were created since.

The procedure had been broken the entire time and had been masked by a target cluster that was never actually empty.

Resolution

Restore the globals — the missing half:

pg_dumpall --globals-only -h source-host -f globals.sql
psql -h target-host -f globals.sql

Expect role "postgres" already exists. That is harmless, and it is why the globals restore cannot be verified by checking for zero errors — and why it must run before the database restore.

Then re-apply just the grants, rather than repeating four hours of data load:

pg_restore -l production.dump | grep ' ACL ' > acl.list
pg_restore -h target-host -d appdb -L acl.list production.dump > acl-restore.log 2>&1
echo "pg_restore exit: $?"

Note the exit status check with nothing piped in front of it.

Verify as the application role:

PGPASSWORD=... psql -h target-host -U billing_app -d appdb \
  -c "SELECT count(*) FROM billing.invoices;"

Verification

The application role runs a real query in every schema, not SELECT 1 and not as a superuser.

\dp shows populated privileges, and a grant diff between source and target is empty.

Zero pg_restore: error lines, and pg_restore’s own exit status is zero.

Object counts match by type, and SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid returns nothing.

The application starts and serves traffic.

Prevention

Two commands, always:

pg_dumpall --globals-only -f globals.sql
pg_dump -Fd -j 4 -f appdb.dir appdb

Measure the right exit status, using redirection rather than a pipe.

Fail on pg_restore: error in the log as an independent check.

Verify as the application role. This is the specific habit that converts a four-hour rehearsal from a data-transfer test into a restore test.

Compare object counts by type.

Rehearse into a genuinely empty cluster, so that stale leftovers cannot mask an incomplete procedure for eight months.