Обсуждение: Is BDR support distributed table on slave nodes with ACID and join support.

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

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

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