Upgrading Postgresql on NixOS

I fail to remember this every time, so I'm putting it here forever!

I typically run this outside a NixOS Container, hence the weird uid/gid business.

NOTE: Update your Nix configuration with the new postgres version prior to performing these tasks, but do NOT perform the switch. We will do that as a part of the below process.

# Set variables
pgsql_old=16
pgsql_new=17
pgsql_basepath=/var/lib/postgresql

# Stop postgres service
systemctl stop postgresql.service

# Or, in my case, stop the container
nixos-container stop <container-name>

# If running inside a base host that doesn't have the postgres user, create it temporarily
if [ ! $(getent group postgres) ]; then
  tempgroup=true
  groupadd -rg 71 postgres
fi
if [ ! $(getent passwd postgres) ]; then
  tempuser=true
  useradd -rg postgres -u 71 postgres
fi

# Delete existing new directory if it exists, then create empty
###  DANGER - HERE BE DRAGONS  ###
### MAKE SURE YOU HAVE BACKUPS ###
rm -rfv "$pgsql_basepath/$pgsql_new"
install -d -m 0700 -o postgres -g postgres "$pgsql_basepath/$pgsql_new"

# Initialize new database
nix-shell -p "postgresql_${pgsql_new}_jit" --command "sudo -u postgres initdb -D $pgsql_basepath/$pgsql_new"

# Locate postgres binaries for upgrade
PGBINOLD=$(nix-shell -p "postgresql_${pgsql_old}_jit" --command 'dirname $(which postgres)')
PGBINNEW=$(nix-shell -p "postgresql_${pgsql_new}_jit" --command 'dirname $(which postgres)')

# Change to old postgres directory for pg_upgrade
cd "$pgsql_basepath/$pgsql_old"

# Run upgrade. Be sure to watch output, it will print more instructions for you, likely a vacuum.
nix-shell -p "postgresql_${pgsql_new}_jit" --command "sudo -u postgres pg_upgrade -k -b $PGBINOLD -B $PGBINNEW -d $pgsql_basepath/$pgsql_old -D $pgsql_basepath/$pgsql_new"

# Back to homedir...
cd

# Run the switch. This will start your service or container.
nixos-rebuild switch

# Run post tasks. Adjust based on the output of the upgrade command.
nix-shell -p "postgresql_${pgsql_new}_jit" --command "sudo -u postgres vacuumdb --all --analyze-in-stages"

# Destroy old cluster
###  DANGER - HERE BE DRAGONS  ###
### MAKE SURE YOU HAVE BACKUPS ###
rm -rfv "$pgsql_basepath/$pgsql_old"

# Delete postgres user if created here
if [ -n "$tempuser" ]; then
  userdel postgres
fi
if [ -n "$tempgroup" ]; then
  groupdel postgres
fi