--- Timothy Brier <[EMAIL PROTECTED]> wrote: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.
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
Tim.
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
