Re: Replicating db structure changes

Robby Russell wrote:
> Hey all,
>
> I'm catching up the replication options for PostgreSQL and was looking
> at slony, but was wondering if I could get some pointers.
>
> If I understand the slony documentation correctly, it doesn't allow me
> to send CREATE/ALTER TABLE/INDEX statements to the master and have those
> replicate to the slaves. Am I mistaken? If this is the case, are there
> any alternatives to slony that would allow this to happen? We're using
> Rails migrations, which generate the corresponding SQL statements and
> then runs those against the production database. This works great, but
> we're not sure how to go about having those migrations properly affect
> slave databases, without running execute statements through slonik.

If you want to do this without any downtime at all, then you need to run
the schema changes through slonik. You'll also want to test it
thoroughly first.

If you can have downtime, then you could just drop the replication, make
the changes and start it up again. Depends on your requirements.

I don't know if any of the commercial replication solutions allow
arbitrary schema changes while live. I'm not sure how you'd start to
implement this with slony, since that would imply replicating system
tables. Even if you could attach triggers, you'd have to sacrifice
having cross-version and partial-db replication.

Do you make that many schema changes to your live system that it's a
problem manually tweaking these rails migration scripts?

--
Richard Huxton
Archonet Ltd