Re: Restoring a postgres database
От | Timothy Brier |
---|---|
Тема | Re: Restoring a postgres database |
Дата | |
Msg-id | 3F0B9A2C.2070903@cepu.ca обсуждение исходный текст |
Ответ на | Re: Restoring a postgres database (Andrew Gould <andrewgould@yahoo.com>) |
Ответы |
Re: Restoring a postgres database
(Martijn van Oosterhout <kleptog@svana.org>)
|
Список | pgsql-general |
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.
В списке pgsql-general по дате отправления: