Platforms:
Windows 2016, Windows 2019
PostgreSQL version:
12.4 Source Binary with our custom MSI installer
Component:
Recovery
Description:
I have a restore TCL script writing restore_command to the ‘recovery.conf’ file since PostgreSQL 9.2. The content of the file has a single restore_command (restore_command='CALL "C:\\Program Files\\Entrust\\ECA\\bin\\RESTWAL.BAT" "c:\\entbackup\\mgrbk20201112190721\\Database\\walfiles\\%f" "%p"') to indicate which backup to restore.
From PostgreSQL v12, the TCL script is updated to write the restore_command to PGDATA\postgresql.auto.conf and then create the recovery.signal file before starting the PostgreSQL service. Please see my test scenario:
Installed PostgreSQL 12.4 on a Windows 2019 VM, database cluster has been initialized and schema created, loaded with data. Created 4 full database base backups to test restore functionality as follow:
Please select the backup to restore.
1) mgrbk20201112190159
2) mgrbk20201112190456
3) mgrbk20201112190721
4) mgrbk20201112190833
Select option 1-4, or 'q' to quit:
I can perform consecutive restores to backup #4, then immediately restores to #1 or any of the listed backups earlier than backup #4. The restore fails if I choose a later backup than the last restored backup (e.g. restored to #1 and then attempt to restore to #2). See error in log:
log:
----
2020-11-16 20:58:15.138 GMT [7712] LOG: database system was interrupted; last known up at 2020-11-12 19:07:36 GMT
2020-11-16 20:58:20.613 GMT [7712] LOG: starting point-in-time recovery to 2020-11-12 19:07:40+00
2020-11-16 20:58:20.690 GMT [7712] LOG: invalid checkpoint record
2020-11-16 20:58:20.690 GMT [7712] FATAL: could not locate required checkpoint record
2020-11-16 20:58:20.690 GMT [7712] HINT: If you are restoring from a backup, touch "C:/easm_entrust_pg_data_12/recovery.signal" and add required recovery options.
If you are not restoring from a backup, try removing the file "C:/easm_entrust_pg_data_12/backup_label".
Be careful: removing "C:/easm_entrust_pg_data_12/backup_label" will result in a corrupt cluster if restoring from a backup.
2020-11-16 20:58:20.694 GMT [4752] LOG: startup process (PID 7712) exited with exit code 1
2020-11-16 20:58:20.694 GMT [4752] LOG: aborting startup due to startup process failure
2020-11-16 20:58:20.703 GMT [4752] LOG: database system is shut down
The same test was performed successfully on the previous PostgreSQL 11.7. Please advise on a solution or a workaround.
Thanks,
Jasmine Ng
Senior Software Developer
Entrust Inc.