backup and restore

From: hiddenhippo <redaudi(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: backup and restore
Date: 2008-01-14 12:36:23
Message-ID: 881d13a4-9564-4910-862b-5ace0a592bf8@j78g2000hsd.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message henry 2008-01-14 12:39:47 Re: tcp_keepalives_idle ignored
Previous Message Stefan Schwarzer 2008-01-14 12:36:03 Re: Installation problem: failed to initialize lc_messages to ""