0

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?

5
  • Shouldn't your recovery.conf file be located on your slave?
    – Earl
    Nov 2, 2020 at 10:54
  • 1
    You need to restore a basebackup taken before you dropped the table! WAL only contains changes made since that backup. You can't go backwards with WAL. Nov 2, 2020 at 11:42
  • NB You are using an absolutely ancient version of Postgres. It last received a maintenance update 4 years ago and is a staggering 9 releases behind the latest supported release. Nov 2, 2020 at 11:53
  • @Colin'tHart my company use this old version of Postgres. My duty is maintaining it and make sure nothing wrong happen. Problem now is wal-archive folder's file is too much and it takes much server's storage. I can remove it with pg_archivecleanup. But if something wrong happen, I need to restore data to the old state.
    – VitDuck
    Nov 6, 2020 at 4:36
  • @Earl if I put recovery.conf in slave server, can I recognize WAL file in master server and restore data base on it?
    – VitDuck
    Nov 6, 2020 at 6:46

1 Answer 1

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.

3
  • sorry for late response, so how can i restore data using data files from backup (that is taken with pg_basebackup)? Is that the file end with '.backup' in wal-archive folder? Can you give me some command, or reference link. It is so appropriate.
    – VitDuck
    Nov 6, 2020 at 4:34
  • I have no idea how your backups are structured. You need to find a way to restore the Postgres data directory -- that needs to be in your backups somewhere. Nov 6, 2020 at 8:26
  • This is exactly what I want to ask. I don't know to restore data on slave base on WAL files that stored in master. I understand your explanation about how backup is working. But if I write recovery.conf file like I described above, it seems that not relevant to master at all. My Postgres data is stored in '/var/lib/pgsql/9.1/data/'. After run pg_basebackup on slave server, data is all copied to slave server. Is that good for backup part?
    – VitDuck
    Nov 6, 2020 at 8:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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