Re: Restoring a postgres database

--- Timothy Brier <briert(at)cepu(dot)ca> wrote:
> Hi,
>
> I've run in to this problem with a database we are
> working. When I
> restore a database schema, I need to restore the
> schema 5 times to
> ensure that the schema is complete. Also some of
> the sequences are not
> restored in a usable form. E.g. If my next sequence
> should be 1000, my
> sequence is set to 1 and I need to run a query to
> reset my sequences.
>
> I have restored other simpler databases in
> PostgreSQL without a problem.
> It is my view that this issue is caused by a
> dependency issue because
> the items that don't get restored the first or
> second time complain that
> a dependency on a function doesn't exist, but all is
> fine after the 5th
> attempt.
>
> I do two pg_dumps. The first is:
> pg_dump -Cs databasename | gzip -cv >
> databasenameschemayyyymmdd.gz
> pg_dump -Ca databasename | gzip -cv >
> databasenamedatayyyymmdd.gz
>
> The database contains 64 tables, 34 views, 244 user
> functions, 34 rules,
> 87 triggers, 202 indexes and 70 sequences.
>
> We are also using inheritance in the database. The
> schemas are standard
> schemas created by PostgreSQL.
>
> I have also used the ability of pg_dump to create a
> schema and data in a
> tar format, but cannot get it to restore the schema
> from the tar. It
> always complains about the functions for plpgsql
> already existing and
> stops. No problem restoring the data from the tar.
>
> I am using PostgreSQL 7.3.3 on RedHat 7.1 - 9.0.
> The database itself
> is great, we've converted a few projects from MSSQL
> to PostgreSQL but I
> am concerned about the integrity of restoring the
> data.
>
> Does anyone know if this will be improved in 7.4?
> Is there a better way
> to do a backup?
>
> To the developers, support team and the community,
>
> Keep up the good work.
>
> Timothy Brier.

I ran into a situation similar to yours regarding
tables with foreign references and escalation rules.
I've noticed that tables seem to get dumped/restored
in the order in which they were created. To fix my
problem, I rearranged the table order in my schema
files. Since the tables were then created in the
correct order, subsequent dumps/restores have gone
smoothly. (I hope it wasn't just dumb luck.)

I dump the schema separately from the data. I have a
python script that separates the table creation
statements into one schema file and the index and
constraint creation statements into a second schema
file. This allows me to recreate the tables, restore
the data, and then recreate indexes and constraints. I
figure if the data does not comply with the
contraints, the dump was bad anyway. (This has yet to
occur.)

I can't help with the sequence field problem; but I
hope you're not having to restore too often.

Best of luck,

Andrew Gould