# Postgres Upgrades

Our [self-hosted workloads](../infra/lab-architecture.md) include multiple Postgres databases that need to be upgraded occasionally. Upgrades between minor versions are trivial and just require the container image to be upgraded (usually via [Renovate](../dev/dev-stack.md)) but major version upgrades require manual actions, described below.

The actual process isn't really a _migration_ between versions; rather, we take a full backup of the old version and restore it into the new version. Performing a true "migration" requires having the old and new binaries available at the same time, which is hard to do with the official Postgres container images.

## Assumptions

The steps below make some assumptions about paths, environment variables, etc. These _should_ hold true for all deployed databases but are worth verifying. The main assumptions are the following:

- The Postgres container has its persistent data volume mounted at `/data`.
- The data volume has enough capacity to store the current DB, a compressed dump of it, and a new DB. If this is going to be hard, you could create separate volumes and moung them at different paths - you'll need to adjust the paths in the steps below if you do this.
- `$PGDATA` is pointing at a major-version-specific subfolder of `/data`, e.g. `$PGDATA=/data/15` if the container is running Postgres 15.x.
- Envionronment variables exist for the user and database name, i.e. `$POSTGRES_USER` and `$POSTGRES_DB`.

## Steps

1. **Prepare**

    1. Stop any workloads that connect to the database. This ensures that nothing tries to make changes to the database while we're in the middle of upgrading it.
    1. Check that recent backups have been made of the database.

1. **Dump the database contents**

    1. Connect to the current database container (using `kubectl -n <namespace> exec -it <postgres-pod> -- bash`) and run the following commands to create a full dump of the database contents.

    ```shell
    pg_dump -U "${POSTGRES_USER}" -d "${POSTGRES_DB}" -c --if-exists -F c > /data/dump
    ```

1. **Start the new database**

    1. Update the Kubernetes manifest to change the Postgres image version.
    1. Update the `$PGDATA` path for the container to the new major version (e.g. from `/data/15` to `/data/16`).
    1. Comment out the health checks in the manifes, because they will fail until the restore finishes.
    1. Deploy the new manifest and wait for the pod to be started and healthy before continuing.

1. **Restore the database contents**

    1. Connect to the new database container using the same `kubectl` command and run the following commands to restore the dump you created earlier.

    ```shell
    pg_restore /data/dump -U "${POSTGRES_USER}" -d "${POSTGRES_DB}"
    ```

    2. Run the following commands (still in the Postgres container) to update the DB collation version and perform a full re-index.

    ```shell
    psql -U "${POSTGRES_USER}" -c "ALTER DATABASE ${POSTGRES_DB} REFRESH COLLATION VERSION;"
    psql -U "${POSTGRES_USER}" -c "REINDEX DATABASE ${POSTGRES_DB};"
    ```

1. **Check everything**

    1. Use `psql` to poke around the DB and check that the tables you expect are there.
    1. Start the workload for this database and check that everything still works

1. **Roll back?**

    1. If anything has gone wrong, you can still roll back at this point by undoing your changes to the manifest (image version and data path) and redeploying it. This will get you back to running the old version, pointing at the old data.
    1. When you're ready to try again, remove the dump file and start again from the top.

1. **Clean up**

    1. Restore the health checks that you commented out earlier, deploy the updated manifest, and make sure the health checks succeed.
    1. When you're certain that everything is working okay (including backups on the new version), you can connect to the container a final time and use the following commands to clean up the temporary files you created:

    ```shell
    rm /data/dump
    rm -r /data/<old version>  # be careful here!
    ```