<tt><font size=2>Hi,</font></tt>
<br>
<br>
<br><tt><font size=2>>> On Thu, May 19, 2011 at 5:05 AM, Eamon Roque
<Eamon.Roque@lex-com.net>wrote:<br>
<br>
>> Hi,<br>
>><br>
>> I've put together a cluster of two nodes running a databank without
shared<br>
>> storage. Both nodes replicate data between them, which is taken
care of by<br>
>> the databank itself.<br>
>><br>
>> I have a resource for the databank and ip. I then created a stateful
clone<br>
>> from the databank resource. I created colocation rules joining
the<br>
>> databank-ms-clone and ip:<br>
>><br>
>> node pgsqltest1<br>
>> node pgsqltest2<br>
>> primitive Postgres-IP ocf:heartbeat:IPaddr2 \<br>
>> params ip="10.19.57.234"
cidr_netmask="32" \<br>
>> op monitor interval="30s"
\<br>
>> meta is-managed="false"<br>
>> primitive resPostgres ocf:heartbeat:pgsql \<br>
>> params pgctl="/opt/PostgreSQL/9.0/bin/pg_ctl"<br>
>>pgdata="/opt/PostgreSQL/9.0/data" psql="/opt/PostgreSQL/9.0/bin/psql"<br>
>> pgdba="postgres" \<br>
>> op monitor interval="1min"
\<br>
>> meta is-managed="false"<br>
>> ms msPostgres resPostgres \<br>
>> meta master-max="1" master-node-max="1"
clone-max="2"<br>
>> clone-node-max="1" notify="true" target-role="started"<br>
>> colocation colPostgres inf: Postgres-IP msPostgres:Master<br>
>> order ordPostgres inf: msPostgres:promote Postgres-IP:start<br>
>> property $id="cib-bootstrap-options" \<br>
>> dc-version="1.1.2-2e096a41a5f9e184a1c1537c82c6da1093698eb5"
\<br>
>> cluster-infrastructure="openais"
\<br>
>> expected-quorum-votes="2"
\<br>
>> stonith-enabled="false" \<br>
>> no-quorum-policy="ignore"
\<br>
>> last-lrm-refresh="1302707146"<br>
>> rsc_defaults $id="rsc-options" \<br>
>> resource-stickiness="200"<br>
>> op_defaults $id="op_defaults-options" \<br>
>> record-pending="false"<br>
>><br>
>> The normal postgres agent doesn't support this functionality,
but I've put<br>
>> together my own using the mysql agent as a model. Before running
the script<br>
>> through ocf-tester, I unmanage the postgres resource.<br>
>><br>
<br>
> Could you show how you implemented promote/demote for pgsql?<br>
</font></tt>
<br><tt><font size=2>Sure, let's start with the ultra-simple "promote"
function:</font></tt>
<br>
<br><tt><font size=2>#</font></tt>
<br><tt><font size=2># These variables are higher up in the file, but they
will probably help with understanding the error of </font></tt>
<br><tt><font size=2># my ways.</font></tt>
<br>
<br><tt><font size=2>CRM_MASTER="${HA_SBIN_DIR}/crm_master"</font></tt>
<br><tt><font size=2>ATTRD_UPDATER="${HA_SBIN_DIR}/attrd_updater"</font></tt>
<br>
<br><tt><font size=2>pgsql_promote() {</font></tt>
<br><tt><font size=2> local output</font></tt>
<br><tt><font size=2> local rc</font></tt>
<br><tt><font size=2> local CHECK_PG_SQL</font></tt>
<br><tt><font size=2> local COMPLETE_STANDBY_QUERY</font></tt>
<br><tt><font size=2> local PROMOTE_SCORE_HIGH</font></tt>
<br><tt><font size=2> local MOD_PSQL_M_FORMAT</font></tt>
<br>
<br>
<br><tt><font size=2> PROMOTE_SCORE_HIGH=1000
</font></tt>
<br><tt><font size=2> CHECK_PG_SQL="SELECT
pg_is_in_recovery()"</font></tt>
<br><tt><font size=2> MOD_PSQL_M_FORMAT="$OCF_RESKEY_psql
-Atc"</font></tt>
<br><tt><font size=2> COMPLETE_STANDBY_QUERY="$MOD_PSQL_M_FORMAT
\"$CHECK_PG_SQL\""</font></tt>
<br>
<br><tt><font size=2> output=$(su -
$OCF_RESKEY_pgdba -c "$COMPLETE_STANDBY_QUERY" 2>&1)</font></tt>
<br><tt><font size=2> echo $output</font></tt>
<br><tt><font size=2> </font></tt>
<br><tt><font size=2> rc=$?</font></tt>
<br><tt><font size=2> </font></tt>
<br><tt><font size=2> case $output in</font></tt>
<br><tt><font size=2>
f)</font></tt>
<br><tt><font size=2>
ocf_log debug
"PostgreSQL Node is running in Master mode..."</font></tt>
<br><tt><font size=2>
return $OCF_RUNNING_MASTER</font></tt>
<br><tt><font size=2>
;;</font></tt>
<br><tt><font size=2> </font></tt>
<br><tt><font size=2>
t)</font></tt>
<br><tt><font size=2>
ocf_log debug
"PostgreSQL Node is in Hot_Standby mode..."</font></tt>
<br><tt><font size=2>
return $OCF_SUCCESS</font></tt>
<br><tt><font size=2>
;;</font></tt>
<br>
<br><tt><font size=2>
*)</font></tt>
<br><tt><font size=2>
ocf_log err "Critical
error in $CHECK_PG_SQL: $output"</font></tt>
<br><tt><font size=2>
return $OCF_ERR_GENERIC
</font></tt>
<br><tt><font size=2>
;;</font></tt>
<br><tt><font size=2> esac</font></tt>
<br>
<br><tt><font size=2>#</font></tt>
<br><tt><font size=2># "Real" promotion is handled here.</font></tt>
<br><tt><font size=2># The trigger file is created and we check for "recovery.conf"
on the host.</font></tt>
<br><tt><font size=2># If we can't find it, then the file will be copied
from the HA-Config into postgres' data folder.</font></tt>
<br><tt><font size=2>#</font></tt>
<br>
<br><tt><font size=2>if ! touch $OCF_RESKEY_trigger_file; then</font></tt>
<br><tt><font size=2> ocf_log err "$OCF_RESKEY_trigger_file
could not be created!"</font></tt>
<br><tt><font size=2> return $OCF_ERR_GENERIC</font></tt>
<br><tt><font size=2>fi</font></tt>
<br>
<br><tt><font size=2>if [ ! -f $OCF_RESKEY_recovery_conf ]; then</font></tt>
<br><tt><font size=2> ocf_log err "$OCF_RESKEY_recovery_conf
doesn't exist!"</font></tt>
<br><tt><font size=2> cp $OCF_RESKEY_recovery_conf_ersatz
$OCF_RESKEY_pgdata</font></tt>
<br><tt><font size=2> return $OCF_SUCCESS</font></tt>
<br><tt><font size=2>fi</font></tt>
<br>
<br><tt><font size=2># If both file exist or can be created, then the failover
fun can start.</font></tt>
<br>
<br><tt><font size=2>ocf_log info "$OCF_RESKEY_trigger_file was created."</font></tt>
<br><tt><font size=2>ocf_log info "$OCF_RESKEY_recovery_conf exists
and can be copied to the correct location."</font></tt>
<br>
<br><tt><font size=2># Sometimes, the master needs a bit of time to take
the reins. So...</font></tt>
<br>
<br><tt><font size=2>while :</font></tt>
<br><tt><font size=2>do</font></tt>
<br><tt><font size=2> pgsql_monitor
warn</font></tt>
<br><tt><font size=2> rc=$?</font></tt>
<br>
<br><tt><font size=2> if [ $rc -eq $OCF_RUNNING_MASTER
]; then</font></tt>
<br><tt><font size=2>
break;</font></tt>
<br><tt><font size=2> fi</font></tt>
<br>
<br><tt><font size=2> ocf_log debug
"Postgres Server could not be promoted. Please wait..."</font></tt>
<br><tt><font size=2> </font></tt>
<br><tt><font size=2> sleep 1</font></tt>
<br>
<br><tt><font size=2>done</font></tt>
<br>
<br><tt><font size=2>ocf_log info "Postgres Server has been promoted.
Please check on the previous master."</font></tt>
<br>
<br><tt><font size=2>#################################</font></tt>
<br><tt><font size=2>#Attributes Update:
#</font></tt>
<br><tt><font size=2>#################################</font></tt>
<br>
<br><tt><font size=2>$ATTRD_UPDATER -n $PGSQL_STATUS_NAME -v \"PRI\"
|| exit $(echo "Eh! Attrd_updater is not working!")</font></tt>
<br>
<br><tt><font size=2>#############################################</font></tt>
<br><tt><font size=2># Resource stickiness pumped up to 1000 : #</font></tt>
<br><tt><font size=2>#############################################</font></tt>
<br>
<br><tt><font size=2>$CRM_MASTER -v $PROMOTE_WERT_HOCH || exit $(echo "crm_master
could not change the Master's status!")</font></tt>
<br>
<br><tt><font size=2>############</font></tt>
<br><tt><font size=2># Success! #</font></tt>
<br><tt><font size=2>############
</font></tt>
<br>
<br><tt><font size=2>return $OCF_SUCCESS</font></tt>
<br>
<br><tt><font size=2>}</font></tt>
<br>
<br><tt><font size=2>######################################################################################################</font></tt>
<br>
<br><tt><font size=2>Thanks!</font></tt>
<br>
<br><tt><font size=2>Éamon<br>
<br>
<br>
>> Unfortunately, promote/demote doesn't work. ocf-tester tries to
use the<br>
>> "crm_attribute -N pgsql1 -n master-pgrql-replication-agent
-l reboot -v<br>
>> 100", but the (unmanaged) resources don't accept the score
change.<br>
>><br>
>> I'm pretty sure that I just need to be hit with a clue stick and
would be<br>
>> grateful for any help.<br>
>><br>
>> Thanks,<br>
>><br>
>> ?amon<br>
>><br>
<br>
<br>
<br>
-- <br>
Serge Dubrouski.</font></tt>