Re: pg_dump backup problems with password authentication

Hi again,

I would like to thank Vasileiadis Spyros, Michael Ansley and
Richard Poole who all pointed out the nature of the problem
and the correct solution.

SUMMARY

The original situation was this:
1) I had changed authentication in pg_hba.conf from trust to
password
2) This gave me a problem with pg_dump
When I ran the command:
pg_dump -D -u -v databasename > database.sql
I didnt't get an Username prompt (the -u switch would do that)

The explanation for this was that the Username prompt appears
in stdout (STanDard OUTput), which *usually* is the screen,
but because I was redirecting output to a file, the Username:
prompt was being saved in the output file (database.sql)

The solution was to write the pg_dump command above, press
ENTER, and then write the username (the Username prompt doesn't
appear, as explained above), press ENTER, write the password
and press ENTER again. Using the -v switch, some lines appear
which show the operations being done (e.g., "reading
user-defined types", "finding Triggers for relation:", etc)

To restore the data, I edited the database.sql with joe
(I found out that my default configuration of pico was
breaking the lines at wrap points). I removed the Username,
and Password lines, and the \connect - postgres line. Then
I wrote the password in the first line (I didn't need to
write the Username, because I was logged in as user
postgres)

Then, I created the new database:
createdb newdatabase

And typed the restore command:
psql newdatabase < database.sql

Et voilá! :)

Thanks for all the help, and I hope this summary will be
useful to other people with the same problem (let me live
in my illusion that I'm not the only one, OK? ;-)

Best wishes,
Ricardo Dias Marques
ricmarques(at)spamcop(dot)net