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.

I have a remote database which holds data for various databases in various locations. I want to do database replication, but only data specific to a certain database should be replicated to it. I can identify the data using a column id.

i.e if my id starts with dba23 then this data should be replicated to database with the dba23 in this case collection_db.

Am using dblink currently, but I want a real-time solution.

suggestions are highly welcome

share|improve this question
    
The xDB Replication Server from EnterpriseDB seem to have this particular feature - "define and apply row filters". But does this work with vanilla PostgreSQL or do one need PostgreSQL Plus? –  JohnP Jul 11 '12 at 4:53
    
@JohnP: I think the xDB row filter feature cannot be used to route replication to certain slaves but only to filter rows from replication altogether. –  tscho Jul 11 '12 at 6:31
    
@tscho Thanks for that clarification. –  JohnP Jul 11 '12 at 9:05
add comment

4 Answers

up vote 3 down vote accepted

You should be able to do this with slony. Slony is an asynchronous (but near real-time) trigger based single-master/multi-slave replication solution for PostgreSQL.

Take a look at the "Retail Store Problem" in this presentation (OpenOffice). This example actually deals with simulating a multi-master replication but the trick used can also be applied in the other direction for your use case:

  1. On the master use table inheritance to route your data (according to the id) from the parent table to a slave specific child table. This can be done with insert triggers or rules.
  2. Replicate the slave specific child table(s) to the slave database.
  3. On the slave use table inheritance again to have the same table name in all databases (parent table only wrapper for slave specific child table).

Instead of table inheritance you could also use INSTEAD OF triggers on views with PostgreSQL 9.1. The little ascii art below should give you an idea of the basic structure of this solution.

db_master                          db_slave_dba23       db_slave_dba17

tbl_parent                         tbl_parent           tbl_parent
|                                  ^                    ^
v                                  |                    |
tbl_child_dba23 --- replicate ---> tbl_child_dba23      |
|                                                       |
v                                                       |
tbl_child_dba17 --- replicate ------------------------> tbl_child_dba17
|
v
...

Be aware that while (under certain pre-conditions) slony can well be used as a production grade replication system, such a complex replication solution can get very cumbersome to maintain and monitor. Especially if DDL statements are involved in your daily database use cases this solution may not be suitable at all.

CAVEATS

  • The replicated tables are read-only on the slaves
  • Replication is not transactional (but neither is dblink)
  • Slony can be a real PITA
share|improve this answer
add comment

If you don't need near real-time sync, you could create views or table functions for each child db in the mother db and sync the data with the help of dblink queries to these functions / views originating from the children dbs.

I would create a schema for each child db and put all the objects there. Makes it easier to handle privileges and keep everything organized.

share|improve this answer
    
am currently using dblink but I want some realtime solution @Erwin –  G. Ngotho Jul 10 '12 at 16:20
    
@user1328181: That's the kind of information that should be in your question. –  Erwin Brandstetter Jul 10 '12 at 16:50
add comment

You can synchronize and subset data with SymmetricDS, an open source asynchronous multi-master replication engine that works with PostgreSQL. It uses triggers for its data capture, similar to slony. The transaction of commits is recorded so that transactions are preserved when syncing. It batches and routes the data to the target database, which gives you a chance to express where data goes. The interval of data synchronization is configurable so it can be close to real time.

One way to subset data using the value in a column is the Column Match Router. Each database (or node, in SymmetricDS parlance) is given an external ID that can be a reference for routing data. For example, one of your database nodes could be named dba23, and you could look for that value to match on a table's column, like this:

MY_COLUMN_NAME=:EXTERNAL_ID

If that router is too simple, you can use a Scripted Router and provide a Beanshell script that can access the row data and decide where to route it.

I've used the SymmetricDS Tutorial (chapter 2 in the User Guide) before as a quick proof of concept to try out ideas. It uses a sample database for a retail store use case with a home office server and multiple store databases. There is a store column on one of the sample tables that could be used to sync each store database its subset of data.

share|improve this answer
add comment

May be earlier it didn't :) good news...enterprisedb xdb replication server now supports row level filters for specific set of data in its multi master and single master replication features.

Its multi-master replication supports vanilla postgresql and enterpriseDB .

It single master replication feature supports variety of databases including oracle, vanilla postgresql, enterprisedb ans microsoft sql server.

row level filters are supported in it 5.1 versions or greater.

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.