PostgreSQL Upgrade

Upgrade auf PostgreSQL 14

Beim Upgrade auf PostgreSQL 14 gibt es noch einen weiteren Punkt zu beachten: Hier ändert sich das Authentifizierungs-Verfahren von md5 auf scram-sha-256. Dabei handelt es sich um Hashing-Verfahren von Passwörtern. Weitere Informationen dazu sind in der PostgreSQL-Dokumentation zu finden.

Nach einem Update von PostgreSQL 13 auf Version 14 werden die Anwendungen zunächst einmal keine Verbindung mehr zur Datenbank aufbauen können, da sich das Hashing-Verfahren geändert hat. In diesem Fall müssen die Passwörter aller PostgreSQL-Benutzer neu gesetzt werden.

Zunächst einmal kann man sich alle Datenbank-Benutzer auflisten lassen:

> echo "\du" | psql -U postgres

So wird das Passwort eines Datenbank-Users neu gesetzt (neu setzen, nicht ändern):

> echo "\password user_name" | psql -U postgres

Hinweis: Das Passwort sollte in diesem Schritt nicht geändert werden, sondern es sollte das gleiche Passwort für den Benutzer verwendet werden, welches vorher auch schon genutzt wurde. Andernfalls müsste auch die Konfiguration des Programms geändert werden, welches den Datenbank-Zugriff benötigt.

Anschließend sollte der Datenbank-Zugriff wieder wie gewohnt funktionieren.

Stand-Alone-Installation

komplett neu machen

Dump erstellen:

> pg_dumpall -E UTF8 -U postgres --if-exists --inserts -c > dump_data+schema.sql
> pg_dumpall -E UTF8 -U postgres --inserts -a > dump_data.sql
> pg_dumpall -E UTF8 -U postgres -s > dump_schema.sql

DB komplett löschen und neu machen

> service postgresql stop
> rm -fr /usr/local/pgsql/data
> su - postgres -c "initdb -D /usr/local/pgsql/data -U postgres -E UTF8 --locale=de_DE.UTF-8"
> service postgresql status
> service postgresql start
> echo -e "\du\n\l" | psql -U postgres

Benutzer anlegen:

> createuser -U postgres --no-superuser --no-createdb --no-createrole MEINUSER

Datenbank für den neuen Benutzer anlegen:

> createdb -E UTF8 --locale=de_DE.UTF-8 -U postgres -O MEINUSER MEINEDB

Dump in die Datenbank des neuen Benutzers einspielen:

> psql -U MEINUSER -d MEINEDB -f dump_xxx.sql
> echo -e "\du\n\l\n\dt" | psql -U MEINUSER -d MEINEDB

So geht es auch

von 16 zu 17

echo -e "\du\n\l" | psql -U postgres
pg_dumpall -E UTF8 -U postgres --if-exists --inserts -c > dump_data+schema.sql
service postgresql stop
 
mv /usr/local/pgsql/data /usr/local/pgsql/data.old
 
pkg info -oa | grep -Fi postgresql
  postgresql16-client-16.9       databases/postgresql16-client
  postgresql16-server-16.9_1     databases/postgresql16-server
pkg install -y databases/postgresql17-server
pkg info -oa | grep -Fi postgresql
  postgresql17-client-17.5       databases/postgresql17-client
  postgresql17-server-17.5_1     databases/postgresql17-server
 
su - postgres -c "initdb -D /usr/local/pgsql/data -U postgres -E UTF8 --locale=de_DE.UTF-8"
cat /usr/local/pgsql/data/PG_VERSION
service postgresql start
echo -e "\du" | psql -U postgres
 
time cat dump_data+schema.sql | psql -U postgres -d template1
  real    41m21.991s
  user    0m4.765s
  sys     0m3.138s
 
echo "SELECT * FROM pg_stat_activity;" | psql -U postgres

prüfen

echo "SELECT COUNT(MEINESPALTE) FROM MEINETAB;" | psql -U MEINUSER -td MEINEDB
echo -e "\du\n\l" | psql -U postgres

mit pg_upgrade

SICHERHEIT GEHT VOR!
Als erstes eine Sicherung anlegen (Bevor das Upgrade begonnen wird!):

> pg_dumpall -E UTF8 -U postgres --if-exists --inserts -c > dumpall.sql

Beachten Sie, dass pg_upgrade nur funktioniert, wenn die Kodierungen der alten und der neuen Datenbankinstanz übereinstimmen. Andernfalls schlägt es fehl.

Bei dieser Methode muß sowohl die alte als auch die neue Version, nebeneinander installiert sein! ⇒ siehe Internet