Wednesday, December 21, 2011

Postgresql - streaming replication, trigger file vs. repmgr 'promote' command

The trigger file is a file  that get's created ( it's up to us when) when you want a failover to happen (i.e. to make the streaming replication stopped), hence, the slave that 'discovers' the trigger file becomes a master or independent node.


Such file location is specified in the recovery.conf configuration file.

# Specifies a trigger file whose presence should cause streaming replication to
# end (i.e., failover).
trigger_file = '/path_to/trigger'

I suppose the server continuously monitors for the presence of such file.

In the example I'm following both master and slave run on the same machine on different ports, so

the failover script creates the trigger file locally, so as I'm running the master on one machine and the slave on another, I'll need to create the trigger file remotely to one of the slaves.

One question remains: ¿how to tell repmgr where to look for the trigger file?

Well, it turns out to not being necessary as a "promote" command is available in repmr."Such command is designed to be more efficient"



http://groups.google.com/group/repmgr/browse_thread/thread/cdb463bc399e1842?pli=1



    On Fri, Aug 12, 2011 at 11:35 PM, Joshua Sierles ...@gmail.com> wrote: > Is there an advantage to using repmgr's 'promote' command on the slave since it requires a restart? What's the difference from using the 'trigger file' method?
    repmgr follow is designed to be fast. That requires you to use repmgr
     promote to make that work correctly.
     The trigger file works, but will cause problems when running multiple standbys.
    

    --
     Simon Riggs                   http://www.2ndQuadrant.com/
     PostgreSQL Development, 24x7 Support, Training & Services



Other references:

http://pgpool.projects.postgresql.org/contrib_docs/simple_sr_setting/ 
http://wiki.postgresql.org/wiki/Streaming_Replication

The gotcha in my implementation is that I'm using repmgr to abstract the streaming replication  configuration , in my opinion this reduces administration complexity but the tradeoff is a more elaborated initial deployment.


So, the failover script reduces itself to:

#!/bin/sh
# Execute command by failover.
# special values:  %d = node id
#                  %h = host name
#                  %p = port number
#                  %D = database cluster path
#                  %m = new master node id
#                  %M = old master node id
#                  %H = new master node host name
#                  %P = old primary node id
#                  %% = '%' character
failed_node_id=$1
failed_host_name=$2
failed_port=$3
failed_db_cluster=$4
new_master_id=$5
old_master_id=$6
new_master_host_name=$7
old_primary_node_id=$8

standby_node=10.0.0.201

cmd=`ssh $standby_node "/var/lib/postgresql/repmgr -f /var/lib/postgresql/repmgr/repmgr.conf standby promote"`
if [ $failed_node_id = $old_primary_node_id ];then      # master failed
# the standby server is promoted
echo $cmd
fi


Now it's time to kill the master.

No comments:

Post a Comment