CODEWITHSUNDEEP
postgresqlreplication
user1331131
user1331131

Reputation: 427

Postgres replication not starting due to wal error

I am using postgres version 9.3.2 on two servers one master, one primary.

I am setting up replication as follows:-

On master:-

  sudo -u postgres psql -c "CREATE USER replicator REPLICATION LOGIN ENCRYPTED PASSWORD 'FOO’;"

Edit postgresql.conf 

listen_address = '*'
wal_level = hot_standby
max_wal_senders = 32 
checkpoint_segments = 8    
wal_keep_segments = 100

Edit pg_hba.conf

hostssl replication     replicator      <SLAVE IP>/32        md5

On Slave:-

Edit postgresql.conf

wal_level = hot_standby
max_wal_senders = 3
checkpoint_segments = 8
wal_keep_segments = 8
hot_standby = on

Run

sudo service postgresql stop

sudo -u postgres rm -rf /var/lib/postgresql/9.3/main

sudo -u postgres pg_basebackup -h <MASTER IP> -D /var/lib/postgresql/9.3/main -U replicator -v -P

CREATE /var/lib/postgresql/9.3/main/recovery.conf

 standby_mode = 'on'
 primary_conninfo = 'host=<MASTER IP> port=5432 user=replicator password=FOO sslmode=require'
 trigger_file = '/tmp/postgresql.trigger'

Run:-

sudo service postgresql restart

When I restart postgres on the slave I get this error message:-

LOG: database system was shut down at 2015-01-14 09:10:50 GMT 2015-01-14 09:11:01 GMT [16741-2] 
LOG: entering standby mode 2015-01-14 09:11:01 GMT [16741-3] WARNING: WAL was generated with wal_level=minimal, data may be missing 2015-01-14 09:11:01 GMT [16741-4] HINT: This happens if you temporarily set wal_level=minimal without taking a new base backup. 2015-01-14 09:11:01 GMT [16741-5] 
FATAL: hot standby is not possible because wal_level was not set to "hot_standby" on the master server 2015-01-14 09:11:01 GMT [16741-6] HINT: Either set wal_level to "hot_standby" on the master, or turn off hot_standby here. 2015-01-14 09:11:01 GMT [16740-1] 
LOG: startup process (PID 16741) exited with exit code 1 2015-01-14 09:11:01 GMT [16740-2] LOG: aborting startup due to startup process failure ... failed!

Why is this happening? I have checked and rechecked that on the master wal_level is set to hot_standby. On the master running "show all" shows that this is the case? I am at a loss as to what I am doing wrong here.

Upvotes: 5

Views: 6156

Answers (1)

Kamus
Kamus

Reputation: 81

You have to restart primary database again to let the current WAL file replayed on the standby, since this WAL file was generated when wal_level=archive.

Upvotes: 1

Related Questions