Hi All,
I have a backup/restore problem I need help with.
I do a simple dump of the whole cluster using pg_dumpall, with role definitions and tutti.
When performing restores, I start with a fresh empty database directory that i create using the createdb utility and specify the super-user to be named "super" and always with the same password, let's say "restore".
Then I feed the database dump to the fresh database using:
# psql.exe -f "dumpfile" -U super
(And I enter the password "restore" to authenticate to my freshly created database).
But then the dump file alters the "super" role since that role also existed in the backed up database. Primarily it changes the roles password. This results in that when the dump-script carries on and comes to creating the database and finally does the "\connect <database>", it fails, since "super" does not have the same password anymore that was specified when psql.exe was started, and then the rest of the script fails since we're not connected to the database anymore. Ultimately the restore fails.
How do I avoid this scenario? I can think of some different ways to work around this, one is to create a user in the default database (createdb) that does not exist in the database dump file and is not altered by this (but how do i guarantee that it does not?). Another is to manually edit the dump-files and have the role alterations happen last in the script, but that requires that the roles at least exist for all roles that owns objects that are about to be restored and for the grants to work. A third work around might be to remove the "PASSWORD 'xxxxxxxxx'" part of the role alteration statement from the dump script. A fourth, and maybe the sloppiest way, could be to add a "trust"-line to pg_hba.conf to disable password auth completely while doing the restore operation?
None of these seem too tempting though. Should this not be thought of in the dg_dumpall generated script? I can't be the only one discovering this "problem" ?
//Kenneth