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 afterpg_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?