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

1: pg_dump moves datamodel but not data: 7.0.3 to 7.1.3-4

Posted by Stan Kaufman on 01/03/02 11:18 PM

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!

2: Response to pg_dump moves datamodel but not data: 7.0.3 to 7.1.3-4 (response to 1)

Posted by Don Baccus on 01/03/02 11:52 PM

Well it's failing on line one of your dump file, which is trying to connect to your database. Did you do a "createuser nsadmin" on your new database? Since it can't connect everything past it is not going to run.

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* ...

3: Response to pg_dump moves datamodel but not data: 7.0.3 to 7.1.3-4 (response to 1)

Posted by Stan Kaufman on 01/04/02 12:46 AM

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...

4: Response to pg_dump moves datamodel but not data: 7.0.3 to 7.1.3-4 (response to 1)

Posted by Stan Kaufman on 01/04/02 01:39 AM

OK, this indeed turned out to be a brain-dead problem (as I had feared). I found at Pascal Scheffer's helpful site (http://pascal.scheffers.net/openacs/pgupdate/x72.html) a tip to strip out all lines that begin with 'connect' from the dump file.

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!