Thread: Replicating db structure changes
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. Are there any other replication options that might work for what we're trying to do? Another option is to begin looking into building our own SQL generation (compatible with Rails migrations) tool that generates slonik-friendly scripts for changes to the database structure. Thanks in advance, Robby -- Robby Russell http://www.robbyonrails.com/ http://www.planetargon.com/
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
On Wed, Jul 25, 2007 at 09:08:56PM -0700, 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. > > Are there any other replication options that might work for what we're > trying to do? Are you looking for failover or loadsharing? If it's just failover, PITR warm standby should have no problem with DDL. But you can't do queries against the slave until after a failover... //Magnus