path: root/src/bin/pg_dump/pg_upgrade

#!/bin/sh
#
# pg_upgrade: update a database without needing a full dump/reload cycle.
# CAUTION: Read the manual page before trying to use this!

# $Header: /cvsroot/pgsql/src/bin/pg_dump/Attic/pg_upgrade,v 1.25 2002/01/11 05:54:59 momjian Exp $
#
# NOTE: we must be sure to update the version-checking code a few dozen lines
# below for each new PostgreSQL release.

TMPFILE="/tmp/pgupgrade.$$"

trap "rm -f $TMPFILE" 0 1 2 3 15

SCHEMA=""
while [ "$#" -gt 1 ]
do
	if [ "X$1" = "X-s" ]
	then	SCHEMA="$2"
		if [ ! -s "$SCHEMA" ]
		then	echo "$SCHEMA does not exist" 1>&2
			exit 1
		fi
		shift 2
	else	echo "Usage:  $0 -s schema_dump old_data_dir" 1>&2
		exit 1
	fi
done

if [ "$#" -ne 1 -o ! "$SCHEMA" ]
then	echo "Usage:  $0 -s schema_dump old_data_dir" 1>&2
	exit 1
fi

OLDDIR="$1"

# check things

if [ ! -d "./data" ]
then	echo "`basename $0` must be run from the directory containing the database directory \`data\' (`dirname $PGDATA`.)" 1>&2
	echo "You must have run initdb to create the template1 database." 1>&2
	exit 1
fi

if [ ! -d "./$OLDDIR" ]
then	echo "You must rename your old data directory to $OLDDIR and run initdb." 1>&2
	exit 1
fi

if [ ! -d "./data/base/1" ]
then	echo "Cannot find database template1 in ./data/base." 1>&2
	echo "Are you running $0 as the postgres superuser?" 1>&2
	exit 1
fi

if [ ! -d "./$OLDDIR/base/1" ]
then	echo "There is no database template1 in ./$OLDDIR/base." 1>&2
	exit 1
fi

if [ ! -r "./data/PG_VERSION" ]
then	echo "Cannot read ./data/PG_VERSION --- something is wrong." 1>&2
	exit 1
fi

if [ ! -r "./$OLDDIR/PG_VERSION" ]
then	echo "Cannot read ./$OLDDIR/PG_VERSION --- something is wrong." 1>&2
	exit 1
fi

# Get the actual versions seen in the data dirs.
DEST_VERSION=`cat ./data/PG_VERSION`
SRC_VERSION=`cat ./$OLDDIR/PG_VERSION`

# Check for version compatibility.
# This code will need to be updated/reviewed for each new PostgreSQL release.

# UPGRADE_VERSION is the expected output database version
UPGRADE_VERSION="7.1"

if [ "$DEST_VERSION" != "$UPGRADE_VERSION" -a "$DEST_VERSION" != "$SRC_VERSION" ]
then	echo "`basename $0` is for PostgreSQL version $UPGRADE_VERSION, but ./data/PG_VERSION contains $DEST_VERSION." 1>&2
	echo "Did you run initdb for version $UPGRADE_VERSION?" 1>&2
	exit 1
fi

# Check that input database is of a compatible version (anything with the same
# physical layout of user tables and indexes should be OK).  I did not write
# something like "$SRC_VERSION -ge $UPGRADE_VERSION" because test(1) isn't bright
# enough to compare dotted version strings properly.  Using a case statement
# looks uglier but is more flexible.

case "$SRC_VERSION" in
#    7.2) ;;
    *)	echo "Sorry, `basename $0` cannot upgrade database version $SRC_VERSION to $DEST_VERSION." 1>&2
	echo "The on-disk structure of tables has changed." 1>&2
	echo "You will need to dump and restore using pg_dumpall." 1>&2
	exit 1;;
esac

pg_resetxlog 2>/dev/null
if [ "$?" -ne 1 ]
then	echo "Unable to find pg_resetxlog in path.
Install it from pgsql/contrib/pg_resetxlog and continue.;  exiting" 1>&2
	exit 1
fi

if ! pg_resetxlog -x | grep -q XID
then	echo "Old version of pg_resetxlog found in path.
Install a newer version from pgsql/contrib/pg_resetxlog.;  exiting" 1>&2
	exit 1
fi

# If the XID is > 2 billion, 7.1 database will have non-frozen XID's in 
# low numbers, and 7.2 will think they are in the future --- bad.

