Forum OpenACS Q&A: pg_dump moves datamodel but not data: 7.0.3 to 7.1.3-4

This seems really bizarre and I find nothing like this discussed in the forums here.

I'm copying an existing OpenACS site to a backup development box, and am upgrading PG from 7.0.3 to 7.1.3-4 in the same step. I've dumped the db on the first box, created a new db on the second box, psql'd postgres.sql into the new db and then pg_dump'd the dump datafile into the new db.

When I log into the new database on the backup box, I find all of the tables from the original site are there (the stock OpenACS tables as well as my custom tables). However, none of the rows are there; select count(*) from users; returns zero rows, for instance, whereas there should be many rows.

The only error I see from the pg_dump that imports the data into the new db is this line--which is the last line in the error file:

psql:/tmp/db.dmp:1: connect: Peer authentication failed for user 'nsadmin'

This seems like an important clue, but I can't intepret it. I initially pg_dump'd the db on the first box as nsadmin, then created the new db and pg_dump'd the dump file into the new db on the second box also as nsadmin.

I tried doing the process as user postgres but I got a peer authentication error for user 'nsadmin' again.

Any clues what is going on? Many thanks in advance!

Don, thanks for the prompt response. I've created both postgres and nsadmin users for the new db. The peer authentication failure occurs at (it appears) the *last* line of the dump file, not the first, since it is the *last* line in the std error redirect file following a whole bunch of NOTICE: CREATEs but no ERRORs. (I get no errors during the original pg_dump of the first database, too, FWIW.)

For no particularly good reason, I've dropped both the postgres and nsadmin users for the new db and re-added them. I still get the same problem, only now the authentication failure is for user 'postgres'. Does the dump file carry info about permissions that isn't clear?

I also tried the -d -D flags with pg_dump and have the same results: the schema gets imported but none of the data, with the same peer authentication line at the end of the error file. Hmm...