Thread: Hot and PITR backups in same server
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.
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
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_walin 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 samedata 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 recoverywhen 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.
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
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 ifserver 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 untransferredWAL 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 servercrash. Andrus.