Troubleshoot PostgreSQL Backups and Disaster Recovery

This page is part of PostgreSQL Backups and Disaster Recovery. For the terms that it uses, see Terms.

These commands use the $REPO_HOST and $INSTANCES selectors from Verify that both kinds of backup work. If you came directly to this page, set them first.

Stanza creation fails

PostgreSQL backups are not initialized. Get the real error, not only the exit code:

# Stanza creation can fail on either side, so read the repository host and the
# pgBackRest sidecar of each instance pod.
kubectl -n "$NS" logs -l "$REPO_HOST" -c pgbackrest --tail=100
kubectl -n "$NS" logs -l "$INSTANCES" -c pgbackrest --tail=100

REPO_HOST_POD="$(kubectl -n "$NS" get pod -l "$REPO_HOST" -o name | head -1)"
kubectl -n "$NS" exec -it "$REPO_HOST_POD" -c pgbackrest -- \
  pgbackrest stanza-create --stanza=db --log-level-console=detail

These are the pgBackRest exit codes that occur on Kubernetes:

PGO runs stanza creation again only when it sees a new repository configuration. To make it try again after you fix the cause, remove the marker annotation:

kubectl -n "$NS" annotate postgrescluster "$PGCLUSTER" \
  pgbackrest.crunchydata.com/stanzas-created- --overwrite

PostgreSQL restore fails in a loop

status.pgbackrest.restore has a non-zero failed count and finished: false, and the count increases each time the Job tries again. After some failed attempts, the operator reports PostgresRestoreFailed. On the tested installs, the condition came after two to five failed attempts, which took up to five minutes. Before that, Ready can still show True while PostgreSQL is stopped:

kubectl -n "$NS" describe netboxenterprise | grep -A3 PostgresRestoreFailed
kubectl -n "$NS" get events --field-selector reason=PostgresRestoreFailed

When the condition shows, Ready is Unknown with WaitingForDependencies, because the reconcile waits for PostgreSQL, which cannot start. This is the same on a cluster that served data before the PostgreSQL restore. To tell a failed PostgreSQL restore from a slow one, read the condition and the log, not Ready.

Read the log of the restore Job for the pgBackRest error code, which gives the cause:

kubectl -n "$NS" logs -l postgres-operator.crunchydata.com/pgbackrest-restore \
  --tail=50

Two causes are in the options that you set in step 2 or in PostgreSQL restore to an earlier point:

• --set names a backup set that is not in the repository. Compare it with pgbackrest info. A label that does not exist fails on every attempt.
• The recovery target is outside the WAL archive range. pgbackrest info also shows this range.

A third cause is not in the options. It stops the automatic PostgreSQL restore of a disaster recovery restore, which uses no options:

ERROR: [058]: target timeline 5 forked from backup timeline 3 at 0/f004e78 which is before backup lsn of 0/13000028

The newest PostgreSQL backup is on a timeline that the newest WAL does not come from. This occurs after a PostgreSQL restore to an earlier point if no full PostgreSQL backup ran after it. A PostgreSQL restore without --set uses the newest PostgreSQL backup, so each attempt fails.

To recover, expire the PostgreSQL backups on the old timeline. pgbackrest info shows the timeline of each PostgreSQL backup in the first 8 digits of its wal start value. In the example error, the PostgreSQL backups to expire have a wal start that begins with 00000003. Expire each one by its label, newest first:

REPO_HOST_POD="$(kubectl -n "$NS" get pod -l "$REPO_HOST" -o name | head -1)"
kubectl -n "$NS" exec "$REPO_HOST_POD" -c pgbackrest -- pgbackrest info --stanza=db

kubectl -n "$NS" exec "$REPO_HOST_POD" -c pgbackrest -- \
  pgbackrest expire --stanza=db --set=<label>

The next attempt of the restore Job then uses the newest remaining PostgreSQL backup. On the tested install, that attempt succeeded.

A network fault between the restore Job and the repository host can also cause this failure. The Job uses all 7 attempts in about 15 minutes. A fault that is longer than that stops the PostgreSQL restore, even after the network recovers.

