Re: Schema Upgrade Howto

From: Berend Tober <btober(at)ct(dot)metrocast(dot)net>
To: Thomas Guettler <hv(at)tbz-pariv(dot)de>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Schema Upgrade Howto
Date: 2008-10-30 21:05:46
Message-ID: 490A21AA.7090701@ct.metrocast.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Thomas Guettler wrote:
> Hi,
>
> is there a schema upgrade howto? I could not find much with google.
>
> There is a running DB and a development DB. The development DB
> has some tables, columns and indexes added. What is the preferred way
> to upgrade?
>
> I see these solutions:
> - pg_dump production DB. Install schema only from dev DB, restore data
> only from dump.
> - Use alter table.
> - Use a tool like apgdiff (never tried it).
>
> I guess all ways will be possible. But what do you suggest?

Use three instances of the database: development, quality
assurance testing, and production. DEV and QAT are occasionally
refreshed from a pg_dump of PRD. Developers work against DEV for
modify-compile-test-(doh, I broke it)-refix-compile-test cycles.
All structural or development-related changes required to the
data base are done with a SQL text file script. The script files
are managed along with the source code in SVN. When developers
are satisfied, the script is applied to QAT and then end-users
test the modified application against QAT. When end-users sign
off that they are satisfied, the same (*unmodifed from as run
against QAT*) script is run on PRD at the same time the same
(*unmodifed from as run against QAT*) application is deployed for
production use.

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Greg Smith 2008-10-30 21:10:08 Re: Decreasing WAL size effects
Previous Message Jason Long 2008-10-30 20:51:18 Re: Decreasing WAL size effects