#!/bin/sh

# $Id: pgsql74_STATE.sh,v 1.4 2005/03/10 22:44:02 ahammond Exp $
# Author: Andrew G. Hammond     ahammond@ca.afilias.info
#
# Copyright (c) 2005 Afilias Canada Corp. All rights reserved.
#
# Redistribution and use in source and binary forms, with or without
# modification, are permitted provided that the following conditions
# are met:
#
#   * Redistributions of source code must retain the above copyright
# notice, this list of conditions and the following disclaimer.
#   * Redistributions in binary form must reproduce the above copyright
# notice, this list of conditions and the following disclaimer in the
# documentation and/or other materials provided with the distribution.
#   * Neither the name of the Afilias Canada Corp. nor the names of its
# contributors may be used to endorse or promote products derived from
# this software without specific prior written permission.
#
# THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
# "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
# LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
# A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
# OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
# SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED
# TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR
# PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF
# LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING
# NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
# SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

CLUSTER='STATE'
PG_VERSION='pgsql74'
PGPORT='5432'
SLONY_VERSION='slony1'

# You shouldn't have to edit below here for new clusters / installs, once
# the initial configuration is completed.

# Sometimes the postmaster needs to be named something other than postgres.
# This should be avoided where possible, but if you must then...
POSTMASTER_USER='postgres'

HOST=`hostname`

PERL_BIN='/usr/bin/perl'

OLD_LOG_FILES_KEPT=100

# We keep all our binaries in a 'binary base' directory. Binaries are
# compiled with very verbose install dirrectories. For example,
# /opt/binaries/pgsql747_x86_64_SuSE_2_6_5-7_111-smp-2005-02-01 or
# /opt/binaries/pgsql747-slony105-AIX52-2005-05-05
# and then a symlink named $PG_VERSION (pgsql74 or pgsql80 in practice) is
# used to indicate which binary is to be used. This allows minor version
# binary upgrades by simply changing a symlink and restarting databases,
# or performing an HACMP failover. The symlink name is important since
# it's also used for pgdata, WAL file relocation, log files, and a bunch
# of other things.
#
# This script relies on the apache project's rotatelogs lot rotator.
# Log-rotation is now supported by newer versions of postgres, so this
# will probably come out at some point.

BINARY_BASE='/path/to/binaries'
PG_BASE_PATH="$BINARY_BASE/$PG_VERSION"
PG_BIN_PATH="$PG_BASE_PATH/bin"
PG_LIB_PATH="$PG_BASE_PATH/lib"
PG_MAN_PATH="$PG_BASE_PATH/man"
APACHE_BIN_PATH="$BINARY_BASE/apache2/bin"

# If you're going to be running multiple postgres instances, you
# probably also care about performance.
#
# A basic performance configuration is to have $BASE as a seperate
# filesystem. We like RAID 10 with lots of disks if you want to do much
# concurrent stuff. Rotation speed isn't terribly important for this, but 
# number of disks in the strip is critical.
# Smaller disks are better since it removes the temptation to make use of
# "all that spare space", on a filesystem which will usually is IO bound.
# And equally removes the need to explain all that "wasted space".
#
# Next, mount a RAID 1 pair of 15kRPM disks on $PGDATA/WAL
# These will be used for the write-ahead logs. Since they're just sequential
# writes durring normal operation, a pair of fast disks is sufficient.
#
# Finally, and optionally, mount a seperate filesystem on $LOG_PREFIX so the
# logging won't eat up valuable IO on the $PGDATA filesystem. This lets you
# turn up your logging levels with relative impunity when you're trying to
# diagnose an issue in production. A pair of really big disks in RAID1 is
# probably sufficient, if you choose to go this way.

