I'm trying to do Pont In Time Recovery from wal archive using postgres 9.6 and i got question: when i'm doing recovery procedure first time everything went ok, but when i'm doing some changes in database after first recovery, and then doing another recovery - i can't see any changes since the first recovery. Maybe it looks bit confused but im trying to explain:

  • 14:00 create db "111"
  • 14:10 сreate table users

CREATE TABLE users( id integer, name varchar, number varchar, PRIMARY KEY( id ));

  • 14:15 insert

INSERT INTO users(id,name, number) VALUES ('1','1111', '1111');

  • 14:20 doing recovery
       recovery_target_time='2017-03-17 14:13:10'

so i got table users without any records, then i:

  • 14:25 insert

INSERT INTO users(id,name, number) VALUES ('1','1111', '1111');

  • 14:27 insert

INSERT INTO users(id,name, number) VALUES ('2','2222', '222');

  • 14:30 doing recovery

recovery_target_time='2017-03-17 14:23:10' or 14:26, 14:30

And i don't see any changes.

What i'm doing wrong? What i need to do after first recovery?

this is the script doing recovery

service postgresql stop

if [ -d "${pg_data}main/" ]; then
sudo rm -r ${pg_data}main/
echo "removing pg_data"
cp -r ${pg_backup}main/ ${pg_data}
cp ${pg_recovery} ${pg_data}main/
rm -r ${pg_data}/main/pg_xlog/*
chown postgres.postgres -R ${pg_data}
chmod 700 -R ${pg_data}
service postgresql start


wal_level = replica                     

fsync = on 
archive_mode = on               

archive_command = 'cp %p /var/archive/%f'              
archive_timeout = 60


restore_command = 'cp /var/archive/%f %p'

recovery_target_time='2017-03-17 14:27:10'

Assuming your base backup is still from the original database, it will recover for that timeline.

If you want it to follow all the time line switches, then your recovery.conf file should have this line in it:

  • jjanes:Yes that's pretty clear. But when i'm doing first time my 'latest' recovery i got what expecting and then i'm trying to do another recovery and i got only latest version of the DB. No mater what time i'm specify in recovery_target_time. I just need to get back in the state before the first recovery was made – Alexey K Mar 21 '17 at 10:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.