61 lines
1.4 KiB
Markdown
61 lines
1.4 KiB
Markdown
# 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 ❄️.
|
|
|