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

  • 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, 2012 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, 2012 at 6:31

3 Answers 3


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

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.


  • The replicated tables are read-only on the slaves
  • Replication is not transactional (but neither is dblink)
  • Slony can be a real PITA

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:


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.


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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