Check the state of the restore Job. From 2.4.0, the PostgresRestoreFailed message gives this command:

kubectl -n "$NS" get jobs -l postgres-operator.crunchydata.com/pgbackrest-restore

• Running: The Job has more attempts and can still succeed. Correct the options, then trigger the PostgreSQL restore again with a new annotation value.

• Failed: Nothing restarts the Job, and a new annotation does not replace it. Delete the Job first. Then correct the options and trigger the PostgreSQL restore again with a new annotation value:

  kubectl -n "$NS" delete job "$PGCLUSTER-pgbackrest-restore"

  After a network fault, the repository is not damaged, and the PostgreSQL restore takes about a minute.

The condition clears when PGO reports a database.

The failed count does not reset. PGO copies failed from the restore Job, and it stops the updates when it removes the Job. A PostgreSQL restore that failed two times and then succeeded therefore keeps failed: 2 in its status. A non-zero count on a cluster that serves your data is not a problem.

The condition is useful when it is present, but its absence proves nothing. When a failed PostgreSQL restore keeps PostgreSQL down, the condition can be absent while Ready is Unknown. In that case, read status.pgbackrest.restore on the PostgresCluster. PostgresDataInitialized=False with reason PGBackRestRestoreFailed on the same object gives the same information in one line.

No PostgreSQL pods after a Velero restore

This is not a stuck restore. A restored PostgresCluster is paused on purpose. The pause stops PGO from making an instance and an empty data directory before the PostgreSQL restore. While the pause is on, PGO creates nothing:

kubectl -n "$NS" get postgrescluster "$PGCLUSTER" \
  -o jsonpath='{.spec.paused}{"  "}{range .status.conditions[?(@.type=="Progressing")]}{.status}/{.reason}{end}{"\n"}'
# true  False/Paused

The operator removes the pause in the same reconcile that adds the data source, and it records an event. This state therefore lasts a few seconds. It continues only when the operator does not reconcile:

# Is the operator running at all?
kubectl -n "$NS" get deploy -l app.kubernetes.io/component=operator

# What did it say about the pause?
kubectl -n "$NS" get events --field-selector reason=PostgresRestorePauseReleased
kubectl -n "$NS" get events --field-selector reason=PostgresRestorePauseHeld

A PostgresRestorePauseHeld warning means that the operator saw the pause and kept it on purpose. The cluster has a repository, but the reconcile made no data source. If the operator removed the pause, PGO would make an empty database over the recovery. Find out why the data source is missing, and correct it. You can recover a paused cluster, but you cannot recover an overwritten one. The next reconcile then removes the pause automatically.

Do not remove the pause manually unless the resource already has a data source. Without a data source, PGO has no PostgreSQL restore to run, so it makes an instance and an empty database. Check first:

kubectl -n "$NS" get postgrescluster "$PGCLUSTER" -o jsonpath='{.spec.dataSource}{"\n"}'

PostgreSQL pods appear immediately after a Velero restore

This state looks like success, but it is not. If the restored cluster is not paused, the resource modifier did not apply, and PGO started before the PostgreSQL restore.

Check the pause, not the Velero restore phase. A Velero restore that skipped the modifier reports Completed with zero errors, because Velero saw no problem. A correct one reports the same phase.

kubectl -n "$NS" get postgrescluster "$PGCLUSTER" \
  -o jsonpath='paused={.spec.paused} dataSource={.spec.dataSource}{"\n"}'

On a newly restored cluster, a missing paused field is the symptom. If PostgreSQL already started with an empty data directory, the disaster recovery restore failed. See Rebuild the database from the repository.

Read the Velero restore phase second, because PartiallyFailed shows a different problem:

kubectl -n velero get restore -o custom-columns=\
'NAME:.metadata.name,PHASE:.status.phase,ERRORS:.status.errors,WARNINGS:.status.warnings'

The ConfigMap that the Restore names must exist in the velero namespace before the Velero restore runs. The two install paths supply different objects. Check the one for your install:

# Embedded Cluster
kubectl -n velero get configmap velero-netbox-enterprise-restore-modifiers
# Helm
kubectl -n velero get configmap netbox-enterprise-restore-modifiers

