# Postgres container

## Update Postgres to new major version (UNTESTED!)

_here 16 => 17_

1. Stop services that use postgres or cut connection by change postgres port
2. Login as postgres user
```shell
sudo -su postgres
old=16
cd /var/lib/postgresql/
pg_old=$(nix-build --no-out-link -A postgresql_${old:?} '<nixpkgs>')
pg_new=$(nix-build --no-out-link -A postgresql_$((old+1)) '<nixpkgs>')
```
3. Initialize new data directory
```shell
$pg_new/bin/initdb --encoding=UTF8 --locale=C $((old+1))
```
4. Run check
```shell
$pg_new/bin/pg_upgrade \
    --old-bindir=$pg_old/bin \
    --new-bindir=$pg_new/bin \
    --old-datadir=/var/lib/postgresql/${old:?} \
    --new-datadir=/var/lib/postgresql/$((old+1)) \
    --clone \
    --check
```
5. Stop the old Postgres
```shell
systemctl stop postgresql
```
6. Run the migration
```shell
$pg_new/bin/pg_upgrade \
    --old-bindir=$pg_old/bin \
    --new-bindir=$pg_new/bin \
    --old-datadir=/var/lib/postgresql/${old:?} \
    --new-datadir=/var/lib/postgresql/$((old+1)) \
    --clone
```
7. Start the new Postgres
```shell
# Change 'services.postgres.packages = pkgs.postgresql_17;' in services/postgres.nix
nixos-rebuild switch
```

Cleanup (after a few days):
```shell
sudo -su postgres
vacuumdb --all --analyze-in-stages
cd /var/lib/postgresql/
./delete_old_cluster.sh
rm delete_old_cluster.sh
```

---

Build with ❤️ and ❄️.