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 ................................
Re: Please help - Keep getting authentication failure when trying to restore database
From
Tom Lane
Date:
=?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