Thread: How to restore from pg_dumpall output

I have a database that was built under a Mandrake 7.2 postgres
installation.  I am now running Debian (woody) on that machine, with
postgresql 7.2.1.

I still have the data directory from the old installation, so I
started a postmaster on a non-standard port, with a -D pointing to
that directory, and as the postgres user did:

        pg_dumpall -p 8050 >/tmp/db.sql

I then tried to restore (again as the postgres user), as documented in
the pg_dumpall man page:

        psql -f /tmp/db.sql template1

It does a few things, and then says:

        psql:/tmp/db.sql:16: \connect: FATAL 1: IDENT authentication
        failed for user "bostonre"

I would have thought the postgres user would be able to restore stuff
for any user.  What am I doing wrong?

--
Laura (mailto:lconrad@laymusic.org , http://www.laymusic.org/ )
(617) 661-8097    fax: (801) 365-6574
233 Broadway, Cambridge, MA 02139

Laura Conrad <lconrad@laymusic.org> writes:
>         pg_dumpall -p 8050 >/tmp/db.sql
> I then tried to restore (again as the postgres user), as documented in
> the pg_dumpall man page:
>         psql -f /tmp/db.sql template1
> It does a few things, and then says:
>         psql:/tmp/db.sql:16: \connect: FATAL 1: IDENT authentication
>         failed for user "bostonre"

I believe this will work better if you use "-X use-set-session-authorization"
to pg_dump (which should be the default, but isn't yet).  Without that,
you need an authorization configuration that will let you connect as
someone else --- which IDENT won't, superuser or no.  (You could set up
an ident map to let that happen, but it'd be tedious if you have lots of
users to keep track of.)

            regards, tom lane

>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:

    Tom> Laura Conrad <lconrad@laymusic.org> writes:
    >> pg_dumpall -p 8050 >/tmp/db.sql
    >> I then tried to restore (again as the postgres user), as documented in
    >> the pg_dumpall man page:
    >> psql -f /tmp/db.sql template1
    >> It does a few things, and then says:
    >> psql:/tmp/db.sql:16: \connect: FATAL 1: IDENT authentication
    >> failed for user "bostonre"

    Tom> I believe this will work better if you use "-X
    Tom> use-set-session-authorization" to pg_dump (which should be
    Tom> the default, but isn't yet).


I tried that, but I still get the same error out of psql, although the
pg_dumpall output now says:

        SET SESSION AUTHORIZATION bostonre;

instead of:

        \connect - bostonre


    Tom> Without that, you need an authorization configuration that
    Tom> will let you connect as someone else --- which IDENT won't,
    Tom> superuser or no.  (You could set up an ident map to let that
    Tom> happen, but it'd be tedious if you have lots of users to keep
    Tom> track of.)

There are only 2.  So I tried doing that, but my first guess on how didn't
work.

But to make that guess, I had to look at pg_hba.conf and it had lots
of useful stuff in it.  I decided it wasn't going to kill me to just
trust everyone on my local machine for 5 minutes, so I did that.  Not
the kosher thing for a real database administrator to do, but this is
my home machine, and the dog doesn't know how to log on.

So I have my database back -- thanks for the help.

--
Laura (mailto:lconrad@laymusic.org , http://www.laymusic.org/ )
(617) 661-8097    fax: (801) 365-6574
233 Broadway, Cambridge, MA 02139