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
Sat, 29 Aug 1998 23:11:27 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
upgrading to 6.4 from 6.3
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

Mon, 31 Aug 1998 09:08:03 +0800
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] upgrading to 6.4 from 6.3
> 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)

Mon, 31 Aug 1998 00:41:16 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] upgrading to 6.4 from 6.3