Hi hackers, We are developing and maintaining a Pacemaker resource agent for PostgreSQL here. This agent is "stateful", ie. it knows where is the master and where are the slaves. See [1]. Now that this resource agent version is close to what we wanted to achieve, we will make some official announce soon, with details & stuff. We welcome feedback, help issues, etc, but on github please, not in this thread.
For the next version, we are now considering to improve the switchover mechanism with appropriate checks for every steps. For reminder, the switchover in PostgreSQL is possible since the following commit: commit 985bd7d49726c9f178558491d31a570d47340459 Author: Fujii Masao <fu...@postgresql.org> Date: Wed Jun 26 02:14:37 2013 +0900 It requires: (1) shutdown the master first (2) make sure the slave received the shutdown checkpoint from the old master (3) promote the slave as master (4) start the old master as slave The problem here is step (2). After discussing IRL with Magnus and Heikki, they confirmed me checking this using pg_xlogdump is fine, eg. (reformated): $ pg_xlogdump 000000010000000B00000014 -s 'B/14D845F8' -n1 rmgr: XLOG len (rec/tot): 72/104, tx: 0, lsn: B/14D845F8, prev B/14D84590, bkp: 0000, desc: checkpoint: redo B/14D845F8; tli 1; prev tli 1; fpw true; xid 0/6422462; oid 1183646; multi 1; offset 0; oldest xid 712 in DB 1; oldest multi 1 in DB 1; oldest running xid 0; shutdown This is possible from the resource agent point of view, but not really in a clean way. It requires: * to keep in memory the last LSN of the master after shutdown * check on the slave this LSN has been received * check the record is a rmgr XLOG with a shutdown information as payload * check this is the very last WAL record received (nothing after). First, looking at the last LSN and creating a cluster attribute (in Pacemaker context) from the old master to share it with slaves is possible, but not really elegant for a resource agent. Then, the -n1 in sample command here avoid pg_xlogdump to exit with an error and a rc=1. But it is not compatible with the last check (very last WAL record) and I need to check the command succeed. A best solution here would be to be able to check from a view on the slave, say pg_stat_standby, when it was connected to the master for the last time, the last wal restored by log shipping, last LSN received by streaming rep, flushed, how/why the SR has been disconnected. As instance, reasons for SR disconnection might be: master shutdown, too much lag, connection reset. I can try to give a try to such patch after some acceptance and discussing what exactly we should push in such view. Comments? Guidance? Thoughts? Other solutions? Thanks! Regards, [1] https://github.com/dalibo/pgsql-resource-agent/tree/master/multistate -- Jehan-Guillaume de Rorthais Dalibo http://www.dalibo.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers