Child pages
  • Postgresql Replication Workshop
Skip to end of metadata
Go to start of metadata



Update: 15th Jan...

Based on an issue encountered during the tutorial at Linuxconf yesterday, the Docker image for next week's tutorial has been updated on the docker hub.  If you are using the docker image, and have already installed it, please do:

sudo docker pull jberkus/pgreplicationtutorial:latest

If you are using the Vagrant machine, no changes are necessary.

See you on Tuesday!

Update: 14th Jan...

Josh has asked me to let everyone know that the new version of the tutorial materials are ready, and have been
pushed to GitHub and Docker Hub (for the docker image).
You can install these now - as described below.

PLEASE INSTALL AND TEST BEFORE THE WORKSHOP! We want to learn about replication, not spend time getting the session environment working...

Note that if you do come prepared, you will leave with a working replicated demo on your laptop.

I understand that Linux & Mac users are less likely to have problems than Windows users.


If anyone does have problems, Catalyst IT are setting up some cloud based VM's where people can undertake the workshop sessions in a NIWA space in the Catalyst IT cloud. The downside of using these is that non-NIWA staff will not have access to these once the workshop is over. Windows users should just need putty installed to SSH to these. Please let us know ASAP if you want to use one of these.

We will provide printed copies of Josh's exercise notes for all attendees.


See you all there!




Not strictly QGIS related, but very much a part of NIWA's QGIS ecosystem, where QGIS is the graphical GIS tool, working with spatial data which is managed in Postgis.

NIWA is taking advantage of Josh Berkus from PG Experts being in New Zealand in January 2015, to have him host a workshop on Postgresql database replication on 20th January.



Postgresql, a popular, powerful and robust Object Relational Database Management System - ORDBMS - (along with Postgis) is a core part of NIWA's strategy for environmental data management. As such, it is very tightly integrated with NIWA's internal and external data discovery and delivery systems, as the foundation upon which many of these services are built.

Robust data management, as the Christchurch earthquakes demonstrated all too well, has to provide for disaster recovery- especially environmental and other data which is needed for informed recovery programmes. One way to do this, is database replication, which, among other things, enables a remote database system to be automatically maintained as a replica datastore, which can take over an operational role in the event of a failure of the primary database. Recent versions of Postgresql have introduced sophisticated replication capabilities as part of the core package. This workshop will provide a hands on session where you can set up your own Postgresql replication cluster.

This is a hands-on, technical workshop intended for those managing databases, not for casual users. It should be about 5 hours, at NIWA Wellington. We will post further details as they come to hand. NIWA will be charging a small fee to non-NIWA attendees to help cover costs.

If you are interested in attending, please contact [email protected] or [email protected].


In Josh's words:

PostgreSQL’s built-in replication is conceptually simple, but the devil
is in the details, and this tutorial is all about the details. We will
cover the core essentials of how to set up and configure binary
replication, using hands-on exercises for some elements, including:

* setup & configuration
* replication security
* cascading replication
* synchronous replication
* failover and failback
* replication management tools
* performance tuning replication

Materials and Docker test environments will be updated at the
pgReplicationTutorial Github project:
( two weeks before the
event. Attendees are strongly encouraged to download it and set up the
learning environment before the tutorial; time will not be given during
the tutorial to do so.

Attendees will leave knowing how to set up a simple replication cluster,
and about the options for more complex configurations.



Quick notes (from Brent) about installing docker on Mint/Ubuntu Linux - if you are new to this - like me...

(We also have at least one Ubuntu user attending using vagrant instead)

The default Ubuntu/Mint version is old, and Mint has a couple of dependency issues anyway - so I followed the docker install instructions here:


sudo apt-get install apt-transport-https
sudo apt
-key adv --keyserver hkp:// --recv-keys 36A1D7869245C8950F966E92D8576A8BA88D21E9
sudo apt
-get install apt-transport-httpssudo sh -c "echo deb docker main\ > /etc/apt/sources.list.d/docker.list"
sudo apt
-get update
sudo apt
-get install lxc-docker

Then followed the steps in

The only (minor?) issue I had that I used sudo to run docker, rather than just my user login - in my case I don't think that is a problem....


Attendees will be expected to bring full-service laptops (i.e. no
tablets) to the workshop.  Linux and Mac laptops are preferred where
there is an option.

They will be expected to install software in preparation for the
exercises.  The software prerequisites are detailed here:

HOWEVER, I will be adding some extra exercises on performance tuning and
the new replication slots in order to make good use of our time.  As
such, I expect to have the final version of the tutorial exercises to
install Wednesday next week.  We will want to contact all attendees by
email at that time about preparation.


(Initial draft only)

9am - 9:20am: Introductions, setup, minor troubleshooting.
9:20am - 10:30am: Tutorial
10:30am - 10:40am: coffee break
10:40am - 12:00n: Tutorial
12:00n - 1:00pm: Lunch
1:00pm - 2:30pm: Tutorial
2:30pm - 3:00pm: Q&A etc.