Re: Schema Upgrade Howto

On Thu, Oct 30, 2008 at 10:54:46AM +0100, 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.

The only sure way to track such changes is by changing the
databases--especially in development--only via scripts, all of which
go into your source code management system.

> What is the preferred way to upgrade?

Via scripts, all of which go in a transaction. It's here that
PostgreSQL's transactional DDL (CREATE, ALTER, DROP, for example)
really shines.

> I see these solutions:
> - pg_dump production DB. Install schema only from dev DB, restore
> data only from dump.

This won't scale, but may work for now while you institute the
development process outlined above. Test this very carefully, just as
you would any other database change.

> - Use alter table.

Yep. See above for how.

> - Use a tool like apgdiff (never tried it).

These tools never have enough information to make a decision
guaranteed to be correct, so the whole class of them is bogus.

> I guess all ways will be possible. But what do you suggest?

See above :)

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate