Is BDR support distributed table on slave nodes with ACID and join support.

1,2,3: You can't shard with BDR. It's only for multimaster (at least for
now). Please read the docs.

On Fri, Jul 17, 2015 at 9:02 AM, Amit Bondwal <bondwal.amit@gmail.com>
wrote:

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 17 July 2015 at 17:33, Dorian Hoxha <dorian.hoxha@gmail.com> wrote:

1,2,3: You can't shard with BDR. It's only for multimaster (at least for
now). Please read the docs.

You can kind-of shard using table partitioning, but the application
has to deal with shard integration because there's no cross-node
querying. So in practice it's no benefit over a bunch of standalone
databases.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 17 July 2015 at 19:37, Amit Bondwal <bondwal.amit@gmail.com> wrote:

Thank you very much all of you for quick response and clearing my mind.

Please reply to the mailing list, not just to me directly.

One more question, can I run a databse of postgres 9.4 on postgres-XL
cluster?

postgres-XL is based on PostgreSQL 9.2, so no.

Postgres-XL doesn't "run on" PostgreSQL. It's a modified (forked)
version of PostgreSQL.

Or you can advise me any better solution.

Not without knowing a lot more about what you're trying to do, what
your requirements are, etc.

As per my knowledge partitioning
of a table have some limits in nos. and I can't extend more storage in a
server upto a limit.

Yes, that's true, but those limits are quite large, and you may be
trying to solve a problem you don't and won't have.

This may well be premature optimisation. I can't know without a lot more info.

Rather than starting with the solution (horizontal partitioning,
sharding) try starting with the problem and requirements, then looking
for solutions from there.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Thanks you very much Craig for clearing these facts and sorry for mail sent
only to you by mistake.

--
Thanks & Regards,

Amit Bondwal

On Fri, Jul 17, 2015 at 6:19 PM, Craig Ringer <craig@2ndquadrant.com> wrote: