Thread: Backup - Restore (pg-dump)

Any Help

I recently upgraded to 7.3.4 from 7.3.2.  Prior to the upgrade I used
"pg_dump -a > olddatafile" for just data and "pg_dump -s > oldschemafile"
for schema using the pg_dump utility from 7.3.2.  After a "successful"
install of 7.3.4, I created the database then went on to "successful"
"psql -f schemafile" and then "psql -f > datafile" and results were as
expected.

However, when i used the "pg_dump -a newdatafile" and "psql -f newdatafile"
commands from the 7.3.4 version, I get random:

psql:bcgdata3:491: ERROR:  $1 referential integrity violation - key
referenced from customer not found in euser
psql:bcgdata3:491: lost synchronization with server, resetting connection
psql:bcgdata3:507: ERROR:  $1 referential integrity violation - key
referenced from billing not found in customer
psql:bcgdata3:507: lost synchronization with server, resetting connection
psql:bcgdata3:528: ERROR:  $1 referential integrity violation - key
referenced from shipping not found in customer
psql:bcgdata3:528: lost synchronization with server, resetting connection
psql:bcgdata3:586: ERROR:  $1 referential integrity violation - key
referenced from javastore not found in euser
psql:bcgdata3:586: lost synchronization with server, resetting connection
psql:bcgdata3:628: ERROR:  $1 referential integrity violation - key
referenced from bcgmodules not found in javastore
psql:bcgdata3:628: lost synchronization with server, resetting connection

I tried each command on old cluster and new cluster and very same results.

However, I successfully update the 7.3.4 database when I "psql -f
olddatafile" using the 7.3.4 psql command.

What might be my problem and/or solution?  I hope I am clear enough for a
positive response.

Thanks Much

C
creid@netbcg.com

creid writes:

> I recently upgraded to 7.3.4 from 7.3.2.  Prior to the upgrade I used
> "pg_dump -a > olddatafile" for just data and "pg_dump -s > oldschemafile"
> for schema using the pg_dump utility from 7.3.2.  After a "successful"
> install of 7.3.4, I created the database then went on to "successful"
> "psql -f schemafile" and then "psql -f > datafile" and results were as
> expected.

This is unnecessary.  The data formats of 7.3.2 and 7.3.4 are compatible.
Just start the new server in place of the old one.

> However, when i used the "pg_dump -a newdatafile" and "psql -f newdatafile"
> commands from the 7.3.4 version, I get random:
>
> psql:bcgdata3:491: ERROR:  $1 referential integrity violation - key
> referenced from customer not found in euser

That is an inevitable consequence of dumping schema and data separately.
If you dump them together, pg_dump will insert special commands to disable
the constraints while loading the data.

> However, I successfully update the 7.3.4 database when I "psql -f
> olddatafile" using the 7.3.4 psql command.

How is that different from what you did above?

> What might be my problem and/or solution?  I hope I am clear enough for a
> positive response.

It would be even clearer if you showed us the exact sequence of your
commands, not dumbed-down versions.   For example, in your case the dump
files where clearly not named "newdatafile".  Also, where did you install
the new and old versions?  Are you sure that running just "psql" will get
you the right version?

--
Peter Eisentraut   peter_e@gmx.net