Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
  2. In the master node create a user for replication. I do that in pgAdmin (it does have superuser privileges)
  3. 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
  4. 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

  5. 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

  6. 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"'

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

share|improve this question
    
To clarify your configuration on the slave, what port numbers do you for these keys in postgresql.conf? 'port' and 'primary_conninfo' ? The former should be 5433 and the latter 5432. –  Mark Stosberg Jun 12 '12 at 19:35
1  
That's what I have right now, but the replication is not working. Shouldn't the ports be the same in master and slave so in case of failover the slave can replace the master? –  michauwilliam Jun 12 '12 at 19:50
1  
So what doesn't work? According to the ps output, the replication seems to work. –  Daniel Vérité Jun 12 '12 at 23:06
1  
The ports cannot be the same when they run on the same machine. Failover is normally done with more then one physical machine as with a single machine it is pointless. –  Eelke Jun 13 '12 at 5:34

2 Answers 2

The steps above are not for a Streaming Replication but for a WAL Archiving Replication setup. If you want to setup Streaming Replication take a look at this instruction Streaming Replication Setup.

share|improve this answer

PostgreSQL replicas never finish recovering. This is by design. Basically a replica is always in "recovering from disaster" mode except that it is using receiving the WAL segments from the master rather than on disk.

So what you are seeing is not cause for concern. If it is not working yet, then you will need to provide a more detailed description of what you are trying to do and what is not working. But as far as you are posting it seems normal.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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