Re: RI and restoring dumps

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(at)comptechnews(dot)com ---------
- CompTechNews Message Board http://www.comptechnews.com/ -
- CompTechServ Tech Services http://www.comptechserv.com/ -
---------- http://www.comptechnews.com/~reaster/ ------------