Recently I installed Postgresql 9.6 on two Ubuntu servers and configured streaming replication between them. Although there are many articles and guides covering this topic, I noticed that most of them are missing some important steps or they contained wrong information. After the setup was done, I thought I should write it down as a complete, simple guide in my blog for future readers.
Here's the most basic and the simplest way to setup streaming replication in Postgresql 9.6.
On your Master Server
==================
1. First we need to create a user for replication.
su - postgres
psql
CREATE USER replication REPLICATION LOGIN ENCRYPTED PASSWORD '123';
You can list the users with the following command:
\du
Edit the
/etc/postgresql/9.6/main/postgresql.conf file and do the following changes.
listen_addresses = '*'
wal_level = replica
max_wal_senders = 5
wal_keep_segments = 32
archive_mode = on
archive_command = 'test ! -f /var/lib/postgresql/9.6/main/archive/%f && cp %p /var/lib/postgresql/9.6/main/archive/%f''
Now we need to edit the
pg_hba.conf file and add a record for the standy server so that it can connect to the primary.
host replication replication 192.168.3.169/32 md5
Note: 192.168.3.169 is the IP address of standby server.
Now create a new directory inside of the 'main' directory for the archive configuration - run the below command as postgres user:
mkdir -p /var/lib/postgresql/9.6/main/archive/
On your Standby Server
===================
service postgresql stop
We shall keep a backup of the postgres data directory.
mv /var/lib/postgresql/9.6/main /var/lib/postgresql/9.6/main_backup
Now we should migrate the data dir from the master server using the pg_basebackup tool.
pg_basebackup -h 192.168.4.168 -D /var/lib/postgresql/9.6/main -U replication -v -P
Note: 192.168.4.168 is the IP address of the Master server.
After the base backup is complete, we should create a recovery.conf file.
/var/lib/postgresql/9.6/main/recovery.conf
standby_mode = 'on'
primary_conninfo = 'host=192.168.4.168 port=5432 user=replication password=123'
restore_command = 'cp /var/lib/postgresql/9.6/main/archive/%f %p'
trigger_file = '/tmp/postgresql.trigger'
Switch back to the root user.
exit
Start the Postgres service
service postgresql start
Verification
==========
Now we have finished setting up streaming replication between the two servers. To verify that both servers are working as expected, switch to the postgres user and run the following command in the Master Server.
su - postgres
psql -x -c "select * from pg_stat_replication;"
You should see the client_address property pointed to standy server's IP address and the state as streaming.
Note: I took the above restore and archive commands from the Postgresql documentation here:
https://www.postgresql.org/docs/9.6/continuous-archiving.html
That's it. I tried my best to keep it as simple as possible. This is only a basic setup to give you an idea on how to setup streaming replication. For a production environment, you might need to set proper values depending on the requirements.
I hope this post will be helpful for someone :) Don't forget to leave a comment if it helped.
