Thread: dump vs dumpall

Howdy,

I am in the process of moving my postgres dbs from an Alpha to a Pentium box
and I can't seem to get pg_dumpall to work.  I have password authentication
enabled and am using the -u option on pg_dumpall, but it never actually asks
me for a userid or password, it just writes out that the password
authentication on postgres failed.  I tried inputting a file with the
username and password (on same and different lines in the file) and I also
tried inputting a file with just the password.  None of these seemed to
work, but I did get different errors each time.  I went into the pg_dumpall
script and noticed that it looks for the postgres_user in the pg_shadow
table.  I am not really sure how to look at what is in that table or how to
make sure it has the correct information.  I'd like to get this to work, but
have to complete this very quickly.  Would it be easiest to just run pg_dump
on all databases?  I have used this and it works fine, I just wasn't sure if
I would be missing some of the information that pg_dumpall outputs.  Below
are the command line errors and the file output from pgdump_all run with the
postgres user's password fed to the utility through a file.

Thanks for your help!
Darrin


File output from pg_dumpall:

\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;

\.
delete from pg_group;
copy pg_group from stdin;

\.
\connect template1
create database "devuserdb" with encoding='SQL_ASCII';
\connect devuserdb
Username: Password:



Command line output from pg_dumpall:

Password: psql: Password authentication failed for user 'postgres'
Password: psql: Password authentication failed for user 'postgres'
Password: psql: Password authentication failed for user 'postgres'
Password: Password: psql: Password authentication failed for user 'postgres'
Connection to database 'devuserdb' failed.
ERROR: Missing '=' after '668' in conninfo

pg_dump failed on devuserdb, exiting

Darrin Ladd writes:

> I am in the process of moving my postgres dbs from an Alpha to a Pentium box
> and I can't seem to get pg_dumpall to work.  I have password authentication
> enabled and am using the -u option on pg_dumpall, but it never actually asks
> me for a userid or password, it just writes out that the password
> authentication on postgres failed.

pg_dumpall doesn't work very well at all with password authentication.
You might be better off temporarily using some other mechanism.  (E.g.,
use the Unix socket with "trust", but change the permissions on the socket
itself.)

--
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/

On Mon, 16 Oct 2000, Peter Eisentraut wrote:

> Darrin Ladd writes:
>
> > I am in the process of moving my postgres dbs from an Alpha to a Pentium box
> > and I can't seem to get pg_dumpall to work.  I have password authentication
> > enabled and am using the -u option on pg_dumpall, but it never actually asks
> > me for a userid or password, it just writes out that the password
> > authentication on postgres failed.
>
> pg_dumpall doesn't work very well at all with password authentication.
> You might be better off temporarily using some other mechanism.  (E.g.,
> use the Unix socket with "trust", but change the permissions on the socket
> itself.)

Try to do it this way:
$ export PGUSER=username
$ export PGPASSWORD=password
$ pg_dumpall > filename

Regards
Robert "BoBsoN" Partyka

BTW... Hello to all on the list ;)

Thanks!  That worked perfectly!!


-----Original Message-----
From: Peter Eisentraut <peter_e@gmx.net>
To: Darrin Ladd <dladd@newfoundcomm.net>
Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Date: Monday, October 16, 2000 1:41 PM
Subject: Re: [GENERAL] dump vs dumpall


>Darrin Ladd writes:
>
>> I am in the process of moving my postgres dbs from an Alpha to a Pentium
box
>> and I can't seem to get pg_dumpall to work.  I have password
authentication
>> enabled and am using the -u option on pg_dumpall, but it never actually
asks
>> me for a userid or password, it just writes out that the password
>> authentication on postgres failed.
>
>pg_dumpall doesn't work very well at all with password authentication.
>You might be better off temporarily using some other mechanism.  (E.g.,
>use the Unix socket with "trust", but change the permissions on the socket
>itself.)
>
>--
>Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/
>