postgres 9.1 streaming cross datacenter for BCP
Part of the high availability / data protection and business continuity planning I'm thinking of setting up my hot standby postgres server in another datacenter.
My current setup is 2 load balanced App servers talking to the master in the same datacenter in Atlanta (other app servers in a different DC will come soon and loadbalancing/failover will be done through DNS). But for the database streaming replication I was thinking of the following setup after having done some reading on Postgres clustering techniques:
1) Postgres 9.1 streaming replication from master to slave(s).
2) pgpool-II on app servers for connection pooling and distribution of R/W queries across Postgres servers.
3) repmgr to manage Postgres manual failover (as much as I like 99.99% uptime I prefer manual automation of this task unless someone can convince me that repmgr automation works flawlessly - dont have time for PoC
4) Cross datacenter streaming replication ???
Questions:
1) Having Atlanta as primary DB site, does anyone have any experience with replication from Atlanta to another DC or some other cross datacenter configuration?
latency between Atlanta and Newark seems best at 23ms/avg with 11 hops.
2) DB size will be growing to a few GB in the first year (less than 10GB) and will grow exponentially when I add more clients in year 2+ (forecasting 300G+). Anythinig I need to be aware of in terms of cross datacenter replication for failover/promoting slave to master and switching back when master comes back.
3) Any other suggestions for Postgres high availability/business continuity planning?
thank you!
Edmond