PostgreSQL 9.5 Streaming Cluster on Ubuntu 16.04

It can be very useful to have PostgreSQL configured in a cluster. PostgreSQL is able to do a few different kinds of clustering right “out of the box.” But, each of the ones that are available out of the box are of the MASTER-SLAVE scenario. Meaning that the first server handles all the writes and reads (the MASTER) and a second server (the SLAVE) gets a COPY of everything that is written to the MASTER.

PostgreSQL offers a few primary different ways for the Db to achieve this — log shipping, binary and streaming.

But, why is this useful? There are a few reasons. First, if your MASTER server goes down you can promote the SLAVE to be the new MASTER and get your database dependent application back on line quickly with little chance of data loss. Second, if you have some very long or intensive queries these can be run against the SLAVE leaving the MASTER to be more responsive (reduced load) for other write activity in your application. Third, boring but necessary I/O intensive administrative tasks like backups can be run against the SLAVE again taking load off the MASTER.

There do appear to be some downsides to the “out of the box” solutions in PostgreSQL. First, there does not appear to be an automated fail-over mechanism meaning that you will have to take a few steps to ensure that your MASTER is truly down and then promote your SLAVE to be the new MASTER. (I’m not 100% on that point.) And, in the MASTER-SLAVE cluster your application cannot WRITE to the SLAVE!

This kind of cluster where your application and read and write to any database server in the cluster and the servers all stay in sync is called a MASTER-MASTER cluster and doesn’t appear to be an “out of the box” option in PostgreSQL 9.5. But, that being said there appear to be many options out there to address this needs. If not true MASTER-MASTER at least intelligent load balancing and/or fail over. Perhaps even too many other options!?!?

The application I am working on now could make good use of a true MASTER-MASTER scenario; the servers working together without being fronted by some balancer solution. But, as I said above there are many approaches to fail-over and MASTER-MASTER cluster configurations so when I have a chance to investigate the current options out there and choose one for my production application I’ll write up what I found. Also, if you follow the PostgreSQL project much it seems this project moves at light speed. I wouldn’t be surprised if MASTER-MASTER is built in sometime soon. I don’t know that but again these guys/gals seem to make useful and solid improvements on this project VERY quickly.

Enough discussion lets get to work!
The run through below is an example of a scenario with one MASTER and 2 SLAVES.

I have 3 servers:
MASTER — Gold (192.168.100.100) –> WRITE new data
SLAVE1 — Silver  (192.168.100.101) –> Large and or intensive READ queries
SLAVE2 — Bronze (192.168.100.102) –> Backups routines and well I’ve got room for a third so why not.

A few assumptions:
You are running Ubuntu Server 16.04 (Latest stable as of this blog post)
You are familiar with the command line and editing files. I us vi below but use any editor you like.
You have a little patience…

On the MASTER:
Let’s get PostgreSQL installed…

sudo -s (Enter your password when prompted)
apt-get install aptitude
aptitude update
aptitude install –with-recommends postgresql postgis

su postgres
psql
CREATE ROLE replication WITH REPLICATION PASSWORD ‘YourSecretPassword’ LOGIN;
\q
exit

cd /etc/postgresql/9.5/main

vi pg_hba.conf and append the line:
host    all     all     192.168.0.0     255.255.0.0     md5
host    replication replication 192.168.100.100/16 md5 #Gold
host    replication replication 192.168.100.101/16 md5 #Silver
host    replication replication 192.168.100.102/16 md5 #Bronze

Note: In the above entries I use /16 and 255.255.0.0 for the netmask part of my entires. Of course adjust the IP scheme and netmask to match your network and server entries. Also, I put all my cluster members in the pg_hba.conf file on each cluster member just so I don’t have to worry about it as I play with promoting and demoting members to/from MASTER/SLAVE status.

vi postgresql.conf find the following entries, uncomment and edit:
listen_addresses = ‘*’
wal_level = hot_standby
max_wal_senders = 10 # Probably overkill as I only have a few slave servers but may add more
wal_keep_segments = 2048 # Probably _WAY_ overkill but as I would’t expect my SLAVE to be down that long. But, I’ve got plenty of disk space so…

service postgresql restart; tail -f /var/log/postgresql/postgresql-9.5-main.log

Watch the logs for a bit to make sure postgresql came back up OK.
Control-C to stop tailing the log

Loosen security for just a little while…

We need to make a couple of changes in security that we will UNDO later in these instructions. These temporary changes in security are needed as we will need to ‘prime’ the SLAVE in a future step to make sure it is ready to go.

Reconfigure SSH…

vi /etc/ssh/sshd_config
change the line:
PermitRootLogin prohibit-password
to:
PermitRootLogin yes

service restart ssh

Enable root login and password…
passwd root
passwd -u root

Again, don’t worry after we ‘prime’ the SLAVE we are going to lock the root account back down.

Ok, so the MASTER is now good to go.  Let’s work on our SLAVE.

On the SLAVE (Both of them if you decide you need two as well):
Let’s get PostgreSQL installed…

sudo -s (Enter your password when prompted)
apt-get install aptitude
aptitude update
aptitude install –with-recommends postgresql postgis

cd /etc/postgresql/9.5/main

vi pg_hba.conf and append the line:
host    all     all     192.168.0.0     255.255.0.0     md5
host    replication replication 192.168.100.100/16 md5 #Gold
host    replication replication 192.168.100.101/16 md5 #Silver
host    replication replication 192.168.100.102/16 md5 #Bronze

