Forum OpenACS Q&A: Re: pg_dump and pg_restoring from production to dev
For what it's worth, this is what I do:
Edit all the \connect statements out of the dump file. I don't fundamentally understand what purpose they serve, but depending on how you set your permissions, they can result in only partial recreation of the db. And it will fail silently without showing in the error log.
Use this syntax -- see http://www.postgresql.org/docs/view.php?version=7.2&idoc=1&file=backup.html#BACKUP-DUMP-RESTORE
psql safe4all-dev < safe4all.dmp 2> errors.txt
This works fine for me anyway.