Re: Hot and PITR backups in same server

On Sun, 2020-05-17 at 17:41 +0300, Andrus wrote:
> How to implement hot standby and PITR recovery possibility in same backup server.
>
> Plan is:
>
> 1. Create base backup using
>
> pg_basebackup --checkpoint=fast --verbose --progress --write-recovery-conf -D /var/lib/postgresql/12/standby
>
> 2. Create backup copy of /var/lib/postgresql/12/standby directory for PITR
>
> 3. set max_wal_size in postgresql.conf to 5 TB
>
> 4. Start backup server for hot standby backups.
>
> If data from earlier point of time is required:
>
> 1. Stop backup server
> 2. Replace its data dirctory from of initial data directory contents created in previous p.2
> 4. Copy pg_wal contents from hot standby pg_wal directory to initial pg_wal directory in base backup
> 5. Specify recovery time in postgresql.conf and start backup server to recover to this point of time.
>
> The most suspicius point is p.4 : copying manually pg_wal contents from hot data to base backup data.
>
> It this OK ? Or is some better way to implement hot stadby and PITR possibility in same computer ?
> Postgres 12 in Debian is used.

This is confused or at least confusing.

- "max_wal_size" of 5TB is clearly insane.

- I don't understand what you mean by "Start backup server for hot standby backups".

Do I get it right that you want to copy a streaming replication standby server's data
directory to perform PITR? That doesn't see like a good plan, because the standby
usually won't be much behind the primary server, and you can only recover to a later
point in time.

If you care to elaborate, perhaps the question can be answered.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com