Thread: Please help - Keep getting authentication failure when trying to restore database

Please help - Keep getting authentication failure when trying to restore database

From
Søren Neigaard
Date:
I have made a pg_dump of my database, and now I want to restore it on
another database. So I follow the instructions on
"http://www2.dk.postgresql.org/users-lounge/docs/7.1/admin/backup.html"
(both databases are 7.1 on Debian), but I keep getting "psql: Password
authentication failed for user 'postgres'" all the time.

I have looked in my dump file, and seen that it tries to connect first as
postgres, and thereafter as my other user that owns the tables. How do I
allow it to connect as these users? Can I somehow disable the passwords, and
how is this done?

Med venlig hilsen/Best regards
Søren Neigaard
System Architect
................................
Mobilethink A/S
Arosgaarden
Åboulevarden 23, 5.sal
DK - 8000 Århus C
Telefon: +45 86207800
Direct: +45 86207810
Fax: +45 86207801
Email: s.neigaard@mobilethink.dk
Web: www.mobilethink.dk
................................


=?iso-8859-1?Q?S=F8ren_Neigaard?= <s.neigaard@mobilethink.dk> writes:
> I have made a pg_dump of my database, and now I want to restore it on
> another database. So I follow the instructions on
> "http://www2.dk.postgresql.org/users-lounge/docs/7.1/admin/backup.html"
> (both databases are 7.1 on Debian), but I keep getting "psql: Password
> authentication failed for user 'postgres'" all the time.

> I have looked in my dump file, and seen that it tries to connect first as
> postgres, and thereafter as my other user that owns the tables. How do I
> allow it to connect as these users? Can I somehow disable the passwords, and
> how is this done?

If you did a pg_dump, and not a pg_dumpall, then you will need to
manually create all the users referenced in the dump before you can
think about loading it.

Password authentication is very tedious to use for this sort of thing,
I'm afraid; you will get prompted for every \connect in the script,
and there might be a lot of 'em.  You might want to temporarily alter
pg_hba.conf to use "trust" authentication method for local connections.

            regards, tom lane