Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
Master Server / Primary Server Set Up
1. Configuration Changes in file “postgresql.conf“
1 2 3 4 5 6 7 8 | listen_address=* port=5432 wal_level=hot_standby max_wal_senders=3 wal_keep_segments=32 hot_standby=on archive_mode=on 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:
1 | host replication postgres SLAVE_SERVER_IP/32 trust |
3. Restart the PostgreSQL service in Primary for changes to get impact:
1 | 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.
1 | pg_ctl -D /usr/local/pgsql/data1/ stop (as postgres user) |
2. Delete Or Rename the existing data directory.
1 | mv /usr/local/pgsql/data1 /usr/local/pgsql/data1_old |
3. Create new data directory.
1 | mkdir /usr/local/pgsql/data1/ |
4.Change owner of newly created data directory.
1 | chown postgres:postgres /usr/local/pgsql/data1/ |
5. Give read, write, execute permission to only postgres user.
1 | chmod 700 /usr/local/pgsql/data1/ |
6. Execute pg_basebackup utility in slave server as postgres user.
1 | 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.
1 2 3 | standby_mode=on primary_conninfo = 'user=postgres host=MASTER-SERVER-IP port=5432' recovery_target_timeline = 'latest' |
8. Start the PostgreSQL service in Slave server.
1 | 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:
1 | SELECT * FROM pg_stat_replication; |
