1

Scenario:

  • Two CentOS 7 machines, with ips 10.0.2.15 and 10.0.2.4
  • Both running PostgreSQL 9.4.6. First one (10.0.2.15) as master, and second one (10.0.2.4) as slave, not listening for clients (just mirroring data via WAL shipping).

Problem: I can make log shipping work (so, slave receives WAL files), but I cannot promote slave to accept connections.

What I did:

In master:

Created user for replication from slave:

psql -c “CREATE USER rep REPLICATION LOGIN CONNECTION LIMIT 1 ENCRYPTED PASSWORD ‘replication’;”

Allowed slave to connect as replication user, with this line in pg_hba.conf:

host    replication    rep    10.0.2.4/32    md5

postgresql.conf:

wal_level = archive
archive_mode = on
archive_command = 'rsync -av %p postgres@10.0.2.4:/var/lib/pgsql/9.4/archive/%f —exclude=postmaster.pid'
max_wal_senders = 3 # Using just 1, but anyway...

In slave:

Deleted data repository

rm -fr /var/lib/pgsql/9.4/data

Made a base backup

pg_basebackup -h 10.0.2.15 -D /var/lib/pgsql/9.4/data -U rep -v -P

(I enter the password, and the base backup is properly made: new data directory now)

Added recovery.conf to new data directory just created. With this info:

standby_mode='off'
restore_command='pg_standby /var/lib/pgsql/9.4/archive %f %p %r'

Directory /var/lib/pgsql/9.4/archive exists in the slave, and it's writable for postgres.

So, the master-slave process is working. WAL files are being sent via rsync. But if I just try this in slave:

pg_ctrl promote

I get the message "server promoting", but nothing happens. And no new messages in the log. Still can't connect to the server via psql. No incoming connections accepted.

What should I do to promote the slave to serve data as a normal server? I'm talking about a high availability scenario, with a warm standby server starting to act as a normal server, allowing clients to connect.

I know I can change parameters in the slave to make it run as normal server, but I just think there should be a quick way to put my slave server to work. I thought it was just pg_ctl promote. But probably, I'm missing something.

Any clues?

1

Ok, made it work by using the -R flag in pg_basebackup. This flag automatically generates the recovery.conf file with the right parameters

From the official doc:

-R
--write-recovery-conf
Write a minimal recovery.conf in the output directory (or into the base archive file when using tar format) to ease setting up a standby server.
  • Just a comment: the recovery.conf generated includes a primary_conninfo parameter. So, this means streaming replication is enabled. If I don't want streaming replication, I should use restore_command instead. – Jorge Arévalo Feb 17 '16 at 23:27
  • The restore_command can be like this: restore_command = ‘cp /var/lib/pgsql/9.4/archive/%f %p’ – Jorge Arévalo Feb 18 '16 at 0:01

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.