Support #698
Updated by Daniel Curtis about 9 years ago
{{>toc}}
This is a guide for setting up master-slave replication with PostgreSQL 9.4 on Debian 8.
*NOTE*: The example servers will be
# pg1.example.com as the *master* at 192.168.0.10
# pg2.example.com as the *slave* at 192.168.0.20
h2. Prepare Master Server
* Enable the contrib contributor apt repositories:
<pre>
sudo sed -i '' -e 's/main/main\ contrib/' /etc/apt/sources.list
</pre>
* Make sure the system is up to date:
<pre>
sudo apt-get update && sudo apt-get upgrade
</pre>
* Install postgresql:
<pre>
sudo apt-get install postgresql postgresql-contrib postgresql-client rsync
</pre>
* Set a password for the postgresql admin user:
<pre>
sudo passwd postgres
</pre>
* Switch to Postgres account:
<pre>
sudo su - postgres
</pre>
* Generate ssh key:
<pre>
ssh-keygen
</pre>
* Copy SSH key to opposite server:
<pre>
ssh-copy-id pg2.example.com
</pre>
*NOTE*: Make sure to see the postgres password on pg2.example.com before copying the SSH id
* Create an account for replication
<pre>
psql -c "CREATE USER replicator REPLICATION LOGIN CONNECTION LIMIT 1 ENCRYPTED PASSWORD 'SuperSecretReplicationPassword';"
</pre>
* Exit from the postgres user:
<pre>
exit
</pre>
* Modify postgres access config file:
<pre>
sudo nano /etc/postgresql/9.4/main/pg_hba.conf
</pre>
#* Add content below:
<pre>
host replication replicator 192.168.0.20/32 md5
</pre>
* Then modify postgres config file:
<pre>
sudo nano /etc/postgresql/9.4/main/postgresql.conf
</pre>
#* The config value should be:
<pre>
listen_addresses = 'localhost,192.168.0.10'
wal_level = 'hot_standby'
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 1
hot_standby = on
</pre>
* Restart Master server:
<pre>
sudo service postgresql restart
</pre>
h2. Prepare Slave Server
* Enable the contrib contributor apt repositories:
<pre>
sudo sed -i '' -e 's/main/main\ contrib/' /etc/apt/sources.list
</pre>
* Make sure the system is up to date:
<pre>
sudo apt-get update && sudo apt-get upgrade
</pre>
* Install postgresql:
<pre>
sudo apt-get install postgresql postgresql-contrib postgresql-client rsync
</pre>
* Set a password for the postgresql admin user:
<pre>
sudo passwd postgres
</pre>
* Switch to Postgres account:
<pre>
sudo su - postgres
</pre>
* Generate ssh key:
<pre>
ssh-keygen
</pre>
* Copy SSH key to opposite server:
<pre>
ssh-copy-id pg1.example.com
</pre>
*NOTE*: Make sure to see the postgres password on pg1.example.com before copying the SSH id
* Create an account for replication
<pre>
psql -c "CREATE USER replicator REPLICATION LOGIN CONNECTION LIMIT 1 ENCRYPTED PASSWORD 'SuperSecretReplicationPassword';"
</pre>
* Exit from the postgres user:
<pre>
exit
</pre>
* Stop PostgreSQL:
<pre>
sudo service postgresql stop
</pre>
* Modify postgres access config file:
<pre>
sudo nano /etc/postgresql/9.4/main/pg_hba.conf
</pre>
#* Add content below:
<pre>
host replication rep 192.168.0.10/32 md5
</pre>
* And modify postgres config file:
<pre>
sudo nano /etc/postgresql/9.4/main/postgresql.conf
</pre>
#* The config value should be:
<pre>
listen_addresses = 'localhost,192.168.0.20'
wal_level = 'hot_standby'
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 1
hot_standby = on
</pre>
h2. Start the Replication
h3. Master Server
* Begin the replication
<pre>
sudo su - postgres
psql -c "select pg_start_backup('initial_backup');"
rsync -cva --inplace --exclude=*pg_xlog* /var/lib/postgresql/9.4/main/ -e ssh pg2.example.com:/var/lib/postgresql/9.4/main/
psql -c "select pg_stop_backup();"
exit
</pre>
h3. Slave Server
* Create recovery.conf file:
<pre>
sudo nano /var/lib/postgresql/9.4/main/recovery.conf
</pre>
#* And add the content below to that file:
<pre>
standby_mode = 'on'
primary_conninfo = 'host=192.168.0.10 port=5432 user=replicator password=SuperSecretReplicationPassword'
trigger_file = '/tmp/postgresql.trigger.5432'
</pre>
* And restart Slave Server to complete the setup:
<pre>
sudo service postgresql start
</pre>
* Check the postgresql log file:
<pre>
tail -n 20 /var/log/postgresql/postgresql-9.4-main.log
</pre>
#* _Example output_:
<pre>
2015-11-21 14:47:31 PST [3237-1] LOG: started streaming WAL from primary at 0/19000000 on timeline 1
2015-11-21 14:47:31 PST [3238-1] [unknown]@[unknown] LOG: incomplete startup packet
2015-11-21 14:47:32 PST [3241-1] postgres@postgres FATAL: the database system is starting up
2015-11-21 14:47:32 PST [3236-3] LOG: redo starts at 0/19000028
2015-11-21 14:47:32 PST [3236-4] LOG: consistent recovery state reached at 0/19000128
2015-11-21 14:47:32 PST [3235-1] LOG: database system is ready to accept read only connections
</pre>
h2. Resources
* https://sonnguyen.ws/replication-master-slave-with-postgresql-9-4-in-ubuntu-14-04/
* https://www.debian-administration.org/article/677/How_to_setup_Postgresql_9.1_Streaming_Replication_Debian_Squeeze
* https://wiki.postgresql.org/index.php?title=Binary_Replication_Tutorial#Binary_Replication_in_6_Steps
* http://owocki.com/streaming-replication-postgres-9-4-ubuntu/
* https://wiki.postgresql.org/wiki/Hot_Standby