0

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?

0

This was most probably an issue on my part. I decided to do everything again, but this time making a full streaming replication following the excellent guide here: https://www.youtube.com/watch?v=Zq4zS0ZbXsc

Then, I stripped this solution from the streaming part (removed connection from hba file and a line from recovery.conf). Just in case someone would want to achieve a similar solution - do not compare WAL files by their 'Last Modified' date! You are interested in copying WALs newer by their 'Create' date. This is due to the fact that PostgreSQL creates some files and then for its own reason changes their 'Last Modified' dates to the ones in the past.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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