Re: Backing up postgresql databases

Peter,

I agree with Tom that it isn't the best solution to have to store a
user/pass in a cron script, but sometimes things have to be done for a
greater purpose. I still have to fiddle with how local ident would work
in my own situation, but the current solution I have is the following.
Have your shell script do

export PGUSER=username
export PGPASSWORD=password

before you run pg_dumpall in the same script. The user/pass would most
likely have to be a superuser to have access to all databases (this is
also not guaranteed depending on your pg_hba.conf). Make the script
read/execute by root but not by anyone else and it will help a tiny bit
with security.

It's ugly but it works. This was taken from some previous suggestions on
the mailing list to get around this same problem for other versions of
pg_dumpall. I'm still hoping to find something better.

Hope that helps.

Tim Frank

>>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<

On 19/03/01, 5:06:16 AM, peter(dot)schuller(at)infidyne(dot)com ("Peter Schuller")
wrote regarding Re: [ADMIN] Backing up postgresql databases:

> Hello,

> > From what I understand (and I'm still fairly new to Postgres) you won't
get
> > a perfect copy while Postmaster is running, but there is a way to backup
the
> > entire database while it's running: pg_dumpall.

> Ah, perfekt! And thanks to Grant for that bash script :)

> I do have a question though. Contrary to Grant, I can't use a trust
policy,
> even on the local machine. As a result, I need to use password
> authentication. However, pg_dumpall seems to generally screw up.

> Wheather or not I do -u has no effect; in either case I get prompted to
the
> password (but not the username). And in either case, I get a buch of
random
> "Password:" type outputs at first (as if it's trying to authenticate
> unsuccessfully several times) intermixed with the other output. When I
> finally get the password prompt, I enter it and I seem to get logged in
(as
> postgres). Then I get another error (invalid script or sql query of some
> sort it looks like).

> Below is some example output; it looks really messy in general with a
bunch
> of failed authentications. Note that I didn't get to enter the password
> until the very last Username:Password:

> defiant:~$ /usr/lib/postgresql/bin/pg_dumpall -u
> Password: psql: Password authentication failed for user 'postgres'
> \connect template1
> select datdba into table tmp_pg_shadow from pg_database where
datname
> = 'template1';
> delete from pg_shadow where usesysid <> tmp_pg_shadow.datdba;
> drop table tmp_pg_shadow;
> copy pg_shadow from stdin;
> Password:
> psql: Password authentication failed for user 'postgres'
> \.
> delete from pg_group;
> copy pg_group from stdin;
> Password:
> psql: Password authentication failed for user 'postgres'
> \.
> Password: Password: psql: Password authentication failed for user
'postgres'
> \connect template1
> create database "postgres";
> \connect postgres
> Username: Password:

> Connection to database 'postgres' failed.
> ERROR: Missing '=' after '31' in conninfo

> pg_dump failed on postgres, exiting

> Is there something obvious I'm doing wrong?

> Thanks!

> --
> / Peter Schuller, InfiDyne Technologies HB

> PGP userID: 0x5584BD98 or 'Peter Schuller <peter(dot)schuller(at)infidyne(dot)com>'
> Key retrival: Send an E-Mail to getpgpkey(at)scode(dot)infidyne(dot)com
> E-Mail: peter(dot)schuller(at)infidyne(dot)com Web: http://scode.infidyne.com

> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)