Pacemaker/Corosync/PostgreSQL Issue

Pacemaker/Corosync/PostgreSQL Issue

The moment I sat on my desk, there were tons of tickets complaining that the database were down. It was 2 node PostgreSQL database HA cluster running the following stack.

  • RedHat Linux 6.x
  • Pacemaker/Corosync
  • PostgreSQL 9.2.x
  • Master-Slave synchronous streaming replication between the 2 PostgreSQL nodes

I ran the crm_mon command immediately and found out that there was something weird with the PostgreSQL. The Pacemaker/Corosync HA wasn’t working as intended. The PostgreSQL database was down. Both nodes are online but only node1 was the slave but PostgreSQL wasn’t running on node2. I started going through the logs.

On Node1

Sep 21 04:10:19 node1 postgres[32318]: [5-1] FATAL: could not connect to the primary server: could not connect to server: No route to host
Sep 21 04:10:19 node1 postgres[32318]: [5-2] #011#011Is the server running on host "<IP address>" and accepting
Sep 21 04:10:19 node1 postgres[32318]: [5-3] #011#011TCP/IP connections on port 5432?
Sep 21 04:10:19 node1 postgres[32318]: [5-4] #011
Sep 21 04:10:22 node1 lrmd[3185]: notice: operation_finished: pgsql_monitor_7000:32319 [ 2015/09/21_04:10:22 INFO: Master does not exist. ]
Sep 21 04:10:22 node1 lrmd[3185]: notice: operation_finished: pgsql_monitor_7000:32319 [ 2015/09/21_04:10:22 WARNING: My data is out-of-date. sta
tus=DISCONNECT ]

On Node2

Sep 21 15:28:41 node2 pengine[3194]: notice: LogActions: Start pgsql:1#011(node2)
Sep 21 15:28:41 node2 pengine[3194]: notice: process_pe_message: Calculated Transition 1120: /var/lib/pacemaker/pengine/pe-input-234.bz2
Sep 21 15:28:42 node2 postgres[61043]: [1-1] LOG: database system was shut down in recovery at 2015-09-19 23:36:56 SGT
Sep 21 15:28:42 node2 postgres[61044]: [1-1] FATAL: the database system is starting up
Sep 21 15:28:42 node2 postgres[61043]: [2-1] LOG: entering standby mode
Sep 21 15:28:42 node2 postgres[61043]: [3-1] LOG: could not read from log file 59, segment 114, offset 0: No such file or directory
Sep 21 15:28:42 node2 postgres[61043]: [4-1] LOG: invalid primary checkpoint record
Sep 21 15:28:42 node2 postgres[61043]: [5-1] LOG: could not read from log file 59, segment 114, offset 0: No such file or directory
Sep 21 15:28:42 node2 postgres[61043]: [6-1] LOG: invalid secondary checkpoint record
Sep 21 15:28:42 node2 postgres[61043]: [7-1] PANIC: could not locate a valid checkpoint record

Explanation

  • Node1 thinks that it is the slave and hence was trying to connect to the master database on node2
  • Node1 wasn’t able to connect to the IP address because that IP address was managed by Pacemaker/Corosync and since PostgreSQL was down, the “IP address” resource won’t be running!
  • Node2 couldn’t start the database because it was corrupted

What I need to do now is to promote the non-corrupted database(node1) to master and re-setup the slave on node2!

Resolution

Execute on node2

Stop the pacemaker/corosync services on node2

root#crm node standby node2
root#"/etc/init.d/pacemaker stop
root#/etc/init.d/cman stop

Execute on node1

Promote node1 to master in both pacemaker/corosync and database level. You don’t have to reboot the server but I just did it because I wanted a “clean” state in node1

root#"crm resource migrate master-group node1
postgres#pg_ctl promote -D $PGDATA
root# init 6

Execute on node2

Re-setup node2 as slave by seeding it from node1. Start pacemaker/corosync services on node2 once the database is re-seeded

root# rm -fr $PGDATA
postgres#pg_basebackup -D /var/lib/pgsql/9.2/data -h 192.168.17.221 --xlog -P -v
root#crm node online node2
root#"/etc/init.d/pacemaker stop
root#/etc/init.d/cman stop
root# crm node online node2

Cheers,
Wei Shan

Advertisements

2 thoughts on “Pacemaker/Corosync/PostgreSQL Issue”

  1. Wei Shan,

    Hello and thanks for you post. May I know if you’ve had database failures in which the failover worked as you expected?

    Also, have you upgraded to PostgreSQL 9.3 or Pacemaker 1.1.11+ yet? The streaming replication is very slightly different and I wonder if you’ve made it work.

    Thanks very much,
    Mike Diehn

    1. Hi Mike,

      Yes, the usual database failover works correctly. Hardware failure and etc.. But because we only have 2 nodes in the Pacemaker/Corosync, it is possible to have split brain issue.

      Not yet. I’m planning to rip out pacemaker/corosync and replace it with repmgr with PostgreSQL 9.5 before June 2016 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s