Re: Schema Upgrade Howto

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.