XID=`pg_resetxlog -n "$OLDDIR" | grep "NextXID" | awk -F'  *' '{print $4}'`
if [ "$SRC_VERSION" = "7.1" -a "$XID" -gt 2000000000 ]
then	echo "XID too high for $0.;  exiting" 1>&2
	exit 1
fi

# Checking done.  Ready to proceed.

# Execute the schema script to create everything

psql "template1" < "$SCHEMA"
if [ $? -ne 0 ]
then	echo "There were errors in the input script $SCHEMA.
$0 aborted." 1>&2
	exit 1
fi

echo "Input script $SCHEMA complete, fixing row commit statuses..."

# XXX do we still need this?
# Now vacuum each result database because our movement of transaction log
# causes some committed transactions to appear as non-committed

vacuumdb -a
if [ $? -ne 0 ]
then	echo "There were errors during VACUUM.
$0 aborted." 1>&2
	exit 1
fi

# Used for scans looking for a database/table name match
# New oid is looked up
pg_dumpall -s > $TMPFILE 2>/dev/null
if [ "$?" -ne 0 ]
then	echo "Unable to dump schema of new database.;  exiting" 1>&2
	exit 1
fi

# we are done with SQL database access
# shutdown forces buffers to disk

pg_ctl stop
if [ "$?" -ne 0 ]
then	echo "Unable to stop database server.;  exiting" 1>&2
	exit 1
fi

echo "Commit fixes complete, moving data files..."

cat "$SCHEMA" | while read LINE
do
	if /bin/echo "$LINE" | grep -q '^\\connect [^	]*$'
	then	OLDDB="$DB"
		DB="`/bin/echo \"$LINE\" | cut -d' ' -f2`"
		if [ "$DB" = "-" ]
		then	DB="$OLDDB"
		fi
		if [ "$DB" = "template1" -o "$DB" = "template0" ]
		then	DB=""
		fi
	fi
	if echo "$LINE" | grep -q "^-- TOC Entry ID [0-9]* (OID "
	then	OID="`echo \"$LINE\" | cut -d' ' -f7 | tr -d ')'`"
	fi
	if echo "$LINE" | egrep -q "^-- Name: [^ ]* Type: (TABLE|INDEX|SEQUENCE) "
	then	TABLE="`echo \"$LINE\" | cut -d' ' -f3`"
		# skip system tables
		if [ "`echo \"$TABLE\" | cut -c 1-3`" = "pg_" ]
		then	TABLE=""
		fi
	fi
	if [ "$DB" -a "$OID" -a "$TABLE" ]
	then
		NEWOID=`awk -F' ' '
				BEGIN 	{ newdb=""; newoid="";
					  newtable=""; ret=0;}
				$1 == "\\\\connect" && $2 != "-" {newdb=$2;}
				$0 ~ /^-- TOC Entry ID [0-9]* .OID / \
					{ newoid = substr($7, 1, length($7)-1);}
				{print $0 >> "/tmp/x";
				print $3 >> "/tmp/x";
				print newdb," ", newoid >> "/tmp/x"}
				($0 ~ /^-- Name: [^ ]* Type: TABLE / || \
				 $0 ~ /^-- Name: [^ ]* Type: INDEX / || \
				 $0 ~ /^-- Name: [^ ]* Type: SEQUENCE /) && \
				newdb == "'"$DB"'" && \
				$3 == "'"$TABLE"'" \
					{ ret=newoid; exit}
				END { print ret;}' $TMPFILE`
		if [ "$NEWOID" -eq 0 ]
		then	echo "Move of database $DB, OID $OID, table $TABLE failed.
New oid not found;  exiting" 1>&2
			exit 1
		fi
		# We use stars so we don't have to worry about database oids
 		if [ `ls "$OLDDIR"/base/*/"$OID" | wc -l` -eq 0 ]
		then	echo "Move of database $DB, OID $OID, table $TABLE failed.
File not found;  exiting" 1>&2
			exit 1
		fi
  		if [ `ls "$OLDDIR"/base/*/"$OID" | wc -l` -gt 1 ]
		then	echo "Move of database $DB, OID $OID, table $TABLE failed.
Too many found;  exiting" 1>&2
			exit 1
		fi
		if [ `ls data/base/*/"$NEWOID" | wc -l` -eq 0 ]
		then	echo "Move of database $DB, OID $OID, table $TABLE to $NEWOID failed.
