How to take periodic pg_basebackup without losing any WAL files. How to pause wal archive

Question

Environment: PostgreSQL 13.x Docker container.

I took a pg_basebackup and have configured PostgreSQL 13.x with wal_archive=on. And it is working as expected.

I see that it is recommended to take pg_basebackup periodically. How can I rotate the base_backups weekly or daily?

Example: If new pg_basebackup is running every Saturday night, Should we consider stopping/pausing wal_archiving for that duration?

#Locations:
pg_basebackup : /db-backup/basebackup
archive_command: /db-backup/wal_files

So want to move archive db-backup every Saturday.

mv /db-backup /db-backup-old

While performing these Should I pause the wal_archiving process? As per docs we can stop/pause it by setting 24.3.1. Setting up WAL archiving

archive_command = ''

Is this the right approach? If so, should we reload the configuration OR any way we can update this configuration on-the-fly? Note: using Postgres-docker container.

What I am trying to achieve is:

If some data is getting written on DB during DB backup rotation, either it should be in new basebackup OR new wal-files directory.

Please correct me if these confusions are irrelevant.

Answer 1

What I have worked out till now is.

 1. Create basebackup which getting saved to /backup_location/basebackups/
 2. Create /backup_location/basebackups/wal-`date`
 3. cp -r /postgres/wal_archive_location/*  /backup_location/basebackups/wal-`date`/

This will causing keeping some duplicate WAL files. (Like today's backup and tomorrow's backup might have same WAL files as we used cp command.

But no data will be lost. And this works.

Answer 2

You can try pg_basebackup with next options:

pg_basebackup --checkpoint=fast --format=tar --wal-method=stream -D "$BACKUP_DIR/$FNAME.bak"

Where --checkpoint=fast sets checkpoint mode to fast (immediate). So all transactions will be flushed to disk

and --wal-method=stream will stream write-ahead log data while the backup is being taken. This method will open a second connection to the server and start streaming the write-ahead log in parallel while running the backup. Therefore, it will require two replication connections not just one. As long as the client can keep up with the write-ahead log data, using this method requires no extra write-ahead logs to be saved on the source server.