pg_plug

text/plain

Download

#!/bin/sh
#-----------------------------------------------------------------------
# pg_plug -- painless upgrade
# PG 7.4 standard -> PG 7.4 with various enhancements
#
# 2003-10-04  mk  new
# 2003-10-14  mk  addpatchinfo
# 2003-11-01  mk  VIEW pg_patchinfo
# 2004-11-04  mk  renamed VIEW to pg_patches;
#                 features selectable via command line (-F)
# 2004-12-14  mk  freeze
#-----------------------------------------------------------------------

ALLFEATURES="22 27"

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

abortscript(){
    echo 1>&2
    echo "$1" 1>&2
    exit 1
}

setconnectable(){
	db=$1
	allow=$2

	$CMD template1 >/dev/null <<EOF
UPDATE pg_database SET datallowconn = $allow WHERE datname = '$db';
EOF
}

freeze(){
	db=$1

	$CMD $db >/dev/null <<EOF
VACUUM FREEZE;
EOF
}

# This does not work for database names with spaces or other funny
# characters.
enumdatabases(){
	echo "SELECT datname FROM pg_database;" \
	| $CMD template1 \
	| sed -e '/1: datname = "/!d' -e 's/.*1: datname = "//' -e 's/".*//'
}

add22indexstat(){
	db=$1

	echo "adding \"pg_indexstat\", ignore error if it already exists"
	$CMD $db >/dev/null <<EOF
CREATE TABLE pg_catalog.pg_indexstat( \
    istindex oid NOT NULL, \
    istcorrel float4 NOT NULL \
) WITHOUT OIDS;
CREATE UNIQUE INDEX pg_indexstat_index_index \
    ON pg_indexstat(istindex);
EOF
}

add27patchinfo(){
	db=$1

	echo "adding \"pg_patchinfo()\", ignore error if it already exists"
	$CMD $db >/dev/null <<EOF
COPY pg_proc FROM stdin WITH OIDS;
2981	pg_patchinfo	11	1	12	f	f	t	t	i	0	2249		show_patchinfo	-	\N
\.
-- copy acl from version()
UPDATE pg_proc SET proacl=(SELECT proacl FROM pg_proc WHERE oid=89) WHERE oid=2981;
CREATE VIEW pg_patches AS SELECT * FROM pg_patchinfo() AS p(name text, version text, base text, descr text);
EOF
}

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

CMDNAME=`basename $0`
BINDIR="bin"
FEATURES=""

while [ "$#" -gt 0 ]
do
	case "$1" in
		--username|-U)
			POSTGRES_SUPERUSERNAME="$2"
			shift;;
		--username=*)
			POSTGRES_SUPERUSERNAME=`echo $1 | sed 's/^--username=//'`
			;;
		-U*)
			POSTGRES_SUPERUSERNAME=`echo $1 | sed 's/^-U//'`
			;;
		--feature|-F)
			FEATURES="$FEATURES $2"
			shift;;
		--feature=*)
			F=`echo $1 | sed 's/^--feature=//'`
			FEATURES="$FEATURES $F"
			;;
		-F*)
			F=`echo $1 | sed 's/^-F//'`
			FEATURES="$FEATURES $F"
			;;
# Data directory. No default, unless the environment
# variable PGDATA is set.
		--pgdata|-D)
			PGDATA="$2"
			shift;;
		--pgdata=*)
			PGDATA=`echo $1 | sed 's/^--pgdata=//'`
			;;
		-D*)
			PGDATA=`echo $1 | sed 's/^-D//'`
			;;

		-*)
			echo "$CMDNAME: invalid option: $1"
			exit 1
			;;

# Non-option argument specifies data directory
		*)
			PGDATA=$1
			;;
	esac
	shift
done

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

if [ -z "$PGDATA" ]
then
  (
    echo "$CMDNAME: no data directory specified"
    echo "You must identify the directory where the data for this database system"
    echo "resides.  Do this with either the invocation option -D or the"
    echo "environment variable PGDATA."
  ) 1>&2
    exit 1
fi


#-----------------------------------------------------------------------
PG_OPT="-O"
PG_OPT="$PG_OPT -c search_path=pg_catalog"
PG_OPT="$PG_OPT -c exit_on_error=true"
#PG_OPT="$PG_OPT -c enable_indexstat=false"
PG_OPT="$PG_OPT -D $PGDATA"
CMD="$BINDIR/postgres $PG_OPT"

: ${FEATURES:=$ALLFEATURES}

# Enable connections to template0
setconnectable template0 true || abortscript "cannot enable connections to template0"

for db in `enumdatabases`
do
	echo converting database $db ...
	for f in $FEATURES
	do
		case $f in
		22)
			add22indexstat $db # || abortscript "cannot convert database $db"
			;;
		27)
			add27patchinfo $db # || abortscript "cannot convert database $db"
			;;
		*)
			abortscript "unknown feature $f"
			;;
		esac
	done

	freeze $db || abortscript "cannot freeze $db"
done

# Re-disable connections to template0
setconnectable template0 false || abortscript "cannot re-disable connections to template0"