MOUNT_POINT='/mountpoint/for/pgdata'
BASE="$MOUNT_POINT/$CLUSTER"
PGDATA="$BASE/$PG_VERSION"
PGWAL="$BASE/WAL/$PG_VERSION"
LOG_PREFIX="$BASE/log"
PG_LOG_PATH="$LOG_PREFIX/$PG_VERSION"
SLONY_LOG_PATH="$LOG_PREFIX/$SLONY_VERSION"
AUTOVACUUM_LOG_PATH="$LOG_PREFIX/autovacuum_$PG_VERSION"
LD_LIBRARY_PATH="$PG_LIB_PATH:$LD_LIBRARY_PATH"

ALL_PATHS="$PGDATA $LOG_PREFIX $PG_LOG_PATH $SLONY_LOG_PATH $AUTOVACUUM_LOG_PATH $PGWAL"

if [ -e "$PGDATA/postgresql.conf" ]; then
        CONFIG_PGPORT=`$PERL_BIN -lne 'print $1 if /^port\s*=\s*(\d+)\s*$/o' $PGDATA/postgresql.conf`
	if [ x"$CONFIG_PGPORT" != x"$PGPORT" ]; then
		echo "PGPORT variable in init does not match port in postgresql.conf."
		echo "Quitting."
		exit 99
	fi
fi
PGTZ='UTC'

ORIG_PATH="$PATH"
PATH="$PG_BIN_PATH:/usr/bin:/bin"


export PATH PGTZ PGPORT PGDATA LD_LIBRARY_PATH PG_LOG_PATH PG_BIN_PATH PG_LIB_PATH PG_MAN_PATH

test -x "$PG_BIN_PATH/postmaster" || (echo "missing postmaster"; exit 1)
test -x "$PG_BIN_PATH/pg_ctl" || (echo "missing pg_ctl"; exit 1)
test -x "$APACHE_BIN_PATH/rotatelogs" || (echo "missing rotatelogs"; exit 1)