Note: In the above entries I use /16 and 255.255.0.0 for the netmask part of my entires. Of course adjust the IP scheme and netmask to match your network and server entries. Also, I put all my cluster members in the pg_hba.conf file on each cluster member just so I don’t have to worry about it as I play with promoting and demoting members to/from MASTER/SLAVE status.

cd /etc/postgresql/9.5/main

vi postgresql.conf find the following entries, uncomment and edit:
listen_addresses = ‘*’
hot_standby = on

cd /var/lib/postgresql/9.5/main

vi recovery.conf and add:

standby_mode          = ‘on’
primary_conninfo      = ‘host=gold.yourdomain.com port=5432 user=replication password=mt59901’
trigger_file = ‘/etc/postgresql/triggerImNowTheMaster’ # This little guy is important as it’s presence lets the server no to kick over from a SLAVE to a MASTER

chown postgres.postgres recovery.conf

This next command is WHY we weakened security above and allowed for root login. Basically, we want to make an exact copy of everything that is on the MASTER (all the files and folders in the PGDATA directory) on our SLAVE. This way when we start the SLAVE it is in sync with the MASTER.

So, issue an rsync command to copy over the files from the MASTER while preserving permissions, etc…

rsync -av -e ssh root@192.168.100.100:/var/lib/postgresql /var/lib

service postgresql restart; tail -f /var/log/postgresql/postgresql-9.5-main.log

Watch the logs for a bit to make sure postgresql came back up OK.
Control-C to stop tailing the log (or continue to watch if you wish)

The log entries should look pretty similar to the MASTER but you will begin  to see entries similar to:
LOG:  started streaming WAL from primary

On the MASTER:

tail -f /var/log/postgresql/postgresql-9.5-main.log

Watch the logs for a bit to make sure postgresql came back up OK.
Control-C to stop tailing the log (or continue to watch if you wish)

But, we need to remember to UNDO the security changes we made on the MASTER

vi /etc/ssh/sshd_config
change the line:
PermitRootLogin yes
to:
PermitRootLogin prohibit-password

service restart ssh

passwd -dl root (this re-locks the root account)
— passwd: password expiry information changed.

Some final items…

In the scenario above the logs are transmitted directly from the MASTER to the SLAVE(S) so unlike Log Shipping no common shared directory is required. However, in the walk through above the transmission of the transactions from the MASTER to the SLAVE is asynchronous so if the MASTER does fail a few transactions might be missing from the SLAVE. Now if MASTER/SLAVE match needs to be closer to perfect for your scenario there is an option in the postgresql.conf file to make this communication synchronous. But, there will be a performance impact as a record written to the MASTER will not be reported as committed to the application until it is also written on the SLAVE.

There are also some variations on this MASTER-SLAVE approach. You can have all the SLAVES look the the MASTER indicating a primary SLAVE to be synchronous and the rest will be asynchronous. Or you can have one PRIMARY SLAVE look to the MASTER as syncronyous and have many other SLAVES look to this PRIMARY SLAVE to get their updates in an asynchronous manner.

For the sake of speed above I lowered root security. DON’T DO THIS IN AN UNSECURED ENVIRONMENT AND CONSIDER ANY NETWORK SEGMENT YOU DON’T HAVE FULL PHYSICAL CONTROL OVER AS INSECURE. I did this out of haste and have a high confidence that my isolated demo/lab network is pretty secure. Priming the SLAVE is important but if I was communicating with the servers in a wild or unprotected environment I’d probably tar/gzip the the PGDATA folder (/var/lib/postgresql) being sure to preserve permissions, move this folder to the SLAVE and extract it there.  There seems to be a command (pg_basebackup) that I’m looking forward to once version 9.6 is released that will also probably make this the preferred approach.

Now that you have your MASTER and a couple of SLAVES setup. TEST, TEST, TEST!!!!

Create a new Db on the MASTER. See it show up on your SLAVES.
Create a tables in the Db on the MASTER. See it show up on your SLAVES.
Make some inserts, updates and deletes in your table. See the changes in your SLAVES.
su to postgres, run psql and enter /du and see ROLES (aka users) you create propagate from the MASTER to the SLAVES.

Take one of your slaves down for a few minutes and bring it back up. Do a “ps as | grep receiver” and you should see something similar to “postgres: wal receiver process  streaming 0/1A0519918” as it tries to catch up with the MASTER. Run the  “ps as | grep receiver” command a few times and you will see the name of the WAL file change during the catch up.

Touch /etc/postgresql/triggerImNowTheMaster on the SLAVE, chown postgres.postgres  /etc/postgresql/triggerImNowTheMaster,  shutdown the MASTER and you should be able to WRITE to the SLAVE. By the way, when you promote a SLAVE you will have to update any other SLAVES you may have to point to the new MASTER in their /var/lib/postgresql/9.5/main/recovery.conf files.

Think of other things that might go wrong and try it. Try new scenarios and try to break it in your test environment; best way to learn! Production is not the place to learn new things about your software and its configuration. Probably every Developer/Admin has learned that one the hard way at some point!   😉

Finally, I hope you found this walk through useful. Please leave a comment if you did and I always welcome upgrades. Good luck with your your new cluster.

One thought on “PostgreSQL 9.5 Streaming Cluster on Ubuntu 16.04

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s