Move SQL tests of pg_stat_io for WAL data to recovery test 029_stats_restart

Three tests in the main regression test suite are proving to not be
portable across multiple runs on a deployed cluster as stats of
pg_stat_io are reset.  Problems happen for tests on:
- Writes of WAL in the init context, when creating a WAL segment.
- Syncs of WAL in the init context, when creating a WAL segment.
- Reads of WAL in the normal context, requiring a WAL record to be read.
For a `make check`, this could rely on the checkpoint record read by the
startup process when starting the cluster, something that is not going
to work for a deployed node.

Two of the three tests are moved to the recovery TAP test
029_stats_restart, where we already check the consistency of stats
data.  The test for syncs is dropped as TAP can run with fsync=off.  The
other two are checked with some data from a freshly-initialized cluster.

Per discussion with Tom Lane, Bertrand Drouvot and Nazir Bilal Yavuz.

Discussion: https://postgr.es/m/915687.1738780322@sss.pgh.pa.us

3 files changed, 33 insertions, 42 deletions

diff --git a/src/test/recovery/t/029_stats_restart.pl b/src/test/recovery/t/029_stats_restart.pl
index be626059177..021e2bf361f 100644
--- a/src/test/recovery/t/029_stats_restart.pl
+++ b/src/test/recovery/t/029_stats_restart.pl
@@ -18,6 +18,21 @@ $node->start;
 my $connect_db = 'postgres';
 my $db_under_test = 'test';
 
+my $sect = "startup";
+
+# Check some WAL statistics after a fresh startup.  The startup process
+# should have done WAL reads, and initialization some WAL writes.
+my $standalone_io_stats = io_stats('init', 'wal', 'standalone backend');
+my $startup_io_stats = io_stats('normal', 'wal', 'startup');
+cmp_ok(
+	'0', '<',
+	$standalone_io_stats->{writes},
+	"$sect: increased standalone backend IO writes");
+cmp_ok(
+	'0', '<',
+	$startup_io_stats->{reads},
+	"$sect: increased startup IO reads");
+
 # create test objects
 $node->safe_psql($connect_db, "CREATE DATABASE $db_under_test");
 $node->safe_psql($db_under_test,
@@ -39,7 +54,7 @@ my $tableoid = $node->safe_psql($db_under_test,
 trigger_funcrel_stat();
 
 # verify stats objects exist
-my $sect = "initial";
+$sect = "initial";
 is(have_stats('database', $dboid, 0), 't', "$sect: db stats do exist");
 is(have_stats('function', $dboid, $funcoid),
 	't', "$sect: function stats do exist");
@@ -342,3 +357,20 @@ sub wal_stats
 
 	return \%results;
 }
+
+sub io_stats
+{
+	my ($context, $object, $backend_type) = @_;
+	my %results;
+
+	$results{writes} = $node->safe_psql(
+		$connect_db, qq{SELECT writes FROM pg_stat_io
+  WHERE context = '$context' AND object = '$object' AND
+    backend_type = '$backend_type'});
+	$results{reads} = $node->safe_psql(
+		$connect_db, qq{SELECT reads FROM pg_stat_io
+  WHERE context = '$context' AND object = '$object' AND
+    backend_type = '$backend_type'});
+
+	return \%results;
+}
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 9a02481ee7e..7d91f047bb3 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -862,33 +862,6 @@ WHERE pg_stat_get_backend_pid(beid) = pg_backend_pid();
  t
 (1 row)
 
--- Test pg_stat_io for WAL in an init context, that should do writes
--- and syncs.
-SELECT sum(writes) AS writes, sum(fsyncs) AS fsyncs
-  FROM pg_stat_io
-  WHERE context = 'init' AND object = 'wal' \gset io_sum_wal_init_
-SELECT :io_sum_wal_init_writes > 0;
- ?column? 
-----------
- t
-(1 row)
-
-SELECT current_setting('fsync') = 'off'
-  OR :io_sum_wal_init_fsyncs > 0;
- ?column? 
-----------
- t
-(1 row)
-
--- Test pg_stat_io for WAL in a normal context, that should do reads as well.
-SELECT SUM(reads) > 0
-  FROM pg_stat_io
-  WHERE context = 'normal' AND object = 'wal';
- ?column? 
-----------
- t
-(1 row)
-
 -----
 -- Test that resetting stats works for reset timestamp
 -----
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index 901e7bd56e3..11628ebc8a1 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -442,20 +442,6 @@ SELECT (current_schemas(true))[1] = ('pg_temp_' || beid::text) AS match
 FROM pg_stat_get_backend_idset() beid
 WHERE pg_stat_get_backend_pid(beid) = pg_backend_pid();
 
--- Test pg_stat_io for WAL in an init context, that should do writes
--- and syncs.
-SELECT sum(writes) AS writes, sum(fsyncs) AS fsyncs
-  FROM pg_stat_io
-  WHERE context = 'init' AND object = 'wal' \gset io_sum_wal_init_
-SELECT :io_sum_wal_init_writes > 0;
-SELECT current_setting('fsync') = 'off'
-  OR :io_sum_wal_init_fsyncs > 0;
-
--- Test pg_stat_io for WAL in a normal context, that should do reads as well.
-SELECT SUM(reads) > 0
-  FROM pg_stat_io
-  WHERE context = 'normal' AND object = 'wal';
-
 -----
 -- Test that resetting stats works for reset timestamp
 -----