This is a re-creation of original post from my old blog.
This tutorial will walk through configuring master/slave servers for PostgreSQL 9.3 using streaming replication.
- You are not trying to configure an existing data set (i.e. fresh install)
- You are running Ubuntu 14.04 LTS
- You have run apt-get update ; apt-get upgrade
- You have two servers to use as a master and a slave
- $HOME and ~/ for the user postgres are set to /var/lib/postgresql
- We will work out of /var/lib/postgresql for all/most commands
- We are not using archiving
Also important to note is that all actions should be run as postgres user, NOT ROOT.
su - postgres cd /var/lib/postgresql
- master (db1 / 192.168.1.10)
- slave (db2 / 192.168.20)
- Data (/var/lib/postgresql/9.3/main)
- Configs (/etc/postgresql/9.3/main)
Step 1 - Stop pgsql
service postgresql stop
Step 2 - Remove existing data
rm -rf /var/lib/postgresql/9.3/main/*
Step 3 - Configure pg_hba.conf on master
To allow our slave to connect to our master, we will need to make sure the pg_hba.conf has an entry for our replicator role to connect.
Now what we want to do is add two entries (ssl and non-ssl) for the user/role replicator.
host replication replicator 192.168.1.20/32 md5 hostssl replication replicator 192.168.1.20/32 md5
This now allows user replicator from IP 192.168.1.20 using md5 password method.
Step 4 - Configure postgresql.conf on master
Next, we will update the postgresql.conf file and change the settings required to get streaming replication working.
There are X settings to change, you can make them look like the following:
listen_addresses = '*' wal_level = hot_standby max_wal_senders = 3 wal_keep_segments = 8 checkpoint_segments = 8
Be sure to check the last setting, checkpoint_segments, as this may have been manually updated if you use the pgtune tool.
Step 5 - Initialize the database on master
Now we are ready to initialize the database directory on the master server. This will create all the necessary files/directories needed to run normally.
At this point we can start postgresql on the master server.
service postgresql start
We should now have a fresh instance of postgresql up and running.
Step 6 - Add replicator user to master
We now need to add our replicator user which will be used to connect and read the replication pseudo-table.
psql -c "CREATE USER replicator REPLICATION LOGIN ENCRYPTED PASSWORD 'your-password';"
Now that our master server is primed and ready, we can move onto the slave.
Step 7 - Configure postgresql.conf on the slave
At this point I would add a note that the following worked for me, but based on the comments in the config file, some are only relevant to the master and may not need to be set, so feel free to try without them.
Now change these settings, and again it's possible that only the hot_standby option need be set.
listen_addresses = '*' hot_standby = on wal_level = hot_standby max_wal_senders = 3 wal_keep_segments = 8 checkpoint_segments = 8
Step 8 - Copy data from master to slave
On the slave, we will use the pg_basebackup utility to get a copy of the master's data. This will prompt for a password which is the one you configured in Step 6 when you created the replicator user.
pg_basebackup -h 192.168.1.10 -D /var/lib/postgresql/9.3/main -U replicator -P -v -x
This should complete successfully, if you see any errors then you will need to try to figure them out before continuing. Now we need to create a recovery.conf file and we will link it in the data directory.
vi /etc/postgresql/9.3/main/recovery.conf primary_conninfo = 'host=192.168.1.10 port=5432 user=replicator password=your-password' trigger_file = '/var/lib/postgresql/9.3/main/failover' standby_mode = 'on'
There are a couple of other options you can specify for the primary_conninfo (and probably more).
Step 9 - Starting the slave
We should now have our fully loaded slave with a copy of the master's data ready to go. We can now start postgresql on the slave.
service postgresql start
That wraps up this tutorial. Part of my learning curve was getting stuck with trying to figure out archiving and how that tied into streaming replication, which it seems they are different solutions. I struggled for hours trying to get this to work before finally disabling the archive options.