PostgreSQL-primary-promotion

We hope you're here to learn, not for an incident...

To promote a PostgreSQL instance to primary (that are using bitnami/postgresql:9.6 Docker image), follow these steps:

• Check backup process
  • if running dump, it will fail, so remember to check that there's an existing recent backup and if needed, stop the running one
• Stop current primary
• Enter in a replica and run touch /tmp/postgresql.trigger.5432, this will become the new primary
• Ensure other replicas are properly aligned to new timeline
  • this should be automatically handled, if not, PostgreSQL will not start due to errors (the file containing this setting is recovery.conf.sample and the correct setting is recovery_target_timeline = 'latest')
• Edit the new primary setting POSTGRESQL_REPLICATION_MODE=master and removing (or commenting with a _ prefix for example) POSTGRESQL_MASTER_HOST, this will redeploy the container with correct ENVs but nothing else different
• Change K8S service discovery in order to point to new primary
• Redeploy pgbouncers if needed

Now your PostgreSQL cluster primary instance is the one you promoted, and there are a missing replica (the one stopped in previous steps). You should rebuild the cluster structure if needed.

There are many jobs that are using replicas to read data (all the ones that includes Uala::JobProps::ReadFromReplicas module). While you're missing a replica, these jobs can fail. You should re-route reads to another replica or to the primary instance (this will cause a huge load to the primary instance), otherwise some workers can be temporarily stopped to allow replica recreation (about ~3 hours at mid 2022).