Also make sure that your Restore names the object that your install has. Velero skips the modifier step with no error when it cannot find the reference.

PostgreSQL restore reports success but PostgreSQL never starts

status.pgbackrest.restore shows finished: true, but the instance pod does not become Ready, and NetBox cannot connect. Check the log of the instance pod:

kubectl -n "$NS" logs -l "$INSTANCES" -c database --tail=50

A cycle like this, which repeats about every 10 seconds, is the known failure:

P00 ERROR: [040]: unable to restore to path '/pgdata/pg18_wal' because it contains files
ERROR: Error creating replica using method pgbackrest: ... '--type=standby' exited with code=40
ERROR: failed to bootstrap (without leader)
INFO: Removing data directory: /pgdata/pg18

The PostgreSQL restore left a bootstrap directory and a WAL directory with files in /pgdata, but no data directory. PostgreSQL deletes the data directory after each failed attempt, but it does not touch the WAL directory. The loop therefore cannot stop by itself. To stop it, empty that WAL directory manually.

INSTANCE_POD="$(kubectl -n "$NS" get pod -l "$INSTANCES" -o name | head -1)"

# Check first. Expect pg18_bootstrap and pg18_wal, and NO pg18.
kubectl -n "$NS" exec "$INSTANCE_POD" -c database -- ls /pgdata

Only if the result matches, run this command. Use your own major version in place of pg18:

kubectl -n "$NS" exec "$INSTANCE_POD" -c database -- \
  bash -c 'rm -rf /pgdata/pg18_wal/*'

PostgreSQL promotes itself on the next cycle, about 30 seconds later, and the pod becomes Ready. This is a bootstrap problem, not a data problem, so the recovered data is not damaged.

Confirm this. Do all four checks in Verify. This page tells you that a Ready pod is not proof, and you deleted files to get this one.

If the pod is still not Ready after a few minutes, the loop has a different cause. Read the instance log again, and treat the PostgreSQL restore as not complete.

The repository volume is full

pgbackrest info shows more full backups than retentionFull, PostgreSQL backups fail on write, or PostgresBackupRepoPressure is True. Monitor the disk usage before this occurs:

kubectl -n "$NS" exec "$REPO_HOST_POD" -c pgbackrest -- df -h /pgbackrest/repo1

Lower retentionFull, or make full backups more frequent. Then expire the old PostgreSQL backups. See Change the retention or the full-backup schedule and Free space now. On a storage class that can expand a volume, you can increase repoStorageSize instead. The storage class of Embedded Cluster cannot expand a volume.

When you lower the retention, pgBackRest expires older PostgreSQL backups. You then cannot do a PostgreSQL restore to a point in time inside those backups.

A volume expansion is not immediate, and it can stop with no message. Read the claim back, and wait until the pending condition clears:

kubectl -n "$NS" get pvc -l netboxlabs.com/managed-by=netbox-operator \
  -o custom-columns='NAME:.metadata.name,SIZE:.status.capacity.storage,CONDITIONS:.status.conditions[*].type' \
  | grep repo

A claim that stays at FileSystemResizePending did not grow, and PostgreSQL backups still fail.

PostgreSQL backups run but the disaster recovery backup does not contain them

Two causes are possible, and the second one is more common. Check both.

1. Velero did not capture the repository volume. A hostPath storage class causes this. It also occurs when repoStorageClassName puts the repository on a different storage class from the other volumes. Ask Velero:

kubectl -n velero get podvolumebackups -l velero.io/backup-name="$BACKUP" \
  -o custom-columns='VOLUME:.spec.volume,PHASE:.status.phase,BYTES:.status.progress.bytesDone'

The repository volume must be in the list, Completed, with a non-zero byte count. If it is missing from a list that has other volumes, that is the cause. See Storage requirements.

2. The repository volume does not have the backup-selection label, so Velero does not select it:

kubectl -n "$NS" get pvc -l netboxlabs.com/managed-by=netbox-operator \
  -o custom-columns='NAME:.metadata.name'

You must see a claim with repo in its name. First, make sure that NS is correct. A wrong namespace also prints an empty list, which looks like a missing label.