Skip to main content

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 and old path /postgres version valuesprior to performing these tasks, but do NOT perform the switch. We will do that as needed.a part of the below process.

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

# Stop postgres service
systemctl stop postgresql.service

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

# 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$postgres_uid -g $postgres_gid "$pgsql_basepath/$pgsql_new"

# 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 $postgres_gid postgres
fi
if [ ! $(getent passwd postgres) ]; then
  tempuser=true
  useradd -rg postgres -u $postgres_uid postgres
fi

# Initialize new database
nix-shell -p "/var/lib/postgresql/17"postgresql_${pgsql_new}_jit"
--command 
"sudo -u postgres /nix/store/infd8iz1j1n8shfvcgf6cdqcavynaya3-postgresql-and-plugins-17.5/bin/initdb -D /var/lib/postgresql/17$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 /nix/store/infd8iz1j1n8shfvcgf6cdqcavynaya3-postgresql-and-plugins-17.5/bin/pg_upgrade -k -b /nix/store/9rl8l079qbn42a69f26qrq1hm9zs8jj6-postgresql-and-plugins-16.9/bin$PGBINOLD -B /nix/store/infd8iz1j1n8shfvcgf6cdqcavynaya3-postgresql-and-plugins-17.5/bin$PGBINNEW -d /var/lib/postgresql/16$pgsql_basepath/$pgsql_old -D /var/lib/postgresql/17$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"
Back to top