PostgreSQL 12.4 restore_command in postgresql.auto.conf

From: Jasmine Ng <Jasmine(dot)Ng(at)entrust(dot)com>
To: "pgsql-bugs(at)lists(dot)postgresql(dot)org" <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: PostgreSQL 12.4 restore_command in postgresql.auto.conf
Date: 2020-11-18 15:42:04
Message-ID: DM6PR11MB4425F72CBCD896F1F0C6A6B69AE10@DM6PR11MB4425.namprd11.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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.

Browse pgsql-bugs by date

  From Date Subject
Next Message Andres Freund 2020-11-18 19:27:43 Re: BUG #16722: PG hanging on COPY when table has close to 2^32 toasts in the table.
Previous Message Bruce Momjian 2020-11-18 14:55:05 Re: Issues with pg_dump command