On 17 July 2015 at 15:02, Amit Bondwal <bondwal.amit@gmail.com> wrote:
> I looked at pg_shard, as per my understanding it is for nosql data, in our
> case we are going to use postgresql RDBMS features without nosql, pg_shard
> have lot on limitation as per our case.
You'll find that _most_ products have a lot of limitations when doing
things like horizontal scaling. BDR is no exception either.
> 1. If start with postgresql 9.4 database, later can I upgrade it to BDR with
> these already large table, can I distribute these tables or shard them.
You need a patched PostgreSQL 9.4 if you're going to use BDR because
it relies on a bunch of functionality that didn't make it into the 9.4
release. (Some didn't make it into 9.5 either).
That patched PostgreSQL can be used like normal PostgreSQL 9.4, but
it's not 100% on-disk compatible. You have to do a dump and reload to
convert to or from stock community PostgreSQL, you can't pg_upgrade or
otherwise convert in-place.
You can run normal PostgreSQL applications on a standalone database on
the BDR-patched PostgreSQL 9.4 by just not installing the BDR
extension in the database. You can then activate BDR later.
Be aware, though, that BDR imposes quite a few limitations on what the
app can do when it comes to DDL. If you activate BDR later, you might
find that things you were expecting to work and that worked fine
before stop working. I'd strongly suggest planning to deploy with BDR
from the start if you're going to use it at all.
> 2. how can I distribute some of tables on my slave nodes, if it have any
> limitations please let me know.
BDR doesn't support sharding. A table can be on all nodes or - using
replication sets - some subset of nodes, but it's the whole table or
none of it.
You can partition the table and shard the partitions using replication
sets, but then each node will only see a subset of the data. There's
no transparent cross-node querying.
You could try to combine foreign data wrappers and inheritance with
partitioning, but by then you're playing database jenga and things
will come toppling down very, very hard.
> 3. if I started with 4 or 5 node cluster with one master and rest of slave
> nodes, how can I take backup of this distributed database to one of my other
> server or backup location.
pg_dump
> Please suggest me what would be the best approch for this.
From your description it sounds like BDR is not particularly suitable
for your use case at this time.
We're looking at adding sharding down the track, but it's quite a way
down the track because there's a fair bit of work on making sure the
core functionality is rock solid and easy to manage.
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services