El 21/08/15 a las 20:45, Florin Andrei escribió:
>
> The single instance scheme is not very reliable. I need to build a new
> DB backend. I'll set up Postgres 9.4. Ideally, I'd like to setup 2
> instances, each instance placed in a different availability zone.
> Master/master replication. I'll put a load balancer (ELB) in front of
> both instances.
It's not clear if the main goal is reliability (or availability), or to
balance writes.
If you are looking for HA, single master with multiple standbys is your
best bet (you can put standbys on different zones).
You can also look at BDR and have masters geographically distributed,
but I'd strongly suggest you look at the link
http://bdr-project.org/docs/stable/weak-coupled-multimaster.html, and
keep in mind that with multi-master systems you will be more prone to
data modification conflicts.
You also have to be aware that BDR tries to keeps the schemas consistent
across all nodes. This is done by replicating DDL statements (not all,
you'll need to check for the not replicated statements and the
prohibited ones here
http://bdr-project.org/docs/stable/ddl-replication-statements.html)
which can bring up locking issues.
If you want to balance writes, I don't think a multi-master clustering
will fit your needs. If this is the case your needs would be better
satisfied with sharding solutions.
> The batch updates and the queries will be sent by the ELB to any
> instance in the cluster; replication will take care of copying the data
> to all instances. I want the whole cluster + the load balancer to act as
> a single instance to everyone connecting to it.
>
> "Eventually consistent" replication is fine. I don't want to share
> storage between PG instances if I can avoid it. I would like to use the
> 9.4.4 packages made for Ubuntu if at all possible (avoiding any patching).
BDR requires patching PostgreSQL for 9.4 and 9.5.
> I see there are many different ways to build a PG cluster. What would be
> the best choice in my case?
>
> If I were to drop the master/master requirement and just do
> master/slave, sending updates to one node, and doing all analytics on
> the other node, what would be the best replication technique in this case?
Stream replication seems the one which might fit better. Trigger based
replication would choke on large bulk loads (unless you split them up
into smaller pieces)
Regards,
--
Martín Marqués http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
