PostgreSQL High Availability on K8s — The good hard way

You have an existing instance of PostgreSQL on Kubernetes and want to migrate it to a highly available cluster? Stolon to the rescue! But hold your excitement, initializing Stolon with existing postgres data is not that simple…

So you’ve already deployed PostgreSQL in your K8s cluster as part of your application, but now you want to make it highly available, since, of course, you don’t want to loose any of your precious data.

There are several open-source projects which will help you achieve this goal, but in this post I’ll be focusing on Stolon, which, according to their documentation is:

a cloud native PostgreSQL manager for PostgreSQL high availability. It’s cloud native because it’ll let you keep an high available PostgreSQL inside your containers (kubernetes integration) but also on every other kind of infrastructure (cloud IaaS, old style infrastructures etc…)

You can read more about Stolon in their GitHub page: https://github.com/sorintlab/stolon

In our case, we’ve chosen to deploy stolon on our K8s cluster, using Helm.

The official Stolon helm chart can be found here: https://github.com/helm/charts/tree/master/stable/stolon

In addition, we’ve modified Stolon’s chart to deploy Stolon with PVCs (and PVs), so a few adjustments had to be made to the original chart.

Stolon installation overview

When deploying Stolon helm chart on your K8s cluster, it’ll create a new Keeper data directory with a few files necessary for the cluster to operate, where among those files, you’ll find the postgres folder with the same structure as the good old postgres folder in your original PostgreSQL deployment.

What is the problem?

As I’ve stated before, stolon will create a new postgres folder, with “empty” database. But how can we use our existing postgres and existing data in a new deployment of Stolon?

What is the solution?!

There are 2 ways to “migrate” data from PostgreSQL to Stolon:

1. pg_dump and pg_restore — Although this is a quite simple solution for this task, not only that it requires the original PostgreSQL database to be available to perform the pg_dump, but you also won’t be able to write any new data to the database until the pg_restore will complete on the new Stolon cluster. I’ll not elaborate on this solution in this post, since it’s quite straight forward, but not suitable for every use case.
2. Start Stolon with existing postgres folder (containing all PGDATA from the original PostgreSQL database) — Although a bit complicated solution (as you’ll see in this post), it’s quite fast to implement, and will require only a very small downtime window. This is what we’re going to discuss in this post.

To initialize a new Stolon cluster with existing PostgreSQL data, you’ll need to run through the following steps:

1. Prepare the existing PostgreSQL database for Stolon.
2. Install an empty Stolon cluster (in init mode “new”) using helm (to get the generated postgresql.conf and pg_hba.conf files) and purge it after.
3. Install stolon (in init mode “existing”) with existing postgres folder, after replacing the above files with the ones generated by the first stolon installation.

1. Prepare the existing PostgreSQL database for Stolon

If you currently have an existing postgres folder which stores all the data from your “old” PostgreSQL database, you’ll have to first “prepare” it to run under stolon, which basically means that there are 2 database roles needs to be created: a superuser and replication.

If you still have your old PostgreSQL database running, you can just create those roles and take the new “modified” postgres folder to stolon (move to step 2).

But if not, you can achieve this by spinning up a new PostgreSQL container (with the postgres folder mounted to it), create the required roles, and kill the container once done.

• First, modify your current postgres/pg_hba.conf file to enable all local connections (although not required, it’ll simplify things).
• Start a new PostgreSQL container (in the same version as your current postgres version where you took the postgres folder from) and mount your postgres folder to it. Here is an example command to lunch such a container:

# docker run -d -e PGDATA=/var/lib/pgsql/9.4/data/ -p 5432:5432 -v /<PATH_TO_POSTGRES>/postgres:/var/lib/pgsql/9.4/data --name "postgres_for_stolon" postgres:9.4 postgres

• Attach to the container and connect to your database

# docker exec -it postgres_for_stolon bash
# psql -h localhost -Upostgres <DB_NAME>

• Create the superuser and replication user roles (their password needs to match the password in Stolon’s helm chart, which we’ll talk about later)

# create role "stolonuser" with login SUPERUSER encrypted password '<STOLON_PASSWORD>';
# create role "repluser" with login replication encrypted password '<REPL_PASSWORD>';

• Exit the container, and delete it (since no longer needed)

# docker stop postgres_for_stolon
# docker rm -f postgres_for_stolon

• Copy the modified postgres folder to all other K8s nodes (this is needed since we’ll need the same postgres data directory on all nodes that will run the Stolon Keepers).

2. Install an empty Stolon cluster using helm

We’ll now install an empty stolon cluster, to get the generated postgresql.conf and pg_hba.conf files, so we can use them later on.

• Clone stolon’s helm chart, which is documented here: https://github.com/helm/charts/tree/master/stable/stolon
• Perform the necessary changes to the values.yaml file, and add your desired pgParameters and clusterSpec according to what is configured in your current PostgreSQL database.
• Deploy the modified chart:

# helm install <PATH_TO_CHART> --name stolon

• Monitor the Keepers’ logs and wait for both the master and standby to be ready

# kubectl logs -f stolon-keeper-0
# kubectl logs -f stolon-keeper-1

• Copy the generated postgresql.conf and pg_hba.conf files to a temporary location (on both K8s nodes which had the keepers running on)

# cp /<STOLON_DATA_DIR>/postgres/postgresql.conf /<STOLON_DATA_DIR>/postgres/pg_hba.conf /tmp/

• Delete stolon’s chart

# helm del --purge stolon

• Delete all contents inside Stolon’s directory (on both K8s nodes)

# rm -rf /<STOLON_DATA_DIR>/*

3. Install stolon with existing postgres folder

We’ll now copy our original postgres data directory to Stolon’s data directory, replace the postgresql.conf and pg_hba.conf files in the original directory with the ones generated before, and start stolon with init mode “existing”.

• Copy the existing postgres folder to Stolon’s folder (on both K8s nodes)

# cp -r /<PATH_TO_POSTGRES_FOLDER>/postgres /<STOLON_DATA_DIR>/

• Replace the modified postgresql.conf and pg_hba.conf files with the existing files in postgres folder (on both K8s nodes)

# cp /tmp/postgresql.conf /tmp/pg_hba.conf /<STOLON_DATA_DIR>/postgres

• Change permissions on Stolon’s folder (on both K8s nodes)

# chown -R 1000:docker /<STOLON_DATA_DIR>/

• Modify stolon’s create-cluster job yaml file to not create a new cluster database, but rather use an existing one (this theoretically can also be done by changing the values.yaml file of the chart or passing some arguments when deploying the chart, but I found it very tricky with this specific property)

# sed -i -e 's/"initMode": "new"/"initMode": "existing", "existingConfig": { "keeperUID": "keeper0" }/g'   <STOLON_CHART_PATH>/templates/hooks/create-cluster-job.yaml

• Install Stolon with the modified chart

# helm install <STOLON_CHART_PATH> --name stolon

A new Stolon cluster will be created (Keepers, Proxies, etc.) but the postgres data dir will not be overwritten, and all your data will be available.

You can monitor the initialization status the same as we did before, and once the master and standby keepers are ready, you can connect to postgres (locally from Stolon Keeper for example) and ensure that your data is still there.