Having recently jumped ship from MSSQL to Postgres I'm finding it a
little difficult getting onto the 'correct path.' Basically I have a
live database and I want to take a copy of that and then restore it
onto a backup server.
Firstly I'm using Postgres 8.2 and pgAdmin 1.6.3
Within the pgAdmin interface I've selected the database I wish to
backup, given it a filename, selected 'Compress' and have chosen blobs
and OIDs. From the documentation it appears that if you use foreign
keys then you should use OIDs. Anyway, the backup appears to work
fine and a file is created. No errors are displayed at any point.
When I come to restore the database I fistly create a blank one, and
then right click and choose restore. The problem is that errors are
thrown because of foreign-key constraints.
With a little more investigation I changed the backup output to
'plain' so that I could see the script generated. It appears that the
backup process is created tables and their associated data in the
wrong order. For example, at the top of the generated file it
attempts to insert data into a given table, however the table dictates
that some entries must have a corresponding entry in another, for
example a user id (a foreign key saying that UID value must exist on
the user tables primary key). After detailing the data for the first
table it then details the data that should go into the parent table,
or in my example above, it then attempts to populate the user table.
When you run the restore process this it fails because the user table
doesn't have the corresponding entries.
Could someone please suggest why this is happening and how, if it can
be done, I fix it?
Thanks