Re: Disabling and enabling constraints and triggers to make pg_restore work - Mailing list pgsql-general

Tom et al ~

I understand that this is all a risky business.  So maybe someone can tell
me a safer way to accomplish what I was trying to accomplish with pg_dump
and data-only pg_restore.

It's a basic bit of database administration work.  I'm trying to establish
two (or if necessary more) instances of the same database - a production
instance and a development instance - and a change management process for
coordinating them.  As you can probably guess:

1. The production instance is the one the users are actually using.  Its
data are The Truth.

2. The development instance is where design changes (to tables, procedures,
and all other database objects) are developed, tested, and readied to go
into production.  Its data are of no value except for testing purposes.

3. The crucial purpose of the change management process is to put into
production each new release of the database design.  The required outcome is
that the new design (from the development instance) be up and running in the
production instance, managing the production data (from the production
instance), which have been preserved without loss.  (Of course, certain
changes in the new design - dropping a table, for example - will cause
certain data to be lost.  That's not the problem I'm wrestling with here.)

So, the process I have in mind goes like this:

1. At the start of a release cycle, drop everything from the development
instance, and copy the schema (with the production data if you want) from
the production instance into the development instance.

2. During the release cycle, the users use the production instance
(including modifying the data in it), and the developers do their work
(which is modifying the design) in the development instance.  The developers
can do whatever they want to the data in the development instance.

3. At the end of the release cycle, empty all the data from the development
instance, shut down the production instance (or at least write-lock up its
data), and copy the production data (data only) into the development
instance.  Then shut down the production instance to users, drop everything
in the production instance, copy everything (data + schema) from development
into production, and reopen it to users.

Note that step 3 also accomplishes step 1 of the next release cycle, so as
soon as step 3 is done, the users can go on using the data and the
developers can begin developing the next release.

It seemed to me that the PostgreSQL utilities for accomplishing this process
would be pg_dump and pg_restore.  And I've been successful using them -
except for that point in step 3 where I need to do a data-only restore from
production into development.  Then I run into the FK violations (when a FK
table gets restored before its PK table) that led me to start this
discussion thread.

So, to say it again:  What I *have* to accomplish is to separate my
production environment from my development environment, and periodically to
move the new version of the design from development into production.  I've
spelled out my whole approach from top to bottom in hopes that someone can
suggest something at some level (different process? different utilities?
different something else?) that will make this possible with PostgreSQL.
There must be thousands of DBAs out there who have solved this problem, and
I hope one of you can tell me how to join your happy ranks.

~ TIA
~ Ken



    -----Original Message-----
    From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
    Sent: Sunday, August 06, 2006 11:35 AM
    To: ken@sunward.org
    Cc: PostgreSQL pg-general List
    Subject: Re: [GENERAL] Disabling and enabling constraints and triggers
    to make pg_restore work

    "Ken Winter" <kwinter@umich.edu> writes:
    > I'm trying to do a data-only pg_restore.  I'm running into a roadblock
    > whenever the restore tries to populate a table with a foreign key
    before it
    > has populated the primary key table that it refers to: This violates
    the FK
    > constraint, which aborts the restore.

    The simplest answer is "don't do that".  A full restore (schema+data)
    will work correctly.  A data-only restore cannot hope to guarantee
    referential integrity, other than by dropping and later recreating all
    the FK constraints, and there's a fatal flaw in that plan: what if it
    doesn't know about all the FK constraints that are in the database it's
    trying to load into?  There could be permissions problems too, if you're
    trying to do it as non-superuser.

    > Someone on that forum suggested "update
    > pg_catalog.pg_class set relchecks=0 where relname ='mytab'" to disable
    and
    > "update pg_catalog.pg_class set relchecks=1 where relname ='mytab'" to
    > re-enable.  But to write to pg_catalog you apparently need to be a
    > superuser, which alas I'm not.

    You should certainly not do anything as risky as messing with relchecks
    by hand --- there was a case just a couple weeks ago where someone
    destroyed his database by fat-fingering an update command like this :-(.
    pg_dump has had a --disable-triggers option for years, and using that is
    far safer.  There's also (as of 8.1) an ALTER TABLE DISABLE TRIGGERS
    command, which is even safer.  However these still require superuser
    privileges, because you can easily break referential consistency by
    misusing them.

                regards, tom lane