Cannot restore data using Postgres WAL files

Question

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?

Answer 1

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 recovery_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.