Subject: upgrading to version 4.0

From: Stan Yates <[email-masked]>
Date: Tue, 21 Jul 92 08:11:03 -0700

I began using Postgres with version 3.1.  I recently installed version
4.0.  How do I retain my old relations without having to recreate and
repopulate each one of them?  I kept the old $POSTGRESHOME/data/base files.
Thanks.

---

From: postarch (Postgres Mailing Archive)
Date: Tue, 21 Jul 92 09:31:37 PDT

> repopulate each one of them?  I kept the old $POSTGRESHOME/data/base files.

Unfortunately this is exactly what you have to do to upgrade.

A while back Tom Vijbrief (I hope I spelled that correctly!) posted shell
scripts he uses to help him do this.  I'm reposting part of this message
below.

Jeff

-------------------- repost ------------------------

I have written two shell scripts which dump a relation (dumprel)
or a whole database (dumpdb) to files.
The schema definition is also saved. The only thing you
lose are rules and indices.

Two other scripts load a relation or a whole database.

Example (dumps database mydata):

mkdir mydatadump
cd mydatadump
dumpdb mydata

On another installation do:

cd mydatadump
loaddb mydata

Here is the shar:

#! /bin/sh
# This is a shell archive.  Remove anything before this line, then unpack
# it by saving it into a file and typing "sh file".  To overwrite existing
# files, type "sh file -c".  You can also feed this as standard input via
# unshar, or by typing "sh <file", e.g..  If this archive is complete, you
# will see the following message at the end:
#		"End of shell archive."
# Contents:  dumpdb dumprel loaddb loadrel
# Wrapped by tom@lightning on Mon Apr 27 11:09:19 1992
PATH=/bin:/usr/bin:/usr/ucb ; export PATH
if test -f 'dumpdb' -a "${1}" != "-c" ; then 
  echo shar: Will not clobber existing file \"'dumpdb'\"
else
echo shar: Extracting \"'dumpdb'\" \(288 characters\)
sed "s/^X//" >'dumpdb' <<'END_OF_FILE'
X#! /bin/sh
X
Xif [ $# -ne 1 ];
Xthen
X  echo Usage: $0 database
X  exit 1
Xfi
X
Xfor i in `monitor -TN -c "retrieve (pg_class.relname) where pg_class.relname !~ \"pg_.*\" and pg_class.relkind = 'r'" $1`
Xdo
X  echo
X  echo "Dumping $i in database $1..."
X  $GEOHOME/postgres/dump/dumprel $i $1
Xdone
X
END_OF_FILE
if test 288 -ne `wc -c <'dumpdb'`; then
    echo shar: \"'dumpdb'\" unpacked with wrong size!
fi
chmod +x 'dumpdb'
# end of 'dumpdb'
fi
if test -f 'dumprel' -a "${1}" != "-c" ; then 
  echo shar: Will not clobber existing file \"'dumprel'\"
else
echo shar: Extracting \"'dumprel'\" \(1506 characters\)
sed "s/^X//" >'dumprel' <<'END_OF_FILE'
X#! /bin/sh
X
Xif [ $# -ne 2 ];
Xthen
X  echo Usage: $0 relation database
X  exit 1
Xfi
X
Xinherits=`monitor -TN -c "retrieve (inh.relname) from inh in pg_class where pg_class.relname = \"$1\" and pg_class.oid = pg_inherits.inhrel and inh.oid = pg_inherits.inhparent" $2 | awk '{ printf del $1; del= " " }' `
X
Xecho $1 inherits from: $inherits
X
Xinherited=`monitor -TN -c "retrieve (pg_attribute.attname) where pg_attribute.attrelid = pg_inherits.inhparent and pg_attribute.attnum > 0 and pg_inherits.inhrel = pg_class.oid and pg_class.relname = \"$1\"" $2 | awk '{ printf del $1; del= " " }' `
X
Xecho $1 inherits attributes: $inherited
X
Xmonitor -TN -c "retrieve (pg_attribute.attname, pg_type.typname, pg_attribute.attnum) where pg_type.oid = pg_attribute.atttypid and pg_attribute.attrelid = pg_class.oid and pg_class.relname = \"$1\"" $2 | \
Xawk '
XBEGIN	{ fn= "'$1.schema'"
X	  inherits= "'"$inherits"'"
X	  inherited= "'"$inherited"'"
X	  n_inherits= split(inherits, inherits_a)
X	  n_inherited= split(inherited, inherited_a)
X	  printf "create '$1' (" > fn
X	}
X$3 > 0	{ 
X	  for (ai= 1; ai <= n_inherited; ai++) {
X	    if (inherited_a[ai] == $1)
X	      next
X	  }
X	  printf del $1 "=" $2 > fn
X		  del= ","
X	}
XEND	{ printf ")" > fn
X	  if (n_inherits > 0) {
X	    printf " inherits (" > fn
X	    for (i= 1; i <= n_inherits; i++) {
X	      printf inherits_a[i] > fn
X	      if (i < n_inherits)
X		printf "," > fn
X	      else
X		printf ")" > fn
X	    }
X	  }
X	  printf "\n" > fn
X	}
X'
X
Xmonitor -c "copy $1 to \""`pwd`"/$1.copy\"" $2
X
END_OF_FILE
if test 1506 -ne `wc -c <'dumprel'`; then
    echo shar: \"'dumprel'\" unpacked with wrong size!
fi
chmod +x 'dumprel'
# end of 'dumprel'
fi
if test -f 'loaddb' -a "${1}" != "-c" ; then 
  echo shar: Will not clobber existing file \"'loaddb'\"
else
echo shar: Extracting \"'loaddb'\" \(214 characters\)
sed "s/^X//" >'loaddb' <<'END_OF_FILE'
X#! /bin/sh
X
Xif [ $# -ne 1 ];
Xthen
X  echo Usage: $0 database
X  exit 1
Xfi
X
Xfor si in `ls -rt *.schema`
Xdo
X  i=`basename $si .schema`
X  echo "Loading $i in database $1..."
X  $GEOHOME/postgres/dump/loadrel $i $1
Xdone
X
END_OF_FILE
if test 214 -ne `wc -c <'loaddb'`; then
    echo shar: \"'loaddb'\" unpacked with wrong size!
fi
chmod +x 'loaddb'
# end of 'loaddb'
fi
if test -f 'loadrel' -a "${1}" != "-c" ; then 
  echo shar: Will not clobber existing file \"'loadrel'\"
else
echo shar: Extracting \"'loadrel'\" \(186 characters\)
sed "s/^X//" >'loadrel' <<'END_OF_FILE'
X#! /bin/sh
X
Xif [ $# -ne 2 ];
Xthen
X  echo Usage: $0 relation database
X  exit 1
Xfi
X
Xawk '{ print $0 "\\g" }' < $1.schema | monitor -q $2
X
Xmonitor -c "copy $1 from \""`pwd`"/$1.copy\"" $2
X
END_OF_FILE
if test 186 -ne `wc -c <'loadrel'`; then
    echo shar: \"'loadrel'\" unpacked with wrong size!
fi
chmod +x 'loadrel'
# end of 'loadrel'
fi
echo shar: End of shell archive.
exit 0
===============================================================================
Tom Vijlbrief
TNO Institute for Perception		Phone: +31 34 63 562 11
P.O. Box 23				Fax:   +31 34 63 539 77
3769 ZG  Soesterberg			E-mail: [email-masked]
The Netherlands				or: uunet!tnosoes!tom
===============================================================================