question Fail over website and database.

[paulfrottawa]

Fail over website and database.

I'm interested in using postgresql replication. The primary data server and website on one site. And the slave data, and secondary webserver setup miles away.

If the primary shuts off and its dns is unreachable then the secondary dns takes over and points the website to it. and the slave replicator can automatically take over and start allowing writes to it.

Where I'm stuck is if the whole primary server doesn't go down how can the secondary take over.

 

[Morte]

This is a messy idea, but might be something to consider (assums table level granularity in replication).

For select on data, use a view that looks at two tables. First one is the primary "main data" second one is "fallover data". Only replicate the "main data" table. The backup server will only do writes to the fallover table. Because people only arrive there when the network is down, this doesn't usually happen. Once the network is back to normal, a process loads those transactions into the "main data" on the primary server.

I think detection would be a router / dns server issue as far as figuring out the connection situation. Just make sure that the TTL on the dns A record is set fairly low (minutes and not hours).

 

[mamalos]

I'm not sure how you're figuring out how your main server is down. If you're using some custom script/program, program it in a way that it will realize when your site is unusable (eg nc to port 80, nc to port 5432, ping, etc), and only then redirect all traffic to the secondary infrastructure.

If I understood correctly, in your configuration, if something is not working correctly on the main server you redirect all requests to the secondary infrastructure. Because, alternatively, you could have also followed a failover strategy on your main infrastructure, so that if your main webserver is up but connectivity to your main database is lost, first try to reload it and if this fails, instruct it to use the secondary database server (which, of course, needs a high-bandwidth connection between the two hosts)...

Anyway, as morte suggested, keep a very low TTL on your DNS records, and set the value to something meaningful with respect with the longest down-time that you want to allow. Don't forget, you can even set it to one second!

Good luck!