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?
Best Answer
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: