I'm doing some testing of dbmirror, and thinking about async replication of
DDL statements such as "CREATE TABLE", etc. Dbmirror uses triggers to
queue any modified rows for retrieval by a slave-based replication process.
Ideally, DDL statements would also propagate similarly without syncronous
human intervention or service interruption, but I'm wondering how doable
that is in the model of trigger-based async replication. Or does one
really have to execute DDL statements on slaves outside of the async
replication flow, i.e. quiet the servers, execute DDL statements to both
master and slaves, then re-enable queries?
I considered setting up triggers on Pgsql system tables to replicate DDL
statements and replicate those system row changes much like user data, and
to also use system table triggers to create replication triggers for newly
added tables. But user triggers are not allowed on system tables, and I'm
not sure it'd be reasonable or wise anyway. Comments or other ideas?
Ed