Thread: PostgreSQL 12.4 restore_command in postgresql.auto.conf

PostgreSQL 12.4 restore_command in postgresql.auto.conf

From
Jasmine Ng
Date:

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.