Re: Backing up postgresql databases

["Peter Schuller" <peter(dot)schuller(at)infidyne(dot)com>]

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