Re: Hot and PITR backups in same server

Hi!

>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?

I want to copy only pg_wal directory contents from this.

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.

>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.

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.

Andrus.