case "$1" in
  start)
        if [ `whoami` != "$POSTMASTER_USER" ]; then 
	        echo "Not currently running as postgres user $POSTMASTER_USER. Quitting."
		exit 99
	fi
        echo "Starting PostgreSQL postmaster"
        ulimit -n 1024
	"$PG_BIN_PATH/pg_ctl" start -D "$PGDATA" < /dev/null 2>&1 | "$APACHE_BIN_PATH/rotatelogs" "$PG_LOG_PATH/pg_%Y-%m-%d_%H:%M:%S.log" 10M &
        ;;
  autovacuum)
        if [ `whoami` != "$POSTMASTER_USER" ]; then 
	        echo "Not currently running as postgres user $POSTMASTER_USER. Quitting."
		exit 99
	fi
	echo "Starting Autovacuum Daemon"
	ulimit -n 1024
	"$PG_BIN_PATH/pg_autovacuum" -H localhost -p $PGPORT -U $POSTMASTER_USER -d 1 -s 800 -S 5 -a 1000 -A 500 -v 10000 -V 2 < /dev/null 2>&1 | \
                "$APACHE_BIN_PATH/rotatelogs" "$AUTOVACUUM_LOG_PATH/pg_autovacuum_%Y-%m-%d_%H:%M:%S.log" 10M &
	;;
  reload)
        if [ `whoami` != "$POSTMASTER_USER" ]; then 
	        echo "Not currently running as postgres user $POSTMASTER_USER. Quitting."
		exit 99
	fi
	echo "Reloading config files"
	"$PG_BIN_PATH/pg_ctl" reload 
	;;
  stop)
        if [ `whoami` != "$POSTMASTER_USER" ]; then 
	        echo "Not currently running as postgres user $POSTMASTER_USER. Quitting."
		exit 99
	fi
        echo "Stopping PostgreSQL postmaster"
        "$PG_BIN_PATH/pg_ctl" -m f stop
        ;;
  env)
	echo "Configuring env vars"
	PATH="$PG_BIN_PATH:$ORIG_PATH"
	MAN_PATH="$PG_MAN_PATH:$MAN_PATH"
	export PATH MAN_PATH
	;;
  logtail)
	echo "Tailing log file"
	(cd "$PG_LOG_PATH"; tail -f `ls -1 pg_*.log | tail -1`)
	;;
  purgelogs)
	for DIR in "$PG_LOG_PATH" "$AUTOVACUUM_LOG_PATH/" "$SLONY_LOG_PATH"; do
		echo "Purging old log files in $DIR"
		pushd "$DIR"
		LOG_COUNT=`ls -1 pg_*.log | wc -l`
		DELETE_COUNT=$(( $LOG_COUNT - $OLD_LOG_FILES_KEPT ))
		if [ $DELETE_COUNT -gt 0 ]; then
			ls -1 pg_*.log | head -$DELETE_COUNT | xargs rm -f -- {}
		fi
		popd
	done
	;;
  mkdir)
        if [ `whoami` != "$POSTMASTER_USER" ]; then 
	        echo "Not currently running as postgres user $POSTMASTER_USER. Quitting."
		exit 99
	fi
        echo "Making dirs..."
        # logging stuff first

        for path in $ALL_PATHS; do 
            if [ ! -d $path ]; then
                echo -n "mkdir $path"
                if mkdir -p $path; then
                    echo " ... created"
                else
                    echo " ... failed"
                fi
            else
                echo "$path already present"
            fi
        done
        ;;

  initdb)
        if [ `whoami` != "$POSTMASTER_USER" ]; then 
	        echo "Not currently running as postgres user $POSTMASTER_USER. Quitting."
		exit 99
    	fi
        if [ -e "$PGDATA/postgresql.conf" ]; then
                echo "$PGDATA/postgresql.conf already exists, skipping initdb"
        else
                INITDB_CMD="\"$PG_BIN_PATH/initdb\" --pgdata \"$PGDATA\" --encoding=SQL_ASCII --locale=C"
                echo "command: $INITDB_CMD"
                echo "-- 5 seconds to abort with CTRL-C"
                sleep 1; echo "-- 4"
                sleep 1; echo "-- 3"
                sleep 1; echo "-- 2"
                sleep 1; echo "-- 1"
                sleep 1; echo "-- starting initdb..."
                if "$PG_BIN_PATH/initdb" --pgdata "$PGDATA" --encoding=SQL_ASCII --locale=C; then
                        echo "You probably need to find an unoccupied port near 5432,"
                        echo "change port=5432 in $PGDATA/postgresql.conf"
                        echo "and make any other configuration changes necessary"
                        echo "before you '$0 start'"
                else
                        echo "That didn't work."
                fi
        fi
        ;;
  movewal)
        if [ `whoami` != "$POSTMASTER_USER" ]; then
            echo "Not currently running as postgres user $POSTMASTER_USER. Quitting."
            exit 99
        fi
        if [ -e "$PGWAL/*" ]; then          # check to make sure new WAL dir is empty
            echo "There are already files in $PGWAL. Quitting."
            exit 99
        fi
        echo "About to try moving $PGDATA/pg_xlog to $PGWAL."
        echo "This will stop the database if it is currently running."
        echo "-- 5 seconds to abort with CTRL-C"
        sleep 1; echo "-- 4"
        sleep 1; echo "-- 3"
        sleep 1; echo "-- 2"
        sleep 1; echo "-- 1"
        echo "-- moving..."
        
        # stop db, just to be sure
        "$PG_BIN_PATH/pg_ctl" -m f stop

        # rename existing WAL
        mv "$PGDATA/pg_xlog" "$PGDATA/old_pg_xlog"
        
        # copy WAL files to new location
        cp "$PGDATA/old_pg_xlog" "$PGWAL"


        # create link
        ln -s "$PGWAL" "$PGDATA/pg_xlog"
        echo "WAL files moved and link created. You should make sure there's still space on the WAL device. Then start the database and check the log."
        ;;
  *)
        echo "Usage: $0 [start|autovacuum|reload|stop|env|mkdir|initdb|movewal]"
        PATH=$ORIG_PATH
        export PATH
        ;;
esac

