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
Last activity: Sep 18, 2023, 07:48 PM