4 ms·

What is the current best option for Postgres failover? I looked at this at the start of the year, and found lots of options, but all of them seemed to have vari

What is the current best option for Postgres failover? I looked at this at the start of the year, and found lots of options, but all of them seemed to have various drawbacks, and none were natively supported or built-in to Postgres.

aidos 9y ago
This is a bit outside my area of expertise. Not sure about the best way to do the automated promotion process, but looking through these docs there's mention of a change to libpq that allows you to specify all the potential hosts and then have it only connect to whichever one is accepting writes (target_session_attrs). http://paquier.xyz/postgresql-2/postgres-10-libpq-read-write/ http://paquier.xyz/postgresql-2/postgres-10-libpq-read-write... Edit to add: I'm also interested to hear the approaches people use here. Today we've been setting up WAL-E to run our db backups so we're in that headspace. I'd actually like to decommission my main db server and provision another one to take its place. Getting the new one up and running from the wal-e archives is easy enough, the switch-over is a little more complex.
martin_marques 9y ago
There is no native solution as there is no unique scenario regarding failover. Some people prefer to do fencing using network tools like firewall rules or managed switches, other just reconfigure their applications connection string to remove the failed node, or even reconfigure connections with a middle-ware like pgbouncer. There surely are other fencing solutions that don't come to my mind now, and others I don't even know of. For failover you can use tools like repmgr which provides automatic failover, or set up your own monitoring which can either trigger an alert to your pager and you run the failover. Or you have it trigger some configuration manager to reset the nodes (I strongly suggest using Ansible ;) ) This is what makes postgres flexible, as it's not tied to one solution.
ww520 9y ago
For local cluster, try DRBD to replicate at the disk level. The DRBD replication can be synchronous and are very fast. Add Linux-HA for health monitoring and automatic failover. When failed over, the standby machine just starts up PostgreSQL and recovers from the transaction log. Uncommitted transactions on the primary machine that haven't been written to disk and replicated will be lost, which is consistent with the transaction semantic. Committed transactions are replicated and recovered from the transaction log at the standby. Depending on the heartbeat interval and how fast PostgreSQL starts up and recovers from the log, the automatic failover can happen within seconds. Configure a virtual IP for the cluster and let the standby machine to take over the IP at failover. Linux-HA does all that for you. The clients talk to the virtual IP and don't have to know the primary or standby machine. They just need to reconnect when disconnected at the failover. The nice thing about DRBD and Linux-HA is that it's not just for PostgreSQL. Any disk based application can be benefited.
- takeda 9y ago
  I would strongly discourage this kind of solution. DRBD is to replicate disk. As pointed out is not Postgres specific, and it doesn't understand the data it replicates. One of nightmare of replication is that there's latency and data never makes it instantly to the other host. This means that there is a possibility that once the master fails, the database file on the other host might be in a corrupted state. If your data is important you should use replication mechanisms provided by the database. Repmgr[1] makes it easier to set up replication, and perform failover. It also has mechanisms to do auto failover, but I would also be careful with that. [1] http://repmgr.org/ http://repmgr.org/
  - ww520 9y ago
    DRBD's replication is synchronous. That means the block device's write() call on the primary won't return until the data is written onto the standby's disk. It's is as strong a guarantee as you can get. In case of the secondary not available, the data are queued up in the primary and sync up with the secondary when it comes back online. If the primary's disk is destroyed at that time, the failure case and data loss is the same as the database's native replication. DRBD is much simpler and less error prone precisely because it does not care to understand the application data. It just deals with disk block. The simplicity also helps performance greatly. Benchmark has shown there's negligible impact when doing synchronous replication.
    - takeda 9y ago
      I guess that's better, but it would just decrease performance. I still think using binlogs is safer and more efficient though, since it transfers only what's needed to make an update.
      - ww520 9y ago
        Performance is the same if not better than database level replication. The database level replication is synchronous too and have to wait for the standby db to finish writing. If it's async, it has a worse data loss problem. The DRBD replication is fast because it intercepts the write call and then issues the network send and writing to the underlying disk in parallel. Gigabit or 10G network IO are magnitudes faster than disk seek/write. The write to disk on the standby server can happen in parallel as the primary's write to underlying disk.
takeda 9y ago
I would recommend repmgr[1] over solutions like DRBD suggested. The simple reason is that DRBD replicates on block level and doesn't understand postgres data structures, while repmgr relies on mechanisms provided by Postgres. So if there is a failure, you're far less likely to learn that all your data is gone, because some important block on disk was not replicated and the database is not readable. With repmgr the worst what could happen is that you might lost few latest transactions, but your data should always be consistent and in working state. [1] http://repmgr.org/ http://repmgr.org/
- ahoka 9y ago
  Repmgr is relatively easy to setup and supports automatic failover with repmgrd. Resyncing a failed node is also just a few commands. The only limitation I have encountered was that I had to run it behind pgbouncer so the clients can always connect to the primary instance. I think that will change with 10 as it will support client side discovery of the primary node.
user5994461 9y ago
I think a reasonable option is to use pgbouncer. I wouldn't comment about it because I have long abandonned databases with no failover in favor of solution that works, but people who tried hard enough seem to get pgbouncer to a working point. postgre fans will hate, but if you really need something with automated failover, you might have to take a look at better databases. Either Postgre/Oracle in the paid SQL world, or elasticsearch/cassandra/riak in the free NoSQL world.

