Sample Header Ad - 728x90

Cannot restore data using Postgres WAL files

0 votes
1 answer
1610 views
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?
Asked by VitDuck (1 rep)
Nov 2, 2020, 10:27 AM
Last activity: Sep 18, 2023, 07:48 PM