Postgresql – WAL Streaming Failure PostgreSQL 9.4

postgresql-9.4

I'm trying to setup streaming replication but I'm stuck, please help!

I followed the steps in https://wiki.postgresql.org/wiki/Streaming_Replication

On Master I have
(postgresql.conf)

archive_mode = on
wal_level = archive
max_wal_senders = 5
wal_keep_senders = 150

(pg_hba.conf)

host replication user-with-rep IP/32 md5

On slave I have
(recovery.conf)
primary_conninfo = ' host=IP port=5432 user=USER password=PWD '
standby_mode = 'on'
restore_command = 'copy "\\path\%f" "%p" '

I start services on primary and then do a backup with

psql -c "SELECT pg_start_backup('label', true)"

Then I copy all files from primary to standby with

xcopy from\path\* to\path /s /i /e

finally

psql -c "SELECT pg_stop_backup()"

When I execute pg_ctl start on my standby I get the following

LOG:  database system was interrupted; last known up at 2015-05-27 14:16:41 EDT
LOG:  entering standby mode
LOG:  restored log file "000000010000000000000028" from archive
LOG:  redo starts at 0/28000090
LOG:  consistent recovery state reached at 0/280000B8
LOG:  unexpected pageaddr 0/24000000 in log segment 000000010000000000000029, offset 0
LOG:  started streaming WAL from primary at 0/29000000 on timeline 1

And on my master I have 

LOG:  database system was shut down at 2015-05-27 16:00:14 EDT
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started

And then nothing else happens, please I really need help with this, I appreciate all comments. Any questions, please feel free to ask.

I would like to have this setup first and then do replication slots.

Best Answer

From your log, it looks like you were successful starting streaming replication.

LOG: unexpected pageaddr 0/24000000 in log segment 000000010000000000000029, offset 0

LOG: started streaming WAL from primary at 0/29000000 on timeline 1

To see more in your log, want to change the logging parameters:

log_destination = 'stderr'
logging_collector = on
log_directory = '/var/log/postgresql/9.4'
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 0

Adding hot_standby = on to your postgresql.conf also makes it easier for debugging as well.

Though, what you really should be doing to see if replication is working in your setup is something like this:

  1. Create a table and generate traffic:

    CREATE TABLE t1 (a int, b int);

INSERT INTO t1 (a,b)
    SELECT random()*100000, random()*100000
             FROM generate_series(1,1000000);

  2. Check the status on the primary and secondary with these queries:

Primary:

SELECT * FROM pg_current_xlog_location();

Hot Standby:

SELECT * FROM pg_last_xlog_replay_location();

If the locations are exactly the same, or very close together, then everything is working just fine.

Hope that helps. =)

Related Question