Can pg_shard be used only for replication between two master servers ?

[Anurag Arora]

pg_shard works great for replication and sharding. 

AFAIK it maintains meta data table for proper routing. But, can we use pg_shard only for replication between two master servers in different geo-location without any meta data table?

Bucardo is also one solution, but as per various documents it performs bad in performance when compared to pg_shard.

[Jason Petersen]

On Aug 18, 2015, at 8:54 AM, Anurag Arora <anura...@gmail.com> wrote:

AFAIK it maintains meta data table for proper routing. But, can we use pg_shard only for replication between two master servers in different geo-location without any meta data table?

pg_shard cannot operate without the metadata tables to route rows and queries. If you make a single large shard with replication (i.e. master_create_worker_shards(‘foo’, shard_count := 1, replication_factor := 2)) it will act as a single large table replicated to two nodes; however, this isn’t really the intended use of pg_shard, so any issues you hit as a result might receive low priority compared to issues resulting from actual sharding.

Bucardo is also one solution, but as per various documents it performs bad in performance when compared to pg_shard.

Have you looked into the BDR (Bi-Directional Replication) project? It requires a patched version of PostgreSQL for bi-directional support, but it sounds as though you only really need uni-directional replication, which can run on stock PostgreSQL 9.4. It uses logical replication (rather than physical, see here) and isn’t trigger-based (unlike, I believe, Bucardo). Give it a look!

--

Jason Petersen

Software Engineer | Citus Data

303.736.9255

ja...@citusdata.com

[anurag...@gmail.com]

Hi Jason, thanks for your reply.

Yes, I have looked into BDR, but because of patched version I am not using BDR.

UDR wont be helpful in my scenario, as both the servers will serve write request( I am taking care of consistency), so basically I am looking for options to sync my two PostgreSQL servers. Bucardo is one solution and other is BDR.

Yes, Bucardo is trigger based and performs bad when compared to UDR or BDR.

Other issue with BDR is that, first we have to copy our existing database and restore it in patched PostgreSQL version because of different ways of storing data by Stock PostgreSQL and patched PostgreSQL. That is not feasible in our scenarios.

So, I think I will go for bucardo as of know and will switch to BDR once it is supported by stock PostgreSQL. 

--

Anurag Arora

Software Developer | ClipMyStuff.com