#!/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 " rpgctl $@ ;; removedb) rpgctl stop rm -vr $DB_PATH ;; bench) rpgbench ;; clone) [[ "$1" ]] || rerror "$0 $action " rrepmgr_clone standby clone $1 ;; forceclone) [[ "$1" ]] || rerror "$0 $action " rrepmgr_clone --force standby clone $1 ;; configure) install -d ${DB_LOG} install -d ${DB_CONF} configure_repmgr ;; standby) [[ "$1" ]] || rerror "$0 $action " rrepmgr standby $@ ;; master) [[ "$1" ]] || rerror "$0 $action " rrepmgr master $@ ;; cluster) [[ "$1" ]] || rerror "$0 $action " 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 " ;; esac } run $@