restore problem with pg_dumpall dump (password authentication fail)
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
"Kenneth Lundin" <kenneth.lundin@dacom.se> writes:
How do I avoid this scenario?
Don't change the postgres user's password in the middle of a dump/restore?
regards, tom lane
Tom,
Well, it's not really "I" that change the password, it happens in the backup
dump file created automatically by pg_dumpall.
The thing is as an administrator, you want to be able to take a backup at
certain points in time and then later flawlessly be able to restore that
backup and end up in exactly the same state you were in when the backup was
created. And since many times when you are doing a restore, you're in a time
pinch since something probably have gone terribly wrong, then you don't want
to have to muck around manipulating dump files in any way, you just want to
do an instant restore and get your faulty system up and running asap.
To my understanding, the prefered way of doing online backups in postgres is
using pg_dump to dump the database. And my point here was that there seem to
be a flaw in this scheme. The script generated by pg_dumpall fails in my
case when I'm doing the restore.
//Kenneth
On Sat, Jan 3, 2009 at 5:40 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
"Kenneth Lundin" <kenneth.lundin@dacom.se> writes:
How do I avoid this scenario?
Don't change the postgres user's password in the middle of a dump/restore?
regards, tom lane
On Saturday 03 January 2009 10:43:09 am Kenneth Lundin wrote:
Tom,
Well, it's not really "I" that change the password, it happens in the
backup dump file created automatically by pg_dumpall.The thing is as an administrator, you want to be able to take a backup at
certain points in time and then later flawlessly be able to restore that
backup and end up in exactly the same state you were in when the backup was
created. And since many times when you are doing a restore, you're in a
time pinch since something probably have gone terribly wrong, then you
don't want to have to muck around manipulating dump files in any way, you
just want to do an instant restore and get your faulty system up and
running asap.To my understanding, the prefered way of doing online backups in postgres
is using pg_dump to dump the database. And my point here was that there
seem to be a flaw in this scheme. The script generated by pg_dumpall fails
in my case when I'm doing the restore.//Kenneth
The problem seem to be here:
"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"."
This is not in fact the case. "super" does not always have the same password.
You are using a different password for the restore role of "super" then the
regular role. When restoring use the same password as in the database. You
might also want to look at pg_dumpall -g which dumps the global parts of the
database cluster i.e roles. I generally do a separate pg_dumpall -g and load
that first so I have my roles in place before doing the rest of the restore.
--
Adrian Klaver
aklaver@comcast.net