home | start  
contact |

Postgresql Repmgr Script

:!:preview:!:

  • db setup clone and replication work
  • status view doesnt
  • loads of dead ends

FIXME

  • ipranges bugy and useless
pgnode.sh
#!/bin/bash
 
# env
PGBIN="/usr/pgsql-9.1/bin"
PGCTL="${PGBIN}/pg_ctl"
INITDB="${PGBIN}/initdb"
PGBENCH="${PGBIN}/pgbench -i -s 1"
PSQL="psql -a -h /tmp"
 
REPMGR_USER="repmgr"
REPMGR_DB="pgbench"
REPMGR="${PGBIN}/repmgr"
REPMGRD="${PGBIN}/repmgrd"
 
#pgnode config
USER="alice"  #owner of the postgres process
PREFIX="/home/alice/pg/env"
NODE_CONF="/home/alice/pg/pgnode.conf"
 
#cluster config
CLUSTER_NAME=sirius
CLUSTER_PORT=5443
CLUSTER_IPRANGE="192.168.1.0/24"
CLUSTER_IPRANGE2="192.168.2.0/24" #FIXME, muesste mit einer range gehen
CLUSTER_IPRANGE3="10.10.1.0/24"
CLUSTER_IPRANGE4="10.10.2.0/24"
 
