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