Hot and PITR backups in same server

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Hot and PITR backups in same server

Andrus Moor
Hi!

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.

Andrus.


Reply | Threaded
Open this post in threaded view
|

Re: Hot and PITR backups in same server

Laurenz Albe
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



Reply | Threaded
Open this post in threaded view
|

Re: Hot and PITR backups in same server

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



Reply | Threaded
Open this post in threaded view
|

Re: Hot and PITR backups in same server

Kyotaro Horiguchi-4
At Mon, 18 May 2020 11:11:44 +0300, "Andrus" <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: Hot and PITR backups in same server

Andrus Moor
Hi!

Thank you.

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

So pg_receivewal  should running in parallel with hot standy server to receive wal files to separate directory.

Will each wal file transferred two times in this case? One time by hot standby server and second time by pg_receivewal.
Main server if priced by amount of bandwidth.
How to receive each wal file only once ? This would allow to decrease network bandwidth and thus cost two times.

pg_receivewal has compression option. Will this compress WAL files before transfer over network ?

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

For hot standby and PITR in same server the following steps are required:

1. Create base backup using pg_basebackup
2. Create copy of base backup for PITR
3. Start hot standby server using data directory created in p.1
4. Run pg_receiceval as background process to save wal files to backup created in p.2

If PITR is required, specify recovery time in base backup created in p.2 in postgresql.conf
Start second postgresql server instance in port 5433 which uses this backup for recovery.

Is this OK or should something changed?

How to run pg_receivewal in background ? Is there some option which creates such service so will automatically restart if server is
restarted ?

How to allow main server to keep sufficient number of WAL segments ?
Replication slot cannot used: if backup server stops replication_slot causes main server to fill disk space with untransferred WAL
files.
After  that main server will also stop with "no space left on device" error.

Or is there some option like to reserve some disk space or limit wal size so that main server can continue on backup server crash.

Andrus.