Thread: pg_dump/pg_dumpall
Greetings, I was just trying to get a live backup of my database and had the following error: uselton]# pg_dumpall > /tmp/osp5_pgsql.dump Password: pg_dumpall: could not connect to database "template1": FATAL: Password authentication failed for user "root" If it really is asking for the 'root' password I am certainly giving it correctly. I am root as I issue the command as well. I've tried a few other guesses at what it might _really_ be wanting (including using an explicit '-h host -p port'),but with no better luck. Any hints would be most appreciated. Cheers, Andrew
--- "Andrew C. Uselton" <acuselton@lbl.gov> wrote: > Greetings, > I was just trying to get a live backup of my database and had the following error: > > uselton]# pg_dumpall > /tmp/osp5_pgsql.dump > Password: > pg_dumpall: could not connect to database "template1": FATAL: Password authentication failed > for user "root" > Actually, if you don't specify a DB username, pg_dumpall uses the calling OS username as a default. It this user doesn't exist, or you enter the wrong password for the DB you will get this error. Regards, Richard Broersma Jr.
On Friday 10 August 2007 11:49, "Andrew C. Uselton" <acuselton@lbl.gov> wrote: > uselton]# pg_dumpall > /tmp/osp5_pgsql.dump > Password: > pg_dumpall: could not connect to database "template1": FATAL: > Password authentication failed for user "root" > > If it really is asking for the 'root' password I am certainly giving > it correctly. I am root as I issue the command as well. I've tried > a few other guesses at what it might _really_ be wanting (including > using an explicit '-h host -p port'), but with no better luck. Any > hints would be most appreciated. Cheers, It is trying to connect as root, because you're logged in to your OS as root, but there probably isn't a root user in the database. You need to connect as a database superuser to do a backup. The normal default superuser is "postgres". Where it gets its password from or even if it requires one depends on your configuration. -- "Democracies survive only until the voters discover that they can vote themselves largesse from the public treasury ..." -- Alexander Tyler
Try adding "-U postgres" to connect as the correct user. http://www.postgresql.org/docs/8.2/interactive/app-pg-dumpall.html -----Original Message----- From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Andrew C. Uselton Sent: Friday, August 10, 2007 2:49 PM To: pgsql-novice@postgresql.org Subject: [NOVICE] pg_dump/pg_dumpall Greetings, I was just trying to get a live backup of my database and had the following error: uselton]# pg_dumpall > /tmp/osp5_pgsql.dump Password: pg_dumpall: could not connect to database "template1": FATAL: Password authentication failed for user "root" If it really is asking for the 'root' password I am certainly giving it correctly. I am root as I issue the command as well. I've tried a few other guesses at what it might _really_ be wanting (including using an explicit '-h host -p port'), but with no better luck. Any hints would be most appreciated. Cheers, Andrew ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Useful info. I'll dig around and see how we set up the db superuser. Certainly I've been trying "-U postgres" right along, but I may be mistaken about what it thinks the password is. Cheers, Andrew Alan Hodgson wrote: > On Friday 10 August 2007 11:49, "Andrew C. Uselton" <acuselton@lbl.gov> > wrote: > >> uselton]# pg_dumpall > /tmp/osp5_pgsql.dump >> Password: >> pg_dumpall: could not connect to database "template1": FATAL: >> Password authentication failed for user "root" >> >> If it really is asking for the 'root' password I am certainly giving >> it correctly. I am root as I issue the command as well. I've tried >> a few other guesses at what it might _really_ be wanting (including >> using an explicit '-h host -p port'), but with no better luck. Any >> hints would be most appreciated. Cheers, >> > > It is trying to connect as root, because you're logged in to your OS as > root, but there probably isn't a root user in the database. > > You need to connect as a database superuser to do a backup. The normal > default superuser is "postgres". Where it gets its password from or > even if it requires one depends on your configuration. > >
I am beginning to understand a little better. Our database is configured with the pg_hba.conf entry (the only entry): local all all md5 sameuser The database cluster was created (initdb) in the default way, with no -pwpromt. Either a superuser password was never created or (less likely) we just don't know what it is. According to the administrative reference if there is no superuser password you can't authenticate against the "postgres" superuser at all. This appears to be harmless (i.e. not having a superuser password) for things like (as root): su -l postgres -c "/usr/bin/createuser ..." but also from the administrative reference I see that pg_dump and pg_dumpall are going to require that I provide the appropriate password, which will fail if the password was never set. So it seems to me I need to figure out how to set that password after the fact. Is this possible? -a Alan Hodgson wrote: > On Friday 10 August 2007 11:49, "Andrew C. Uselton" <acuselton@lbl.gov> > wrote: > >> uselton]# pg_dumpall > /tmp/osp5_pgsql.dump >> Password: >> pg_dumpall: could not connect to database "template1": FATAL: >> Password authentication failed for user "root" >> >> If it really is asking for the 'root' password I am certainly giving >> it correctly. I am root as I issue the command as well. I've tried >> a few other guesses at what it might _really_ be wanting (including >> using an explicit '-h host -p port'), but with no better luck. Any >> hints would be most appreciated. Cheers, >> > > It is trying to connect as root, because you're logged in to your OS as > root, but there probably isn't a root user in the database. > > You need to connect as a database superuser to do a backup. The normal > default superuser is "postgres". Where it gets its password from or > even if it requires one depends on your configuration. > >
"Andrew C. Uselton" <acuselton@lbl.gov> writes:
> I am beginning to understand a little better. Our database is
> configured with the pg_hba.conf entry (the only entry):
> local all all md5 sameuser
It's a bit late at night, but I'm fairly sure that the "sameuser" option
is ignored here. If md5 is the only auth method allowed then you should
have to give a password to get in.
> The database cluster was created (initdb) in the default way, with no
> -pwpromt. Either a superuser password was never created or (less
> likely) we just don't know what it is. According to the administrative
> reference if there is no superuser password you can't authenticate
> against the "postgres" superuser at all. This appears to be harmless
> (i.e. not having a superuser password) for things like (as root):
> su -l postgres -c "/usr/bin/createuser ..."
Well, if that works then there's something going on here that you didn't
tell us. Perhaps postgres has a ~/.pgpass file to provide its password?
If not, I have to guess that you're looking at the wrong pg_hba.conf,
or that it doesn't describe what the server's really doing (ie it was
changed but the server was never restarted or SIGHUP'd since then).
The usual way of forcing things into a sane state if you're presented
with a database you can't get into is to change pg_hba.conf to allow
trust or ident auth (don't use trust if there are people you don't trust
allowed to log into the machine), and restart the server. Then you can
log in as the postgres user and set its password; then change
pg_hba.conf to the configuration you want and SIGHUP again.
regards, tom lane