PostgreSQL Streaming Replication

Question

I'm trying to set up two PostgreSQL servers on one machine and perform a streaming replication. I have succeeded once, but when i've tried again following exactly the same steps it doesn't work.. Those are the steps: I have $PGDATA = home/postgresql/9.1/data and $STANDBY = home/postgresql/9.1/data2

1. Set up two nodes:

initdb -D $PGDATA

initdb -D $STANDBY

1. In the master node create a user for replication. I do that in pgAdmin (it does have superuser privileges)

2. In the master node in pg_hba.conf add the part that allows standby to connect:

host replication repuser 127.0.0.1/0 md5

1. In the master node in postgresql.conf set:

max_wal_senders = 1

archive_mode = on

archive_command = 'cp %p ~/postgresql/backup/archivedir/%f'

wal_level = archive

wal_keep_segments = 32

1. Start the master node and do the base backup:

psql -d dellstore2 -c "SELECT pg_start_backup('backup for replication', true)"

rsync -av ${PGDATA}/ $STANDBY --exclude postmaster.pid

psql -d dellstore2 -c "select pg_stop_backup()"

pg_stop_backup says that everything is fine, all the WAL files were archived

1. In the standby (data2) node I create recovery.conf with:

standby_mode = 'on'

primary_conninfo = 'host=127.0.0.1 port=5432 user=repuser password=haslo'

trigger_file = '/home/michau/postgresql/replication.trigger'

restore_command = 'cp /home/michau/postgresql/backup/archivedir/%f "%p"'

1. Start the master node, then start the standby node - replication should start and standby should catch up with the master. That was exactly what happened the first time. Now when I start the standby I get: "Address already in use" error. Of course both standby and master have the same port specified in postgresql.conf (they have exactly the same postgresql.conf files). If I change the port in standby to let's say 5433 then I get:

LOG: database system was shut down in recovery at 2012-06-12 19:48:01 CEST

LOG: entering standby mode

cp: cannot stat /home/michau/postgresql/backup/archivedir/000000010000000000000007: No such file or directory

LOG: consistent recovery state reached at 0/7000070

LOG: record with zero length at 0/7000070

cp: cannot stat /home/michau/postgresql/backup/archivedir/000000010000000000000007: No such file or directory

LOG: streaming replication successfully connected to primary

LOG: redo starts at 0/7000070

And it just hangs here. Running ps -ef | grep postgresql yields:

michau 2491 1898 0 19:46 pts/0 00:00:00 postgres -D /home/michau/postgresql/9.1/data

michau 2493 2491 0 19:46 ? 00:00:01 postgres: writer process

michau 2494 2491 0 19:46 ? 00:00:00 postgres: wal writer process

michau 2495 2491 0 19:46 ? 00:00:00 postgres: autovacuum launcher process

michau 2496 2491 0 19:46 ? 00:00:00 postgres: archiver process last was 000000010000000000000008

michau 2497 2491 0 19:46 ? 00:00:00 postgres: stats collector process

michau 2571 2214 0 19:49 pts/1 00:00:00 postgres -D /home/michau/postgresql/9.1/data2

michau 2572 2571 0 19:49 ? 00:00:01 postgres: startup process recovering 000000010000000000000009

michau 2575 2571 0 19:49 ? 00:00:01 postgres: writer process

michau 2578 2571 0 19:49 ? 00:00:02 postgres: wal receiver process streaming 0/99782DC

michau 2579 2491 0 19:49 ? 00:00:00 postgres: wal sender process repuser 127.0.0.1(42142) streaming 0/99782DC

michau 2586 2491 0 19:51 ? 00:00:00 postgres: michau postgres ::1(49941) idle

michau 2587 2491 0 19:51 ? 00:00:01 postgres: michau dellstore2 ::1(49942) idle

The recovering 0000000010000009 where changing for a while, but for half an hour it doesn't anymore.

I'm sure there is something I must have done the first time and not written down or something, but I am at a complete loss to say what it was. I would appreciate any help.

Answer 1

I went through the steps you posted above and I get the exact errors as you posted but I was able to solve the issue.

I tried to merge your steps with the steps posted in this site http://www.debian-administration.org/article/How_to_setup_Postgresql_9.1_Streaming_Replication_Debian_Squeeze

In addition to the steps you posted, I added 2 more steps from the site I provided. The 2 steps are the followings:

• Delete all the files and folders in the standby server before rsync those from the master server.
• In master's postgresql.conf, set the wal_level to hot_standby instead of archive and set the hot_standby to on.

Answer 2

Your standby is continuously recovering which is exactly how streaming replication works in PostgreSQL. Are you able to log into your standby and run queries? If so, congrats, it is working. If not, please post what happens instead.