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.