Re: RI and restoring dumps
От | Robert B. Easter |
---|---|
Тема | Re: RI and restoring dumps |
Дата | |
Msg-id | 00121318253624.00289@comptechnews обсуждение исходный текст |
Ответ на | Re: RI and restoring dumps (Matt Beauregard <matt@designscape.com.au>) |
Список | pgsql-general |
On Wednesday 13 December 2000 17:45, Matt Beauregard wrote: > On Wed, Dec 13, 2000 at 05:30:49PM -0500, Robert B. Easter wrote: > > On Wednesday 13 December 2000 17:04, Robert B. Easter wrote: > > > Has anyone experienced referential integrity (RI) problems while > > > restoring data from a dump? Like, if the dump doesn't restore the data > > > in the right order, then primary keys might not be in place before the > > > foreign keys are restored. I want to know this from people who have > > > experience before I do use RI! > > > > Ok, I'm replying to my own post here, but I was reading another message > > (above) about a FOREIGN KEY that REFERENCES the same table as it is in. > > When doing a restore of this table, I guess you would have to disable > > those RI checks. I know it can be done (right?), but how? Or is this > > really automatic and not to be worried about!? :) > > The triggers by whick FK constraints are implemented are the very last > things in the dumpfile, so the data would be loaded into the database > without any constraint checking. I shouldn't think it possible to > have RI problems with this method of restoration assuming the table > data was consistent when it was dumped. Ok, that makes perfect sense. Thanks. I see that pg_dump and pg_dumpall can dump just the schema (no data) or just the data (no schema). I was thinking, sometimes it would be nice to dump just the TABLE schema into one file, then all the data into a second file, and then all the functions/triggers/rules into another file. The restore would be, (1) restore table schema, (2) restore data to tables, and then (3) restore all functions/triggers/rules/views and procedural languages. pg_dump just puts the tables and functions all together when doing a schema dump. I guess it's not that hard to manually break that file into two after the last table. I have used a three-file method for my database. So, I'd use pg_dump to dump just the data into a file. Then I'd have the table schema (without functions etc) in one file, and another file containing all my PL/SQL functions etc. But, I would not use pg_dump to get the the table schema and functions, again I'd just have to leave those two hand-written files sitting around and load the data inbetween running those two files. I've done it this way so I can make small changes to the procedures or tables if I have to. I'm curious how other people handle their backup procedures, especially when they have many stored procedures and/or RI keys. -- -------- Robert B. Easter reaster@comptechnews.com --------- - CompTechNews Message Board http://www.comptechnews.com/ - - CompTechServ Tech Services http://www.comptechserv.com/ - ---------- http://www.comptechnews.com/~reaster/ ------------
В списке pgsql-general по дате отправления: