~a

upgrading postgresql on arch (in case you fucked up)

1stop postgresql
2stop akkoma   # make sure nothing is trying to hit the database

pg_upgrade will fail if you initdb with different params

so i’d made a file-level backup at least

1sudo mv /var/lib/postgres/data /var/lib/postgres/olddata

i tried to

1sudo mkdir /var/lib/postgres/data /var/lib/postgres/tmp
2sudo chown postgres:postgres /var/lib/postgres/data /var/lib/postgres/tmp
3
4sudo -iu postgres
5cd /var/lib/postgres/tmp
6initdb -D /var/lib/postgres/data --locale=en_US.UTF-8 --encoding=UTF8 --data-checksums

this caused pg_upgrade to fail because the old data didn’t have --data-checksums enabled

pg_dumpall will fail with extensions like rum

ok so that didn’t work, let’s try the manual dump and reload. exit out of the postgres session and back to your user

1yay -S postgresql-old-upgrade
2sudo rm -r /var/lib/postgres/data
3sudo mkdir /var/lib/postgres/data
4sudo chown -R postgres:postgres /var/lib/postgres/data
5
6sudo -iu postgres
7initdb -D /var/lib/postgres/data
8/opt/pgsql-14/bin/pg_ctl -D /var/lib/postgres/olddata/ start

the old version should be running now

1pg_dumpall -h /tmp -f /tmp/old_backup.sql

aaaaand this failed because of something to do with pg_rum

downgrading to get a good dump

ok let’s kill the old postgres

1sudo killall postgres

copy the old data over and set permissions

1sudo rm -r /var/lib/postgres/data
2sudo cp -r /var/lib/postgres/olddata /var/lib/postgres/data
3sudo chown -R postgres:postgres /var/lib/postgres/data

downgrade to the last version of postgres

1yay -S downgrade
2sudo downgrade postgresql
3start postgresql

dump

1pg_dumpall > /tmp/dumpall.sql

now we can upgrade again?

1stop postgresql
2yay -S postgresql
3yay -S pg_rum     # do a clean build?

now we try to restore data

1sudo rm -r /var/lib/postgres/data
2sudo mkdir /var/lib/postgres/data
3sudo chown -R postgres:postgres /var/lib/postgres/data
4
5sudo -Hu postgres initdb -D /var/lib/postgres/data --locale=en_US.UTF-8 --encoding=UTF8 --data-checksums
6
7start postgresql
8
9sudo -Hu postgres psql -f /tmp/dumpall.sql template1

this worked for me

etc

while you’re here you might as well update and migrate akkoma if you’re still running that?

1sudo -Hu akkoma MIX_ENV=prod mix deps.get
2sudo -Hu akkoma MIX_ENV=prod mix ecto.migrate
3start akkoma