#cluster locations
DB_PATH="${PREFIX}/var/lib/pgsql/${CLUSTER_NAME}/data"
DB_LOG="${PREFIX}/var/lib/pgsql/${CLUSTER_NAME}/log"
DB_CONF="${PREFIX}/var/lib/pgsql/${CLUSTER_NAME}/repmgr"
 
  #ui
  rerror(){ echo $@; exit 1; }
  rinfo(){ echo -ne "\033[1;30m>\033[0;33m>\033[1;33m> \033[0m${@}\n" >&2;}
 
  #commands
  rpsql(){ $PSQL -p $CLUSTER_PORT -U $USER $REPMGR_DB -c "$1"; }
  rpsql_admin(){ $PSQL -p $CLUSTER_PORT -U $USER template1 -c "$1"; }
  rpgctl(){ $PGCTL -D ${DB_PATH} $1 >> $DB_LOG/pg.log 2>&1 & }
  rpgctl_cmd(){ $PGCTL -D ${DB_PATH} $1; }
  rpgbench(){ $PGBENCH $REPMGR_DB -h /tmp -p ${CLUSTER_PORT}; }
 rrepmgrd(){ PGENGINE=$PGBIN PATH="$PATH:$PGENGINE" $REPMGRD -f ${DB_CONF}/$db.repmgr.conf --monitoring-history --verbose >> ${DB_LOG}/repmgr.log 2>&1 & }
  rrepmgrd_log(){ tail -f ${DB_LOG}/*; }
  rrepmgrd_stop(){ pkill repmgrd; }
  rrepmgr(){ PGENGINE=$PGBIN PATH="$PATH:$PGENGINE" $REPMGR -f ${DB_CONF}/$db.repmgr.conf --verbose $@; }
  rrepmgr_asrepmgr(){ PGENGINE=$PGBIN PATH="$PATH:$PGENGINE" $REPMGR -f ${DB_CONF}/$db.repmgr.conf -U ${REPMGR_USER} --verbose $@; }
  rrepmgr_clone(){ PGENGINE=$PGBIN PATH="$PATH:$PGENGINE" $REPMGR -D ${DB_PATH} -d ${REPMGR_DB} -p ${CLUSTER_PORT} -U ${REPMGR_USER} -R ${USER} --verbose $@; }
 
  ##config (#TODO: use templates)
  configure_node( ) {
    [ "$db" ] || db=$(hostname)
    rinfo "configure nodename $db"
    ( cat <<-NEWNODE
db=$db
NEWNODE
    ) > $NODE_CONF; }
 
  configure_cluster( ) {
    rinfo "configure cluster ${CLUSTER_NAME} (port: ${CLUSTER_PORT})"
    ( cat <<-NEWPORT
listen_addresses='*'
wal_level = 'hot_standby'
archive_mode = on
archive_command = 'exit 0' # Managed by repmgr
max_wal_senders = 10
wal_keep_segments = 640     # 10 GB required on pg_xlog
#wal_keep_segments = 5000     # 80 GB required on pg_xlog
hot_standby = on
unix_socket_directory = '/tmp'
log_directory = 'pg_log'
external_pid_file = 'postgres.pid'
port=$CLUSTER_PORT
NEWPORT
    ) > ${DB_PATH}/postgresql.conf;                                                                                                              
    rinfo "configure permissions ${REPMGR_USER}@${CLUSTER_IPRANGE}"
    rinfo "configure permissions ${USER}@${CLUSTER_IPRANGE}"
    ( cat <<-DB1
local   all     $USER                                ident
host    all     $USER        127.0.0.1/32            ident
host    all     $USER        $CLUSTER_IPRANGE        trust
host    all     $USER        $CLUSTER_IPRANGE2        trust
host    all     $USER        $CLUSTER_IPRANGE3        trust
host    all     $USER        $CLUSTER_IPRANGE4        trust
host    all     $USER        ::1/128                 ident
host    ${REPMGR_DB}     ${REPMGR_USER}        127.0.0.1/32            trust
host    ${REPMGR_DB}     ${REPMGR_USER}        $CLUSTER_IPRANGE        trust
host    ${REPMGR_DB}     ${REPMGR_USER}        $CLUSTER_IPRANGE2        trust
host    ${REPMGR_DB}     ${REPMGR_USER}        $CLUSTER_IPRANGE3        trust
host    ${REPMGR_DB}     ${REPMGR_USER}        $CLUSTER_IPRANGE4        trust
host    ${REPMGR_DB}     ${REPMGR_USER}        ::1/128                 trust
host    replication     ${REPMGR_USER}        127.0.0.1/32            trust
host    replication     ${REPMGR_USER}        $CLUSTER_IPRANGE        trust
host    replication     ${REPMGR_USER}        $CLUSTER_IPRANGE2        trust
host    replication     ${REPMGR_USER}        $CLUSTER_IPRANGE3        trust
host    replication     ${REPMGR_USER}        $CLUSTER_IPRANGE4        trust
host    replication     ${REPMGR_USER}        ::1/128                 trust
DB1
    )  > ${DB_PATH}/pg_hba.conf; }
 
  configure_repmgr( ) {
    rinfo "configure replication ${DB_CONF}/$db.repmgr.conf"
    rinfo "conninfo='host=$db user=${REPMGR_USER} dbname=pgbench port=${CLUSTER_PORT}'"
    ( cat <<-RMC
cluster=${CLUSTER_NAME}
node=$db
node_name=$db
conninfo='host=$db user=${REPMGR_USER} dbname=pgbench port=${CLUSTER_PORT}'
master_response_timeout=60
reconnect_attempts=6
reconnect_interval=10
failover=automatic
priority=-1
promote_command='$REPMGR standby promote -f ${REPMGR_CONF}/$db.repmgr.conf'
follow_command='$REPMGR standby follow -f ${REPMGR_CONF}/$db.repmgr.conf -W'
RMC
    ) > ${DB_CONF}/$db.repmgr.conf; }
 
# master script                                                                                                                                  
 
  [ -f $NODE_CONF ] && source $NODE_CONF
  [ "$db" ] || configure_node
 
  rinfo "pgnode $db"
  rinfo "cluster: **${CLUSTER_NAME}** (${REPMGR_USER}@${CLUSTER_IPRANGE}:${CLUSTER_PORT})"
  rinfo "db: $DB_PATH"
 
run( ) {
 
  action=$1
  shift
 
  case $action in
    createdb)
      $INITDB ${DB_PATH}/
      install -d ${DB_LOG} ;;
    configuredb)
      configure_cluster ;;
    initdb)
      rpsql_admin "create database $REPMGR_DB"
      rpsql_admin "create role ${REPMGR_USER} login superuser"
      rpsql_admin "create role ${USER} login superuser" ;;
    db)
      [[ "$1"  ]] || rerror "$0 $action <cmd>"
      rpgctl $@ ;;
    removedb)
      rpgctl stop
      rm -vr $DB_PATH ;;
 
    bench) rpgbench ;;
 
    clone)
      [[ "$1"  ]] || rerror "$0 $action <node>"
      rrepmgr_clone standby clone $1 ;;
    forceclone)
      [[ "$1"  ]] || rerror "$0 $action <node>"
      rrepmgr_clone --force standby clone $1 ;;
 
    configure)
      install -d ${DB_LOG}
      install -d ${DB_CONF}
      configure_repmgr ;;
 
    standby)
        [[ "$1"  ]] || rerror "$0 $action <register,clone,follow,promote>"
      rrepmgr standby $@ ;;
    master)
        [[ "$1"  ]] || rerror "$0 $action <register>"
      rrepmgr master $@ ;;
    cluster)
        [[ "$1"  ]] || rerror "$0 $action <show,cleanup>"
      rrepmgr master $@ ;;
 
    start)
      rrepmgrd ;;
    stop)
      rrepmgrd_stop ;;
    monitor)
      rrepmgrd_log ;;
    status)
      #rpsql "select * from repmgr_${CLUSTER_NAME}.repl_monitor"
      rpsql "select * from repmgr_${CLUSTER_NAME}.repl_nodes"
      rpsql "select * from repmgr_${CLUSTER_NAME}.repl_status" ;;
    cleanup)
      rrepmgr cluster cleanup -k 1 ;;
    *)
      echo "$0 <createdb,configouredb,removedb,db,init,status,bench>" ;;
  esac
}
 
run $@
 

Log In