Re: backup and restore

On 14 jan, 10:36, hiddenhippo <reda(dot)(dot)(dot)(at)gmail(dot)com> wrote:
> 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

It's quite simple to have problems with pgAdmin's backup procedure.
Althought pgAdmin 3 uses pg_dump and pg_restore utilities, I could
report many errors when did backups and/or restores from it.

I suggest you to use pg_dump command line utility into a plain SQL
file, so you can compress it with bzip2 to maximum compress rates.
When you restore, decompress the file (if you have packed it) and use
the psql to load the script file into the new and clean database.