Slony1 PostgreSQL Replication - adding rules to replicated tables

Author

adding rules to replicated tables

Victoria Parsons

2005-06-29, 7:24 am

Hi,

I am replicating a database out and the slaves need to keep logs of changesto some tables. Before replication, I had a tablename_changelog. Whenever the table <tablename> gets data inserted/updated/deleted it followed a ruleto write the old and new data to the changelog. There is a rule on the change log that sends out a notify when it is written to.

Now that I am replicating the main table data down, I only want to keep these changelog tables on each slave, not on master. The problem I have is that when slony sets itself up in my database it removes (or hides) the rules on <tablename> that write my changes into the changelog table. These rules won't adversly affect my replication as they don't ever write into the replicated table, only read from. I can add them back in after replication is setup, but then I hit difficulties trying to anything else with slonik commands, like uninstall table or node, as these rules "shouldn't" be there. I'massuming this will break any change of master scripts aswell.

Is there anything clever I can do other than setup replication, create rules on slaves, remove the extra rules on slaves before uniinistalling/changing master node, then recreate rules afterwards.

Cheers,
Vicki

____________________
____________________
__________

Victoria Parsons
Research & Development

StreamShield Networks

Tel 0870 114 9465
Fax 0870 114 9467

This message should be regarded as confidential. If you have received this email in error please notify the sender and destroy it immediately.
Statements of intent shall only become binding when confirmed in hard copy by an authorized signatory.

--
This message has been scanned for viruses and potentially
harmful content by StreamShield Protector.

Christopher Browne

2005-06-29, 11:24 am

Victoria Parsons wrote:

> Hi,
>
> I am replicating a database out and the slaves need to keep logs of
> changes to some tables. Before replication, I had a
> tablename_changelog. Whenever the table <tablename> gets data
> inserted/updated/deleted it followed a rule to write the old and new
> data to the changelog. There is a rule on the change log that sends
> out a notify when it is written to.
>
> Now that I am replicating the main table data down, I only want to
> keep these changelog tables on each slave, not on master. The problem
> I have is that when slony sets itself up in my database it removes (or
> hides) the rules on <tablename> that write my changes into the
> changelog table. These rules won't adversly affect my replication as
> they don't ever write into the replicated table, only read from. I can
> add them back in after replication is setup, but then I hit
> difficulties trying to anything else with slonik commands, like
> uninstall table or node, as these rules "shouldn't" be there. I'm
> assuming this will break any change of master scripts aswell.
>
> Is there anything clever I can do other than setup replication, create
> rules on slaves, remove the extra rules on slaves before
> uniinistalling/changing master node, then recreate rules afterwards.
>
May I point you to the STORE TRIGGER command?

<http://linuxfinances.info/info/stmtstoretrigger.html>

That allows triggers to be "reactivated."

The effect there is that the triggers are applied to ALL nodes; that may
not be exactly what you want.

It would be a neat idea to improve the STORE TRIGGER command so that
triggers could be activated just on a particular set of nodes.

