Master Server / Primary Server Set Up
1. Configuration Changes in file “postgresql.conf“
archive_command='cp %p /usr/local/archive/%f'
2. Configuration Changes in file “pg_hba.conf” Make an entry for replication user with Slave server IP:
host replication postgres SLAVE_SERVER_IP/32 trust
3. Restart the PostgreSQL service in Primary for changes to get impact:
pg_ctl -D /usr/local/pgsql/data/ restart (as postgres user)
Slave Server/ Secondary Server Set Up
1.Stop the PostgreSQL service in slave server if running.
pg_ctl -D /usr/local/pgsql/data1/ stop (as postgres user)
2. Delete Or Rename the existing data directory.
mv /usr/local/pgsql/data1 /usr/local/pgsql/data1_old
3. Create new data directory.
4.Change owner of newly created data directory.
chown postgres:postgres /usr/local/pgsql/data1/
5. Give read, write, execute permission to only postgres user.
chmod 700 /usr/local/pgsql/data1/
6. Execute pg_basebackup utility in slave server as postgres user.
pg_basebackup -U postgres -h MASTER-SERVER-IP -p 5432 -D /usr/local/pgsql/data1/ -R
7. After backup completion, create or edit “recovery.conf” file.
primary_conninfo = 'user=postgres host=MASTER-SERVER-IP port=5432'
recovery_target_timeline = 'latest'
8. Start the PostgreSQL service in Slave server.
pg_ctl -D /usr/local/pgsql/data1/ start (as postgres user)
9. Check the Postgres log, It will show message as below:
“database system is ready to accept read only connections.”
* To check the replication status, you can run below query in master to get the slave server details:
SELECT * FROM pg_stat_replication;