Re: Backup Question

Shaun Thomas wrote:
>> Wrong. The database cannot check all data for consistency
>> upon backup. For one, that would take way too long.
>
> Well, what I meant, was that it would stop the database if it couldn't
> apply one of the transaction logs for whatever reason. It wasn't
> "inconsistent enough" for that. :)

It would not stop the database, it would stop applying the WALs
and run into an endless loop (contact primary, get told that it doesn't
have the WAL, try to read archive logs, fail, reiterate).

> > If you backup the standby, then you won't have a backup_label file.
> > You cannot restore a backup without that.
>
> Well, the backup_label gets copied to the archive log path when
> pg_stop_backup gets called. So, I do have it. But beyond that, I have
> the start/stop WAL locations, so I can get all the required files to apply,
> which are all that is really necessary.
>
>> 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 suppose I could build in a function to pause the backup until the
> restartpoint replays on the replica. Then at least, the backup "starts"
> on both systems with the same assumptions.

I'm not sure if checkpoints on primary and standby are synchronized.

Why don't you just shutdown the standby and take an offline
backup? That could certainly be used for PITR.
After the backup, restart the standby and let it catch up.

Yours,
Laurenz Albe