How to upgrade postgres

upgrade_postgres

Assume the new version is 18, old is 17

* If new postgres is not available in code, make sure Tap petere/postgresql exsists
* Do not uninstall the old database version until the end
(admin) brew install postgresql@18

new_major=18
new_minor=0
old_major=17
old_minor=6

PATH=$HOMEBREW_CELLAR/postgresql@$new_major/$new_major.$new_minor/bin:$PATH pgxnclient install vector

(postgres)

$HOMEBREW_CELLAR/postgresql@$new_major/$new_major.$new_minor/bin/initdb ~/var/postgres_new \
-U admin --locale=C --encoding=UTF8

stop the database (as postgres user)
(postgres) $ pg_ctl -mf stop

LC_ALL=C /opt/homebrew/Cellar/postgresql@$new_major/$new_major.$new_minor/bin/pg_upgrade \
-b /opt/homebrew/Cellar/postgresql@$old_major/$old_major.$old_minor/bin \
-B /opt/homebrew/Cellar/postgresql@$new_major/$new_major.$new_minor/bin \
-d ~/var/postgres -D ~/var/postgres_new -U admin

migrate ~/var/postgres_new/pg_hba.conf and ~/var/postgres_new/postgresql.conf manually
mv ~/var/postgres ~/var/postgres_old; mv ~/var/postgres_new ~/var/postgres

start new db: pg_ctl start
rm -rf ~/var/postgres_old

(admin)
brew uninstall postgresql@17
brew link -f postgresql@18

----
To build VectorChord:
git clone https://github.com/tensorchord/VectorChord
rust up (upgrade)
PGRX_PG_CONFIG_PATH="$(brew --prefix postgresql@$new_major)/bin/pg_config" \
cargo build --release --features "pg$new_major"  
cp target/release/libvchord.dylib to $HOMEBREW_PREFIX/lib/postgresql@$new_major/vchord.dylib
cp vchord.control and all sql install / upgrade files to $HOMEBREW_PREFIX/share/postgresql@$new_major/extension