Re: Backup Question

Shaun Thomas wrote:

> I have a revised backup process that's coming out inconsistent, and I'm not entirely sure why. I call
> pg_start_backup(), tar.gz the contents elsewhere, then pg_stop_backup(). Nothing crazy. Upon restore,
> two of my tables report duplicate IDs upon executing my redaction scripts. The "duplicate" records
> ended up having different ctid's, suggesting the log replay was incomplete. However, nothing in the
> restore logs suggest this is the case, and either way, the database wouldn't have come up if it were.
> (right?)

Wrong. The database cannot check all data for consistency
upon backup. For one, that would take way too long.

> Now, the main difference, is that I'm doing the backup process on our streaming replication node. The
> backup process calls the pg_start_backup() function on the upstream provider, backs up the local
> content, then calls pg_stop_backup() on the upstream provider. In both cases, it captures the
> start/stop transaction log positions to grab all involved archived WAL files. I already know the start
> xlog position is insufficient, because those transaction logs may not have replayed on the standby
> yet, so I also grab 3xcheckpoint_timeout extra older files (before backup start), just in case.
>
> So, I get no complaints of missing or damaged archive log files. Yet the restore is invalid. I checked
> the upstream, and those duplicate rows are not present; it's clearly the backup that's at fault. I
> remember having this problem a couple years ago, but I "fixed" it by working filesystem snapshots into
> the backup script. I can do that again, but it seems like overkill, honestly.

If you backup the standby, then you won't have a backup_label file.
You cannot restore a backup without that.

Moreover, recovery needs a checkpoint/restartpoint to start.
Restartpoints on the standby won't be the same as checkpoints
on the primary, so I believe that even with the backup_label
file you would not be able to restore the data.

I'm not sure about the second point, maybe somebody can confirm or refute that.

Yours,
Laurenz Albe