Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

There is a system with up to 256 nodes in db cluster. We need to replicate some data from one of them across to all others. But for every row to replicate there is a list of nodes where it must be replicated while on other nodes same row becomes orphan without one of parent table keys. To filter them out, or to prevent them from replicating needlessly, I'm trying to find a way to teach Slony to select nodes for replication however can't find such feature readily available. Has anyone tried it (customizing source code of its functions, etc.)?

_id,_text,_nodes
----------------
1   'one' {1,101} 
2   'two' {2,102}
256 '...' {0,256}

Row id=1 is then replicated to node 1 and 101, etc.

Similar discussion Replicating only significant records to specific database however doesn't point in the right direction yet.

share|improve this question
add comment

2 Answers 2

I don't see a way to do this directly. As I understand it, Slony sets replication destinations on the table, not row, level.

How I would solve this specific problem would be to materialize views based on replication destination group, and then use triggers to maintain those. Slony should then be able to replicate these tables to the slaves. I don't know if you can have triggers on the slaves that populate the main tables, but if not, there are ways around that. This requires decoupling the initial tables from the replication, and adds some complexity, of course.

share|improve this answer
    
Materialized view? Seems we'll duplicate our data plus additional overhead in maintaining it with triggers. On top of it it's difficult to trace errors in triggers because they do not interrupt update transactions that start them. I'm not sure the system in question allows it. I'm beginning to think more in the direction of hacking Slon source code somehow, like bounce ins/updates on slave if new row violates integrity check. Or maybe use a different replication engine? I hear xDB Enterprise has some sort of "row filter". –  Gonki Oct 4 '12 at 9:42
    
Oh I agree, hence the "and adds complexity." I just don't see an alternative. –  Chris Travers Oct 4 '12 at 10:13
    
Actually I do see an alternative which would be to break out VIM and hack on the Slony source code. –  Chris Travers Oct 4 '12 at 10:13
add comment

I was able to achieve this with PGQ using push-pull queuing schema. Biggest challenge was programming consumers correctly but now it works great! Also faster than Slon.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.