Thread: backup: pg_dumpall and full backups in general
user "dado" is me, with full powers.
#: pg_dumpall
--
-- pg_dumpall (7.1.3)
--
\connect template1
DELETE FROM pg_shadow WHERE usesysid <> (SELECT datdba FROM pg_database
WHERE datname = 'template0');
Password:
psql: Password authentication failed for user 'dado'
DELETE FROM pg_group;
Password:
<- here I typed the pw
CREATE GROUP "adminGroup" WITH SYSID 1;
Password:
psql: fe_sendauth: no password supplied
ALTER GROUP "adminGroup" ADD USER "";
Password:
psql: fe_sendauth: no password supplied
ALTER GROUP "adminGroup" ADD USER "";
Password:
psql: fe_sendauth: no password supplied
ALTER GROUP "adminGroup" ADD USER "";
Password:
<- here I typed the pw
--
-- Database template1
--
\connect template1 postgres
\connect template1 postgres
Connection to database 'template1' failed.
fe_sendauth: no password supplied
pg_dump failed on template1, exiting
It keeps asking for a password but won't take one.
It seems pg_dumpall won't pass it to psql.
Any idea?
BTW, I wrote my own pg_dumpall in Python with added functionality.
I think I need to move back to the original pg_dumpall just in case
things change and mine script stops working.
But wrapping around pg_dump, pg_dumpall, and psql isn't very easy
because of the prompts.
Any advice here? Is it ok for me to keep rely on my backup utility?
Also, I want to know what I need to make backups of in order to recreate
a full install of PostgreSQL.
Does pg_dump take care of everything? If I reload my backups, will I
have an exact copy of the original install?
I mean, not considering OIDs and such.
Thanks.
--
Dado Feigenblatt Wild Brain, Inc.
Technical Director (415) 216-2053
dado@wildbrain.com San Francisco, CA.
Dado Feigenblatt <dado@wildbrain.com> writes:
> [ problems with pg_dumpall and password protection ]
Password protection just doesn't play very nicely with dump scripts.
Especially not scripts you'd like to invoke from cron jobs.
I'd strongly recommend setting up a different authorization method.
One way that's reasonably secure is to run an ident server (non-broken
one of course) and enable ident authorization over local loopback only.
Then a simple "export PGHOST=127.0.0.1" frees you from entering any
more passwords.
regards, tom lane
ident authorization (was backup: pg_dumpall and full backups in general)
From
Dado Feigenblatt
Date:
Hi Tom, I followed your sugestion and got identd up and running on the server. It seems to be working fine. I think my problem now is with pg_hba.conf and pg_ident.conf. During a psql session I can't switch to another user using template1=\connect dado_test dado IDENT authentication failed for user 'dado' Previous connection kept I thought it was because I was using the "sameuser" map in pg_hba.conf host all 127.0.0.1 255.255.255.255 ident sameuser So I changed the map name pg_hba.conf host all 127.0.0.1 255.255.255.255 ident usermap and added to pg_ident.conf the other user names postgres might have to switch to usermap postgres postgres usermap postgres purp usermap postgres dado usermap postgres greg I still get the same error. Any idea? Thanks. Tom Lane wrote: >Dado Feigenblatt <dado@wildbrain.com> writes: > >>[ problems with pg_dumpall and password protection ] >> > >Password protection just doesn't play very nicely with dump scripts. >Especially not scripts you'd like to invoke from cron jobs. >I'd strongly recommend setting up a different authorization method. > >One way that's reasonably secure is to run an ident server (non-broken >one of course) and enable ident authorization over local loopback only. >Then a simple "export PGHOST=127.0.0.1" frees you from entering any >more passwords. > > regards, tom lane > >---------------------------(end of broadcast)--------------------------- >TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > -- Dado Feigenblatt Wild Brain, Inc. Technical Director (415) 216-2053 dado@wildbrain.com San Francisco, CA.
Dado Feigenblatt <dado@wildbrain.com> writes:
> So I changed the map name pg_hba.conf
> host all 127.0.0.1 255.255.255.255 ident usermap
> and added to pg_ident.conf the other user names postgres might have to
> switch to
> usermap postgres postgres
> usermap postgres purp
> usermap postgres dado
> usermap postgres greg
That looks like it ought to work. Have you looked in the postmaster log
to see if any additional information is recorded about the
authentication failure? We don't always tell the client everything
we know, for security reasons...
regards, tom lane
Re: ident authorization (was backup: pg_dumpall and full backups in general)
From
Dado Feigenblatt
Date:
Tom Lane wrote: >Dado Feigenblatt <dado@wildbrain.com> writes: > >>So I changed the map name pg_hba.conf >> >> host all 127.0.0.1 255.255.255.255 ident usermap >> >>and added to pg_ident.conf the other user names postgres might have to >>switch to >> >> usermap postgres postgres >> usermap postgres purp >> usermap postgres dado >> usermap postgres greg >> > >That looks like it ought to work. Have you looked in the postmaster log >to see if any additional information is recorded about the >authentication failure? We don't always tell the client everything >we know, for security reasons... > > regards, tom lane > I had never played with the logs (it was off). I was playing with it trying to get some output on the ident problem. All of the sudden ident started working. I hate when it happens. Now we won't ever know what was the problem. The worse is that I don't know if it will come back. Is there anything you'd like me to try? Otherwise, I'll let you know when it sneaks on me again. Thanks PS: And I haven't figured out yet how to get the log facility to log what I need :( The docs on loging are not very clear. Do you know of any mail thread or document some where? -- Dado Feigenblatt Wild Brain, Inc. Technical Director (415) 216-2053 dado@wildbrain.com San Francisco, CA.