aidos 9y ago

This is a bit outside my area of expertise. Not sure about the best way to do the automated promotion process, but looking through these docs there's mention of a change to libpq that allows you to specify all the potential hosts and then have it only connect to whichever one is accepting writes (target_session_attrs). http://paquier.xyz/postgresql-2/postgres-10-libpq-read-write/ http://paquier.xyz/postgresql-2/postgres-10-libpq-read-write... Edit to add: I'm also interested to hear the approaches people use here. Today we've been setting up WAL-E to run our db backups so we're in that headspace. I'd actually like to decommission my main db server and provision another one to take its place. Getting the new one up and running from the wal-e archives is easy enough, the switch-over is a little more complex.

martin_marques 9y ago

There is no native solution as there is no unique scenario regarding failover. Some people prefer to do fencing using network tools like firewall rules or managed switches, other just reconfigure their applications connection string to remove the failed node, or even reconfigure connections with a middle-ware like pgbouncer. There surely are other fencing solutions that don't come to my mind now, and others I don't even know of. For failover you can use tools like repmgr which provides automatic failover, or set up your own monitoring which can either trigger an alert to your pager and you run the failover. Or you have it trigger some configuration manager to reset the nodes (I strongly suggest using Ansible ;) ) This is what makes postgres flexible, as it's not tied to one solution.

ww520 9y ago

For local cluster, try DRBD to replicate at the disk level. The DRBD replication can be synchronous and are very fast. Add Linux-HA for health monitoring and automatic failover. When failed over, the standby machine just starts up PostgreSQL and recovers from the transaction log. Uncommitted transactions on the primary machine that haven't been written to disk and replicated will be lost, which is consistent with the transaction semantic. Committed transactions are replicated and recovered from the transaction log at the standby. Depending on the heartbeat interval and how fast PostgreSQL starts up and recovers from the log, the automatic failover can happen within seconds. Configure a virtual IP for the cluster and let the standby machine to take over the IP at failover. Linux-HA does all that for you. The clients talk to the virtual IP and don't have to know the primary or standby machine. They just need to reconnect when disconnected at the failover. The nice thing about DRBD and Linux-HA is that it's not just for PostgreSQL. Any disk based application can be benefited.

takeda 9y ago
I would strongly discourage this kind of solution. DRBD is to replicate disk. As pointed out is not Postgres specific, and it doesn't understand the data it replicates. One of nightmare of replication is that there's latency and data never makes it instantly to the other host. This means that there is a possibility that once the master fails, the database file on the other host might be in a corrupted state. If your data is important you should use replication mechanisms provided by the database. Repmgr[1] makes it easier to set up replication, and perform failover. It also has mechanisms to do auto failover, but I would also be careful with that. [1] http://repmgr.org/ http://repmgr.org/
- ww520 9y ago
  DRBD's replication is synchronous. That means the block device's write() call on the primary won't return until the data is written onto the standby's disk. It's is as strong a guarantee as you can get. In case of the secondary not available, the data are queued up in the primary and sync up with the secondary when it comes back online. If the primary's disk is destroyed at that time, the failure case and data loss is the same as the database's native replication. DRBD is much simpler and less error prone precisely because it does not care to understand the application data. It just deals with disk block. The simplicity also helps performance greatly. Benchmark has shown there's negligible impact when doing synchronous replication.
  - takeda 9y ago
    I guess that's better, but it would just decrease performance. I still think using binlogs is safer and more efficient though, since it transfers only what's needed to make an update.
    - ww520 9y ago
      Performance is the same if not better than database level replication. The database level replication is synchronous too and have to wait for the standby db to finish writing. If it's async, it has a worse data loss problem. The DRBD replication is fast because it intercepts the write call and then issues the network send and writing to the underlying disk in parallel. Gigabit or 10G network IO are magnitudes faster than disk seek/write. The write to disk on the standby server can happen in parallel as the primary's write to underlying disk.

takeda 9y ago

I would recommend repmgr[1] over solutions like DRBD suggested. The simple reason is that DRBD replicates on block level and doesn't understand postgres data structures, while repmgr relies on mechanisms provided by Postgres. So if there is a failure, you're far less likely to learn that all your data is gone, because some important block on disk was not replicated and the database is not readable. With repmgr the worst what could happen is that you might lost few latest transactions, but your data should always be consistent and in working state. [1] http://repmgr.org/ http://repmgr.org/

ahoka 9y ago
Repmgr is relatively easy to setup and supports automatic failover with repmgrd. Resyncing a failed node is also just a few commands. The only limitation I have encountered was that I had to run it behind pgbouncer so the clients can always connect to the primary instance. I think that will change with 10 as it will support client side discovery of the primary node.

user5994461 9y ago

I think a reasonable option is to use pgbouncer. I wouldn't comment about it because I have long abandonned databases with no failover in favor of solution that works, but people who tried hard enough seem to get pgbouncer to a working point. postgre fans will hate, but if you really need something with automated failover, you might have to take a look at better databases. Either Postgre/Oracle in the paid SQL world, or elasticsearch/cassandra/riak in the free NoSQL world.