Re: 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