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 12
echo $1 12
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;
2981pg_patchinfo11 1 12 f f t t
i 0 2249show_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.
) 12
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
