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

Ken Winter wrote:

>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:
>
>
You need three: DEV, QAT, and PRD. Changes must be moved in a controlled
fashion from DEV through QAT before committing to PRD. Your developers
generally do not touch QAT or PRD.

>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.
>
>
Wrong. TESTING happens in QAT; DEVELOPMENT happens in DEV. Keep your
thinking, and that of your team, straight and disciplined in this
regard. I suppose developers do "test" in DEV as they develop, but
Testing with a capital T, meaing acceptance-for-production testing,
happens separate from the DEV environoment: it is performed not by
developers but by the customer -- or a sufficiently close facsimile
there of. That way you practice in QAT exactly what you will do to PRD
so that you know it works before you do it for real..

>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.
>
>
This is were you use pg_dump, pg_restore, and psql for the first time --
to create QAT and DEV instances that are identical to PRD. Maybe you
have something more sophisticated that provides a means to limit the
amount and protect confidentiality of the PRD data by some
transformation on the way out to DEV, but QAT should, ideally, be
identical to PRD, and access to QAT should be restricted pretty much the
same as to PRD.

>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.
>
>
The last sentence suggests a lack of discipline (which is revealed more
fully in step 3). The "whatever they want" has to be formalized. Any
changes the developers propose making the the data base, be it DDL or
DML, needs to happen via a script or set of scripts, which are fully
documented and commented so that the changes are understandable,
repeatable, auditable and testable. In the perfect world of my
imagination, the scripts would be digitially signed with gnupg by
developers for release to QAT, and then signed by the customer for
release to PRD. And the scripts would be verified as authentic and
unchanged prior to application against QAT and PRD.

You might not be able to script your changes to the application, but you
certainly will maintain change control using Subversion and tag releases
corresponding to the DDL/DML script sets, in fact the scripts will be
part of your SVN repository, and similarly follow a gnupg signing
policy. Infact, your entire revision should go in a RPM so that the data
base changes and application changes are released as a single unit that
is signed and tamper resistant and can be applied in a simple and easily
repeated manner.

>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.
>
>

This is a very screwy procedure. Don't do it like that.

Test the entire release in QAT by an automated application of all the
changes -- DDL/DML scripts for the data base, and merge of source code
diffs from the SVN repository for the application. Have the customer
test QAT until comfortable and then digitally sign the change package.
Then apply the change to PRD exactly like you did to QAT.