AMI/Packer deployment considerations

[thoughtpolice]

Currently, https:/supabase/postgres uses Packer to create AMIs, and customers get their AMIs provisioned for them when they start a project. Also, it would be nice if there was a way to "upgrade" an AMI with a different build of PostgreSQL, even after it's provisioned.

This repository doesn't do that yet, but I think there's a path to make it work. In the Nix world, I think that can be solved with a few steps: copying the closure, installing it into the default profile, and then switching it out.

Step 1: Copy the closure into the system

For any path in the Nix store /nix/store (let's call it $STORE_PATH), you can run the following to download it onto your existing machine at any time:

nix copy --from https://nix-postgres-artifacts.s3.amazonaws.com $STORE_PATH

This will recursively download and copy all dependencies onto the machine.

Step 2: Install into the default profile (AKA root)

However, there's a problem: how do you know what $STORE_PATH to use? And how would you point systemd to it, for example? Ideally, there would be a "stable path" that would refer to a version of PostgreSQL.

Luckily, Nix does have such a feature: it's called profiles, and there is a default profile, assigned to the root user. When the root user installs something, it goes into the default profile, and is available for everyone. Let's look at that on my machine:

austin@GANON:~/work/nix-postgres$ sudo -i

root@GANON:~# nix-env -q
nix-2.17.1
nss-cacert-3.92

root@GANON:~# ls /nix/var/nix/profiles/default/
bin  etc  lib  libexec  manifest.nix  share

root@GANON:~# ls /nix/var/nix/profiles/default/bin
nix        nix-channel          nix-copy-closure  nix-env   nix-instantiate   nix-shell
nix-build  nix-collect-garbage  nix-daemon        nix-hash  nix-prefetch-url  nix-store

nix-env -q is the way of querying what's installed for the root user. By default, a profile named foobar will have a "symlink farm" located at /nix/var/nix/profiles/{foobar} with all the files within it. If you install things into a profile, they will show up there.

Here's an example of installing a variant of postgresql from this system:

root@GANON:~# nix-env -i /nix/store/82p89d65l81446nfbxwaszg0pn9d2lss-postgresql-and-plugins-15.3
installing 'postgresql-and-plugins-15.3'
building '/nix/store/6b5mw00gkjiykhss5j21z97f49pzpbg7-user-environment.drv'...

root@GANON:~# nix-env -q
nix-2.17.1
nss-cacert-3.92
postgresql-and-plugins-15.3

root@GANON:~# ls /nix/var/nix/profiles/default/bin
clusterdb    nix-collect-garbage  pg_amcheck         pg_isready       pg_test_timing       psql
createdb     nix-copy-closure     pg_archivecleanup  pg_receivewal    pgtopo_export        raster2pgsql
createuser   nix-daemon           pg_basebackup      pg_recvlogical   pgtopo_export-3.3.3  raster2pgsql-3.3.3
dropdb       nix-env              pgbench            pg_repack        pgtopo_import        reindexdb
dropuser     nix-hash             pg_checksums       pg_resetwal      pgtopo_import-3.3.3  shp2pgsql
ecpg         nix-instantiate      pg_config          pg_restore       pg_upgrade           shp2pgsql-3.3.3
initdb       nix-prefetch-url     pg_controldata     pg_rewind        pg_verifybackup      vacuumdb
nix          nix-shell            pg_ctl             pgsql2shp        pg_waldump           vacuumlo
nix-build    nix-store            pg_dump            pgsql2shp-3.3.3  postgres
nix-channel  oid2name             pg_dumpall         pg_test_fsync    postmaster

You can use nix-env -i $STORE_PATH to install any path into the default profile for the root user. So we used nix-env -i to install it, then looked at the default profile. As expected, our PostgreSQL binaries are located in the /bin directory.

Thus, the only thing we need to do is install the package into the root profile, and point systemd to use binaries in /nix/var/nix/profiles/default/bin.

Once a version of the server is installed, systemctl daemon-reload; systemctl restart postgresql (or something to that effect) should bounce the service properly.

The only real change that should be needed is just pointing systemd to the right path. After that, it's typical ops stuff.

Side note: zero-downtime upgrades?

This strategy doesn't by default allow zero-downtime upgrades, because the postgresql service must be bounced. There are an array of solutions to this but I think they're probably not for me to decide. The most likely solution is probably some usage of pg_bouncer, I assume, though that has other consequences for things like prepared statements.

In the above example, just restarting the database with a new version of the server will probably take some time to bring the tables up. This is worth keeping in mind.