Обсуждение: upgrading to 6.4 from 6.3

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

Bruce Momjian wrote:
>
> 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.

But how about pg_log & pg_variable ?
Shouldn't nextOid, nextXid and transaction commit/abort infos
be restored ?

>
> 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?

No, I didn't.

Vadim

> Bruce Momjian wrote:
> >
> > 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.
>
> But how about pg_log & pg_variable ?
> Shouldn't nextOid, nextXid and transaction commit/abort infos
> be restored ?

OK, I have added an 'mv' of pg_log and pg_variable from the old
instance.  The only problem I see is that the postmaster is running
during the script, so shared memory is enabled.  If I copy them during
that time, do they get preseved, or does the next backend overwrite
pg_variable?

I wonder if I have to stop the postmaster before I do this, and tell the
user to restart it.  The new pid lock file would help in this case.


--
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)