0

I will soon need to setup a database server on a budget. We are planning to use Postgres on Ubuntu (most likely) and we'd like to have a backup server. For replication, I think I'll just use Postgres's Streaming Replication. It does all that I need and is simple to set up.

But what if the main server fails and we need the backup? Is there a way to route data to the backup without a third computer?

Let's say my main computer has the domain name PG-MAIN and my backup has the domain name PG-BACKUP. If I had a third computer I could (for example) check to see if PG-MAIN is up and if it is down, rename PG-BACKUP to PG-MAIN. Then, any query sent to PG-MAIN would still succeed. Maybe that would be a bad solution. I don't actually know. (Although this Server Fault answer says it's a bad idea to use DNS that way.)

But is there a way for the backup to silently take the place of the main computer upon failure without the use of a third computer? I can't think of anything. And by "failure", I mean like the database daemon stops responding or a piano falls on the computer.

If it turns out to be absolutely, positively impossible, what would be cost-effective way of handling the fallback? The client applications will be custom-made. Should the logic of fallback part of the system be handled by the client applications? (That last part sounds like a very bad idea.)

Community
  • 1
eje211
  • 117
  • 6
  • Are you talking about `Postgres` H.A ? – krisFR May 28 '15 at 23:28
  • @krisFR Yes, I mean the streaming replication that's part of Postgres High Availability. – eje211 May 28 '15 at 23:29
  • I'm pretty sure you read this : http://www.postgresql.org/docs/9.3/static/warm-standby.html#STREAMING-REPLICATION In that case what have you tried that is not working ? – krisFR May 28 '15 at 23:33
  • We haven't actually built anything yet. My issue is: if computer 1 fails but the data is still on computer 2, is there a way to route connections to the second computer without using a third computer, such as a DNS. I don't think there is. – eje211 May 28 '15 at 23:47
  • Seems you are more concerned by a server problem than a pure DB problem...in both cases something like [keepalived](http://www.keepalived.org) should work for you as it provides a VIP for a given service that you can monitor on each node – krisFR May 29 '15 at 00:20
  • repmgr + pgbouncer or haproxy. Failback is painful at the moment, but see pg_rewind. – Craig Ringer May 29 '15 at 01:01

0 Answers0