Thread: replicating DDL statements
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
> 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? One potential problem you might run into is triggers on said added tables. I had to disable my user-made triggers on slave databases because those triggers would add rows to secondary audit tables, which were also covered by replication. In other words, I would have had duplicate entries in my audit tables... one for the master being replicated, one for the slave as the trigger executed. Additionally, I have functions that set timestamps to reflect the last updated time of a row. If I kept the triggers on the mirrored database, the timestamps will differ from the slave to the master (and possibly even the other slaves). I imagine things could get hairy pretty fast. Greg
On Thursday March 6 2003 11:25, Gregory Wood wrote: > > 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? > > One potential problem you might run into is triggers on said added > tables. > > I had to disable my user-made triggers on slave databases because ... Anyone know if there a way to disable triggers for the current transaction? Much like the "SET CONSTRAINTS ALL DEFERRED"? Is that doable? Ed
On Thursday March 6 2003 11:33, Ed L. wrote: > On Thursday March 6 2003 11:25, Gregory Wood wrote: > > > 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? > > > > One potential problem you might run into is triggers on said added > > tables. > > > > I had to disable my user-made triggers on slave databases because ... > > Anyone know if there a way to disable triggers for the current > transaction? Much like the "SET CONSTRAINTS ALL DEFERRED"? Is that > doable? And in the absence of a SET TRIGGERS='off' command, anyone see any concerns with the following? UPDATE pg_trigger SET tgenabled = 'f' WHERE tgname LIKE '%whatever%'; Ed
On Thu, Mar 06, 2003 at 02:45:41PM -0700, Ed L. wrote:
> And in the absence of a SET TRIGGERS='off' command, anyone see any concerns
> with the following?
>
> UPDATE pg_trigger
> SET tgenabled = 'f'
> WHERE tgname LIKE '%whatever%';
I have heard warnings that this is not guaranteed to work, as there
may still be places in the code where checking that table is not
happening. Perhaps that was only true for older versions, though?
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
On Thursday March 6 2003 3:36, Andrew Sullivan wrote: > On Thu, Mar 06, 2003 at 02:45:41PM -0700, Ed L. wrote: > > And in the absence of a SET TRIGGERS='off' command, anyone see any > > concerns with the following? > > > > UPDATE pg_trigger > > SET tgenabled = 'f' > > WHERE tgname LIKE '%whatever%'; > > I have heard warnings that this is not guaranteed to work, as there > may still be places in the code where checking that table is not > happening. Perhaps that was only true for older versions, though? Unfortunately not. It doesn't work for me on 7.3.2 with a triggered C function (dbmirror). :( Ed
On Thursday, March 6, 2003, at 06:21 PM, Ed L. wrote: > On Thursday March 6 2003 3:36, Andrew Sullivan wrote: >> On Thu, Mar 06, 2003 at 02:45:41PM -0700, Ed L. wrote: >>> And in the absence of a SET TRIGGERS='off' command, anyone see any >>> concerns with the following? >>> >>> UPDATE pg_trigger >>> SET tgenabled = 'f' >>> WHERE tgname LIKE '%whatever%'; >> >> I have heard warnings that this is not guaranteed to work, as there >> may still be places in the code where checking that table is not >> happening. Perhaps that was only true for older versions, though? > > Unfortunately not. It doesn't work for me on 7.3.2 with a triggered C > function (dbmirror). :( Is the goal to simply turn off triggers (I haven't really been following this thread)? Why not do what pg_dump (in 7.3 anyways) does: OFF: update pg_class set reltriggers = 0 where relname = 'TABLE_NAME'; ON: update pg_class set reltriggers = (select count(*) from pg_trigger where pg_class.oid = tgrelid) where relname = 'TABLE_NAME'; eric
On Thursday March 6 2003 4:53, Eric B. Ridge wrote:
> >>> And in the absence of a SET TRIGGERS='off' command, anyone see any
> >>> concerns with the following?
> >>>
> >>> UPDATE pg_trigger
> >>> SET tgenabled = 'f'
> >>> WHERE tgname LIKE '%whatever%';
> >>
> ... Why not do what pg_dump (in 7.3 anyways) does:
>
> OFF:
> update pg_class set reltriggers = 0 where relname = 'TABLE_NAME';
>
> ON:
> update pg_class set reltriggers = (select count(*) from pg_trigger
> where pg_class.oid = tgrelid) where relname = 'TABLE_NAME';
That works, thanks. Maybe a minor variant of that could be the
implementation of
SET TRIGGERS="off/on"
if it did not already exist, but I defer to the source code gurus. Now if I
could just figure out how to reliably replicate DDL statements
asyncronously via triggers, or some other way compatible with a trigger
approach.
Ed
I read that PG-R does not replicate DDL statements. Does eRServer? Ed
Hi Ed, Ed L. wrote: > I read that PG-R does not replicate DDL statements. Does eRServer? The present version of eRServer doesn't replicate DDL statements either. In the future it probably will, but not yet. Regards and best wishes, Justin Clift > Ed -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi
On Thu, Mar 06, 2003 at 06:08:27PM -0700, Ed L. wrote:
> I read that PG-R does not replicate DDL statements. Does eRServer?
I know this is also no help, since it's vapour right now, but there
was some talk a while back of using the PITR mechanism that is
proposed to do a kind of async replication, too. It would
_certainly_ capture DDL (and everything else which committed). Of
course, AFAIK it doesn't exist yet.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
On Friday March 7 2003 6:21, Andrew Sullivan wrote: > On Thu, Mar 06, 2003 at 06:08:27PM -0700, Ed L. wrote: > > I read that PG-R does not replicate DDL statements. Does eRServer? > > I know this is also no help, since it's vapour right now, but there > was some talk a while back of using the PITR mechanism that is > proposed to do a kind of async replication, too. It would > _certainly_ capture DDL (and everything else which committed). Of > course, AFAIK it doesn't exist yet. If it's not too complex for a short answer, could you or someone else share thoughts on how PITR would facilitate async replication? Ed
Ed L. wrote: > On Friday March 7 2003 6:21, Andrew Sullivan wrote: > > On Thu, Mar 06, 2003 at 06:08:27PM -0700, Ed L. wrote: > > > I read that PG-R does not replicate DDL statements. Does eRServer? > > > > I know this is also no help, since it's vapour right now, but there > > was some talk a while back of using the PITR mechanism that is > > proposed to do a kind of async replication, too. It would > > _certainly_ capture DDL (and everything else which committed). Of > > course, AFAIK it doesn't exist yet. > > If it's not too complex for a short answer, could you or someone else share > thoughts on how PITR would facilitate async replication? PITR archives WAL changes. You could pass those to another server and re-execute those changes. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian <pgman@candle.pha.pa.us> writes: > > If it's not too complex for a short answer, could you or someone else share > > thoughts on how PITR would facilitate async replication? > > PITR archives WAL changes. You could pass those to another server and > re-execute those changes. To expand on this: Oracle calls this a warm standby database. It's primary intent is for having a very rapid response to a critical hardware failure. You can bring up the standby database and swap ip addresses within minutes. No time needed to perform the restore. How you get async replication from that is a bit of a clever hack. Since Oracle 8 you could open the warm standby *in read-only* mode as a query database. This was great for doing large batch job queries on a live OLTP database. You could also do logical (pg_dump style) backups from that database, or data exports destined for full scale DSS systems or other batch processing. I seem to recall one of the features promised in Oracle 9 was to be able to open this database in read-write mode. I haven't worked with Oracle 9 so I don't know if they actually delivered this or not. I assume that means having read-only tablespaces to which you apply logs coming from the source database while being able to do write operations on tables in separate non-synced tablespaces. Still that sounds much more complicated, it sounds like it requires segregating the logs for tablespaces. However, being able to mount the warm standby as a read-only query database is a real tempting low-hanging fruit. It means being able to protect your OLTP database from large batch jobs interfering, while at the same time providing the instantaneous holy grail in backup restore times. -- greg