Poloautomatická migrace na vyšší verzi s využitím pg upgrade

#!/bin/bash

# set -x

echo "ATTENTION!!! Break script immediately when it try to upgrade to unexpected release"

# Expects configuration files in PG_DATA. PG_BASE_DIR should be writable
# for postgres user.
PG_BASE_DIR="/var/lib/pgsql"
PG_DATA="/var/lib/pgsql/data"
PG_VERSION=`cat $PG_DATA/PG_VERSION`

# target major and minor release
MAJOR_RELEASE="9.2"
MINOR_RELEASE="7-9"

TARGET_RELEASE="$MAJOR_RELEASE.$MINOR_RELEASE.*"
PACKAGE="postgresql"

POSTGRES="$PACKAGE-$TARGET_RELEASE"
SERVER="$PACKAGE-server-$TARGET_RELEASE"
LIBS="$PACKAGE-libs-$TARGET_RELEASE"
CONTRIB="$PACKAGE-contrib-$TARGET_RELEASE"
UPGRADE="$PACKAGE-upgrade-$TARGET_RELEASE"
DEVEL="$PACKAGE-devel-$TARGET_RELEASE"

if [[ "$PG_VERSION" >= "$TARGET_RELEASE" ]]; then
  echo "migration was processed, server is $MAJOR_RELEASE already"
  exit 1;
fi

set -e

while true; do
    read -p "Do you wish to upgrade PostgreSQL to $MAJOR_RELEASE? (y/n) " yn
    case $yn in
        [Yy]* ) break;;
        [Nn]* ) exit 1;;
        * ) echo "Please answer yes or no.";;
    esac
done

set +e

echo "stop postgresql"
su postgres -c "cd;pg_ctl stop -D  $PG_DATA -m fast"
STATUS=$?
if [ $STATUS != 1 -a $STATUS != 0 ]; then
  su postgres -c "cd;pg_ctl status -D  $PG_DATA"
  while [ $? != 1 ]; do
    sleep 1
    su postgres -c "cd;pg_ctl status -D  $PG_DATA"
  done
fi

yum clean all 

# Attention: yum with --noplugis ignores version lock!!!
echo "verify postgresql target release binaries"
yum list $POSTGRES -y  --noplugins
if [ $? -ne 0 ]; then
  echo "missing PostgreSQL $TARGET_RELEASE packages"
  exit 1;
fi

# don't allow errors
set -e
echo "upgrade postgresql binaries"
yum upgrade $POSTGRES $SERVER $LIBS $CONTRIB $DEVEL  --noplugins
yum install $UPGRADE -y  --noplugins

echo "backup postgresql databases"
su postgres -c "mkdir $PG_BASE_DIR/data$MAJOR_RELEASE"
su postgres -c "/usr/bin/initdb -D $PG_BASE_DIR/data$MAJOR_RELEASE/"

echo "fix configuration"
# sed -i "s/shared_preload_libraries/#shared_preload_libraries/" $PG_DATA/postgresql.conf

echo "start upgrade"
su postgres -c "mkdir $PG_BASE_DIR/upgrade_data"
su postgres -c "cd $PG_BASE_DIR/upgrade_data; pg_upgrade -b /usr/lib64/pgsql/postgresql-$PG_VERSION/bin -B /usr/bin/ -d $PG_DATA -D $PG_BASE_DIR/data$MAJOR_RELEASE -k"

echo "revert back changes in configuration"
# sed -i "s/#shared_preload_libraries/shared_preload_libraries/" $PG_DATA/postgresql.conf
sed -i "s/#listen_addresses = 'localhost'/listen_addresses = '*'/" $PG_DATA/postgresql.conf

echo "switch data dir"
mv $PG_DATA $PG_BASE_DIR/data$PG_VERSION
mv $PG_BASE_DIR/data$MAJOR_RELEASE $PG_DATA

echo "clean"
rm -f -r $PG_BASE_DIR/upgrade_data

echo "reuse original pg_hba.conf and postgresql.conf"
'cp' -rf $PG_BASE_DIR/data$PG_VERSION/pg_hba.conf $PG_DATA
'cp' -rf $PG_BASE_DIR/data$PG_VERSION/postgresql.conf $PG_DATA

echo "uninstall postgresql-upgrade package"
yum remove $UPGRADE -y

echo "fix selinux labeling, reboot is required"
restorecon -R  $PG_DATA

echo "start services"
/etc/init.d/postgresql start

echo "migration to PostgreSQL $TARGET_RELEASE is done"

Případný downgrade:

• Zastavit PostgreSQL
• Předchozí adresář (např. /var/lib/pgsql/data91) přejmenovat zpět (na /var/lib/pgsql/data).

yum remove postgresql-upgrade
yum downgrade postgresql postgresql-server postgresql-contrib postgresql-libs postgresql-devel