[Klug-general] Postgresql backup and restoration

[Karl Buckland]

Hi all,

A Postgresql specific question here as it's driving me crazy. I have a
moderately large (6Gb) Postgresql database running 8.1.10. I'm
currently trying to copy the database to another server and rename it.
I'm using pg_dump to dump the database specifically, then I'm piping
the output to sed to rename the database and finally piping that
output to psql to insert the new database. From what I can see, this
appears to be the standard way to do things?

The structure of the database seems to be fine, with table
definitions, foreign keys, indices, triggers, views, functions, etc,
all copied. However a lot of the data isn't getting copied. The errors
seem to indicate that it's because of the triggers and foreign keys.
For example, Postgres is trying to add data for table a that has a
foreign key for table b, before it has imported the data for table b.
Having used MySQL's dump and import I haven't experienced this issue.

The Postgresql docs
(http://www.postgresql.org/docs/8.1/static/populate.html) suggest
removing the foreign key constraints, but helpfully don't tell you
how. I don't really want to have to remove the foreign key constraints
manually for all tables and then re-add them as there are 430 tables.
Is it possible to disable the constraints whilst importing (which is
what MySQL must do)?

Does anyone have any suggestions? I'm afraid migrating to MySQL (where
this stuff seems to just work!) isn't a valid suggestion (yet!).

Karl