pgsql resource fails to start with error 'My data may be inconsistent. You have to remove <path>/PGSQL.lock file to force start' in a RHEL High Availability cluster with Pacemaker

Environment

• Red Hat Enterprise Linux (RHEL) 6, 7, 8, 9 with the High Availability Add-On
• pacemaker
• One or more pgsql resources managed by the cluster which is configured as master/slave or promotable

Issue

• Postgres down on Slave in our cluster

• Our pgsql resource is failing to start after a node rebooted

• pgsql resource failing with "My data may be inconsistent. you have to remove /PGSQL.lock file to force start."

• pcs status shows an error from my pgsql resource referencing a lock file

     Failed Actions:
     * postgresql_start_0 on node2.example.com 'unknown error' (1): call=16, status=complete, exitreason='My data may be inconsistent. You have to remove /var/opt/rh/rh-postgresql94/lib/pgsql/tmp/PGSQL.lock file to force start.', last-rc-change='Tue Feb 14 23:21:14 2017', queued=0ms, exec=194ms
  

Resolution

NOTE: The resource-agent pgsql does not allow for automatic recovery of the postgresql resource when the resource is stopped or demoted when the node is master or promoted for the resource-agent. When the pgsql resource-agent is stopped or demoted then manually intervention will always be required for the resource to start again on that cluster node.

If you want to have automatic recovery (when the resource is stopped or demoted successfully) of a postgresql resource that is configured as master/slave or promotable resource-agent then use the resource-agent pgsqlms that is provided by the package resource-agents-paf on RHEL 8.5 or later.

Do all of the following to allow the resource to successfully start the pgsql resource-agent:

• Put the node where the resource is failing to start into standby mode with pcs cluster standby from that node.

     # pcs cluster standby <node name>
  

• On the node where the resource is failing, if any resources need to be activated in order to inspect the pgsql data/contents, make those resources available with pcs resource debug-start. NOTE: This operation functions outside the control of the cluster resource management, and should be used with caution. Please contact Red Hat Support for assistance if you are not familiar or comfortable with the implications of this operation.

• Review the contents of the database on this host and determine if it is acceptable to start the database again (and possibly let it replicate from the master, if that type of configuration is in use). If so, remove the lock file described in the error message that was presented earlier.

• Then unstandby the cluster node.

     # pcs cluster unstandby <node name>
  

• Clean up the resource failures for that cluster node. The resource should successfully start because the the lock file removed.

     # pcs cluster resource cleanup
  

Root Cause

When a pgsql resource promotes a cluster node to be master or promoted then a lock file is created <path>/PGSQL.lock. This file requires manual intervention by the user to remove the file. If this file exists when the resource is started, then the resource-agent pgsql will to start.

When a pgsql resource is running and the host that is master or promoted then when that resource is stopped or demoted then it will require manually intervention in order to start the resource again. By design, the pgsql does not remove the <path>/PGSQL.lock file when the pgsql resource is stopped or demoted and requires the user to remove that file. This is intentional, to prevent the database from being in an inconsistent state that the administrator would have to determine if the node should be allowed to return to service.

Diagnostic Steps

• Check the /var/log/messages file for the error message: My data may be inconsistent. you have to remove <path>/PGSQL.lock file to force start..

This solution is part of Red Hat’s fast-track publication program, providing a huge library of solutions that Red Hat engineers have created while supporting our customers. To give you the knowledge you need the instant it becomes available, these articles may be presented in a raw and unedited form.