Forum OpenACS Q&A: pg_dump moves datamodel but not data: 7.0.3 to 7.1.3-4
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
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!
The mystery here is how did the table schema get loaded? If it failed to connect on the first line of the dump file there should be *nothing* ...
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...
There were indeed several switches between the postgres and nsadmin users in my dump file--notably from the lines that created all the tables (as nsadmin) to the ones that inserted the data (as postgres). pg_dump was barfing on the switch. (Note: this was a dump file created with the -d and -D flags.)
Mystery solved. Thanks for the direct help, Don, and the indirect help, Pascal!