Hi. I had to implement something similar some time ago. Basically, a group of database servers (postgres) geographically distributed, with each one having a group of servers in each datacenter, and each server preferring the nearest database server, but allowing connections to a further one if the nearest is down.
After going through different solutions (pgpool between them), I got to Postgres BDR with HAProxy. Each app server connects to the local HAProxy, which forwards the connection to the nearest available database server(preference is set directly in the HAProxy configuration). That way, I get high availability and replication happens really fast, right after the transaction is committed. The only drawback with Postgres BDR is it has some limitations: - New databases are NOT replicated; but you can have any number of databases with no problem. - Users & roles must be replicated manually, as BDR works at database-level. - There are some DDL restrictions: mostly due to how BDR works internally. In my experience, none of them has been a real problem. Full list: http://bdr-project.org/docs/stable/ddl-replication-statements.html - DDL replication may sometimes mean automatic cancellation of running transactions. so it must be carefully planned. However, consider TEMPORARY TABLES are not replicated, so DDL on them is not affected by BDR restrictions. - Even when BDR documentations says nothing about this, it can have trouble replicating really large transactions. In my experience, my BDR cluster stopped replicating (had to rebuild it) when an app made a 8 million records update in a single transaction. Since that app was corrected, nothing similar has ever happened, and I think most apps should not have problems with this. Alvaro Aguayo Jefe de Operaciones Open Comb Systems E.I.R.L. Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103 | RPC: (+51) 954183248 Website: www.ocs.pe ----- Original Message ----- From: "Simon Windsor" <simon.wind...@cornfield.me.uk> To: pgsql-general@postgresql.org Sent: Saturday, 21 January, 2017 2:38:59 PM Subject: [GENERAL] PgPool or alternatives Hi My employer wants to move from an in house Oracle solution to a cloud based Postgres system. The system will involve a number of data loaders running 24x7 feeding several Postgres Databases that will be used by internal applications and external customer applications. For the record, internal and external applications make heavy use of Temporary tables, that are session related. This requirement means I cannot consider normal replication methods. Is PgPool the only viable that will allow the system the data loaders to feed [n] databases that will be functional identical? Simon -- Simon Windsor Eml: simon.wind...@cornfield.me.uk Tel: 01454 617689 Mob: 0755 197 9733 “There is nothing in the world that some man cannot make a little worse and sell a little cheaper, and he who considers price only is that man's lawful prey.” -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
