I'm using Windows Server 2008 R2 with 64-bit PostgreSQL 9.2 on both test machines. What I wish to achieve is that one server will be a master and other will be a slave. This is just for tests, finally the servers will work in 1 master - N slaves scenario. Once for each 24h, all changes from the master are meant to be replicated on the slave(s).
I decided to use native replication without streaming (no need for that). Ideally I wish just to deliver manually (via some script) WALs that are needed to the slave, restart the machine and let it update itself.
So I set up my master:
postgresql.conf:
wal_level = hot_standby
archive_mode = on
archive_command = 'copy "%p" "C:\\archive\\%f"'
And my slave machine:
postgresql.conf:
hot_standby = on
recovery.conf:
restore_command = 'copy "C:\\archive\\%f" "%p"'
Then I started the master machine, I wrote a few things to database. I executed the pg_start_backup('base');
command. Then, replaced whole 'data' folder on the slave with files from the master (just without the configs and postmaster.pid file). Now, I executed pg_stop_backup();
and copied files between my master's and slave's archive folders. After starting the slave's PostgreSQL service everything went good - database was there.
I deleted WALs from archive folders, did a small change in the master's db, again start_backup with stop_backup, copied the WALs, and... it does not work. I even tried the simplest case - once it worked after first copying, I restarted the service. Again failure. Each time it logs:
WARNING: WAL was generated with wal_level=minimal, data may be missing
HINT: This happens if you temporarily set wal_level=minimal without taking a new base backup.
FATAL: hot standby is not possible because wal_level was not set to "hot_standby" on the master server
HINT: Either set wal_level to "hot_standby" on the master, or turn off hot_standby here.
LOG: startup process (PID 4320) exited with exit code 1
LOG: aborting startup due to startup process failure
I double checked my configs and they are fine; Wal level is clearly set to hot_standby
. Any idea what am I doing wrong?