Hi,
Am Freitag, den 19.10.2018, 22:50 +0900 schrieb Michael Paquier:
> On Wed, Oct 17, 2018 at 05:30:05PM -0400, Andrew Dunstan wrote:
> Thanks. This is now committed after some tweaks to the comments, a bit
> earlier than I thought first.
I found an issue with this [d55241af7, "Use whitelist to choose files
scanned with pg_verify_checksums"] commit, namely, it makes
pg_verify_checksums no longer scan non-default tablespaces. So if you
have all of your data in tablespaces, it will more-or-less immediately
return with success.
I've extended the test suite to induce corruption in a table located in
a non-default tablespace, see the attached patch.
If fails like this, i.e. does not detect the corruption:
t/002_actions.pl .. 14/42
# Failed test 'fails with corrupted data in non-default tablespace status
(got 0 vs expected 1)'
# at t/002_actions.pl line 87.
# Failed test 'fails with corrupted data in non-default tablespace stdout
/(?^:Bad checksums:.*1)/'
# at t/002_actions.pl line 87.
# 'Checksum scan completed
# Data checksum version: 1
# Files scanned: 1102
# Blocks scanned: 2861
# Bad checksums: 0
# '
# doesn't match '(?^:Bad checksums:.*1)'
# Failed test 'fails with corrupted data in non-default tablespace stderr
/(?^:checksum verification failed)/'
# at t/002_actions.pl line 87.
# ''
# doesn't match '(?^:checksum verification failed)'
# Looks like you failed 3 tests of 42.
t/002_actions.pl .. Dubious, test returned 3 (wstat 768, 0x300)
Failed 3/42 subtests
The problem is that "PG_12_201811201" is not considered a valid relation
file by isRelFileName(), so it skips it and the rest of the tablespace.
I had a quick look at fixing this but did not manage to immediately come
up with a solution, so posting here for now.
Michael
--
Michael Banck
Projektleiter / Senior Berater
Tel.: +49 2166 9901-171
Fax: +49 2166 9901-100
Email: michael.ba...@credativ.de
credativ GmbH, HRB Mönchengladbach 12080
USt-ID-Nummer: DE204566209
Trompeterallee 108, 41189 Mönchengladbach
Geschäftsführung: Dr. Michael Meskes, Jörg Folz, Sascha Heuer
Unser Umgang mit personenbezogenen Daten unterliegt
folgenden Bestimmungen: https://www.credativ.de/datenschutz
diff --git a/src/bin/pg_verify_checksums/t/002_actions.pl b/src/bin/pg_verify_checksums/t/002_actions.pl
index 0e1725d9f2..fd64de050e 100644
--- a/src/bin/pg_verify_checksums/t/002_actions.pl
+++ b/src/bin/pg_verify_checksums/t/002_actions.pl
@@ -5,7 +5,7 @@ use strict;
use warnings;
use PostgresNode;
use TestLib;
-use Test::More tests => 36;
+use Test::More tests => 42;
# Initialize node with checksums enabled.
my $node = get_new_node('node_checksum');
@@ -54,31 +54,7 @@ command_fails(['pg_verify_checksums', '-D', $pgdata],
"fails with online cluster");
# Create table to corrupt and get its relfilenode
-$node->safe_psql('postgres',
- "SELECT a INTO corrupt1 FROM generate_series(1,10000) AS a;
- ALTER TABLE corrupt1 SET (autovacuum_enabled=false);");
-
-my $file_corrupted = $node->safe_psql('postgres',
- "SELECT pg_relation_filepath('corrupt1')");
-my $relfilenode_corrupted = $node->safe_psql('postgres',
- "SELECT relfilenode FROM pg_class WHERE relname = 'corrupt1';");
-
-# Set page header and block size
-my $pageheader_size = 24;
-my $block_size = $node->safe_psql('postgres', 'SHOW block_size;');
-$node->stop;
-
-# Checksums are correct for single relfilenode as the table is not
-# corrupted yet.
-command_ok(['pg_verify_checksums', '-D', $pgdata,
- '-r', $relfilenode_corrupted],
- "succeeds for single relfilenode with offline cluster");
-
-# Time to create some corruption
-open my $file, '+<', "$pgdata/$file_corrupted";
-seek($file, $pageheader_size, 0);
-syswrite($file, '\0\0\0\0\0\0\0\0\0');
-close $file;
+my $relfilenode_corrupted = create_corruption($node, 'corrupt1', 'pg_default');
# Global checksum checks fail
$node->command_checks_all([ 'pg_verify_checksums', '-D', $pgdata],
@@ -95,6 +71,72 @@ $node->command_checks_all([ 'pg_verify_checksums', '-D', $pgdata, '-r',
[qr/checksum verification failed/],
'fails for corrupted data on single relfilenode');
+# Drop corrupt table again and make sure there is no more corruption
+$node->start;
+$node->safe_psql('postgres', 'DROP TABLE corrupt1;');
+$node->stop;
+$node->command_ok(['pg_verify_checksums', '-D', $pgdata],
+ 'succeeds again: '.$node->data_dir);
+
+# Create table to corrupt in a non-default tablespace and get its relfilenode
+my $tablespace_dir = $node->data_dir."/../ts_corrupt_dir";
+mkdir ($tablespace_dir);
+$node->start;
+$node->safe_psql('postgres', "CREATE TABLESPACE ts_corrupt LOCATION '".$tablespace_dir."';");
+$relfilenode_corrupted = create_corruption($node, 'corrupt2', 'ts_corrupt');
+$node->command_checks_all([ 'pg_verify_checksums', '-D', $pgdata],
+ 1,
+ [qr/Bad checksums:.*1/],
+ [qr/checksum verification failed/],
+ 'fails with corrupted data in non-default tablespace');
+
+# Drop corrupt table again and make sure there is no more corruption
+$node->start;
+$node->safe_psql('postgres', 'DROP TABLE corrupt2;');
+$node->stop;
+$node->command_ok(['pg_verify_checksums', '-D', $pgdata],
+ 'succeeds again');
+
+# Utility routine to create a table with corrupted checksums.
+# It stops the node (if running), and starts it again.
+sub create_corruption
+{
+ my $node = shift;
+ my $table = shift;
+ my $tablespace = shift;
+
+ $node->safe_psql('postgres',
+ "SELECT a INTO ".$table." FROM generate_series(1,10000) AS a;
+ ALTER TABLE ".$table." SET (autovacuum_enabled=false);");
+
+ $node->safe_psql('postgres',
+ "ALTER TABLE ".$table." SET TABLESPACE ".$tablespace.";");
+
+ my $file_corrupted = $node->safe_psql('postgres',
+ "SELECT pg_relation_filepath('".$table."');");
+ my $relfilenode_corrupted = $node->safe_psql('postgres',
+ "SELECT relfilenode FROM pg_class WHERE relname = '".$table."';");
+
+ # Set page header and block size
+ my $pageheader_size = 24;
+ my $block_size = $node->safe_psql('postgres', 'SHOW block_size;');
+ $node->stop;
+
+ # Checksums are correct for single relfilenode as the table is not
+ # corrupted yet.
+ command_ok(['pg_verify_checksums', '-D', $pgdata,
+ '-r', $relfilenode_corrupted],
+ "succeeds for single relfilenode with offline cluster");
+
+ # Time to create some corruption
+ open my $file, '+<', "$pgdata/$file_corrupted";
+ seek($file, $pageheader_size, 0);
+ syswrite($file, '\0\0\0\0\0\0\0\0\0');
+ close $file;
+
+ return $relfilenode_corrupted;
+}
+
# Utility routine to check that pg_verify_checksums is able to detect
# correctly-named relation files filled with some corrupted data.
sub fail_corrupt
