<html><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /></head><body style='font-size: 10pt; font-family: Verdana,Geneva,sans-serif'>
<p>Hello everyone, </p>
<p> I'm stuck with a multi-site cluster configuration and I'd really appreciate some help/pointers. </p>
<p> The scenario: I'd like to build two (whole) clusters in two different DCs, one of them acting as Master and the other as standby/Slave. Both clusters have a proxy, some web nodes and only one db node. I'm not planning to add HA for pgsql inside the clusters, failing over to the other cluster in case something goes south seems to be acceptable. </p>
<p> So I ended up with pcs + booth.</p>
<p> - Currently both clusters start up, the main one starts a master psql service when the ticket is granted, the slave starts a replicating psql. </p>
<p> - When ticket is revoked, master DB is stopped. </p>
<p> - Slave DB is not getting promoted and I understand it doesnt even have a reason to do so. </p>
<p> Basically what I need is the slave to be promoted to master when the ticket is granted to it. </p>
<p><br /></p>
<p> I'm not fully sure that Im not in the wrong, to achieve this I had to use two different pgsql resource configuration on the two clusters.</p>
<p> Master cluster: </p>
<div><span>pcs -f cib.xml resource create pgsql ocf:heartbeat:pgsql \</span></div>
<div><span>pgctl="/usr/lib/postgresql/15/bin/pg_ctl" \</span></div>
<div><span>psql="/usr/bin/psql" \</span></div>
<div><span>pgdata="/var/lib/postgresql/15/main/" \</span></div>
<div><span>node_list="sa_psql" \</span></div>
<div><span>logfile="/var/log/postgresql/postgresql-15-main.log" \</span></div>
<div><span>socketdir="/var/run/postgresql/" \</span></div>
<div><span>op monitor interval="11s" \</span></div>
<div><span>op monitor interval="10s" role="Master" \</span></div>
<div><span>op start timeout="60s" \</span></div>
<div><span>op stop timeout="60s" \</span></div>
<div><span>--group pgsql_group</span></div>
<div><span> </span></div>
<div><span># forcing pgsql to only reside only on the psql node</span></div>
<div><span>pcs -f cib.xml constraint location pgsql prefers la_psql=INFINITY</span></div>
<div><span>pcs -f cib.xml constraint location pgsql prefers la_worker1=-INFINITY</span></div>
<div><span>pcs -f cib.xml constraint location pgsql prefers la_hagw=-INFINITY</span></div>
<div> </div>
<div><span># Fence on ticket loss if we were promoted</span></div>
<div><span>pcs -f cib.xml constraint ticket set pgsql role=Promoted setoptions loss-policy=fence ticket=sqlticket</span></div>
<p><br /></p>
<p> Slave cluster :</p>
<div><span>pcs -f cib.xml resource create pgsql ocf:heartbeat:pgsql \</span></div>
<div><span>pgctl="/usr/lib/postgresql/15/bin/pg_ctl" \</span></div>
<div><span>psql="/usr/bin/psql" \</span></div>
<div><span>pgdata="/var/lib/postgresql/15/main/" \</span></div>
<div><span>node_list="sa_psql" \</span></div>
<div><span>logfile="/var/log/postgresql/postgresql-15-main.log" \</span></div>
<div><span>socketdir="/var/run/postgresql/" \</span></div>
<div><span>restore_command="cp /var/lib/pgsql/pg_archive/%f %p" \</span></div>
<div><span>master_ip="_master_ip_" \</span></div>
<div><span>repuser="repuser" \</span></div>
<div><span>rep_mode="slave" \</span></div>
<div><span>replication_slot_name="replica_1_slot" \</span></div>
<div><span>primary_conninfo_opt="password=***** keepalives_idle=60 keepalives_interval=5 keepalives_count=5" \</span></div>
<div><span>op monitor interval="31s" \</span></div>
<div><span>op monitor interval="30s" role="Promoted" \</span></div>
<div><span>op start timeout="60s" \</span></div>
<div><span>op stop timeout="60s" \</span></div>
<div><span>op promote timeout="120s" \</span></div>
<div><span>--group pgsql_group</span></div>
<div> </div>
<div># Force pgsql to only run on the sql node</div>
<div><span>pcs -f cib.xml constraint location pgsql prefers sa_psql=INFINITY</span></div>
<div><span>pcs -f cib.xml constraint location pgsql prefers sa_worker1=-INFINITY</span></div>
<div><span>pcs -f cib.xml constraint location pgsql prefers sa_hagw=-INFINITY</span></div>
<div> </div>
<div><span># Without this service wouldnt satart</span></div>
<div><span>pcs -f cib.xml constraint ticket set pgsql role=Promoted setoptions loss-policy=demote ticket=sqlticket</span></div>
<div> </div>
<div><span>When configuration is pushed and ticket granted, they start up in m/s straming replication mode. However, debug-promote on slave psql returns with: </span></div>
<div><span>Operation force-promote for pgsql (ocf:heartbeat:pgsql) returned 6 (not configured: Not in a replication mode.)<br /></span></div>
<div> </div>
<div><span>Which is strange, bc:</span></div>
<div><span>is_replication() {<br /> if [ "$OCF_RESKEY_rep_mode" != "none" -a "$OCF_RESKEY_rep_mode" != "slave" ]; then<br /> return 0<br /> fi<br /> return 1<br />}<br /></span></div>
<div> </div>
<div> </div>
<div><span> So I'm pretty much stuck. </span></div>
<div><span> I'm also not sure that booth is a definite must here, sometimes I feel I was better off putting all of them into one bit cluster with an external tie breaker. But now I got sooo much time invested, I'd love to see it through.</span></div>
<div> </div>
<div><span>All help is greatly appreciated!</span></div>
<div> </div>
<div><span>Thank you,</span></div>
<div><span>András</span></div>
<p><br /></p>
<div id="signature">
<div class="pre" style="margin: 0; padding: 0; font-family: monospace">---<br />Olcsó Virtuális szerver:<br /><a href="http://www.ProfiVPS.hu" target="_blank" rel="noopener noreferrer">http://www.ProfiVPS.hu</a><br /><br />Támogatás: <a href="mailto:Support@ProfiVPS.hu">Support@ProfiVPS.hu</a></div>
</div>
</body></html>