On Wed, Jul 06, 2022 at 03:27:28PM +0900, Michael Paquier wrote:
> On Thu, Jun 09, 2022 at 04:49:01PM +0900, Michael Paquier wrote:
> > Rebased to cope with the recent changes in this area.
>
> Please find attached an updated version of this patch, where I have
> extended the support of the upgrade script down to 9.5 as origin
> version, as ~9.4 now fail because of cluster_name, so Cluster.pm does
> not support that. FWIW, I have created an extra set of dumps down to
> 9.4.
This was using the old psql rather than the new one.
Before v10, psql didn't have \if.
I think Cluster.pm should be updated to support the upgrades that upgrade.sh
supported. I guess it ought to be fixed in v15.
--
Justin
>From 02d31c8ad1f50d4014a6839734b109ab24189c41 Mon Sep 17 00:00:00 2001
From: Michael Paquier <mich...@paquier.xyz>
Date: Wed, 6 Jul 2022 15:23:24 +0900
Subject: [PATCH 1/2] Add more filtering capabilities in the dumps.
This adds support for some filtering capabilities, for some tests done
down to v9.5. This allows the tests to pass with v14, while v9.5~13
still generate a few diffs, but these are minimal compared to what
happened before this commit.
---
src/bin/pg_upgrade/t/002_pg_upgrade.pl | 85 ++++++++++++++++++++------
1 file changed, 66 insertions(+), 19 deletions(-)
diff --git a/src/bin/pg_upgrade/t/002_pg_upgrade.pl b/src/bin/pg_upgrade/t/002_pg_upgrade.pl
index 9ed48c4e06a..4a79a9e13d9 100644
--- a/src/bin/pg_upgrade/t/002_pg_upgrade.pl
+++ b/src/bin/pg_upgrade/t/002_pg_upgrade.pl
@@ -30,6 +30,39 @@ sub generate_db
"created database with ASCII characters from $from_char to $to_char");
}
+# Filter the contents of a dump before its use in a content comparison.
+# This returns the path to the filtered dump.
+sub filter_dump
+{
+ my ($node, $tempdir, $dump_file_name) = @_;
+ my $dump_file = "$tempdir/$dump_file_name";
+ my $dump_contents = slurp_file($dump_file);
+
+ # Remove the comments.
+ $dump_contents =~ s/^\-\-.*//mgx;
+ # Remove empty lines.
+ $dump_contents =~ s/^\n//mgx;
+
+ # Locale setup has changed for collations in 15~.
+ $dump_contents =~
+ s/(^CREATE\sCOLLATION\s.*?)\slocale\s=\s'und'/$1 locale = ''/mgx
+ if ($node->pg_version < 15);
+
+ # Dumps taken from <= 11 use EXECUTE PROCEDURE. Replace it
+ # with EXECUTE FUNCTION.
+ $dump_contents =~
+ s/(^CREATE\sTRIGGER\s.*?)\sEXECUTE\sPROCEDURE/$1 EXECUTE FUNCTION/mgx
+ if ($node->pg_version < 12);
+
+ my $dump_file_filtered = "$tempdir/${dump_file_name}_filter";
+ open(my $dh, '>', $dump_file_filtered)
+ || die "opening $dump_file_filtered";
+ print $dh $dump_contents;
+ close($dh);
+
+ return $dump_file_filtered;
+}
+
# The test of pg_upgrade requires two clusters, an old one and a new one
# that gets upgraded. Before running the upgrade, a logical dump of the
# old cluster is taken, and a second logical dump of the new one is taken
@@ -60,7 +93,10 @@ my $oldnode =
# To increase coverage of non-standard segment size and group access without
# increasing test runtime, run these tests with a custom setting.
# --allow-group-access and --wal-segsize have been added in v11.
-$oldnode->init(extra => [ '--wal-segsize', '1', '--allow-group-access' ]);
+my %node_params = ();
+$node_params{extra} = [ '--wal-segsize', '1', '--allow-group-access' ]
+ if $oldnode->pg_version >= 11;
+$oldnode->init(%node_params);
$oldnode->start;
# The default location of the source code is the root of this directory.
@@ -147,19 +183,19 @@ if (defined($ENV{oldinstall}))
# Initialize a new node for the upgrade.
my $newnode = PostgreSQL::Test::Cluster->new('new_node');
-$newnode->init(extra => [ '--wal-segsize', '1', '--allow-group-access' ]);
+$newnode->init(%node_params);
my $newbindir = $newnode->config_data('--bindir');
my $oldbindir = $oldnode->config_data('--bindir');
# Take a dump before performing the upgrade as a base comparison. Note
# that we need to use pg_dumpall from the new node here.
-$newnode->command_ok(
- [
- 'pg_dumpall', '--no-sync',
- '-d', $oldnode->connstr('postgres'),
- '-f', "$tempdir/dump1.sql"
- ],
- 'dump before running pg_upgrade');
+my @dump_command = (
+ 'pg_dumpall', '--no-sync', '-d', $oldnode->connstr('postgres'),
+ '-f', "$tempdir/dump1.sql");
+# --extra-float-digits is needed when upgrading from a version older than 11.
+push(@dump_command, '--extra-float-digits', '0')
+ if ($oldnode->pg_version < 12);
+$newnode->command_ok(\@dump_command, 'dump before running pg_upgrade');
# After dumping, update references to the old source tree's regress.so
# to point to the new tree.
@@ -286,24 +322,35 @@ if (-d $log_path)
}
# Second dump from the upgraded instance.
-$newnode->command_ok(
- [
- 'pg_dumpall', '--no-sync',
- '-d', $newnode->connstr('postgres'),
- '-f', "$tempdir/dump2.sql"
- ],
- 'dump after running pg_upgrade');
+@dump_command = (
+ 'pg_dumpall', '--no-sync', '-d', $newnode->connstr('postgres'),
+ '-f', "$tempdir/dump2.sql");
+# --extra-float-digits is needed when upgrading from a version older than 11.
+push(@dump_command, '--extra-float-digits', '0')
+ if ($oldnode->pg_version < 12);
+$newnode->command_ok(\@dump_command, 'dump after running pg_upgrade');
+
+# Filter the contents of the dumps from the old version of any contents.
+my $dump1_filtered = "$tempdir/dump1.sql";
+my $dump2_filtered = "$tempdir/dump2.sql";
+
+# No need to apply filters on the dumps if working on the same version.
+if ($oldnode->pg_version != $newnode->pg_version)
+{
+ $dump1_filtered = filter_dump($oldnode, $tempdir, "dump1.sql");
+ $dump2_filtered = filter_dump($newnode, $tempdir, "dump2.sql");
+}
# Compare the two dumps, there should be no differences.
-my $compare_res = compare("$tempdir/dump1.sql", "$tempdir/dump2.sql");
+my $compare_res = compare($dump1_filtered, $dump2_filtered);
is($compare_res, 0, 'old and new dumps match after pg_upgrade');
# Provide more context if the dumps do not match.
if ($compare_res != 0)
{
my ($stdout, $stderr) =
- run_command([ 'diff', "$tempdir/dump1.sql", "$tempdir/dump2.sql" ]);
- print "=== diff of $tempdir/dump1.sql and $tempdir/dump2.sql\n";
+ run_command([ 'diff', $dump1_filtered, $dump2_filtered ]);
+ print "=== diff of $dump1_filtered and $dump2_filtered\n";
print "=== stdout ===\n";
print $stdout;
print "=== stderr ===\n";
--
2.17.1
>From 010626219cb977da08583a5bf8cc235bbc1bb3a9 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Sat, 25 Jun 2022 01:07:56 -0500
Subject: [PATCH 2/2] WIP: old versions
---
src/bin/pg_upgrade/t/002_pg_upgrade.pl | 18 ++++++++++++------
src/test/perl/PostgreSQL/Test/Cluster.pm | 8 ++++----
2 files changed, 16 insertions(+), 10 deletions(-)
diff --git a/src/bin/pg_upgrade/t/002_pg_upgrade.pl b/src/bin/pg_upgrade/t/002_pg_upgrade.pl
index 4a79a9e13d9..0f6b7afd78e 100644
--- a/src/bin/pg_upgrade/t/002_pg_upgrade.pl
+++ b/src/bin/pg_upgrade/t/002_pg_upgrade.pl
@@ -63,8 +63,8 @@ sub filter_dump
return $dump_file_filtered;
}
-# The test of pg_upgrade requires two clusters, an old one and a new one
-# that gets upgraded. Before running the upgrade, a logical dump of the
+# The test of pg_upgrade requires two clusters, an old one and a new one.
+# Before running the upgrade, a logical dump of the
# old cluster is taken, and a second logical dump of the new one is taken
# after the upgrade. The upgrade test passes if there are no differences
# in these two dumps.
@@ -99,7 +99,7 @@ $node_params{extra} = [ '--wal-segsize', '1', '--allow-group-access' ]
$oldnode->init(%node_params);
$oldnode->start;
-# The default location of the source code is the root of this directory.
+# The location of the source code is the root of this directory.
my $srcdir = abs_path("../../..");
# Set up the data of the old instance with a dump or pg_regress.
@@ -165,6 +165,12 @@ else
is($rc, 0, 'regression tests pass');
}
+# Initialize a new node for the upgrade.
+my $newnode = PostgreSQL::Test::Cluster->new('new_node');
+$newnode->init(xextra => [ '--wal-segsize', '1', '--allow-group-access' ]);
+my $newbindir = $newnode->config_data('--bindir');
+my $oldbindir = $oldnode->config_data('--bindir');
+
# Before dumping, get rid of objects not existing or not supported in later
# versions. This depends on the version of the old server used, and matters
# only if different major versions are used for the dump.
@@ -172,11 +178,11 @@ if (defined($ENV{oldinstall}))
{
# Note that upgrade_adapt.sql from the new version is used, to
# cope with an upgrade to this version.
- $oldnode->command_ok(
+ $newnode->command_ok(
[
- 'psql', '-X',
+ "$newbindir/psql", '-X',
'-f', "$srcdir/src/bin/pg_upgrade/upgrade_adapt.sql",
- 'regression'
+ '-d', $oldnode->connstr('regression'),
],
'ran adapt script');
}
diff --git a/src/test/perl/PostgreSQL/Test/Cluster.pm b/src/test/perl/PostgreSQL/Test/Cluster.pm
index c8c7bc5045a..45cc4d74779 100644
--- a/src/test/perl/PostgreSQL/Test/Cluster.pm
+++ b/src/test/perl/PostgreSQL/Test/Cluster.pm
@@ -476,7 +476,7 @@ sub init
mkdir $self->archive_dir;
PostgreSQL::Test::Utils::system_or_bail('initdb', '-D', $pgdata, '-A',
- 'trust', '-N', @{ $params{extra} });
+ 'trust', @{ $params{extra} });
PostgreSQL::Test::Utils::system_or_bail($ENV{PG_REGRESS},
'--config-auth', $pgdata, @{ $params{auth_extra} });
@@ -486,8 +486,8 @@ sub init
print $conf "restart_after_crash = off\n";
print $conf "log_line_prefix = '%m [%p] %q%a '\n";
print $conf "log_statement = all\n";
- print $conf "log_replication_commands = on\n";
- print $conf "wal_retrieve_retry_interval = '500ms'\n";
+ #print $conf "log_replication_commands = on\n";
+ #print $conf "wal_retrieve_retry_interval = '500ms'\n";
# If a setting tends to affect whether tests pass or fail, print it after
# TEMP_CONFIG. Otherwise, print it before TEMP_CONFIG, thereby permitting
@@ -827,7 +827,7 @@ sub start
# compatibility with older versions.
$ret = PostgreSQL::Test::Utils::system_log(
'pg_ctl', '-w', '-D', $self->data_dir,
- '-l', $self->logfile, '-o', "--cluster-name=$name",
+ '-l', $self->logfile, # '-o', "--cluster-name=$name",
'start');
if ($ret != 0)
--
2.17.1
