This took more work than I originally thought to get it right, but I'm getting satisfied with the Chef resource I wrote to set up a PostgreSQL primary or replica, with a TLS cert provided by Let's Encrypt.
Now I'm going to perform the PostgreSQL upgrade in a VM and make sure everything goes smoothly
LGTM! Let's merge after deploying it, in case the turn_ip is required for STUN to function
As discussed in #147 it makes sense to first upgrade the current PostgreSQL server on andromeda to version 12, since the replication configuration has been improved (and simplified). In version 10 the replication settings had to be added to a config file in the data directory, since 12 they live in postgresql.conf with the rest of the config.
I have followed this tutorial to set up replication between two VMs: https://www.percona.com/blog/2019/10/11/how-to-set-up-streaming-replication-in-postgresql-12/
The new set up will have PostgreSQL run with TLS enabled, with certs generated by Let's Encrypt, so the clients and replication can be achieved securely.
In order to secure the servers, we will use a firewall rule that only allows connections from the IP of the servers that need it. We will also use client authentication to limit access to specific databases for each server, and allow for the replication server to the replication database
Now I need to remove some hardcoded values from the new recipes, using instead values from the Chef nodes living in the repo in nodes
I think we can start with the upgrade to 12 on Andromeda and switch to the new config, maybe on Tuesday? After this first step that will require a short downtime, we can install PostgreSQL to the new Hetzner server and confirm replication works, this will not require downtime
Thanks, I got replication in Postgres 12 to work and it is much better. Its options are part of the normal config, no more recovery.conf in the data directory, and the initial sync can now set up the master. PostgreSQL provide official packages for 12 on Ubuntu 18.04, so that's easy to set up using the existing upstream cookbook
We should update from Postgres 10 to 12 before setting up replication on the new server, I think that would be the easiest route. For our setup with just one database pg_upgrade looks like a good fit, I remember using it before, probably to switch from PostgreSQL 9 to 10