Re: Restoring a postgres database

Andrew Gould wrote:
> --- 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
>
>
Thanks for the reply. I don't do alot of restores. But I would like to
see the issue addressed so it would be easier for other people who use
PostgreSQL and need to do a restore without jumping through these hoops.
At the same time I realize and appreciate the hard work that has gone
into this DB and that there are other priorities.

Tim.