1

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.

  • I'm setting up a warm standby by the way – lamvann May 27 '15 at 20:29
1

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. =)

|improve this answer|||||

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.