Re: Restoring a postgres database - Mailing list pgsql-general

I've been reading about these problems (but never experienced myself). I've
always wondered if it would be possible to write a script to scan through a
schema dump searching for dependancies. You can then use tsort to dump out
the order that things should be restored in.

Has anyone attempted this at all?

On Wed, Jul 09, 2003 at 12:29:32AM -0400, Timothy Brier wrote:
> Andrew Gould wrote:
> >--- Timothy Brier <briert@cepu.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.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> "the West won the world not by the superiority of its ideas or values or
> religion but rather by its superiority in applying organized violence.
> Westerners often forget this fact, non-Westerners never do."
>   - Samuel P. Huntington