How to drop all connections to a specific database in postgres without stopping the server?

Running multiple web servers and database replication; any ideas?

• Working on finding a solution to a particular problem we have at the office,we have a web host and at times our web server (Apache) crashes; which causes our web app to be unavailable to users. Usually as a result of the number of users, we noticed that there is a rise in the number of database connections at peak times. Option 1: So we were thinking of getting a server solely to server as the database  server(mysql) and another as as our web server on the same host. Q:Is there a better way to solve this problem while using a single server? Q:is there anyway to manage the number of connections to the database server without causing any serious issues for the users? Option 2: We were thinking of getting another server (i.e. one with both apache and mysql running), in the case where the first becomes unavailable, all the traffic switches to the second. The issue here is that mysql only supports 1 way replication (i.e. master-slave). Instance: normally the slave mirrors the database in the master, in a situation where there is a fail over,and a user performs a write action on the slave, when the master comes back up, how can we also replicate all the writes in the slave to the master for the sake of consistency( without which a user might find out that some data he saved previously seems not to be available, apart from primary key conflicts). Q:How can this be implemented, to cater for this need? Also, would really love any suggestions on solutions (This problem is a new one for us).

• Answer:

  MySQL actually does support Master-Master replication (you need to use auto_increment primary keys and use the auto_increment_increment [or something like that] parameter]).  But I wouldn't recommend that for your situation. I think you have two options for scaling and redundancy.  First, you can switch from MySQL to a massively-hosted database solution that has lots of redundancy built in.  Perhaps unfortunately, the massively-hosted ones are NoSQL, so you'll have to make some adjustments in your application.  SimpleDB is probably the best one for your purposes, since it has a lot of support for SQL and is pretty easy to get started with.  If your database is really big, however, it might get expensive.  And with SimpleDB, you won't know until you start testing it.  The CPU time cost is usually the most expensive cost with SimpleDB, so test with small sets first. The other option is to have redundant application servers and redundant database servers.  The usual way of doing this with MySQL is to have a load balancer in front of the application servers, and just have an online slave for the database that's replicating data and can be promoted to master in a pinch.  I think the best and cheapest way to do this (and the way we do it at BuildFax) is to use RightScale + Amazon Web Services EC2 + Amazon's Elastic Load Balancer.  You can easily vertically scale the database server, and you can use small or micro instances for your application servers and your database replication slave, if that's possible. At the end of the day, you're going to need separate application and database servers, and you're going to need at least two of any server so you can handle downtime.  The downside of the standard MySQL deployment is that you have to manually fail-over to the slave.  There is a product in beta from ScaleBase that promises to automatically fail-over, although you'll need to introduce another type of redundant server into the mix (a ScaleBase proxy server that sits in between the application server and the database server).