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.
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)
SELECT pg_start_backup('TestPITR');
Taken backup(tar) of Postgresql data directory (opt/PostgreSQL/10/ data)>>tar zcf backup20180810.tar data/
SELECT pg_stop_backup();
Created some more tables and noted down their timestamp (for PITR, which will be added in recovery.conf file as recovery_target_time )
Stopped the DB>>service postgresql-10 stop
Created a new directory >>mkdir pgbackup
Moved tar file into the pgbackup directory >>mv backup20180810.tar /opt/PostgreSQL/10/pgbackup
Copied archive files into the pgbackup directory>> cp -r /mnt/server/archive/ /opt/PostgreSQL/10/pgbackup/
Renamed the old data directory( opt/PostgreSQL/10/ data )to bad.data >>> mv data bad.data (now opt/PostgreSQL/10/ bad.data)
untar the back taken in step 2>>tar -xvf backup20180810.tar
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
Started the database >> service postgresql-10 start (Our db works fine)
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'
Restarted the db server>> service postgresql-10 restart
It doesn't restart also there is no log entry in log folder(opt/PostgreSQL/10/data/log) looks like it has stopped creating logs after restart.
