(I've used other DBs in the past, but I'm fairly new to PG.)
Currently I have a single PG 9.3 instance in the cloud. A Python script
run as a cron job is connecting to it over the network and is doing the
batch updates every hour, usually in append mode. Users have various
custom scripts which are used for analytics queries and connect to the
single 9.3 instance over the network and run their queries a few times a
day. Dataset is a few dozen GB.
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.
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).
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?
(We are also considering a migration from the batch update model to a
more continuous stream.)
--
Florin Andrei
http://florin.myip.org/