Postgresql – Cannot restore data using Postgres WAL files

postgresql-9.1recoverytransaction-log

I'm a newbie at PostgreSQL.
I'm trying to setup a streaming replication (postgresql 9.1).
And I want to test backup & restore on local.
I also made backup files successfully (I think so) by using pg_basebackup
But cannot restore data.
Here is how I set my database up.

  • Postgres on server1 (master)

    Configure file: /var/lib/pgsql/9.1/data/postgresql.conf

     # WRITE AHEAD LOG
 wal_level = hot_standby

 # - Archiving -
 archive_mode = on
 archive_command = 'cp %p /var/lib/pgsql/9.1/wal-archive/%f'

 # - Master Server -
 max_wal_senders = 5
 wal_keep_segments = 32

 # - Standby Servers -
 hot_standby = on

  • Start master and insert some data

  • Postgres on server2 (slave)

    Configure file: /var/lib/pgsql/9.1/data/postgresql.conf (content is same as master)

  • Create recovery.conf file on slave

     // vi /var/lib/pgsql/9.1/data/recovery.conf
 standby_mode = 'on'
 primary_conninfo = 'host={master_hostname} port=5432 user=repl_user password={my_password}'

  • Start slave => and data is automatically synced with master

  • Create WAL backup

     // slave
 service postgresql-9.1 stop
 rm -rf /var/lib/pgsql/9.1/data/*
 su - postgres
 pg_basebackup -h {master_hostname} -p 5432 -U repl_user -D /var/lib/pgsql/9.1/data --xlog --checkpoint=spread --progress

  • WAL files is generated in /var/lib/pgsql/9.1/wal-archive on master after pg_basebackup is executed in slave

     // something like this
 000000070000000000000013
 000000070000000000000014
 000000070000000000000015
 000000070000000000000015.00000020.backup

  • Drop one table (master)

     DROP TABLE {table_name}

  • Restore that dropped table using WAL files (master)

     service postgresql-9.1 stop 
 vi /var/lib/pgsql/9.1/data/recovery.conf

 // add this line to recovery.conf
 restore_command = 'cp /var/lib/pgsql/9.1/wal-archive/%f %p'

  • Start master again

     service postgresql-9.1 start

But that dropped table is not restored on master. I don't know why.
I also tried to add some other option, but everything is not worked.

recovery_target_time = '2020-10-29 08:20:00 UTC'
recovery_target_inclusive = false

Please help on how to restore data using WAL files.

If I accidentally drop a table on master, how can I restore it using WAL files? Which configuration should I setup on recovery.conf file? Is just write restore_command in recovery.conf file and start postgres again make server restore the lastest backup point data?

Best Answer

You need to read up on how backup and recovery works. WAL doesn't let you go backwards in time, only forwards.

When restoring you would normally restore your data files from your backup (eg one taken with pg_basebackup). That will get you back to the moment the backup was started. If you want to go further in time, you need to supply the WAL files for changes made since the backup. In your recovery_conf you can specify to restore to the time just before the table was dropped using recovert_target_time = as you have done.

Normally such a restore would be done in a secondary environment. You would then export the dropped table(s) or modified data and import that into your production database (or run update/insert/delete statements to change the state of the database). That way you don't lose other modifications made since the table was dropped.

Related Question