Restore a full PostgreSQL server including passwords
4
votes
2
answers
204
views
I'm preparing a migration from one server to another. Actually, the same hardware but a full reinstall of a newer OS version. So everything including PostgreSQL needs to be reinstalled. But the user data must remain the same. This question is about restoring a full Postgres database server with all databases, structure, data, roles, passwords and everything. The old version is Postgres 13, the new is version 16.
I can make a full dump with this command:
pg_dumpall -U postgres -h localhost --clean --if-exists --quote-all-identifiers | gzip > ~/full-postgres.sql.gz
On the new server, however, it can't be loaded. Currently this is in a test environment. Apparently I've created the default database with a new password, but the restoring now left the server in an unusable state. I did this:
gunzip -c ~/full-postgres.sql.gz | psql -U postgres -h localhost
It asked for the password and started running some commands. Then the last two lines are these:
\connect: connection to server at "localhost" (127.0.0.1), port 5432 failed: FATAL: password authentication failed for user "postgres"
connection to server at "localhost" (127.0.0.1), port 5432 failed: FATAL: password authentication failed for user "postgres"
I cannot connect to the server anymore with the old or new password. Guess that attempt is lost and I'll have to restart my test with a fresh server installation.
What's the correct procedure to get a totally complete dump of Postgres 13 and load all the data, passwords etc. into a fresh Postgres 16 installation? Running on Ubuntu 20.04/24.04.
Needless to say that I cannot recreate users and passwords manually because I don't have all the passwords. Users must be able to connect with their existing password again on the new server. So whereever Postgres keeps the relevant data, it needs to be restored from the dump.
While I'm thinking of it, a related configuration change is that the old server had pg_hba.conf entries with the "md5" method and the new will have "scram-sha-256" everywhere. Would this prevent preserving the passwords maybe? So would progress just leave me behind here?
PS: I could also copy the raw database files on disk and restore those, if it helps to preserve all data. Just remember the version has changed from 13 to 16.
Asked by ygoe
(303 rep)
May 24, 2025, 12:17 PM
Last activity: May 25, 2025, 11:14 AM
Last activity: May 25, 2025, 11:14 AM