Tuesday, December 20, 2011

Postgresql - pgpool online recovery

What's online recovery ?
+ It's available in replication mode.
+ It means attaching a node while still servicing clients.

Prerrequisites
+ A recovery target node must be in detached  state before doing online recovery
+ If the target PostgreSQL server has already started, it's necessary to shut it down.

Stages

  1. CHECKPOINT
  2. First stage of online recovery - custom script
  3. Wait until all clients have disconnected
  4. CHECKPOINT
  5. Second stage of online recovery- custom script
  6. Start up postmaster (perform pgpool_remote_start)
  7. Node attach 
In my opinion, the script for the first stage of online recovery can be left alone, as I'm using repmgr to make things easier to handle.

The only instruction I'll need for attaching the new node will be something like this:
repmgr -D /var/lib/pgsql/9.0 standby clone masterNode
 
There's also the possibility to specify this instruction in the first stage and in the second one 
something like:
 
repmgr -D /var/lib/pgsql/9.0 --force standby clone masterClone 
The first stage will create the equivalent to a base backup, and the second stage will catch
up with the latest changes that clients made during the first stage of recovery.
As with many things, the only way to know for sure the effects (positives and negatives) of
this approach will be doing some tests. 
You can specify a script executed during the first stage. pgpool-II passes three arguments to the script.
  1. The database cluster path of a master node.
  2. The hostname of a recovery target node.
  3. The database cluster path of a recovery target node.
Taking into account the parameters passed by pgpool both scripts could be like the following:
If you wish to add a PostgreSQL server dynamically, add 'backend_hostname' and
its associated parameters and reload pgpool.conf. pgpool-II registers this new
node as a detached node.
 
#!/bin/bash
# first stage recovery 
 # $1 The database cluster path of a master node.
 # $2 The hostname of a recovery target node.
 # $3 The database cluster path of a recovery target node.
  MASTERNODE=10.0.0.113
  cmd1=`ssh $2 "rm -r $3"`
  cmd2=`ssh $2 "/usr/lib/postgresql/9.1/bin/repmgr -D $3  standby clone $MASTERNODE"`
echo $cmd1
echo $cmd2
 
#-------------------------------------------------------------------------------------------------
#!/bin/bash
# second stage recovery 
 # $1 The database cluster path of a master node.
 # $2 The hostname of a recovery target node.
 # $3 The database cluster path of a recovery target node.
  MASTERNODE=10.0.0.113
  cmd1=`ssh $2 "/usr/lib/postgresql/9.1/bin/repmgr -D $3  --force standby clone $MASTERNODE"`
echo $cmd1

#-----------------------------------------------------------------------------------------------

#!/bin/bash
#
# Start PostgreSQL on the recovery target node
#
if [ $# -ne 2 ]
then
echo "pgpool_remote_start remote_host remote_datadir"
exit 1
fi

cmd=`ssh $1 "/usr/lib/postgresql/9.1/bin/pg_ctl start -D $2 2>/dev/null 1>/dev/null < /dev/null &"`
echo $cmd



 

No comments:

Post a Comment