Re: Hot and PITR backups in same server - Mailing list pgsql-general

At Mon, 18 May 2020 11:11:44 +0300, "Andrus" <kobruleht2@hot.ee> wrote in 
> Hi!
> 
> >This is confused or at least confusing.
> >- "max_wal_size" of 5TB is clearly insane.

As Laurentz said, even if you set it to 5TB, no WAL files older than
the files needed by the last checkpoint don't remain. If you don't
need a hot-standby, you can use pg_receivewal to save WAL files on the
"standby" server.  If you need the hot-standby, WAL files should be
archived.

> >- 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?
> 
> I want to copy only pg_wal directory contents from this.

If so, what you need seems to be pg_receivewal, not a full-fledged
server.

> After pg_basebackup has finished, copy of its data directory is saved
> for possible PITR.
> Its pg_wal contents will be replaced with current pg_wal directory
> contents.
> 
> After that recovery time will set it postgres.conf and separate server
> in 5433 is used to preform PITR using this data directory.
> 
> >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.
> 
> I will get data from copy taken when hot backup server was created and
> replace its pg_wal directory contents from pg_wal in current backup
> server pg_wal directory.
> Since max pg wal size is big it should contain all WAL segments from
> time where base backup was taken.

As mentioned above, that assumption is wrong.  You need to archive WAL
files out of pg_wal directory, or pg_recievewal.

> >If you care to elaborate, perhaps the question can be answered.
> 
> Currently binary async hot standby backup server is working OK and
> replicates main sever database almost immediately.
> 
> However if important data is deleted in same day, it cannot recovered
> since hot standby server database has replicated same data as in
> master server.
> Master server disk space is limited. It cannot keep large number of
> wal segments.
> Standby server has lot of free disk space.

recovery_min_apply_delay might be useful for you.  With the setting,
the standby follows the primary after that minutes or hours.

https://www.postgresql.org/docs/12/runtime-config-replication.html#GUC-RECOVERY-MIN-APPLY-DELAY

I haven't even tried by myself, but the following steps would perhaps
work.

0. If data is found to be broken on the primary.

1. Stop the standby immediately and take a cold backup including
 pg_wal directory.

2. You may find the just dropped data in the standby.

3. If you need to advance the standby, you can proceed to recover upto
  arbitrary LSN after x min ago using recovery_target_lsn/time.  If
  you go too far, start again from the backup taken in the step 1.
  
https://www.postgresql.org/docs/12/runtime-config-wal.html#RUNTIME-CONFIG-WAL-RECOVERY-TARGET


> I'm looking for a way to collect data for PITR recovery (WAL segments)
> in standby server.
> 
> I have initial base backup of data directory created using
> pg_basebackup.
> All WAL segments from master server after pg_basebackup should saved
> in backup server and should be used for PITR recovery when needed.
> 
> How to use hot standby server for this ?
> 
> Or should separate cluster in 5433 port created and pg_rewind or
> something other used for PITR.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center