I found an interesting postgres cluster management system using Pacemaker. It is created by GoCardless. They provide a vagrant box, so we can play and learn it easily.
Setup
See Getting started.
Migration test
When you run the vagrant box, three postgres servers and pgbouncer are started.
You can see the status of the cluster by issuing crm_mon -Afr command.
Online: [ pg01 pg02 pg03 ]
Full list of resources:
PgBouncerVIP (ocf::heartbeat:IPaddr2): Started pg01
PostgresqlVIP (ocf::heartbeat:IPaddr2): Started pg03
Master/Slave Set: msPostgresql [Postgresql]
Masters: [ pg03 ]
Slaves: [ pg01 pg02 ]
Node Attributes:
* Node pg01:
+ Postgresql-data-status : STREAMING|SYNC
+ Postgresql-status : HS:sync
+ master-Postgresql : 100
* Node pg02:
+ Postgresql-data-status : STREAMING|POTENTIAL
+ Postgresql-status : HS:potential
+ master-Postgresql : -INFINITY
* Node pg03:
+ Postgresql-data-status : LATEST
+ Postgresql-master-baseline : 0000000002000090
+ Postgresql-status : PRI
+ master-Postgresql : 1000
In this case, pg03 is the primary server, pg01 is a synchronous standby server, and pg02 is a potential server.
If you issue the command migrator.py, pg03 is terminated and pg01 is promoted to the new primary.
$ /vagrant/migrator.py
Online: [ pg01 pg02 pg03 ]
Full list of resources:
PgBouncerVIP (ocf::heartbeat:IPaddr2): Started pg01
PostgresqlVIP (ocf::heartbeat:IPaddr2): Started pg01
Master/Slave Set: msPostgresql [Postgresql]
Masters: [ pg01 ]
Slaves: [ pg02 ]
Stopped: [ pg03 ]
Node Attributes:
* Node pg01:
+ Postgresql-data-status : LATEST
+ Postgresql-master-baseline : 0000000003000090
+ Postgresql-status : PRI
+ master-Postgresql : 1000
* Node pg02:
+ Postgresql-data-status : STREAMING|SYNC
+ Postgresql-status : HS:sync
+ master-Postgresql : 100
* Node pg03:
+ Postgresql-data-status : DISCONNECT
+ Postgresql-status : STOP
+ master-Postgresql : -INFINITY
Impression
This is one simple example. It is not surprising that this alone. However, they have managed their system perfectly using pacemaker. See this presentation. I think that they have huge experience and a very excellent toolset.
As well known, there is no de-facto standard of the postgres cluster management system yet. For example, repmgr cannot manage VIP (Virtual IP), i.e., users should always know which is the primary server. I think that they operate very well using Pacemaker.
However, I have a great concern about Pacemaker. In short, the resource agent of PostgreSQL is too complicated (over 1500 lines); therefore, nobody can maintain it well. GoCardless uses a very old version of the resource agent, but it will not be able to use in the near future because the recovery method will be changed in version 10. Thus, they have to update the resource agent. I don’t know whether they can do it well.