migrate-postgres.sh

#!/bin/bash

# Your original postgres is assumed to be running on the local machine
# on $OLD_PORT. You may wish to modify --encoding.
DB_NAME=live
OLD_PORT=5432
DUMP_OPTIONS="--no-owner --encoding=latin1"

# This is the configuration of your new postgres. Version is purely
# used to autopopulate the NEW_PG variable and some status messages.
# Your postmaster should not be running when you run this script -
# it will be started and stopped, and will run on the NEW_PORT
# specified (instead of whatever the postgresql.conf says).
VERSION=8.4
NEW_PG=/usr/local/pgsql-v$VERSION
NEW_DATA=$NEW_PG/data
NEW_PORT=9000
NEW_DB_NAME=${DB_NAME}_migration_test
LOG_WHERE="/var/log/postgres/migrate-v${VERSION}"

# Disable this to run for real, although you can still do
# DEBUG=echo ./migrate-postgres.sh
DEBUG=echo

$DEBUG echo "Starting new postmaster in ${NEW_PG}/bin, with data in ${NEW_DATA} on port ${NEW_PORT}"
$DEBUG $NEW_PG/bin/pg_ctl start -D $NEW_DATA -l $LOG_WHERE.postmaster.log -o "-p ${NEW_PORT}"
$DEBUG $NEW_PG/bin/createdb -p ${NEW_PORT} ${NEW_DB_NAME}

$DEBUG echo "Started at: `date`"
$DEBUG echo "Started at: `date`" $DEBUG_REDIR ${LOG_WHERE}.timing.log

DUMP_OLD_DB="$NEW_PG/bin/pg_dump $DUMP_OPTIONS -p ${OLD_PORT} ${DB_NAME}"

$DEBUG $DUMP_OLD_DB | $DEBUG $NEW_PG/bin/psql -p ${NEW_PORT} ${NEW_DB_NAME}

$DEBUG echo "Updating postgis..."
$DEBUG $NEW_PG/bin/psql -p ${NEW_PORT} -f /usr/local/share/postgis/lwpostgis_upgrade.sql -d ${NEW_DB_NAME}

$DEBUG echo "Updating fuzzystrmatch..."
$DEBUG $NEW_PG/bin/psql -p ${NEW_PORT} -f $NEW_PG/share/contrib/fuzzystrmatch.sql -d ${NEW_DB_NAME}

$DEBUG echo "Finished at: `date`" $DEBUG_REDIR ${LOG_WHERE}.timing.log
$DEBUG echo "Finished at: `date`"

# I'm going to drop it by hand, because I'm paranoid
#$DEBUG $NEW_PG/bin/dropdb -p $NEW_PORT $NEW_DB_NAME
$DEBUG $NEW_PG/bin/pg_ctl stop -D $NEW_DATA