Re: Disabling and enabling constraints and triggers to make pg_restore work

"Ken Winter" <ken(at)sunward(dot)org> writes:
> ...
> So, the process I have in mind goes like this:
> ...
> 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.

I'm not really sure why you'd expect a tool like pg_dump to be helpful
with this sort of scenario. In almost every interesting case, there's
going to be something you have to *do* to the data in order to load it
into the new schema --- you'll have refactored the tables, changed
representations, or whatever. So ISTM that foreign key constraints
are the least of your worries; and even more to the point, you almost
certainly *don't* want to bypass revalidating them, if you've made any
least little change in the data relationships. So my advice would
be "drop the FK constraints, load data, re-add FK constraints".

The nearby comments about having a more formalized development process
sound good to me too, but in any case I think this application goes
beyond what pg_dump can be expected to do.

regards, tom lane