Re: backup and restore

T.J. Adami wrote:
> 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

OK. Latest is 8.2.6 I believe, make sure you keep up to date with minor
updates.
Oh, and pgAdmin has released version 1.8 now, although that shouldn't
affect backups.

>> 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.

You probably want "compress" - that format allows you do partial
restores as well as being compressed. I can't tell whether you've got
any blobs ("large objects" in PG terminology). I don't think you'll need
to dump OIDs unless you're explicitly using them. The manuals have
recommended against that for some years now, so if you can remember
which bit gave you the impression you need them for foreign keys then
that would be useful.

> Anyway, the backup appears to work
>> fine and a file is created. No errors are displayed at any point.

Excellent. You should also have a "messages" tab that shows the pg_dump
command it uses. You can run the same from the command-line to dump your
database. If you've got "verbose messages" turned on then it will name
each item as it dumps it too. I'm working from pgAdmin 1.8 here, but I
don't think it was any different in 1.6

>> 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.

OK, that's down to one of three things:
1. The database wasn't empty - perhaps you had a partial restore
in-place. Unlikely, since I'm sure you repeated your steps above.

2. The dump was in plain-text and you were doing separate schema + data
restores. We can rule this out because you've said otherwise.

3. You aren't restoring as a super-user (typically "postgres"), so it
can't disable + re-enable foreign keys. That's what I think is happening.

>> 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.

If you look more carefully, you should see that all the foreign keys are
created at the bottom of the file after the data restore.

>> 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.

Hmm - shouldn't be, and if so then please let the pgAdmin people know!
They're always working to improve the package and it's

> Althought pgAdmin 3 uses pg_dump and pg_restore utilities, I could
> report many errors when did backups and/or restores from it.

Did you keep any notes, and can you reproduce them?

> 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.

You're better off using the compressed mode imnsho. The flexibility is
well worth any minor loss vs bzip2's compression. Check out the -l / -L
options to let you pick and choose what you restore.

--
Richard Huxton
Archonet Ltd