#!/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.21 2002/01/10 03:05:48 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="" DATA="" 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 elif [ "X$1" = "X-d" ] then DATA="$2" if [ ! -s "$DATA" ] then echo "$DATA does not exist" 1>&2 exit 1 fi shift 2 else echo "Usage: $0 -s schema_dump [ -d data_dump ] old_data_dir" 1>&2 exit 1 fi done if [ "$#" -ne 1 -o ! "$SCHEMA" ] then echo "Usage: $0 -s schema_dump [ -d data_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. DESTVERSION=`cat ./data/PG_VERSION` SRCVERSION=`cat ./$OLDDIR/PG_VERSION` # Check for version compatibility. # This code will need to be updated/reviewed for each new PostgreSQL release. # MYVERSION is the expected output database version MYVERSION="7.1" if [ "$SRCVERSION" = "7.1" -a ! "$DATA" ] then echo "$0 requires a full data dump file to upgrade from version $SRCVERSION." 1>&2 echo "Use the '-d' parameter to specify the dump file" 1>&2 exit 1 fi if [ "$DESTVERSION" != "$MYVERSION" -a "$DESTVERSION" != "$SRCVERSION" ] then echo "$0 is for PostgreSQL version $MYVERSION, but ./data/PG_VERSION contains $DESTVERSION." 1>&2 echo "Did you run initdb for version $MYVERSION?" 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 "$SRCVERSION -ge $MINVERSION" because test(1) isn't bright # enough to compare dotted version strings properly. Using a case statement # looks uglier but is more flexible. case "$SRCVERSION" in # 7.2) ;; *) echo "Sorry, `basename $0` cannot upgrade database version $SRCVERSION to $DESTVERSION." 1>&2 echo "The on-disk structure of tables has changed." 1>&2 echo "You will need to dump and restore using pg_dump." 1>&2 exit 1;; esac # OK, ready to proceed. # Execute the schema script to create everything, except modify any # sequences with int4 maximums if we are upgrading from 7.1. cat $SCHEMA | awk -F' ' '{ if ("'"$SRCVERSION"'" == "7.1" && $1 == "CREATE" && $2 == "SEQUENCE" && ($9 >= 2147483646 && # handle OS round ($9 <= 2147483648)) { for(i=1; i < NF; i++) if (i != 9) printf "%s ", $i; else printf "%s ", "9223372036854775807"; print; } else print $0; }' | psql "template1" if [ $? -ne 0 ] then echo "There were errors in the input script $SCHEMA. $0 aborted." 1>&2 exit 1 fi if [ "$SRCVERSION" != "7.1" ] then echo "Input script $SCHEMA complete, fixing row commit statuses..." else echo "Input script $SCHEMA complete, setting int8 sequences..." # Set all the sequence counters because they are not brought over # in the schema dump, and the old 7.1 sequences where int4 in size # so bringing over the file wouldn't help us anyway. cat $DATA | awk '$0 == "\\connect " || "SELECT setval (" \ {print $0;}' | psql "template1" if [ $? -ne 0 ] then echo "There were errors in the input script $SCHEMA. $0 aborted." 1>&2 exit 1 fi echo "Int8 sequences set, fixing row commit statuses..." fi # Now vacuum each result database in case our transaction increase # causes all the XID's to be marked with the frozen XID. psql -d template1 -At -c "SELECT datname FROM pg_database" | while read DB do echo "VACUUM;" | psql "$DB" if [ $? -ne 0 ] then echo "There were errors during VACUUM. $0 aborted." 1>&2 exit 1 fi done # should be pretty small file pg_dumpall -s > $TMPFILE 2>/dev/null # flush 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) " 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 /) && \ 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.\nNew 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.\nFile 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.\nToo 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.\nFile 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.\nToo 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 \n to $NEWOID failed.; exiting" 1>&2 exit 1 fi TABLE="" fi done # 7.1 has non-compressed log file format if [ "$SRCVERSION" = "7.1" ] then # pg_log is oid 1269 in 7.1 LOGSIZE=`ls -l "$OLDDIR"/global/1269 "$OLDDIR"/global/1269.* 2>/dev/null | awk -F' *' ' BEGIN {sum=0;} {sum += $5;} END {print sum;}'` # check < 2gig # set max transaction id else # how to handle 7.2? rm -r data/pg_clog && mv "$OLDDIR"/data/pg_clog data/pg_clog && mv "$OLDDIR"/data/global/pg_control data/global/pg_control if [ "$?" -ne 0 ] then echo "Moving of transaction and control files failed.; exiting" 1>&2 exit 1 fi fi pg_ctl start if [ "$?" -ne 0 ] then echo "Unable to restart database server.; exiting" 1>&2 exit 1 fi echo "You may remove the $OLDDIR directory with 'rm -r $OLDDIR'." exit 0