On Sun, Feb 25, 2018 at 04:20:41PM -0500, Tom Lane wrote:
> No; you're reading the commits in reverse order. The hack I thought we
> could use to preserve that behavior didn't work.
>
> AFAICS, the only way we could maintain something like the old behavior
> here is to promote it to a full-fledged feature, something like a
> pg_dump/pg_restore option "--ignore-read-only" to make those programs
> issue "SET default_transaction_read_only = off" at the right times,
> which in turn would need to be invoked by a similar pg_dumpall switch.
...
> While that doesn't seem like it would be terribly hard to do, I don't
> personally have the interest to go do it.
OK, I see.
Luckily, pg_upgradecluster allows running custom snippets at
appropriate times during the upgrade:
HOOK SCRIPTS
Some PostgreSQL extensions like PostGIS need metadata in
auxiliary tables which must not be upgraded from the old
version, but rather initialized for the new version
before copying the table data. For this purpose,
extensions (as well as administrators, of course) can
drop upgrade hook scripts into
/etc/postgresql-common/pg_upgradecluster.d/. Script file
names must consist entirely of upper and lower case
letters, digits, underscores, and hyphens; in particular,
dots (i. e. file extensions) are not allowed.
Scripts in that directory will be called with the following arguments:
<old version> <cluster name> <new version> <phase>
Phases:
init
A virgin cluster of version new version has been
created, i. e. this new cluster will already have
template1 and postgres, but no user databases.
Please note that you should not create tables in
this phase, since they will be overwritten by the
dump/restore or pg_upgrade operation.
finish
All data from the old version cluster has been
dumped/reloaded into the new one. The old cluster
still exists, but is not running.
Failing scripts will abort the upgrade. The scripts
are called as the user who owns the database.
which will allow solving the problem with something like the
attached sript.
Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
#!/bin/sh
#==============================================================
#
# This script modifies the GNUmed databases such that an
# upgrade using Debian's <pg_upgradecluster> may succeed.
#
# Drop this file into /etc/postgresql-common/pg_upgradecluster.d/
# if you are using a postgresql-common package < v122 on Debian
# and want to upgrade your cluster using <pg_upgradecluster>.
#
# Note that pg_upgradecluster will run this as user <postgres>.
#
# author: Karsten Hilbert
# license: GPL v2 or later
#==============================================================
DB_VERSIONS="2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20"
#==============================================================
# There really should not be any need to
# change anything below this line.
#==============================================================
# read data from pg_upgradecluster API
OLD_VER="$1"
CLUSTER_NAME="$2"
NEW_VER="$3"
PHASE="$4"
# appropriate phase ?
if test "${PHASE}" = "init" ; then
echo " - removing readonly attribute from GNUmed databases..."
ACTION="off"
elif test "${PHASE}" = "finish" ; then
echo " - re-adding readonly attribute to GNUmed databases..."
ACTION="on"
else
echo "INVALID pg_upgrade PHASE: <${PHASE}>";
exit 1;
fi
# setup files
TS=`date +%Y-%m-%d-%H-%M-%S`
SQL_FILE="/tmp/gm-pg_upgradecluster-helper-readonly_${ACTION}-${TS}.sql"
LOG_FILE="/tmp/gm-pg_upgradecluster-helper-readonly_${ACTION}-${TS}.log"
# create SQL script
echo "\\unset ON_ERROR_STOP" > ${SQL_FILE}
for DB_VER in ${DB_VERSIONS} ; do
echo "" >> ${SQL_FILE}
echo "BEGIN;" >> ${SQL_FILE}
echo "ALTER DATABASE \"gnumed_v${DB_VER}\" SET default_transaction_read_only TO ${ACTION};" >> ${SQL_FILE}
echo "END;" >> ${SQL_FILE}
done
# get port of OLD cluster as suggested by pg_upgradecluster maintainer
CMD="use PgCommon; \$p = get_cluster_port \"${OLD_VER}\", \"${CLUSTER_NAME}\"; print \"\$p\\n\""
PORT=`perl -I/usr/share/postgresql-common/ -e "$CMD"`
# run SQL script against OLD cluster
echo "Accessing port: ${PORT}" > ${LOG_FILE} 2>&1
psql -p ${PORT} -f ${SQL_FILE} >> ${LOG_FILE} 2>&1
# also run it against NEW cluster on finish
if test "${PHASE}" = "finish" ; then
CMD="use PgCommon; \$p = get_cluster_port \"${NEW_VER}\", \"${CLUSTER_NAME}\"; print \"\$p\\n\""
PORT=`perl -I/usr/share/postgresql-common/ -e "$CMD"`
echo "Accessing port: ${PORT}" >> ${LOG_FILE} 2>&1
psql -p ${PORT} -f ${SQL_FILE} >> ${LOG_FILE} 2>&1;
fi
exit 0
