Search code examples
postgresqlpitr

Can not do PITR with specific time in PotgreSQL


I'm doing PITR with below settings and getting error as below.

archive_command = 'test ! -f /usr/wal_archive/%f && cp %p /usr/wal_archive/%f'
restore_command = 'cp /usr/wal_archive/%f "%p"'
recovery_target_time = '2020-12-09 15:20:00.0'
recovery_target_timeline = 'latest'

Error:

2020-12-09 17:43:43.029 IST [39613] LOG:  invalid checkpoint record
2020-12-09 17:43:43.029 IST [39613] FATAL:  could not locate required checkpoint record
2020-12-09 17:43:43.029 IST [39613] HINT:  If you are restoring from a backup, touch "/var/lib/postgresql/12/main/recovery.signal" and add required recovery options.   If you are not restoring from a backup, try removing the file "/var/lib/postgresql/12/main/backup_label".   Be careful: removing "/var/lib/postgresql/12/main/backup_label" will result in a corrupt cluster if restoring from a backup.
2020-12-09 17:43:43.030 IST [39612] LOG:  startup process (PID 39613) exited with exit code 1
2020-12-09 17:43:43.030 IST [39612] LOG:  aborting startup due to startup process failure
2020-12-09 17:43:43.031 IST [39612] LOG:  database system is shut down pg_ctl: could not start server Examine the log output.`

Any hint?


Solution

  • Why do people read blogs if all is spelled out in the documentation?

    1. Set recovery configuration settings in postgresql.conf (see Section 19.5.4) and create a file recovery.signal in the cluster data directory. You might also want to temporarily modify pg_hba.conf to prevent ordinary users from connecting until you are sure the recovery was successful.