[ClusterLabs] Replicated PGSQL woes

[Keisuke MORI]

2016-10-14 2:04 GMT+09:00 Israel Brewster <israel at ravnalaska.net>:
> Summary: Two-node cluster setup with latest pgsql resource agent. Postgresql
> starts initially, but failover never happens.

> Oct 13 08:29:47 CentTest1 pgsql(pgsql_96)[19602]: INFO: Master does not
> exist.
> Oct 13 08:29:47 CentTest1 pgsql(pgsql_96)[19602]: WARNING: My data is
> out-of-date. status=DISCONNECT
> Oct 13 08:29:51 CentTest1 pgsql(pgsql_96)[19730]: INFO: Master does not
> exist.
> Oct 13 08:29:51 CentTest1 pgsql(pgsql_96)[19730]: WARNING: My data is
> out-of-date. status=DISCONNECT
>
> Those last two lines repeat indefinitely, but there is no indication that
> the cluster ever tries to promote centtest1 to master. Even if I completely
> shut down the cluster, and bring it back up only on centtest1, pacemaker
> refuses to start postgresql on centtest1 as a master.

This is because the data on centtest1 is considered "out-of-date"-ed
(as it says :) and and promoting the node to master might corrupt your
database.

>
> What can I do to fix this? What troubleshooting steps can I follow? Thanks.
>

It seems that the latest data should be only on centtest2 so the
recovering steps should be something like:
 - start centtest2 as master
 - take the basebackup from centtest2 to centtest1
 - start centtest1 as slave
 - make sure the replications is working properly

see below for details.
http://clusterlabs.org/wiki/PgSQL_Replicated_Cluster


Also, it would be helpful to check 'pgsql-data-status' and
'pgsql-status' attributes displayed by 'crm_mon -A' to diagnose
whether the replications is going well or not.

The slave node should have the attributes like below, otherwise the
replications is going something wrong and the node will never be
promoted because it does not have the proper data.

```
* Node node2:
    + master-pgsql                      : 100
    + pgsql-data-status                 : STREAMING|SYNC
    + pgsql-status                      : HS:sync
```



-- 
Keisuke MORI