1

I am trying for PITR on a duplicate node. Here are the steps I am following.

  1. Start backup on source node.
select pg_start_backup('backup')
  1. Copy all the files on destination node using these rsync commands on the destination node:
rsync  -av sourcedb:/TPINFO01/wal_archive/ /TPINFO01/wal_archive
rsync --delete -av sourcedb:/TPINFO01/datadg/tablespace/ /TPINFO01/datadg/tablespace
rsync --delete -av sourcedb:/TPINFO01/datadg/data/ /TPINFO01/datadg/data --exclude 'pg_log' --exclude 'pg_replslot' --exclude 'postgresql.conf' --exclude 'recovery.conf'
  1. Stop the backup on source node
select pg_stop_backup();
  1. Through crontab, keep copying the WAL files from the source DB every min. There is continuous WAL archiving enabled on source DB.
* * * 08 * /home/postgres/rsync_wal.sh > /dev/null 2>&1

rsync_wal.sh

#!/bin/sh

rsync -av sourcedb:/TPINFO01/wal_archive/ /TPINFO01/wal_archive
  1. On next day, I create recovery.conf on targetdb as follows:
recovery_target_time = '2021-08-16 02:00:00 JST'
recovery_target_action = 'pause'
recovery_target_inclusive = 'false'
restore_command = 'rsync -a /TPINFO01/wal_archive/%f %p'
  1. It restores the files but stops with the following error:
2021-08-16 16:33:52.853 JST,,,3608,,611a14e0.e18,1,,2021-08-16 16:33:52 JST,,0,LOG,00000,"database system was interrupted while in recovery at log time 2021-08-16 02:27:59 JST",,"If this has occurred more than once some data might be corrupted and you might need to choose an earlier recovery target.",,,,,,,""
2021-08-16 16:33:53.023 JST,,,3608,,611a14e0.e18,2,,2021-08-16 16:33:52 JST,,0,LOG,00000,"starting point-in-time recovery to 2021-08-16 02:00:00+09",,,,,,,,,""
2021-08-16 16:33:53.120 JST,,,3608,,611a14e0.e18,3,,2021-08-16 16:33:52 JST,,0,LOG,00000,"restored log file ""0000000100009928000000A7"" from archive",,,,,,,,,""
2021-08-16 16:33:53.144 JST,,,3608,,611a14e0.e18,4,,2021-08-16 16:33:52 JST,1/0,0,LOG,00000,"redo starts at 9928/A73B0DA8",,,,,,,,,""
2021-08-16 16:33:53.145 JST,,,3608,,611a14e0.e18,5,,2021-08-16 16:33:52 JST,1/0,0,LOG,00000,"recovery stopping before commit of transaction 74385349, time 2021-08-16 02:30:01.469553+09",,,,,,,,,""
2021-08-16 16:33:53.145 JST,,,3608,,611a14e0.e18,6,,2021-08-16 16:33:52 JST,1/0,0,FATAL,XX000,"requested recovery stop point is before consistent recovery point",,,,,,,,,""

What is wrong here? How can the DB on the target node be corrupted when it wasn't even started? Am I missing some step?

This is what the backup file looks like

START WAL LOCATION: 9928/9D000028 (file 00000001000099280000009D)
STOP WAL LOCATION: 9928/9D000168 (file 00000001000099280000009D)
CHECKPOINT LOCATION: 9928/9D000098
BACKUP METHOD: pg_start_backup
BACKUP FROM: master
START TIME: 2021-08-15 21:58:00 JST
LABEL: backup
START TIMELINE: 1
STOP TIME: 2021-08-15 22:03:44 JST
STOP TIMELINE: 1
Improve this question
2
  • Whatever the reason, the error indicates that the WAL segment that got archived during pg_stop_backup() (and that contains the backup end record) didn't make it to the standby.
    – Laurenz Albe
    Aug 16, 2021 at 19:02
  • That worked, I tried to take the backup again. Ensured that the crontab script to rsync the WAL files was running simultaneously and then followed the same steps as above and it worked this time.
    – Abhishek B
    Aug 17, 2021 at 0:18

2 Answers 2

Reset to default
0

As mentioned in the comments by Laurenz Albe, I was missing the WAL file during the pg_stop_backup().

I repeated the steps above, with the only difference that the cronjob to copy the WAL segments from source to target DB was running continuously during the backup as well so as to ensure that no WAL segments were missed.

It worked this way and I could do PITR for my desired time.

Improve this answer
0

It seems like your WAL archiving is not working reliably.

Rather than using rsync from a cron job to copy WAL segments, which seems to miss segments occasionally, use PostgreSQL's archive_command to archive WAL segments. Then PostgreSQL will make sure that all segments are archived.

Improve this answer
4
  • The archive_command is in place on the source DB which is sending the files from pg_wal folder to wal_archive, from where I am picking those segments. Since it is a PROD DB, I can't change the config file for my testing, which is why I copied the whole backup on a separate DB and had been trying PITR on that one.
    – Abhishek B
    Aug 17, 2021 at 3:56
  • If you have an archive_command anyway, why the cron job? Put the rsync into the archive_command, just like you do in the restore_command.
    – Laurenz Albe
    Aug 17, 2021 at 3:58
  • Maybe I am misunderstanding your comment, but I can't make changes on the source DB's archive_command and add rsync to get the files on this target server, as the source DB is PROD environment and changes are not allowed. I can only make a copy of it. The cron job is on the target server. Also, the WAL segments sent to the wal_archive folder on the source DB are removed by a script every 2 mins and sent to a backup server. So that is most likely the reason my rsync missed some of the WAL segments.
    – Abhishek B
    Aug 17, 2021 at 4:06
  • Yes, that must be the case. Since it seems that you are only experimenting rather than trying to set up a reliable operation, I'll stop worrying.
    – Laurenz Albe
    Aug 17, 2021 at 4:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.