We have an existing web application that uses Postgresql 9.0 and PGPool-ii. I am thinking of migrating our infrastructore to Amazon EC2 and was inspired by the following link: http://aws.typepad.com/aws/2008/12/running-everything-on-aws-soocialcom.html that uses a similar architecture.
Since Amazon RDS doesn't support PGSQL, we are going to stick with PGPool-ii to load-balance the queries on the different DB servers and keep them synchronzed between each others.
So we plan to deploy 3 frontend web servers which will contain the following : - Web Server + PHP code - PGPool-ii
Then, we would have 2 database servers on separate Amazon instances with only PGSQL. These 2 PG servers would be used by the PGPools located on the 3 frontend servers.
My question is that I don't know if this solution is reliable enough as multiple PGPool will access multiple PGSQL servers. Most examples of PGPool demonstrates a single PGPool that uses N underlying PGSQL servers. Is it a good pratice to deploy a PGPool instance on each web server ?
If not, is there any other/better architecture to avoid having SPOF using Amazon ?
Thank you very much for your replies.