Skip site navigation (1) Skip section navigation (2)

Re: Backing up postgresql databases

From: "Peter Schuller" <peter(dot)schuller(at)infidyne(dot)com>
To: John W Cunningham <john(at)pickaprof(dot)com>
Cc: Peter Schuller <peter(dot)schuller(at)infidyne(dot)com>, pgsql-admin(at)postgresql(dot)org
Subject: Re: Backing up postgresql databases
Date: 2001-03-19 09:56:10
Message-ID: 20010319045610.D439@prometheus (view raw, whole thread or download thread mbox)
Lists: pgsql-admin

> 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;
   psql: Password authentication failed for user 'postgres'
   delete from pg_group;
   copy pg_group from stdin;
   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?


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

In response to


pgsql-admin by date

Next:From: Robert HentoshDate: 2001-03-19 13:00:41
Subject: Re: Postgres/Postmaster logging and log rotation
Previous:From: Tom LaneDate: 2001-03-19 06:40:01
Subject: Re: SHMEM settings under FreeBSD

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group