Re: Difference in the tablespace folders on primary and secondary nodes - Mailing list pgsql-general

Greetings,

* Abhishek Bhola (abhishek.bhola@japannext.co.jp) wrote:
> > Basically, this is not a valid way to perform a backup/restore of PG.
>
> Is it not valid only for PG 15 or even for earlier versions? I have always
> referred to this https://www.postgresql.org/docs/8.1/backup-online.html

For earlier versions too.

> > better, use an existing well maintained backup/restore tool for this
>
> Is there any tool you could recommend? A tool to bring back the standby
> node when it was down for a day or so for some OS/firmware upgrade.
> I have tried pgBackRest, but it requires a control node. So anything that
> doesn't involve a 3rd server for this situation would be helpful.

pgBackRest doesn't actually require a control node, though it's
certainly recommended to have one.

Also, if you use physical replication slots, you can have the primary
hold on to the WAL necessary for the replica to catch back up until the
replica is back and then it can just fetch the WAL from the primary and
catch up without any kind of re-sync being necessary.

If you're worried about running out of space on the primary for this
(which is an entirely valid concern...) then you could ship the WAL
files to an archive system or location using pgBackRest or possibly some
other tool and then have the replica fetch the WAL from there once it's
back online.

pgBackRest also has the ability to do incremental backups and delta
restores.  An incremental backup will only store the data that's changed
since the prior backup, minimizing the storage space in the repo and the
time required for the backup.  A delta restore will only restore those
files on the replica which are different from what was in the backup and
that can be done using timestamp/file-size because pgBackRest tracks
that information and will set it on restore.  pgBackRest does also have
an option to do checksum-based restores, which it will automatically use
if anything looks odd regarding the timestamps.

> > this also doesn't grab and restore the absolutely required
> > backup_label file that's returned from pg_backup_stop()
>
> I tried running pg_backup_start('backup') and pg_backup_stop() on my DB,
> but I did not see any backup_label_file being created.
> psql (15.1)
> Type "help" for help.
>
> postgres=# select pg_backup_start('backup');
>  pg_backup_start
> -----------------
>  68/32000028
> (1 row)
>
> postgres=# select pg_backup_stop();
> NOTICE:  all required WAL segments have been archived
>                                 pg_backup_stop
> -------------------------------------------------------------------------------
>  (68/32000100,"START WAL LOCATION: 68/32000028 (file
> 000000010000006800000032)+
>  CHECKPOINT LOCATION: 68/32000060
>   +
>  BACKUP METHOD: streamed
>    +
>  BACKUP FROM: primary
>   +
>  START TIME: 2023-07-26 08:51:28 JST
>    +
>  LABEL: backup
>    +
>  START TIMELINE: 1
>    +
>  ","16724 /PGDATA/datadg/tbs1
>   +
>  16725 /PGDATA/datadg/tbs2
>    +
>  ")
> (1 row)
>
> I read the documentation on this page
> https://www.postgresql.org/docs/current/functions-admin.html

What's returned from pg_backup_stop() is the backup_label that needs to
be stored with the files which were part of the backup.  Note that you
should *not* store the backup_label in the data directory of the primary
because if the system crashes then it won't come back up without someone
going in and removing that file.  That's how the old exclusive method
worked which was deprecated and then finally removed because of this
issue.

> > The desired contents of the backup label file and the tablespace map file
> > are returned as part of the result of the function and must be written to
> > files in the backup area.
>
> I don't understand, "*must be written to files in the backup area*". Does
> it mean we need to manually create a file first on the master node and then
> rsync it to the backup node?

No, don't do that.  You should back up all the files and then store the
backup_label with those files.  Do *not* put a backup_label into the
data directory on the primary.

* Abhishek Bhola (abhishek.bhola@japannext.co.jp) wrote:
> I got the latest documentation
> <https://www.postgresql.org/docs/current/continuous-archiving.html#BACKUP-LOWLEVEL-BASE-BACKUP>and
> understood that I was supposed to run
> `select * from pg_backup_stop();`  and store the labelfile output to a file
> in the data directory of the secondary node.

The backup_label should be stored with the backed up files and then
restored as part of restoring the backup, yes.

> I also understood that this Low-level API backup is not really
> a recommended way and I need to move to another method.

I would recommend that, yes.

> However, till I fully set up a tool like pgBackRest (of which you are one
> of the contributors - very impressive!) for PROD, can you please tell me:
> 1) Are there any other options that are safe to use yet fast? Like you said
> rsync --size-only would miss a lot of changes, but anything that would also
> not start syncing every data file with only different timestamp, even
> though it is exactly the same, including the checksum.

Re-syncing a replica due to it being offline for OS patching or such
really isn't typically the approach to use- better to have a WAL archive
and just let the replica catch up by playing the WAL forward.  A re-sync
might make sense if the replica becomes very far out of date such that
there's a huge backlog of WAL or if you're generating a huge amount of
WAL that WAL replay just can't keep up, but I'm guessing neither of
those are really the case here.  If they are though, increental backups
and delta restores with pgBackRest is an approach that has worked for
high-throughput systems like those.

> 2) While rsyncing from the master node, do I need to exclude `pg_wal`
> folder?

Along with a bunch of other ones, as discussed in the documentation.

> 3) The replica/standby node should have access to the WAL segments archived
> during time between start and stop backup, right?

To perform any recovery of an online backup, all of the WAL generated
during the backup (between the pg_backup_start and pg_backup_stop) must
be preserved and made available via the restore_command for replay.

> 4) What kind of data corruption would have been introduced due to the
> absence of labelfile and if there is a way to identify the corruption? So
> for example, if I am able to reindex all the tables, select all the data
> and vacuum the db, do I still need to pg_dump and pg_restore those DBs?

Any kind.  Even taking those steps wouldn't provide a guarantee that
there's no corruption.  Doing a pg_dump/pg_restore would at least help
you find any data that is invalid according to the constraints which are
set in the database and that's a good first step but there could still
be other corruption.

Note that the corruption being discussed here would only be on the
replica, because that's where the writes that happened between the
backup start and the backup end got lost due to the lack of a backup
label.  Of course, if you've failed over to the replica, then you'd end
up with the now-primary having that corruption.

Thanks,

Stephen