upgrading to 6.4 from 6.3 - Mailing list pgsql-hackers

Here is the last thing I think I promised for 6.4.  It is a script that
will allow 6.3.* users to upgrade to 6.4 without reloading all their
data.

It works by copying the user tables/index files to a backup directory,
destroying and recreating the database, then using the
pg_dump/pg_dumpall output to re-create the tables and indexes, without
the COPY commands.  It then moves the table/index files back into the
directory.

If it works and people like it, I will write up a manual page, and
mention it so people can use it.

I am not sure how to preserve the database owner because I need to
drop/recreate the database.  Any ideas?


Vadim, I assume you did not change anything in the table or index
structure.  You mentioned an index change, but I do not think you made
it, did you?

--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)
:

trap "rm -f /tmp/$$" 0 1 2 3 15
if [ "$#" -eq 0 ]
then    echo "Usage:  $0 [-f inputfile] database" 1>&2
    exit 1
fi

if [ "X$1" = "X-f" ]
then    INPUT="$2"
    shift 2
else    trap "rm -f /tmp/$$ /tmp/$$a" 0 1 2 3 15
    INPUT="/tmp/$$a"
    cat >/tmp/$$a
fi

if [ "$#" -ne 1 ]
then    echo "Usage:  $0 [-f input_file] database" 1>&2
    exit 1
fi

DATABASE="$1"

# check things

if [ ! -d "./data" ]
then    echo "$0 must be run from the top of the postgres directory tree." 1>&2
    exit 1
fi
if [ ! -d "./data/base/template1" ]
then    echo "$0 must be run as the postgres superuser." 1>&2
    exit 1
fi

# move user tables out of the way

cd data/base

mkdir pg_upgrade

for DIR in *
do
    if [ -d "$DIR" -a \
        \( "$DATABASE" = "$DIR" -o "$DATABASE" = "template1" \) ]
    then    rm -rf pg_upgrade/"$DIR"
        mkdir pg_upgrade/"$DIR"
        cd $DIR
        for FILE in *
        do
            if [ `expr "$FILE" : "pg_"` -ne 3 -a \
                "$FILE" != "PG_VERSION" ]
            then    mv $FILE ../pg_upgrade/$DIR/$FILE
            fi
        done
        cd ..
        echo "Dropping and recreating database $DIR"
        destroydb "$DIR"
        createdb "$DIR"
    fi
done

# remove COPY statements

cat "$INPUT" | awk '    {
                if (toupper($0) ~ /^COPY /)
                    while (getline $0 > 0 && $0 != "\\.")
                        ;
                else    print $0;
            }' >/tmp/$$

#create empty tables/indexes

psql "$DATABASE" <"/tmp/$$"

for DIR in *
do
    if [ -d "$DIR" -a \
        \( "$DATABASE" = "$DIR" -o "$DATABASE" = "template1" \) ]
    then    cd $DIR
        mv ../pg_upgrade/$DIR/* . && rmdir ../pg_upgrade/$DIR
        rmdir ../pg_upgrade/$DIR 2>/dev/null # if empty, remove it
        cd ..
    fi
done

rmdir pg_upgrade

if [ "$?" -ne 0 ]
then    echo "There is something left in data/base/pg_upgrade." 1>&2
    exit 1
fi