My scenario is I have two servers at addresses 192.168.1.191 and 192.168.1.171, and installed Ubuntu 14.04 and PostgreSQL 9.4.
To know how to install PostgreSQL, please read Install PostgreSQL 9.4 in Ubuntu 14.04
Step 1: Make sure that the two servers are able to SSH freely between them
Switch to Postgres account:
Generate ssh key:
Copy SSH key to opposite server:
|
ssh-copy-id opposite_ip_address |
Note: you have to do this step for the both servers
Step 2: Setup Master Node
Create an account for replication
|
psql -c "CREATE USER rep REPLICATION LOGIN CONNECTION LIMIT 1 ENCRYPTED PASSWORD 'yourpassword';" |
Modify postgres access config file:
|
vi /etc/postgresql/9.4/main/pg_hba.conf |
Add content below:
|
host replication rep IP_address_of_slave/32 md5 |
And modify postgres config file:
|
vi /etc/postgresql/9.4/main/postgresql.conf |
The config value should be:
|
listen_addresses = 'localhost,IP_address_of_THIS_host' wal_level = 'hot_standby' archive_mode = on archive_command = 'cd .' max_wal_senders = 1 hot_standby = on |
And now, restart Master server:
|
service postgresql restart |
Step 3: Setup Slave Node
Stop PostgreSQL:
Modify postgres access config file:
|
vi /etc/postgresql/9.4/main/pg_hba.conf |
Add content below:
|
host replication rep IP_address_of_master/32 md5 |
And modify postgres config file:
|
vi /etc/postgresql/9.4/main/postgresql.conf |
The config value should be:
|
listen_addresses = 'localhost,<span class="highlight">IP_address_of_THIS_host</span>' wal_level = 'hot_standby' archive_mode = on archive_command = 'cd .' max_wal_senders = 1 hot_standby = on |
Step 4: Start the replication
In Master Server, run command below to start the replication
|
psql -c "select pg_start_backup('initial_backup');" rsync -cva --inplace --exclude=*pg_xlog* /var/lib/postgresql/9.1/main/ slave_IP_address:/var/lib/postgresql/9.1/main/ psql -c "select pg_stop_backup();" |
In Slave Server, create recovery.conf file:
|
vi /var/lib/postgresql/9.4/main/recovery.conf |
And add the content below to that file:
|
standby_mode = 'on' primary_conninfo = 'host=master_IP_address port=5432 user=rep password=yourpassword' trigger_file = '/tmp/postgresql.trigger.5432' |
And restart Slave Server to complete the setup:
It is time for testing, you can try to create and insert data to Master Server. It will be synchronized to Slave Server