On Tue, 03 May 2005 02:45:09 -0400, Tom Lane <[EMAIL PROTECTED]> wrote:
> I'm starting to think
>it'd be worth setting up a mechanism to handle such changes
>automatically.
I've been using this skeleton for quite some time now. Magnus'
psql ... | while read D
might be more robust than my
for db in `enumdatabases`
Servus
Manfred
#!/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"
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
