Point in Time recovery on PostgreSQL (10.3.1)

[amit tripathi <amittripathi1445@xxxxxxxxx>]

Hi All,

We need some help with the point in recovery test . We have followed the below steps but after creating recovery.conf file and restarting the machine our DB is getting corrupted and not getting started.

Please look at the below steps and help us analyze what is going wrong there. We have used offline postgresql installer to set up database. Required configuration like wal_level=hot_stanby, archive_mode=on, and archive_command='cp %p /mnt/server/archive/%f' in postgresql.conf are also set properly.

 

1. After DB set up, We have created a tablcespace, db and mapped db with the tablespace. Also created some tables to generate transaction files in pg_wal and archive directory (which is mnt/server/archive) 

2. SELECT pg_start_backup('TestPITR');

3. Taken backup(tar) of Postgresql data directory (opt/PostgreSQL/10/ data)>>tar zcf backup20180810.tar data/

4. SELECT pg_stop_backup();

5. Created some more tables and noted down their timestamp (for PITR, which will be added in recovery.conf file as recovery_target_time )

6. Stopped the DB>>service postgresql-10 stop

7. Created a new directory >>mkdir pgbackup

8. Moved tar file into the pgbackup directory >>mv backup20180810.tar /opt/PostgreSQL/10/pgbackup

9. Copied archive files into the pgbackup directory>>  cp -r /mnt/server/archive/ /opt/PostgreSQL/10/pgbackup/

10. Renamed the old data directory( opt/PostgreSQL/10/ data )to bad.data >>> mv data bad.data (now opt/PostgreSQL/10/ bad.data)

11. untar the  back taken in step 2>>tar -xvf backup20180810.tar

12. Moved the data directory to old path >>>mv /opt/PostgreSQL/10/pgbackup/data/ /opt/PostgreSQL/10/ (Now i have opt/PostgreSQL/10/ data-derived from tar file and with old transaction logs in pg_wal) 

13  Copied the updated pg_wal logs from bad.data to data >>>cp -r /opt/PostgreSQL/10/bad.data/pg_wal/0* /opt/PostgreSQL/10/data/pg_wal

14. Started the database  >> service postgresql-10 start (Our db works fine)

15. Created a recovery.conf file inside data folder >>opt/PostgreSQL/10/ data --given the permission and user as postgres 

Our recovery.conf file has two following parameters. recovery_target_time was taken from step-5

restore_command = 'cp /opt/PostgreSQL/10/pgbackup/archive/%f %p'
recovery_target_time = '2018-08-10 02:56:31'

16. Restarted the db server>> service postgresql-10 restart  

It doesn't restart instead we are getting below error(yesterday's log) on linux host. Also there is no log entry in log folder(opt/PostgreSQL/10/data/log) looks like it has stopped creating logs after restart.

journalctl -xe
Aug 09 06:47:53 scspr0512196001 systemd[1]: Starting PostgreSQL 10 database s
-- Subject: Unit postgresql-10.service has begun start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit postgresql-10.service has begun starting up.
Aug 09 06:47:53 scspr0512196001 pg_ctl[17697]: waiting for server to start...
Aug 09 06:47:53 scspr0512196001 pg_ctl[17697]: pg_ctl: could not start server
Aug 09 06:47:53 scspr0512196001 pg_ctl[17697]: Examine the log output.
Aug 09 06:47:53 scspr0512196001 systemd[1]: postgresql-10.service: control pr
Aug 09 06:47:53 scspr0512196001 systemd[1]: Failed to start PostgreSQL 10 dat
-- Subject: Unit postgresql-10.service has failed
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit postgresql-10.service has failed.
--
-- The result is failed.
Aug 09 06:47:53 scspr0512196001 systemd[1]: Unit postgresql-10.service entere
Aug 09 06:47:53 scspr0512196001 systemd[1]: postgresql-10.service failed.
Aug 09 06:47:53 scspr0512196001 polkitd[740]: Unregistered Authentication Age