File not found;  exiting" 1>&2
			exit 1
		fi
		if [ `ls data/base/*/"$NEWOID" | wc -l` -gt 1 ]
		then	echo "Move of database $DB, OID $OID, table $TABLE to $NEWOID failed.
Too many found;  exiting" 1>&2
			exit 1
		fi
		mv -f "$OLDDIR"/base/*/"$OID" data/base/*/"$NEWOID"
		if [ "$?" -ne 0 ]
		then	echo "Move of database $DB, OID $OID, table $TABLE 
to $NEWOID failed.;  exiting" 1>&2
			exit 1
		fi
		TABLE=""
	fi
done


# Set this so the next VACUUM sets the old row XID's as "frozen"
pg_resetxlog -x "$XID" data
if [ "$?" -ne 0 ]
then	echo "Unable to set new XID.;  exiting" 1>&2
	exit 1
fi

# set last checkpoint location from old database

CHKPOINT=`pg_resetxlog -n "$OLDDIR" | grep "checkpoint location:" |
	awk -F'  *' '{print $4}'`
if [ "$CHKPOINT" = "" ]
then	echo "Unable to get old checkpoint location.;  exiting" 1>&2
	exit 1
fi

pg_resetxlog -l `echo "$CHKPOINT | tr '/' ' '` data
if [ "$?" -ne 0 ]
then	echo "Unable to set new checkpoint location.;  exiting" 1>&2
	exit 1
fi

# Restart server with moved data

pg_ctl start
if [ "$?" -ne 0 ]
then	echo "Unable to restart database server.;  exiting" 1>&2
	exit 1
fi

# Set sequence values for 7.1-version sequences, which were int4.
if [ "$SRC_VERSION" = "7.1" ]
else	echo "Set int8 sequence values from 7.1..."

	psql -d template1 -At -c "SELECT datname FROM pg_database" | 
	while read DB
	do	
		echo "$DB"
		# XXX is concurrency a problem here?
		psql -d "$DB" -At -c "SELECT relname FROM pg_class where relkind = 'S';" |
		while read SEQUENCE
		do
			psql -d "$DB" -At <<SQL_END

-- This table matches the 7.1 sequence schema
CREATE TABLE pg_upgrade_temp_seq_int4 (
	sequence_name name
	last_value    integer
	increment_by  integer
	max_value     integer
	min_value     integer
	cache_value   integer
	log_cnt       integer
	is_cycled     "char"
	is_called     "char"
);

-- Move int8 columns of sequence out of the way
UPDATE pg_attribute
SET attrelid = 1 -- OID of template1, not used anywhere else  XXX correct?
WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = '$SEQUENCE');

-- Replace with int4 sequence columns
UPDATE pg_attribute
SET attrelid = 
	(SELECT oid FROM pg_class WHERE relname = '$SEQUENCE')
WHERE attrelid = 
	(SELECT oid FROM pg_class WHERE relname = 'pg_upgrade_temp_seq_int4');

-- Mark sequence as ordinary table so we can do COPY
UPDATE pg_class
SET relkind = 't'
WHERE relname = '$SEQUENCE';

-- COPY sequence out
COPY "$SEQUENCE" TO '/tmp/$$';

-- Delete int4 row from sequence
-- XXX truncate ok?
TRUNCATE "$SEQUENCE";

-- Prepare int4 sequence table for removal and remove it
UPDATE pg_attribute
SET attrelid = 
	(SELECT oid FROM pg_class WHERE relname = 'pg_upgrade_temp_seq_int4')
WHERE attrelid = 
	(SELECT oid FROM pg_class WHERE relname = '$SEQUENCE');

DROP TABLE pg_upgrade_temp_seq_int4;

-- Restore int8 version of sequence
UPDATE pg_attribute
SET attrelid = (SELECT oid FROM pg_class WHERE relname = '$SEQUENCE')
WHERE attrelid = 1;

-- Load new values
COPY "$SEQUENCE" FROM '/tmp/$$';

-- If previous max was int4, make it int8
UPDATE "$SEQUENCE"
SET max_value = 9223372036854775807
WHERE max_value BETWEEN 2147483646 AND 2147483648; -- OS rounding

-- Restore sequence flag
UPDATE pg_class
SET relkind = 'S'
WHERE relname = '$SEQUENCE';

SQL_END
		if [ $? -ne 0 ]
		then	echo "There were errors during int4 sequence restore.
	$0 aborted." 1>&2
			exit 1
		done
	done
fi

echo "You may remove the $OLDDIR directory with 'rm -r $OLDDIR'."

exit 0