The challenging part would be to 'reshape' the triggers when something
like MOVE SET is run. It could take a fair bit of configuration
information to handle that for general cases. Indeed, configuring that
to be handled "automagically" strikes me as requiring a pretty
complicated configuration scheme :-(.

It's certainly not impossible; a carefully thought-out proposal for how
to handle it needs to emerge...

Hannu Krosing

2005-06-29, 11:24 am

On K, 2005-06-29 at 11:02 -0400, Christopher Browne wrote:
> Victoria Parsons wrote:
>
....[color=darkred]
> May I point you to the STORE TRIGGER command?
>
> <http://linuxfinances.info/info/stmtstoretrigger.html>
>
> That allows triggers to be "reactivated."

How does this relate to RULEs ?

Slony disables both triggers and rules, but provides only a command for
reactivating triggers.

> The effect there is that the triggers are applied to ALL nodes; that may
> not be exactly what you want.
>
> It would be a neat idea to improve the STORE TRIGGER command so that
> triggers could be activated just on a particular set of nodes.

Same for EXECUTE SCRIPT - it would be much better, if the script couls
be executed onyl on a set of nodes (same as defined by a slony set).

> The challenging part would be to 'reshape' the triggers when something
> like MOVE SET is run. It could take a fair bit of configuration
> information to handle that for general cases. Indeed, configuring that
> to be handled "automagically" strikes me as requiring a pretty
> complicated configuration scheme :-(.

If this would be uniquely defined by a slony set, no additional info
would be needed.

> It's certainly not impossible; a carefully thought-out proposal for how
> to handle it needs to emerge...

--
Hannu Krosing <hannu-7C/ iILuz2RdeoWH0uzbU5w@
public.gmane.org>

Victoria Parsons

2005-07-05, 11:23 am

I'm still trying to add some rules back into my replicated (slave) database=
s. Its not going too bad but I'm at a point where I'm stuck and looking for=
a clue.=0D

After subscribing a slave node to the master I run a script that inserts al=
l the rules I need. Before unsubscribing the set for any reason I run a scr=
ipt to remove them all. This all works fine and I can start and stop the re=
plication without any errors caused.=0D

The problem is that if I start inserting the rules immediately after issuin=
g the subscriber set command the "new" rules get deleted as the set subscri=
bes. So I got round it by adding a sleep for 5 seconds between subscribing =
and adding new rules. When the rules are in the slavedb they work fine, but=
it means they didn't fire for some of the first data that got copied by co=
py set (because I was still sleeping while it happened) =0D

I can't see any way around this without modifying slony source as the subsc=
ribe set rolls it all into a single command. =0D
So my two potential plans for going forward are=0D
a) try and split the subscribe set command into a 'remove rules and trigger=
s' phase and a 'start replicating' phase. =0D
b) trying to copy the 'store trigger' function to make a 'store rules' func=
tion. =0D

I've had a brief look at the source and can't decide where to start. Can an=
yone suggest why one or both of these ideas wouldn't work or which might be=
easiest to implement.=0D

Cheers,=0D
Vicki Parsons =0D

-----Original Message-----=0D
From: Hannu Krosing [mailto:hannu-7C/ iILuz2RdeoWH0uzbU5w@
public.gmane.org]=0D
Sent: 29 June 2005 17:36=0D
To: Christopher Browne=0D
Cc: Victoria Parsons; Slony Mailing List=0D
Subject: Re: [Slony1-general] adding rules to replicated tables=0D

On K, 2005-06-29 at 11:02 -0400, Christopher Browne wrote:=0D
> Victoria Parsons wrote:=0D
> =0D
....=0D[color=darkred]
=0D[color=darkred]
> May I point you to the STORE TRIGGER command?=0D
> =0D
> <http://linuxfinances.info/info/stmtstoretrigger.html>=0D
> =0D
> That allows triggers to be "reactivated."=0D

How does this relate to RULEs ?=0D

Slony disables both triggers and rules, but provides only a command for=0D
reactivating triggers.=0D

> The effect there is that the triggers are applied to ALL nodes; that may=
=0D
> not be exactly what you want.=0D
> =0D
> It would be a neat idea to improve the STORE TRIGGER command so that=0D
> triggers could be activated just on a particular set of nodes.=0D

Same for EXECUTE SCRIPT - it would be much better, if the script couls=0D
be executed onyl on a set of nodes (same as defined by a slony set).=0D

> The challenging part would be to 'reshape' the triggers when something=0D
> like MOVE SET is run. It could take a fair bit of configuration=0D
> information to handle that for general cases. Indeed, configuring that=
=0D
> to be handled "automagically" strikes me as requiring a pretty=0D
> complicated configuration scheme :-(.=0D

If this would be uniquely defined by a slony set, no additional info=0D
would be needed.=0D

> It's certainly not impossible; a carefully thought-out proposal for how=
=0D
> to handle it needs to emerge...=0D

-- =0D
Hannu Krosing <hannu-7C/ iILuz2RdeoWH0uzbU5w@
public.gmane.org>=0D

-- =0D
This message has been scanned for viruses and potentially=0D
harmful content by StreamShield Protector.=0D

This message should be regarded as confidential. If you have received this =
email in error please notify the sender and destroy it immediately.=0D
Statements of intent shall only become binding when confirmed in hard copy =
by an authorized signatory.=0D

--=20
This message has been scanned for viruses and potentially
harmful content by StreamShield Protector.

Sponsored Links