pg_upgrade/test.sh and v9.5
In fa66b6dee, Micheal fixed test.sh to work back to v11, so I suppose nobody is
trying to run it with older versions, as I was endeavored to do.
With the attached patch, I'm able to test upgrades back to v9.6.
In 9.5, there are regression diffs from CONTEXT lines from non-error messages,
which is a v9.5 change (0426f349e). The first "make check" fails before even
getting to the upgrade part:
| NOTICE: trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT
|- CONTEXT: SQL statement "INSERT INTO main_table VALUES (NEW.a, NEW.b)"
|- PL/pgSQL function view_trigger() line 17 at SQL statement
I tried a lot of things but couldn't find one that worked. I just tried this,
which allows the "make check" to pass, but then fails due missing symbols in
libpq during the upgrade phase. Maybe I'm missing something - Tom must have
tested psql against old versions somehow before de-supporting old versions (but
maybe not like this).
| time make check -C src/bin/pg_upgrade oldsrc=`pwd`/new/95 oldbindir=`pwd`/new/95/tmp_install/usr/local/pgsql/bin with_temp_install="LD_LIBRARY_PATH=`pwd`/new/95/tmp_install/usr/local/pgsql/lib"
I tried installcheck, but then that fails because psql doesn't accept multiple
-c options (it runs the final -c command only).
| EXTRA_REGRESS_OPTS="--bindir `pwd`/new/95/tmp_install/usr/local/pgsql/bin" LD_LIBRARY_PATH=`pwd`/new/95/tmp_install/usr/local/pgsql/lib PGHOST=/tmp time make installcheck
| ...
| ============== creating database "regression" ==============
| ERROR: database "regression" does not exist
| STATEMENT: ALTER DATABASE "regression" SET lc_messages TO 'C';ALTER DATABASE "regression" SET lc_monetary TO 'C';ALTER DATABASE "regression" SET lc_numeric TO 'C';ALTER DATABASE "regression" SET lc_time TO 'C';ALTER DATABASE "regression" SET bytea_output TO 'hex';ALTER DATABASE "regression" SET timezone_abbreviations TO 'Default';
| ERROR: database "regression" does not exist
| command failed: "/home/pryzbyj/src/postgres/new/95/tmp_install/usr/local/pgsql/bin/psql" -X -c "CREATE DATABASE \"regression\" TEMPLATE=template0" -c "ALTER DATABASE \"regression\" SET lc_messages TO 'C';ALTER DATABASE \"regression\" SET lc_monetary TO 'C';ALTER DATABASE \"regression\" SET lc_numeric TO 'C';ALTER DATABASE \"regression\" SET lc_time TO 'C';ALTER DATABASE \"regression\" SET bytea_output TO 'hex';ALTER DATABASE \"regression\" SET timezone_abbreviations TO 'Default';" "postgres"
pg_regress was changed to do that recently:
commit f45dc59a38cab1d2af6baaedb79559fe2e9b3781
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date: Wed Oct 20 18:44:37 2021 -0400
Improve pg_regress.c's infrastructure for issuing psql commands.
--
Justin
Attachments:
0001-wip-test.sh-allow-pg_upgrade-check-from-v10-and-earl.patchtext/x-diff; charset=us-asciiDownload
From ccc113ccaef45b09077faa250e126e7c480324d7 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@telsasoft.com>
Date: Sun, 2 Jan 2022 15:00:19 -0600
Subject: [PATCH] wip: test.sh: allow pg_upgrade check from v10 and earlier
---
src/bin/pg_upgrade/test.sh | 32 +++++++++++++++++++++-------
src/bin/pg_upgrade/upgrade_adapt.sql | 8 +++----
2 files changed, 28 insertions(+), 12 deletions(-)
diff --git a/src/bin/pg_upgrade/test.sh b/src/bin/pg_upgrade/test.sh
index ef328b3062f..c0a569ed6e3 100644
--- a/src/bin/pg_upgrade/test.sh
+++ b/src/bin/pg_upgrade/test.sh
@@ -24,7 +24,12 @@ standard_initdb() {
# without increasing test runtime, run these tests with a custom setting.
# Also, specify "-A trust" explicitly to suppress initdb's warning.
# --allow-group-access and --wal-segsize have been added in v11.
- "$1" -N --wal-segsize 1 --allow-group-access -A trust
+ if [ "$newsrc" = "$oldsrc" ]; then
+ "$@" -N -A trust --wal-segsize 1 --allow-group-access
+ else
+ "$@" -N -A trust
+ fi
+
if [ -n "$TEMP_CONFIG" -a -r "$TEMP_CONFIG" ]
then
cat "$TEMP_CONFIG" >> "$PGDATA/postgresql.conf"
@@ -82,12 +87,25 @@ mkdir "$temp_root"
oldsrc=`cd "$oldsrc" && pwd`
newsrc=`cd ../../.. && pwd`
+# See similar logic in pg_upgrade/exec.c
+#oldpgversion0=`"$oldbindir"/pg_ctl --version`
+#oldpgversion=`echo "$oldpgversion0" |awk 'NF>=3 && $3~/^[0-9]+(\.[0-9]+|devel$)/{split($3, a, "\\\\."); print int(a[1])}'`
+#[ -n "$oldpgversion" ] || {
+ #echo "Could not determine version of old cluster: $oldpgversion0";
+ #exit 1
+#}
+oldpgversion=`grep '#define PG_VERSION_NUM' "$oldsrc"/src/include/pg_config.h | awk '{print $3}'`
+
# We need to make pg_regress use psql from the desired installation
# (likely a temporary one), because otherwise the installcheck run
# below would try to use psql from the proper installation directory
# of the target version, which might be outdated or not exist. But
# don't override anything else that's already in EXTRA_REGRESS_OPTS.
-EXTRA_REGRESS_OPTS="$EXTRA_REGRESS_OPTS --bindir='$oldbindir'"
+if [ "$oldpgversion" -ge 90500 ]; then
+ EXTRA_REGRESS_OPTS="$EXTRA_REGRESS_OPTS --bindir='$oldbindir'"
+else
+ EXTRA_REGRESS_OPTS="$EXTRA_REGRESS_OPTS --psqldir='$bindir'"
+fi
export EXTRA_REGRESS_OPTS
# While in normal cases this will already be set up, adding bindir to
@@ -176,8 +194,6 @@ createdb "regression$dbname3" || createdb_status=$?
extra_dump_options=""
if "$MAKE" -C "$oldsrc" installcheck-parallel; then
- oldpgversion=`psql -X -A -t -d regression -c "SHOW server_version_num"`
-
# Before dumping, tweak the database of the old instance depending
# on its version.
if [ "$newsrc" != "$oldsrc" ]; then
@@ -239,16 +255,16 @@ pg_upgrade $PG_UPGRADE_OPTS --no-sync -d "${PGDATA}.old" -D "$PGDATA" -b "$oldbi
# Windows hosts don't support Unix-y permissions.
case $testhost in
MINGW*|CYGWIN*) ;;
- *) if [ `find "$PGDATA" -type f ! -perm 640 | wc -l` -ne 0 ]; then
- echo "files in PGDATA with permission != 640";
+ *) if [ `find "$PGDATA" -type f ! -perm 640 ! -perm 600 | wc -l` -ne 0 ]; then
+ echo "files in PGDATA with permission NOT IN (640,600)";
exit 1;
fi ;;
esac
case $testhost in
MINGW*|CYGWIN*) ;;
- *) if [ `find "$PGDATA" -type d ! -perm 750 | wc -l` -ne 0 ]; then
- echo "directories in PGDATA with permission != 750";
+ *) if [ `find "$PGDATA" -type d ! -perm 750 ! -perm 700 | wc -l` -ne 0 ]; then
+ echo "directories in PGDATA with permission NOT IN (750,700)";
exit 1;
fi ;;
esac
diff --git a/src/bin/pg_upgrade/upgrade_adapt.sql b/src/bin/pg_upgrade/upgrade_adapt.sql
index 27c4c7fd011..d47d2075f5c 100644
--- a/src/bin/pg_upgrade/upgrade_adapt.sql
+++ b/src/bin/pg_upgrade/upgrade_adapt.sql
@@ -84,8 +84,8 @@ DO $stmt$
\if :oldpgversion_le13
-- Until v10, operators could only be dropped one at a time, so be careful
-- to stick with one command for each drop here.
-DROP OPERATOR public.#@# (pg_catalog.int8, NONE);
-DROP OPERATOR public.#%# (pg_catalog.int8, NONE);
-DROP OPERATOR public.!=- (pg_catalog.int8, NONE);
-DROP OPERATOR public.#@%# (pg_catalog.int8, NONE);
+DROP OPERATOR IF EXISTS public.#@# (pg_catalog.int8, NONE);
+DROP OPERATOR IF EXISTS public.#%# (pg_catalog.int8, NONE);
+DROP OPERATOR IF EXISTS public.!=- (pg_catalog.int8, NONE);
+DROP OPERATOR IF EXISTS public.#@%# (pg_catalog.int8, NONE);
\endif
--
2.17.1