From c0d58f1273dee6bd5f4a907c16d88b1cb69cc958 Mon Sep 17 00:00:00 2001 From: Mark Dilger Date: Wed, 6 Jan 2021 15:47:08 -0800 Subject: [PATCH v3 9/9] Adding contrib module pg_amcheck Adding new contrib module pg_amcheck, which is a command line interface for running amcheck's verifications against tables and indexes. --- contrib/Makefile | 1 + contrib/pg_amcheck/Makefile | 28 + contrib/pg_amcheck/pg_amcheck.c | 884 +++++++++++++++++++++ contrib/pg_amcheck/pg_amcheck.control | 5 + contrib/pg_amcheck/t/001_basic.pl | 9 + contrib/pg_amcheck/t/002_nonesuch.pl | 60 ++ contrib/pg_amcheck/t/003_check.pl | 248 ++++++ contrib/pg_amcheck/t/004_verify_heapam.pl | 496 ++++++++++++ contrib/pg_amcheck/t/005_opclass_damage.pl | 52 ++ doc/src/sgml/contrib.sgml | 1 + doc/src/sgml/filelist.sgml | 1 + doc/src/sgml/pgamcheck.sgml | 493 ++++++++++++ 12 files changed, 2278 insertions(+) create mode 100644 contrib/pg_amcheck/Makefile create mode 100644 contrib/pg_amcheck/pg_amcheck.c create mode 100644 contrib/pg_amcheck/pg_amcheck.control create mode 100644 contrib/pg_amcheck/t/001_basic.pl create mode 100644 contrib/pg_amcheck/t/002_nonesuch.pl create mode 100644 contrib/pg_amcheck/t/003_check.pl create mode 100644 contrib/pg_amcheck/t/004_verify_heapam.pl create mode 100644 contrib/pg_amcheck/t/005_opclass_damage.pl create mode 100644 doc/src/sgml/pgamcheck.sgml diff --git a/contrib/Makefile b/contrib/Makefile index 7a4866e338..0fd4125902 100644 --- a/contrib/Makefile +++ b/contrib/Makefile @@ -30,6 +30,7 @@ SUBDIRS = \ old_snapshot \ pageinspect \ passwordcheck \ + pg_amcheck \ pg_buffercache \ pg_freespacemap \ pg_prewarm \ diff --git a/contrib/pg_amcheck/Makefile b/contrib/pg_amcheck/Makefile new file mode 100644 index 0000000000..74554b9e8d --- /dev/null +++ b/contrib/pg_amcheck/Makefile @@ -0,0 +1,28 @@ +# contrib/pg_amcheck/Makefile + +PGFILEDESC = "pg_amcheck - detects corruption within database relations" +PGAPPICON = win32 + +PROGRAM = pg_amcheck +OBJS = \ + $(WIN32RES) \ + pg_amcheck.o + +REGRESS_OPTS += --load-extension=amcheck --load-extension=pageinspect +EXTRA_INSTALL += contrib/amcheck contrib/pageinspect + +TAP_TESTS = 1 + +PG_CPPFLAGS = -I$(libpq_srcdir) +PG_LIBS_INTERNAL = -L$(top_builddir)/src/fe_utils -lpgfeutils $(libpq_pgport) + +ifdef USE_PGXS +PG_CONFIG = pg_config +PGXS := $(shell $(PG_CONFIG) --pgxs) +include $(PGXS) +else +subdir = contrib/pg_amcheck +top_builddir = ../.. +include $(top_builddir)/src/Makefile.global +include $(top_srcdir)/contrib/contrib-global.mk +endif diff --git a/contrib/pg_amcheck/pg_amcheck.c b/contrib/pg_amcheck/pg_amcheck.c new file mode 100644 index 0000000000..992615ab3b --- /dev/null +++ b/contrib/pg_amcheck/pg_amcheck.c @@ -0,0 +1,884 @@ +/*------------------------------------------------------------------------- + * + * pg_amcheck.c + * Detects corruption within database relations. + * + * Copyright (c) 2017-2020, PostgreSQL Global Development Group + * + * IDENTIFICATION + * contrib/pg_amcheck/pg_amcheck.c + * + *------------------------------------------------------------------------- + */ +#include "postgres_fe.h" + +#include "catalog/pg_am.h" +#include "catalog/pg_class.h" +#include "common/logging.h" +#include "common/username.h" +#include "common/connect.h" +#include "common/string.h" +#include "fe_utils/exit_utils.h" +#include "fe_utils/option_utils.h" +#include "fe_utils/print.h" +#include "fe_utils/query_utils.h" +#include "fe_utils/simple_list.h" +#include "fe_utils/string_utils.h" +#include "pg_getopt.h" +#include "storage/block.h" + +typedef struct ConnectOptions +{ + char *dbname; + char *host; + char *port; + char *username; +} ConnectOptions; + +typedef enum trivalue +{ + TRI_DEFAULT, + TRI_NO, + TRI_YES +} trivalue; + +typedef struct +{ + bool notty; /* stdin or stdout is not a tty (as determined + * on startup) */ + trivalue getPassword; /* prompt for a username and password */ + const char *progname; /* in case you renamed pg_amcheck */ + bool strict_names; /* The specified names/patterns should to + * match at least one entity */ + bool on_error_stop; /* The checking of each table should stop + * after the first corrupt page is found. */ + bool skip_frozen; /* Do not check pages marked all frozen */ + bool skip_visible; /* Do not check pages marked all visible */ + bool check_indexes; /* Check btree indexes */ + bool check_toast; /* Check associated toast tables and indexes */ + bool check_corrupt; /* Check indexes even if table is corrupt */ + bool heapallindexed; /* Perform index to table reconciling checks */ + bool rootdescend; /* Perform index rootdescend checks */ + bool verbose; + long startblock; /* Block number where checking begins */ + long endblock; /* Block number where checking ends, inclusive */ +} AmCheckSettings; + +static AmCheckSettings settings; + +/* Connection to backend */ +static PGconn *conn; + +/* + * Object inclusion/exclusion lists + * + * The string lists record the patterns given by command-line switches, + * which we then convert to lists of Oids of matching objects. + */ +static SimpleStringList schema_include_patterns = {NULL, NULL}; +static SimpleOidList schema_include_oids = {NULL, NULL}; +static SimpleStringList schema_exclude_patterns = {NULL, NULL}; +static SimpleOidList schema_exclude_oids = {NULL, NULL}; + +static SimpleStringList table_include_patterns = {NULL, NULL}; +static SimpleOidList table_include_oids = {NULL, NULL}; +static SimpleStringList table_exclude_patterns = {NULL, NULL}; +static SimpleOidList table_exclude_oids = {NULL, NULL}; + +static SimpleStringList index_include_patterns = {NULL, NULL}; +static SimpleOidList index_include_oids = {NULL, NULL}; +static SimpleStringList index_exclude_patterns = {NULL, NULL}; +static SimpleOidList index_exclude_oids = {NULL, NULL}; + +/* + * Cstring list of relkinds which qualify as tables for our purposes when + * processing table inclusion or exclusion patterns. + */ +#define TABLE_RELKIND_LIST CppAsString2(RELKIND_RELATION) ", " \ + CppAsString2(RELKIND_MATVIEW) ", " \ + CppAsString2(RELKIND_PARTITIONED_TABLE) + +#define INDEX_RELKIND_LIST CppAsString2(RELKIND_INDEX) + +/* + * List of main tables to be checked, compiled from above lists, and + * corresponding list of toast tables. The lists should always be + * the same length, with InvalidOid in the toastlist for main relations + * without a corresponding toast relation. + */ +static SimpleOidList mainlist = {NULL, NULL}; +static SimpleOidList toastlist = {NULL, NULL}; + + +/* + * Functions for running the various corruption checks. + */ +static void check_tables(SimpleOidList *checklist); +static uint64 check_table(Oid tbloid, long startblock, long endblock, + bool on_error_stop, bool check_toast); +static uint64 check_indexes(Oid tbloid, const SimpleOidList *include_oids, + const SimpleOidList *exclude_oids); +static uint64 check_index(const char *idxoid, const char *idxname, + const char *tblname); + +/* + * Functions implementing standard command line behaviors. + */ +static void parse_cli_options(int argc, char *argv[], + ConnectOptions *connOpts); +static void usage(void); +static void showVersion(void); +static void NoticeProcessor(void *arg, const char *message); + +static void get_table_check_list(const SimpleOidList *include_nsp, + const SimpleOidList *exclude_nsp, + const SimpleOidList *include_tbl, + const SimpleOidList *exclude_tbl); + +#define EXIT_BADCONN 2 + +int +main(int argc, char **argv) +{ + ConnectOptions connOpts; + bool have_password = false; + char *password = NULL; + bool new_pass; + + pg_logging_init(argv[0]); + set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("pg_amcheck")); + + if (argc > 1) + { + if ((strcmp(argv[1], "-?") == 0) || + (argc == 2 && (strcmp(argv[1], "--help") == 0))) + { + usage(); + exit(EXIT_SUCCESS); + } + if (strcmp(argv[1], "--version") == 0 || strcmp(argv[1], "-V") == 0) + { + showVersion(); + exit(EXIT_SUCCESS); + } + } + + memset(&settings, 0, sizeof(settings)); + settings.progname = get_progname(argv[0]); + + conn = NULL; + setDecimalLocale(); + + settings.notty = (!isatty(fileno(stdin)) || !isatty(fileno(stdout))); + + settings.getPassword = TRI_DEFAULT; + + settings.startblock = -1; + settings.endblock = -1; + + /* + * Default behaviors for user settable options. Note that these default + * to doing all the safe checks and none of the unsafe ones, on the theory + * that if a user says "pg_amcheck mydb" without specifying any additional + * options, we should check everything we know how to check without + * risking any backend aborts. + */ + + settings.on_error_stop = false; + settings.skip_frozen = false; + settings.skip_visible = false; + + /* Index checking options */ + settings.check_indexes = true; + settings.check_corrupt = true; + settings.heapallindexed = false; + settings.rootdescend = false; + + /* + * Reconciling toasted attributes from the main table against the toast + * table can crash the backend if the toast table or index are corrupt. + * We can optionally check the toast table and then the toast index prior + * to checking the main table, but if the toast table or index are + * concurrently corrupted after we conclude they are valid, the check of + * the main table can crash the backend. The oneous is on any caller who + * enables this option to make certain the environment is sufficiently + * stable that concurrent corruptions of the toast is not possible. + */ + settings.check_toast = false; + + parse_cli_options(argc, argv, &connOpts); + + if (settings.getPassword == TRI_YES) + { + /* + * We can't be sure yet of the username that will be used, so don't + * offer a potentially wrong one. Typical uses of this option are + * noninteractive anyway. + */ + password = simple_prompt("Password: ", false); + have_password = true; + } + + /* loop until we have a password if requested by backend */ + do + { +#define ARRAY_SIZE 8 + const char **keywords = pg_malloc(ARRAY_SIZE * sizeof(*keywords)); + const char **values = pg_malloc(ARRAY_SIZE * sizeof(*values)); + + keywords[0] = "host"; + values[0] = connOpts.host; + keywords[1] = "port"; + values[1] = connOpts.port; + keywords[2] = "user"; + values[2] = connOpts.username; + keywords[3] = "password"; + values[3] = have_password ? password : NULL; + keywords[4] = "dbname"; /* see do_connect() */ + if (connOpts.dbname == NULL) + { + if (getenv("PGDATABASE")) + values[4] = getenv("PGDATABASE"); + else if (getenv("PGUSER")) + values[4] = getenv("PGUSER"); + else + values[4] = "postgres"; + } + else + values[4] = connOpts.dbname; + keywords[5] = "fallback_application_name"; + values[5] = settings.progname; + keywords[6] = "client_encoding"; + values[6] = (settings.notty || + getenv("PGCLIENTENCODING")) ? NULL : "auto"; + keywords[7] = NULL; + values[7] = NULL; + + new_pass = false; + conn = PQconnectdbParams(keywords, values, true); + if (!conn) + fatal("could not connect to database %s: out of memory", values[4]); + + free(keywords); + free(values); + + if (PQstatus(conn) == CONNECTION_BAD && + PQconnectionNeedsPassword(conn) && + !have_password && + settings.getPassword != TRI_NO) + { + /* + * Before closing the old PGconn, extract the user name that was + * actually connected with. + */ + const char *realusername = PQuser(conn); + char *password_prompt; + + if (realusername && realusername[0]) + password_prompt = psprintf("Password for user %s: ", + realusername); + else + password_prompt = pg_strdup("Password: "); + PQfinish(conn); + + password = simple_prompt(password_prompt, false); + free(password_prompt); + have_password = true; + new_pass = true; + } + + if (!new_pass && PQstatus(conn) == CONNECTION_BAD) + { + pg_log_error("could not connect to database %s: %s", + values[4], PQerrorMessage(conn)); + PQfinish(conn); + exit(1); + } + } while (new_pass); + + if (settings.verbose) + PQsetErrorVerbosity(conn, PQERRORS_VERBOSE); + + /* + * Expand schema, table, and index exclusion patterns, if any. Note that + * non-matching exclusion patterns are not an error, even when + * --strict-names was specified. + */ + expand_schema_name_patterns(conn, + &schema_exclude_patterns, + NULL, + &schema_exclude_oids, + false); + expand_rel_name_patterns(conn, + &table_exclude_patterns, + NULL, + NULL, + TABLE_RELKIND_LIST, + AMTYPE_TABLE, + &table_exclude_oids, + false, + false); + expand_rel_name_patterns(conn, + &index_exclude_patterns, + NULL, + NULL, + INDEX_RELKIND_LIST, + AMTYPE_INDEX, + &index_exclude_oids, + false, + false); + + /* Expand schema selection patterns into Oid lists */ + if (schema_include_patterns.head != NULL) + { + expand_schema_name_patterns(conn, + &schema_include_patterns, + &schema_exclude_oids, + &schema_include_oids, + settings.strict_names); + if (schema_include_oids.head == NULL) + fatal("no matching schemas were found"); + } + + /* Expand table selection patterns into Oid lists */ + if (table_include_patterns.head != NULL) + { + expand_rel_name_patterns(conn, + &table_include_patterns, + &schema_exclude_oids, + &table_exclude_oids, + TABLE_RELKIND_LIST, + AMTYPE_TABLE, + &table_include_oids, + settings.strict_names, + false); + if (table_include_oids.head == NULL) + fatal("no matching tables were found"); + } + + /* Expand index selection patterns into Oid lists */ + if (index_include_patterns.head != NULL) + { + expand_rel_name_patterns(conn, + &index_include_patterns, + &schema_exclude_oids, + &index_exclude_oids, + INDEX_RELKIND_LIST, + AMTYPE_INDEX, + &index_include_oids, + settings.strict_names, + false); + if (index_include_oids.head == NULL) + fatal("no matching indexes were found"); + } + + /* + * Compile list of all tables to be checked based on namespace and table + * includes and excludes. + */ + get_table_check_list(&schema_include_oids, &schema_exclude_oids, + &table_include_oids, &table_exclude_oids); + + PQsetNoticeProcessor(conn, NoticeProcessor, NULL); + + if (settings.check_toast) + check_tables(&toastlist); + check_tables(&mainlist); + + return 0; +} + +/* + * Check each table from the given checklist per the user specified options. + */ +static void +check_tables(SimpleOidList *checklist) +{ + const SimpleOidListCell *cell; + + for (cell = checklist->head; cell; cell = cell->next) + { + uint64 corruptions = 0; + + if (!OidIsValid(cell->val)) + continue; + + corruptions = check_table(cell->val, + settings.startblock, + settings.endblock, + settings.on_error_stop, + settings.check_toast); + + if (settings.check_indexes) + { + /* Optionally skip the index checks for a corrupt table. */ + if (corruptions && !settings.check_corrupt) + continue; + + corruptions += check_indexes(cell->val, + &index_include_oids, + &index_exclude_oids); + } + } +} + +/* + * Checks the given table for corruption, returning the number of corruptions + * detected and printed to the user. + */ +static uint64 +check_table(Oid tbloid, long startblock, long endblock, + bool on_error_stop, bool check_toast) +{ + PQExpBuffer querybuf; + PGresult *res; + int i; + char *skip; + char *toast; + const char *stop; + uint64 corruption_cnt = 0; + + if (settings.skip_frozen) + skip = pg_strdup("'all frozen'"); + else if (settings.skip_visible) + skip = pg_strdup("'all visible'"); + else + skip = pg_strdup("'none'"); + stop = on_error_stop ? "true" : "false"; + toast = check_toast ? "true" : "false"; + + querybuf = createPQExpBuffer(); + + appendPQExpBuffer(querybuf, + "SELECT c.relname, v.blkno, v.offnum, v.attnum, v.msg " + "FROM public.verify_heapam(" + "relation := %u, " + "on_error_stop := %s, " + "skip := %s, " + "check_toast := %s, ", + tbloid, stop, skip, toast); + if (startblock < 0) + appendPQExpBuffer(querybuf, "startblock := NULL, "); + else + appendPQExpBuffer(querybuf, "startblock := %ld, ", startblock); + + if (endblock < 0) + appendPQExpBuffer(querybuf, "endblock := NULL"); + else + appendPQExpBuffer(querybuf, "endblock := %ld", endblock); + + appendPQExpBuffer(querybuf, ") v, pg_catalog.pg_class c " + "WHERE c.oid = %u", tbloid); + + res = PQexec(conn, querybuf->data); + if (PQresultStatus(res) == PGRES_TUPLES_OK && PQntuples(res) > 0) + { + corruption_cnt += PQntuples(res); + for (i = 0; i < PQntuples(res); i++) + { + if (!PQgetisnull(res, i, 3)) + printf("relation %s, block %s, offset %s, attribute %s\n %s\n", + PQgetvalue(res, i, 0), /* relname */ + PQgetvalue(res, i, 1), /* blkno */ + PQgetvalue(res, i, 2), /* offnum */ + PQgetvalue(res, i, 3), /* attnum */ + PQgetvalue(res, i, 4)); /* msg */ + else if (!PQgetisnull(res, i, 2)) + printf("relation %s, block %s, offset %s\n %s\n", + PQgetvalue(res, i, 0), /* relname */ + PQgetvalue(res, i, 1), /* blkno */ + PQgetvalue(res, i, 2), /* offnum */ + PQgetvalue(res, i, 4)); /* msg */ + else if (!PQgetisnull(res, i, 1)) + printf("relation %s, block %s\n %s\n", + PQgetvalue(res, i, 0), /* relname */ + PQgetvalue(res, i, 1), /* blkno */ + PQgetvalue(res, i, 4)); /* msg */ + else if (!PQgetisnull(res, i, 0)) + printf("relation %s\n %s\n", + PQgetvalue(res, i, 0), /* relname */ + PQgetvalue(res, i, 4)); /* msg */ + else + printf("%s\n", PQgetvalue(res, i, 4)); /* msg */ + } + } + else if (PQresultStatus(res) != PGRES_TUPLES_OK) + { + corruption_cnt++; + printf("relation with OID %u\n %s\n", tbloid, PQerrorMessage(conn)); + } + + PQclear(res); + destroyPQExpBuffer(querybuf); + return corruption_cnt; +} + +static uint64 +check_indexes(Oid tbloid, const SimpleOidList *include_oids, + const SimpleOidList *exclude_oids) +{ + PQExpBuffer querybuf; + PGresult *res; + int i; + uint64 corruption_cnt = 0; + + querybuf = createPQExpBuffer(); + appendPQExpBuffer(querybuf, + "SELECT i.indexrelid, ci.relname, ct.relname" + "\nFROM pg_catalog.pg_index i, pg_catalog.pg_class ci, " + "pg_catalog.pg_class ct" + "\nWHERE i.indexrelid = ci.oid" + "\n AND i.indrelid = ct.oid" + "\n AND ci.relam = %u" + "\n AND i.indrelid = %u", + BTREE_AM_OID, tbloid); + include_filter(querybuf, "i.indexrelid", include_oids); + exclude_filter(querybuf, "i.indexrelid", exclude_oids); + + res = PQexec(conn, querybuf->data); + if (PQresultStatus(res) == PGRES_TUPLES_OK) + { + for (i = 0; i < PQntuples(res); i++) + corruption_cnt += check_index(PQgetvalue(res, i, 0), + PQgetvalue(res, i, 1), + PQgetvalue(res, i, 2)); + } + else + { + corruption_cnt++; + printf("%s\n", PQerrorMessage(conn)); + } + + PQclear(res); + destroyPQExpBuffer(querybuf); + + return corruption_cnt; +} + +static uint64 +check_index(const char *idxoid, const char *idxname, const char *tblname) +{ + PQExpBuffer querybuf; + PGresult *res; + uint64 corruption_cnt = 0; + + querybuf = createPQExpBuffer(); + appendPQExpBuffer(querybuf, + "SELECT public.bt_index_parent_check('%s'::regclass, %s, %s)", + idxoid, + settings.heapallindexed ? "true" : "false", + settings.rootdescend ? "true" : "false"); + res = PQexec(conn, querybuf->data); + if (PQresultStatus(res) != PGRES_TUPLES_OK) + { + corruption_cnt++; + printf("index check failed for index %s of table %s:\n", + idxname, tblname); + printf("%s", PQerrorMessage(conn)); + } + + PQclear(res); + destroyPQExpBuffer(querybuf); + + return corruption_cnt; +} + +static void +parse_cli_options(int argc, char *argv[], ConnectOptions *connOpts) +{ + static struct option long_options[] = + { + {"check-toast", no_argument, NULL, 'z'}, + {"dbname", required_argument, NULL, 'd'}, + {"endblock", required_argument, NULL, 'e'}, + {"exclude-index", required_argument, NULL, 'I'}, + {"exclude-schema", required_argument, NULL, 'N'}, + {"exclude-table", required_argument, NULL, 'T'}, + {"heapallindexed", no_argument, NULL, 'a'}, + {"help", optional_argument, NULL, '?'}, + {"host", required_argument, NULL, 'h'}, + {"index", required_argument, NULL, 'i'}, + {"no-password", no_argument, NULL, 'w'}, + {"on-error-stop", no_argument, NULL, 'o'}, + {"password", no_argument, NULL, 'W'}, + {"port", required_argument, NULL, 'p'}, + {"rootdescend", no_argument, NULL, 'r'}, + {"schema", required_argument, NULL, 'n'}, + {"skip", required_argument, NULL, 'S'}, + {"skip-corrupt", no_argument, NULL, 'C'}, + {"skip-indexes", no_argument, NULL, 'X'}, + {"skip-toast", no_argument, NULL, 'Z'}, + {"startblock", required_argument, NULL, 'b'}, + {"strict-names", no_argument, NULL, 's'}, + {"table", required_argument, NULL, 't'}, + {"toast-endblock", required_argument, NULL, 'E'}, + {"toast-startblock", required_argument, NULL, 'B'}, + {"username", required_argument, NULL, 'U'}, + {"verbose", no_argument, NULL, 'v'}, + {"version", no_argument, NULL, 'V'}, + {NULL, 0, NULL, 0} + }; + + int optindex; + int c; + + memset(connOpts, 0, sizeof *connOpts); + + while ((c = getopt_long(argc, argv, "ab:Cd:e:h:i:I:n:N:op:rsS:t:T:U:vVwWXzZ?1", + long_options, &optindex)) != -1) + { + char *endptr; + switch (c) + { + case 'a': + settings.heapallindexed = true; + break; + case 'b': + settings.startblock = strtol(optarg, &endptr, 10); + if (*endptr != '\0') + fatal("relation starting block argument contains garbage characters"); + if (settings.startblock > (long)MaxBlockNumber) + fatal("relation starting block argument out of bounds"); + break; + case 'C': + settings.check_corrupt = false; + break; + case 'd': + connOpts->dbname = pg_strdup(optarg); + break; + case 'e': + settings.endblock = strtol(optarg, &endptr, 10); + if (*endptr != '\0') + fatal("relation ending block argument contains garbage characters"); + if (settings.endblock > (long)MaxBlockNumber) + fatal("relation ending block argument out of bounds"); + break; + case 'h': + connOpts->host = pg_strdup(optarg); + break; + case 'i': + simple_string_list_append(&index_include_patterns, optarg); + break; + case 'I': + simple_string_list_append(&index_exclude_patterns, optarg); + break; + case 'n': /* include schema(s) */ + simple_string_list_append(&schema_include_patterns, optarg); + break; + case 'N': /* exclude schema(s) */ + simple_string_list_append(&schema_exclude_patterns, optarg); + break; + case 'o': + settings.on_error_stop = true; + break; + case 'p': + connOpts->port = pg_strdup(optarg); + break; + case 's': + settings.strict_names = true; + break; + case 'S': + if (pg_strcasecmp(optarg, "all-visible") == 0) + { + settings.skip_visible = true; + settings.skip_frozen = false; + } + else if (pg_strcasecmp(optarg, "all-frozen") == 0) + { + settings.skip_frozen = true; + settings.skip_visible = false; + } + else + { + exit(EXIT_FAILURE); + pg_log_error("invalid skip option"); + } + break; + case 'r': + settings.rootdescend = true; + break; + case 't': /* include table(s) */ + simple_string_list_append(&table_include_patterns, optarg); + break; + case 'T': /* exclude table(s) */ + simple_string_list_append(&table_exclude_patterns, optarg); + break; + case 'U': + connOpts->username = pg_strdup(optarg); + break; + case 'V': + showVersion(); + exit(EXIT_SUCCESS); + case 'w': + settings.getPassword = TRI_NO; + break; + case 'W': + settings.getPassword = TRI_YES; + break; + case 'X': + settings.check_indexes = false; + break; + case 'v': + settings.verbose = true; + break; + case 'z': + settings.check_toast = true; + break; + case 'Z': + settings.check_toast = false; + break; + case '?': + if (optind <= argc && + strcmp(argv[optind - 1], "-?") == 0) + { + /* actual help option given */ + usage(); + exit(EXIT_SUCCESS); + } + else + { + /* getopt error (unknown option or missing argument) */ + goto unknown_option; + } + break; + case 1: + { + if (!optarg || strcmp(optarg, "options") == 0) + usage(); + else + goto unknown_option; + + exit(EXIT_SUCCESS); + } + break; + default: + unknown_option: + fprintf(stderr, "Try \"%s --help\" for more information.\n", + settings.progname); + exit(EXIT_FAILURE); + break; + } + } + + /* + * if we still have arguments, use it as the database name and username + */ + while (argc - optind >= 1) + { + if (!connOpts->dbname) + connOpts->dbname = argv[optind]; + else if (!connOpts->username) + connOpts->username = argv[optind]; + else + pg_log_warning("extra command-line argument \"%s\" ignored", + argv[optind]); + + optind++; + } + + if (settings.endblock >= 0 && settings.endblock < settings.startblock) + fatal("relation ending block argument precedes starting block argument"); +} + +/* + * usage + * + * print out command line arguments + */ +static void +usage(void) +{ + printf("pg_amcheck is the PostgreSQL command line frontend for the amcheck database corruption checker.\n"); + printf("\n"); + printf("Usage:\n"); + printf(" pg_amcheck [OPTION]... [DBNAME [USERNAME]]\n"); + printf("\n"); + printf("General options:\n"); + printf(" -V, --version output version information, then exit\n"); + printf(" -?, --help show this help, then exit\n"); + printf(" -s, --strict-names require include patterns to match at least one entity each\n"); + printf(" -o, --on-error-stop stop checking at end of first corrupt page\n"); + printf(" -v, --verbose output verbose messages\n"); + printf("\n"); + printf("Schema checking options:\n"); + printf(" -n, --schema=PATTERN check relations in the specified schema(s) only\n"); + printf(" -N, --exclude-schema=PATTERN do NOT check relations in the specified schema(s)\n"); + printf("\n"); + printf("Table checking options:\n"); + printf(" -t, --table=PATTERN check the specified table(s) only\n"); + printf(" -T, --exclude-table=PATTERN do NOT check the specified table(s)\n"); + printf(" -b, --startblock begin checking table(s) at the given starting block number\n"); + printf(" -e, --endblock check table(s) only up to the given ending block number\n"); + printf(" -S, --skip=OPTION do NOT check \"all-frozen\" or \"all-visible\" blocks\n"); + printf("\n"); + printf("TOAST table checking options:\n"); + printf(" -z, --check-toast check associated toast tables and toast indexes\n"); + printf(" -Z, --skip-toast do NOT check associated toast tables and toast indexes\n"); + printf("\n"); + printf("Index checking options:\n"); + printf(" -X, --skip-indexes do NOT check any btree indexes\n"); + printf(" -i, --index=PATTERN check the specified index(es) only\n"); + printf(" -I, --exclude-index=PATTERN do NOT check the specified index(es)\n"); + printf(" -C, --skip-corrupt do NOT check indexes if their associated table is corrupt\n"); + printf(" -a, --heapallindexed check index tuples against the table tuples\n"); + printf(" -r, --rootdescend search from the root page for each index tuple\n"); + printf("\n"); + printf("Connection options:\n"); + printf(" -d, --dbname=DBNAME database name to connect to\n"); + printf(" -h, --host=HOSTNAME database server host or socket directory\n"); + printf(" -p, --port=PORT database server port\n"); + printf(" -U, --username=USERNAME database user name\n"); + printf(" -w, --no-password never prompt for password\n"); + printf(" -W, --password force password prompt (should happen automatically)\n"); + printf("\n"); + printf("Report bugs to <%s>.\n", PACKAGE_BUGREPORT); + printf("%s home page: <%s>\n", PACKAGE_NAME, PACKAGE_URL); +} + +static void +showVersion(void) +{ + puts("pg_amcheck (PostgreSQL) " PG_VERSION); +} + +/* + * for backend Notice messages (INFO, WARNING, etc) + */ +static void +NoticeProcessor(void *arg, const char *message) +{ + (void) arg; /* not used */ + pg_log_info("%s", message); +} + +static void +get_table_check_list(const SimpleOidList *include_nsp, const SimpleOidList *exclude_nsp, + const SimpleOidList *include_tbl, const SimpleOidList *exclude_tbl) +{ + PQExpBuffer querybuf; + PGresult *res; + int i; + + querybuf = createPQExpBuffer(); + + appendPQExpBuffer(querybuf, + "SELECT c.oid, c.reltoastrelid" + "\nFROM pg_catalog.pg_class c, pg_catalog.pg_namespace n" + "\nWHERE n.oid OPERATOR(pg_catalog.=) c.relnamespace" + "\n AND c.relkind OPERATOR(pg_catalog.=) ANY(ARRAY[%s])\n", + TABLE_RELKIND_LIST); + include_filter(querybuf, "n.oid", include_nsp); + exclude_filter(querybuf, "n.oid", exclude_nsp); + include_filter(querybuf, "c.oid", include_tbl); + exclude_filter(querybuf, "c.oid", exclude_tbl); + + res = ExecuteSqlQuery(conn, querybuf->data, PGRES_TUPLES_OK); + for (i = 0; i < PQntuples(res); i++) + { + simple_oid_list_append(&mainlist, atooid(PQgetvalue(res, i, 0))); + simple_oid_list_append(&toastlist, atooid(PQgetvalue(res, i, 1))); + } + + PQclear(res); + destroyPQExpBuffer(querybuf); +} diff --git a/contrib/pg_amcheck/pg_amcheck.control b/contrib/pg_amcheck/pg_amcheck.control new file mode 100644 index 0000000000..395f368101 --- /dev/null +++ b/contrib/pg_amcheck/pg_amcheck.control @@ -0,0 +1,5 @@ +# pg_amcheck extension +comment = 'command-line tool for verifying relation integrity' +default_version = '1.3' +module_pathname = '$libdir/pg_amcheck' +relocatable = true diff --git a/contrib/pg_amcheck/t/001_basic.pl b/contrib/pg_amcheck/t/001_basic.pl new file mode 100644 index 0000000000..dfa0ae9e06 --- /dev/null +++ b/contrib/pg_amcheck/t/001_basic.pl @@ -0,0 +1,9 @@ +use strict; +use warnings; + +use TestLib; +use Test::More tests => 8; + +program_help_ok('pg_amcheck'); +program_version_ok('pg_amcheck'); +program_options_handling_ok('pg_amcheck'); diff --git a/contrib/pg_amcheck/t/002_nonesuch.pl b/contrib/pg_amcheck/t/002_nonesuch.pl new file mode 100644 index 0000000000..189f05ef0a --- /dev/null +++ b/contrib/pg_amcheck/t/002_nonesuch.pl @@ -0,0 +1,60 @@ +use strict; +use warnings; + +use PostgresNode; +use TestLib; +use Test::More tests => 14; + +# Test set-up +my ($node, $port); +$node = get_new_node('test'); +$node->init; +$node->start; +$port = $node->port; + +# Load the amcheck extension, upon which pg_amcheck depends +$node->safe_psql('postgres', q(CREATE EXTENSION amcheck)); + +######################################### +# Test connecting to a non-existent database + +command_fails_like( + [ 'pg_amcheck', '-p', "$port", 'qqq' ], + qr/\Qpg_amcheck: error: could not connect to database qqq: FATAL: database "qqq" does not exist\E/, + 'connecting to a non-existent database'); + +######################################### +# Test connecting with a non-existent user + +command_fails_like( + [ 'pg_amcheck', '-p', "$port", '-U=no_such_user', 'postgres' ], + qr/\Qpg_amcheck: error: could not connect to database postgres: FATAL: role "=no_such_user" does not exist\E/, + 'connecting with a non-existent user'); + +######################################### +# Test checking a non-existent schema, table, and patterns with --strict-names + +command_fails_like( + [ 'pg_amcheck', '-p', "$port", '-n', 'nonexistent' ], + qr/\Qpg_amcheck: error: no matching schemas were found\E/, + 'checking a non-existent schema'); + +command_fails_like( + [ 'pg_amcheck', '-p', "$port", '-t', 'nonexistent' ], + qr/\Qpg_amcheck: error: no matching tables were found\E/, + 'checking a non-existent table'); + +command_fails_like( + [ 'pg_amcheck', '-p', "$port", '--strict-names', '-n', 'nonexistent*' ], + qr/\Qpg_amcheck: error: no matching schemas were found for pattern\E/, + 'no matching schemas'); + +command_fails_like( + [ 'pg_amcheck', '-p', "$port", '--strict-names', '-t', 'nonexistent*' ], + qr/\Qpg_amcheck: error: no matching tables were found for pattern\E/, + 'no matching tables'); + +command_fails_like( + [ 'pg_amcheck', '-p', "$port", '--strict-names', '-i', 'nonexistent*' ], + qr/\Qpg_amcheck: error: no matching indexes were found for pattern\E/, + 'no matching indexes'); diff --git a/contrib/pg_amcheck/t/003_check.pl b/contrib/pg_amcheck/t/003_check.pl new file mode 100644 index 0000000000..30bbbdeddd --- /dev/null +++ b/contrib/pg_amcheck/t/003_check.pl @@ -0,0 +1,248 @@ +use strict; +use warnings; + +use PostgresNode; +use TestLib; +use Test::More tests => 45; + +my ($node, $port); + +# Returns the filesystem path for the named relation. +# +# Assumes the test node is running +sub relation_filepath($) +{ + my ($relname) = @_; + + my $pgdata = $node->data_dir; + my $rel = $node->safe_psql('postgres', + qq(SELECT pg_relation_filepath('$relname'))); + die "path not found for relation $relname" unless defined $rel; + return "$pgdata/$rel"; +} + +# Stops the test node, corrupts the first page of the named relation, and +# restarts the node. +# +# Assumes the node is running. +sub corrupt_first_page($) +{ + my ($relname) = @_; + my $relpath = relation_filepath($relname); + + $node->stop; + my $fh; + open($fh, '+<', $relpath); + binmode $fh; + seek($fh, 32, 0); + syswrite($fh, '\x77\x77\x77\x77', 500); + close($fh); + $node->start; +} + +# Stops the test node, unlinks the file from the filesystem that backs the +# relation, and restarts the node. +# +# Assumes the test node is running +sub remove_relation_file($) +{ + my ($relname) = @_; + my $relpath = relation_filepath($relname); + + $node->stop(); + unlink($relpath); + $node->start; +} + +# Test set-up +$node = get_new_node('test'); +$node->init; +$node->start; +$port = $node->port; + +# Load the amcheck extension, upon which pg_amcheck depends +$node->safe_psql('postgres', q(CREATE EXTENSION amcheck)); + +# Create schemas and tables for checking pg_amcheck's include +# and exclude schema and table command line options +$node->safe_psql('postgres', q( +-- We'll corrupt all indexes in s1 +CREATE SCHEMA s1; +CREATE TABLE s1.t1 (a TEXT); +CREATE TABLE s1.t2 (a TEXT); +CREATE INDEX i1 ON s1.t1(a); +CREATE INDEX i2 ON s1.t2(a); +INSERT INTO s1.t1 (a) (SELECT gs::TEXT FROM generate_series(1,10000) AS gs); +INSERT INTO s1.t2 (a) (SELECT gs::TEXT FROM generate_series(1,10000) AS gs); + +-- We'll corrupt all tables in s2 +CREATE SCHEMA s2; +CREATE TABLE s2.t1 (a TEXT); +CREATE TABLE s2.t2 (a TEXT); +CREATE INDEX i1 ON s2.t1(a); +CREATE INDEX i2 ON s2.t2(a); +INSERT INTO s2.t1 (a) (SELECT gs::TEXT FROM generate_series(1,10000) AS gs); +INSERT INTO s2.t2 (a) (SELECT gs::TEXT FROM generate_series(1,10000) AS gs); + +-- We'll corrupt all tables and indexes in s3 +CREATE SCHEMA s3; +CREATE TABLE s3.t1 (a TEXT); +CREATE TABLE s3.t2 (a TEXT); +CREATE INDEX i1 ON s3.t1(a); +CREATE INDEX i2 ON s3.t2(a); +INSERT INTO s3.t1 (a) (SELECT gs::TEXT FROM generate_series(1,10000) AS gs); +INSERT INTO s3.t2 (a) (SELECT gs::TEXT FROM generate_series(1,10000) AS gs); + +-- We'll leave everything in s4 uncorrupted +CREATE SCHEMA s4; +CREATE TABLE s4.t1 (a TEXT); +CREATE TABLE s4.t2 (a TEXT); +CREATE INDEX i1 ON s4.t1(a); +CREATE INDEX i2 ON s4.t2(a); +INSERT INTO s4.t1 (a) (SELECT gs::TEXT FROM generate_series(1,10000) AS gs); +INSERT INTO s4.t2 (a) (SELECT gs::TEXT FROM generate_series(1,10000) AS gs); +)); + +# Corrupt indexes in schema "s1" +remove_relation_file('s1.i1'); +corrupt_first_page('s1.i2'); + +# Corrupt tables in schema "s2" +remove_relation_file('s2.t1'); +corrupt_first_page('s2.t2'); + +# Corrupt tables and indexes in schema "s3" +remove_relation_file('s3.i1'); +corrupt_first_page('s3.i2'); +remove_relation_file('s3.t1'); +corrupt_first_page('s3.t2'); + +# Leave schema "s4" alone + + +# The pg_amcheck command itself should return a success exit status, even +# though tables and indexes are corrupt. An error code returned would mean the +# pg_amcheck command itself failed, for example because a connection to the +# database could not be established. +# +# For these checks, we're ignoring any corruption reported and focusing +# exclusively on the exit code from pg_amcheck. +# +$node->command_ok( + [ 'pg_amcheck', '-p', $port, 'postgres' ], + 'pg_amcheck all schemas and tables'); + +$node->command_ok( + [ 'pg_amcheck', '-X', '-p', $port, 'postgres' ], + 'pg_amcheck all schemas, tables and indexes'); + +$node->command_ok( + [ 'pg_amcheck', '-X', '-p', $port, 'postgres', '-n', 's1' ], + 'pg_amcheck all objects in schema s1'); + +$node->command_ok( + [ 'pg_amcheck', '-X', '-p', $port, 'postgres', '-n', 's*', '-t', 't1' ], + 'pg_amcheck all tables named t1 and their indexes'); + +$node->command_ok( + [ 'pg_amcheck', '-p', $port, 'postgres', '-T', 't1' ], + 'pg_amcheck all tables not named t1'); + +$node->command_ok( + [ 'pg_amcheck', '-p', $port, 'postgres', '-N', 's1', '-T', 't1' ], + 'pg_amcheck all tables not named t1 nor in schema s1'); + +# Scans of indexes in s1 should detect the specific corruption that we created +# above. For missing relation forks, we know what the error message looks +# like. For corrupted index pages, the error might vary depending on how the +# page was formatted on disk, including variations due to alignment differences +# between platforms, so we accept any non-empty error message. +# +$node->command_like( + [ 'pg_amcheck', '-p', $port, 'postgres', '-n', 's1', '-i', 'i1' ], + qr/index "i1" lacks a main relation fork/, + 'pg_amcheck index s1.i1 reports missing main relation fork'); + +$node->command_like( + [ 'pg_amcheck', '-p', $port, 'postgres', '-n', 's1', '-i', 'i2' ], + qr/.+/, # Any non-empty error message is acceptable + 'pg_amcheck index s1.s2 reports index corruption'); + + +# In schema s3, the tables and indexes are both corrupt. Ordinarily, checking +# of indexes will not be performed for corrupt tables, but the --check-corrupt +# option (-c) forces the indexes to also be checked. +# +$node->command_like( + [ 'pg_amcheck', '-p', $port, 'postgres', '-n', 's3', '-i', 'i1' ], + qr/index "i1" lacks a main relation fork/, + 'pg_amcheck index s3.i1 reports missing main relation fork'); + +$node->command_like( + [ 'pg_amcheck', '-p', $port, 'postgres', '-n', 's3', '-i', 'i2' ], + qr/.+/, # Any non-empty error message is acceptable + 'pg_amcheck index s3.s2 reports index corruption'); + + +# Check that '-x' and '-X' work as expected. Since only index corruption +# (and not table corruption) exists in s1, '-X' should give no errors, and +# '-x' should give errors about index corruption. +# +$node->command_like( + [ 'pg_amcheck', '-p', $port, 'postgres', '-n', 's1' ], + qr/.+/, # Any non-empty error message is acceptable + 'pg_amcheck over tables and indexes in schema s1 reports corruption'); + +$node->command_like( + [ 'pg_amcheck', '-X', '-p', $port, 'postgres', '-n', 's1' ], + qr/^$/, # Empty + 'pg_amcheck over only tables in schema s1 reports no corruption'); + + +# Check that table corruption is reported as expected, with or without +# index checking +# +$node->command_like( + [ 'pg_amcheck', '-p', $port, 'postgres', '-n', 's2' ], + qr/could not open file/, + 'pg_amcheck over tables in schema s2 reports table corruption'); + +$node->command_like( + [ 'pg_amcheck', '-X', '-p', $port, 'postgres', '-n', 's2' ], + qr/could not open file/, + 'pg_amcheck over tables and indexes in schema s2 reports table corruption'); + +# Check that no corruption is reported in schema s4 +$node->command_like( + [ 'pg_amcheck', '-X', '-p', $port, 'postgres', '-n', 's4' ], + qr/^$/, # Empty + 'pg_amcheck over schema s4 reports no corruption'); + +# Check that no corruption is reported if we exclude corrupt schemas +$node->command_like( + [ 'pg_amcheck', '-X', '-p', $port, 'postgres', '-N', 's1', '-N', 's2', '-N', 's3' ], + qr/^$/, # Empty + 'pg_amcheck excluding corrupt schemas reports no corruption'); + +# Check that no corruption is reported if we exclude corrupt tables +$node->command_like( + [ 'pg_amcheck', '-X', '-p', $port, 'postgres', '-T', 't1', '-T', 't2' ], + qr/^$/, # Empty + 'pg_amcheck excluding corrupt tables reports no corruption'); + +# Check errors about bad block range command line arguments. We use schema s4 +# to avoid getting messages about corrupt tables or indexes. +command_fails_like( + [ 'pg_amcheck', '-p', $port, 'postgres', '-n', 's4', '-b', 'junk' ], + qr/\Qpg_amcheck: error: relation starting block argument contains garbage characters\E/, + 'pg_amcheck rejects garbage startblock'); + +command_fails_like( + [ 'pg_amcheck', '-p', $port, 'postgres', '-n', 's4', '-e', '1234junk' ], + qr/\Qpg_amcheck: error: relation ending block argument contains garbage characters\E/, + 'pg_amcheck rejects garbage endblock'); + +command_fails_like( + [ 'pg_amcheck', '-p', $port, 'postgres', '-n', 's4', '-b', '5', '-e', '4' ], + qr/\Qpg_amcheck: error: relation ending block argument precedes starting block argument\E/, + 'pg_amcheck rejects invalid block range'); diff --git a/contrib/pg_amcheck/t/004_verify_heapam.pl b/contrib/pg_amcheck/t/004_verify_heapam.pl new file mode 100644 index 0000000000..271eca7da6 --- /dev/null +++ b/contrib/pg_amcheck/t/004_verify_heapam.pl @@ -0,0 +1,496 @@ +use strict; +use warnings; + +use PostgresNode; +use TestLib; + +use Test::More tests => 22; + +# This regression test demonstrates that the pg_amcheck binary supplied with +# the pg_amcheck contrib module correctly identifies specific kinds of +# corruption within pages. To test this, we need a mechanism to create corrupt +# pages with predictable, repeatable corruption. The postgres backend cannot +# be expected to help us with this, as its design is not consistent with the +# goal of intentionally corrupting pages. +# +# Instead, we create a table to corrupt, and with careful consideration of how +# postgresql lays out heap pages, we seek to offsets within the page and +# overwrite deliberately chosen bytes with specific values calculated to +# corrupt the page in expected ways. We then verify that pg_amcheck reports +# the corruption, and that it runs without crashing. Note that the backend +# cannot simply be started to run queries against the corrupt table, as the +# backend will crash, at least for some of the corruption types we generate. +# +# Autovacuum potentially touching the table in the background makes the exact +# behavior of this test harder to reason about. We turn it off to keep things +# simpler. We use a "belt and suspenders" approach, turning it off for the +# system generally in postgresql.conf, and turning it off specifically for the +# test table. +# +# This test depends on the table being written to the heap file exactly as we +# expect it to be, so we take care to arrange the columns of the table, and +# insert rows of the table, that give predictable sizes and locations within +# the table page. +# +# The HeapTupleHeaderData has 23 bytes of fixed size fields before the variable +# length t_bits[] array. We have exactly 3 columns in the table, so natts = 3, +# t_bits is 1 byte long, and t_hoff = MAXALIGN(23 + 1) = 24. +# +# We're not too fussy about which datatypes we use for the test, but we do care +# about some specific properties. We'd like to test both fixed size and +# varlena types. We'd like some varlena data inline and some toasted. And +# we'd like the layout of the table such that the datums land at predictable +# offsets within the tuple. We choose a structure without padding on all +# supported architectures: +# +# a BIGINT +# b TEXT +# c TEXT +# +# We always insert a 7-ascii character string into field 'b', which with a +# 1-byte varlena header gives an 8 byte inline value. We always insert a long +# text string in field 'c', long enough to force toast storage. +# +# We choose to read and write binary copies of our table's tuples, using perl's +# pack() and unpack() functions. Perl uses a packing code system in which: +# +# L = "Unsigned 32-bit Long", +# S = "Unsigned 16-bit Short", +# C = "Unsigned 8-bit Octet", +# c = "signed 8-bit octet", +# q = "signed 64-bit quadword" +# +# Each tuple in our table has a layout as follows: +# +# xx xx xx xx t_xmin: xxxx offset = 0 L +# xx xx xx xx t_xmax: xxxx offset = 4 L +# xx xx xx xx t_field3: xxxx offset = 8 L +# xx xx bi_hi: xx offset = 12 S +# xx xx bi_lo: xx offset = 14 S +# xx xx ip_posid: xx offset = 16 S +# xx xx t_infomask2: xx offset = 18 S +# xx xx t_infomask: xx offset = 20 S +# xx t_hoff: x offset = 22 C +# xx t_bits: x offset = 23 C +# xx xx xx xx xx xx xx xx 'a': xxxxxxxx offset = 24 q +# xx xx xx xx xx xx xx xx 'b': xxxxxxxx offset = 32 Cccccccc +# xx xx xx xx xx xx xx xx 'c': xxxxxxxx offset = 40 SSSS +# xx xx xx xx xx xx xx xx : xxxxxxxx ...continued SSSS +# xx xx : xx ...continued S +# +# We could choose to read and write columns 'b' and 'c' in other ways, but +# it is convenient enough to do it this way. We define packing code +# constants here, where they can be compared easily against the layout. + +use constant HEAPTUPLE_PACK_CODE => 'LLLSSSSSCCqCcccccccSSSSSSSSS'; +use constant HEAPTUPLE_PACK_LENGTH => 58; # Total size + +# Read a tuple of our table from a heap page. +# +# Takes an open filehandle to the heap file, and the offset of the tuple. +# +# Rather than returning the binary data from the file, unpacks the data into a +# perl hash with named fields. These fields exactly match the ones understood +# by write_tuple(), below. Returns a reference to this hash. +# +sub read_tuple ($$) +{ + my ($fh, $offset) = @_; + my ($buffer, %tup); + seek($fh, $offset, 0); + sysread($fh, $buffer, HEAPTUPLE_PACK_LENGTH); + + @_ = unpack(HEAPTUPLE_PACK_CODE, $buffer); + %tup = (t_xmin => shift, + t_xmax => shift, + t_field3 => shift, + bi_hi => shift, + bi_lo => shift, + ip_posid => shift, + t_infomask2 => shift, + t_infomask => shift, + t_hoff => shift, + t_bits => shift, + a => shift, + b_header => shift, + b_body1 => shift, + b_body2 => shift, + b_body3 => shift, + b_body4 => shift, + b_body5 => shift, + b_body6 => shift, + b_body7 => shift, + c1 => shift, + c2 => shift, + c3 => shift, + c4 => shift, + c5 => shift, + c6 => shift, + c7 => shift, + c8 => shift, + c9 => shift); + # Stitch together the text for column 'b' + $tup{b} = join('', map { chr($tup{"b_body$_"}) } (1..7)); + return \%tup; +} + +# Write a tuple of our table to a heap page. +# +# Takes an open filehandle to the heap file, the offset of the tuple, and a +# reference to a hash with the tuple values, as returned by read_tuple(). +# Writes the tuple fields from the hash into the heap file. +# +# The purpose of this function is to write a tuple back to disk with some +# subset of fields modified. The function does no error checking. Use +# cautiously. +# +sub write_tuple($$$) +{ + my ($fh, $offset, $tup) = @_; + my $buffer = pack(HEAPTUPLE_PACK_CODE, + $tup->{t_xmin}, + $tup->{t_xmax}, + $tup->{t_field3}, + $tup->{bi_hi}, + $tup->{bi_lo}, + $tup->{ip_posid}, + $tup->{t_infomask2}, + $tup->{t_infomask}, + $tup->{t_hoff}, + $tup->{t_bits}, + $tup->{a}, + $tup->{b_header}, + $tup->{b_body1}, + $tup->{b_body2}, + $tup->{b_body3}, + $tup->{b_body4}, + $tup->{b_body5}, + $tup->{b_body6}, + $tup->{b_body7}, + $tup->{c1}, + $tup->{c2}, + $tup->{c3}, + $tup->{c4}, + $tup->{c5}, + $tup->{c6}, + $tup->{c7}, + $tup->{c8}, + $tup->{c9}); + seek($fh, $offset, 0); + syswrite($fh, $buffer, HEAPTUPLE_PACK_LENGTH); + return; +} + +# Set umask so test directories and files are created with default permissions +umask(0077); + +# Set up the node. Once we create and corrupt the table, +# autovacuum workers visiting the table could crash the backend. +# Disable autovacuum so that won't happen. +my $node = get_new_node('test'); +$node->init; +$node->append_conf('postgresql.conf', 'autovacuum=off'); + +# Start the node and load the extensions. We depend on both +# amcheck and pageinspect for this test. +$node->start; +my $port = $node->port; +my $pgdata = $node->data_dir; +$node->safe_psql('postgres', "CREATE EXTENSION amcheck"); +$node->safe_psql('postgres', "CREATE EXTENSION pageinspect"); + +# Get a non-zero datfrozenxid +$node->safe_psql('postgres', qq(VACUUM FREEZE)); + +# Create the test table with precisely the schema that our corruption function +# expects. +$node->safe_psql( + 'postgres', qq( + CREATE TABLE public.test (a BIGINT, b TEXT, c TEXT); + ALTER TABLE public.test SET (autovacuum_enabled=false); + ALTER TABLE public.test ALTER COLUMN c SET STORAGE EXTERNAL; + CREATE INDEX test_idx ON public.test(a, b); + )); + +# We want (0 < datfrozenxid < test.relfrozenxid). To achieve this, we freeze +# an otherwise unused table, public.junk, prior to inserting data and freezing +# public.test +$node->safe_psql( + 'postgres', qq( + CREATE TABLE public.junk AS SELECT 'junk'::TEXT AS junk_column; + ALTER TABLE public.junk SET (autovacuum_enabled=false); + VACUUM FREEZE public.junk + )); + +my $rel = $node->safe_psql('postgres', qq(SELECT pg_relation_filepath('public.test'))); +my $relpath = "$pgdata/$rel"; + +# Insert data and freeze public.test +use constant ROWCOUNT => 16; +$node->safe_psql('postgres', qq( + INSERT INTO public.test (a, b, c) + VALUES ( + 12345678, + 'abcdefg', + repeat('w', 10000) + ); + VACUUM FREEZE public.test + )) for (1..ROWCOUNT); + +my $relfrozenxid = $node->safe_psql('postgres', + q(select relfrozenxid from pg_class where relname = 'test')); +my $datfrozenxid = $node->safe_psql('postgres', + q(select datfrozenxid from pg_database where datname = 'postgres')); + +# Find where each of the tuples is located on the page. +my @lp_off; +for my $tup (0..ROWCOUNT-1) +{ + push (@lp_off, $node->safe_psql('postgres', qq( +select lp_off from heap_page_items(get_raw_page('test', 'main', 0)) + offset $tup limit 1))); +} + +# Check that pg_amcheck runs against the uncorrupted table without error. +$node->command_ok(['pg_amcheck', '-p', $port, 'postgres'], + 'pg_amcheck test table, prior to corruption'); + +# Check that pg_amcheck runs against the uncorrupted table and index without error. +$node->command_ok(['pg_amcheck', '-p', $port, 'postgres'], + 'pg_amcheck test table and index, prior to corruption'); + +$node->stop; + +# Sanity check that our 'test' table has a relfrozenxid newer than the +# datfrozenxid for the database, and that the datfrozenxid is greater than the +# first normal xid. We rely on these invariants in some of our tests. +if ($datfrozenxid <= 3 || $datfrozenxid >= $relfrozenxid) +{ + fail('Xid thresholds not as expected'); + $node->clean_node; + exit; +} + +# Some #define constants from access/htup_details.h for use while corrupting. +use constant HEAP_HASNULL => 0x0001; +use constant HEAP_XMAX_LOCK_ONLY => 0x0080; +use constant HEAP_XMIN_COMMITTED => 0x0100; +use constant HEAP_XMIN_INVALID => 0x0200; +use constant HEAP_XMAX_COMMITTED => 0x0400; +use constant HEAP_XMAX_INVALID => 0x0800; +use constant HEAP_NATTS_MASK => 0x07FF; +use constant HEAP_XMAX_IS_MULTI => 0x1000; +use constant HEAP_KEYS_UPDATED => 0x2000; + +# Helper function to generate a regular expression matching the header we +# expect verify_heapam() to return given which fields we expect to be non-null. +sub header +{ + my ($blkno, $offnum, $attnum) = @_; + return qr/relation test, block $blkno, offset $offnum, attribute $attnum\s+/ms + if (defined $attnum); + return qr/relation test, block $blkno, offset $offnum\s+/ms + if (defined $offnum); + return qr/relation test\s+/ms + if (defined $blkno); + return qr/relation test\s+/ms; +} + +# Corrupt the tuples, one type of corruption per tuple. Some types of +# corruption cause verify_heapam to skip to the next tuple without +# performing any remaining checks, so we can't exercise the system properly if +# we focus all our corruption on a single tuple. +# +my @expected; +my $file; +open($file, '+<', $relpath); +binmode $file; + +for (my $tupidx = 0; $tupidx < ROWCOUNT; $tupidx++) +{ + my $offnum = $tupidx + 1; # offnum is 1-based, not zero-based + my $offset = $lp_off[$tupidx]; + my $tup = read_tuple($file, $offset); + + # Sanity-check that the data appears on the page where we expect. + if ($tup->{a} ne '12345678' || $tup->{b} ne 'abcdefg') + { + fail('Page layout differs from our expectations'); + $node->clean_node; + exit; + } + + my $header = header(0, $offnum, undef); + if ($offnum == 1) + { + # Corruptly set xmin < relfrozenxid + my $xmin = $relfrozenxid - 1; + $tup->{t_xmin} = $xmin; + $tup->{t_infomask} &= ~HEAP_XMIN_COMMITTED; + $tup->{t_infomask} &= ~HEAP_XMIN_INVALID; + + # Expected corruption report + push @expected, + qr/${header}xmin $xmin precedes relation freeze threshold 0:\d+/; + } + if ($offnum == 2) + { + # Corruptly set xmin < datfrozenxid + my $xmin = 3; + $tup->{t_xmin} = $xmin; + $tup->{t_infomask} &= ~HEAP_XMIN_COMMITTED; + $tup->{t_infomask} &= ~HEAP_XMIN_INVALID; + + push @expected, + qr/${$header}xmin $xmin precedes oldest valid transaction ID 0:\d+/; + } + elsif ($offnum == 3) + { + # Corruptly set xmin < datfrozenxid, further back, noting circularity + # of xid comparison. For a new cluster with epoch = 0, the corrupt + # xmin will be interpreted as in the future + $tup->{t_xmin} = 4026531839; + $tup->{t_infomask} &= ~HEAP_XMIN_COMMITTED; + $tup->{t_infomask} &= ~HEAP_XMIN_INVALID; + + push @expected, + qr/${$header}xmin 4026531839 equals or exceeds next valid transaction ID 0:\d+/; + } + elsif ($offnum == 4) + { + # Corruptly set xmax < relminmxid; + $tup->{t_xmax} = 4026531839; + $tup->{t_infomask} &= ~HEAP_XMAX_INVALID; + + push @expected, + qr/${$header}xmax 4026531839 equals or exceeds next valid transaction ID 0:\d+/; + } + elsif ($offnum == 5) + { + # Corrupt the tuple t_hoff, but keep it aligned properly + $tup->{t_hoff} += 128; + + push @expected, + qr/${$header}data begins at offset 152 beyond the tuple length 58/, + qr/${$header}tuple data should begin at byte 24, but actually begins at byte 152 \(3 attributes, no nulls\)/; + } + elsif ($offnum == 6) + { + # Corrupt the tuple t_hoff, wrong alignment + $tup->{t_hoff} += 3; + + push @expected, + qr/${$header}tuple data should begin at byte 24, but actually begins at byte 27 \(3 attributes, no nulls\)/; + } + elsif ($offnum == 7) + { + # Corrupt the tuple t_hoff, underflow but correct alignment + $tup->{t_hoff} -= 8; + + push @expected, + qr/${$header}tuple data should begin at byte 24, but actually begins at byte 16 \(3 attributes, no nulls\)/; + } + elsif ($offnum == 8) + { + # Corrupt the tuple t_hoff, underflow and wrong alignment + $tup->{t_hoff} -= 3; + + push @expected, + qr/${$header}tuple data should begin at byte 24, but actually begins at byte 21 \(3 attributes, no nulls\)/; + } + elsif ($offnum == 9) + { + # Corrupt the tuple to look like it has lots of attributes, not just 3 + $tup->{t_infomask2} |= HEAP_NATTS_MASK; + + push @expected, + qr/${$header}number of attributes 2047 exceeds maximum expected for table 3/; + } + elsif ($offnum == 10) + { + # Corrupt the tuple to look like it has lots of attributes, some of + # them null. This falsely creates the impression that the t_bits + # array is longer than just one byte, but t_hoff still says otherwise. + $tup->{t_infomask} |= HEAP_HASNULL; + $tup->{t_infomask2} |= HEAP_NATTS_MASK; + $tup->{t_bits} = 0xAA; + + push @expected, + qr/${$header}tuple data should begin at byte 280, but actually begins at byte 24 \(2047 attributes, has nulls\)/; + } + elsif ($offnum == 11) + { + # Same as above, but this time t_hoff plays along + $tup->{t_infomask} |= HEAP_HASNULL; + $tup->{t_infomask2} |= (HEAP_NATTS_MASK & 0x40); + $tup->{t_bits} = 0xAA; + $tup->{t_hoff} = 32; + + push @expected, + qr/${$header}number of attributes 67 exceeds maximum expected for table 3/; + } + elsif ($offnum == 12) + { + # Corrupt the bits in column 'b' 1-byte varlena header + $tup->{b_header} = 0x80; + + $header = header(0, $offnum, 1); + push @expected, + qr/${header}attribute 1 with length 4294967295 ends at offset 416848000 beyond total tuple length 58/; + } + elsif ($offnum == 13) + { + # Corrupt the bits in column 'c' toast pointer + $tup->{c6} = 41; + $tup->{c7} = 41; + + $header = header(0, $offnum, 2); + push @expected, + qr/${header}final toast chunk number 0 differs from expected value 6/, + qr/${header}toasted value for attribute 2 missing from toast table/; + } + elsif ($offnum == 14) + { + # Set both HEAP_XMAX_LOCK_ONLY and HEAP_KEYS_UPDATED + $tup->{t_infomask} |= HEAP_XMAX_LOCK_ONLY; + $tup->{t_infomask2} |= HEAP_KEYS_UPDATED; + + push @expected, + qr/${header}tuple is marked as only locked, but also claims key columns were updated/; + } + elsif ($offnum == 15) + { + # Set both HEAP_XMAX_COMMITTED and HEAP_XMAX_IS_MULTI + $tup->{t_infomask} |= HEAP_XMAX_COMMITTED; + $tup->{t_infomask} |= HEAP_XMAX_IS_MULTI; + $tup->{t_xmax} = 4; + + push @expected, + qr/${header}multitransaction ID 4 equals or exceeds next valid multitransaction ID 1/; + } + elsif ($offnum == 16) # Last offnum must equal ROWCOUNT + { + # Set both HEAP_XMAX_COMMITTED and HEAP_XMAX_IS_MULTI + $tup->{t_infomask} |= HEAP_XMAX_COMMITTED; + $tup->{t_infomask} |= HEAP_XMAX_IS_MULTI; + $tup->{t_xmax} = 4000000000; + + push @expected, + qr/${header}multitransaction ID 4000000000 precedes relation minimum multitransaction ID threshold 1/; + } + write_tuple($file, $offset, $tup); +} +close($file); +$node->start; + +# Run pg_amcheck against the corrupt table with epoch=0, comparing actual +# corruption messages against the expected messages +$node->command_checks_all( + ['pg_amcheck', '--check-toast', '--skip-indexes', '-p', $port, 'postgres'], + 0, + [ @expected ], + [ qr/^$/ ], + 'Expected corruption message output'); + +$node->teardown_node; +$node->clean_node; diff --git a/contrib/pg_amcheck/t/005_opclass_damage.pl b/contrib/pg_amcheck/t/005_opclass_damage.pl new file mode 100644 index 0000000000..c24f154883 --- /dev/null +++ b/contrib/pg_amcheck/t/005_opclass_damage.pl @@ -0,0 +1,52 @@ +# This regression test checks the behavior of the btree validation in the +# presence of breaking sort order changes. +# +use strict; +use warnings; +use PostgresNode; +use TestLib; +use Test::More tests => 6; + +my $node = get_new_node('test'); +$node->init; +$node->start; + +# Create a custom operator class and an index which uses it. +$node->safe_psql('postgres', q( + CREATE EXTENSION amcheck; + + CREATE FUNCTION int4_asc_cmp (a int4, b int4) RETURNS int LANGUAGE sql AS $$ + SELECT CASE WHEN $1 = $2 THEN 0 WHEN $1 > $2 THEN 1 ELSE -1 END; $$; + + CREATE OPERATOR CLASS int4_fickle_ops FOR TYPE int4 USING btree AS + OPERATOR 1 < (int4, int4), OPERATOR 2 <= (int4, int4), + OPERATOR 3 = (int4, int4), OPERATOR 4 >= (int4, int4), + OPERATOR 5 > (int4, int4), FUNCTION 1 int4_asc_cmp(int4, int4); + + CREATE TABLE int4tbl (i int4); + INSERT INTO int4tbl (SELECT * FROM generate_series(1,1000) gs); + CREATE INDEX fickleidx ON int4tbl USING btree (i int4_fickle_ops); +)); + +# We have not yet broken the index, so we should get no corruption +$node->command_like( + [ 'pg_amcheck', '-X', '-p', $node->port, 'postgres' ], + qr/^$/, + 'pg_amcheck all schemas, tables and indexes reports no corruption'); + +# Change the operator class to use a function which sorts in a different +# order to corrupt the btree index +$node->safe_psql('postgres', q( + CREATE FUNCTION int4_desc_cmp (int4, int4) RETURNS int LANGUAGE sql AS $$ + SELECT CASE WHEN $1 = $2 THEN 0 WHEN $1 > $2 THEN -1 ELSE 1 END; $$; + UPDATE pg_catalog.pg_amproc + SET amproc = 'int4_desc_cmp'::regproc + WHERE amproc = 'int4_asc_cmp'::regproc +)); + +# Index corruption should now be reported +$node->command_like( + [ 'pg_amcheck', '-p', $node->port, 'postgres' ], + qr/item order invariant violated for index "fickleidx"/, + 'pg_amcheck all schemas, tables and indexes reports fickleidx corruption' +); diff --git a/doc/src/sgml/contrib.sgml b/doc/src/sgml/contrib.sgml index ae2759be55..797b4dc61e 100644 --- a/doc/src/sgml/contrib.sgml +++ b/doc/src/sgml/contrib.sgml @@ -119,6 +119,7 @@ CREATE EXTENSION module_name; &oldsnapshot; &pageinspect; &passwordcheck; + &pgamcheck; &pgbuffercache; &pgcrypto; &pgfreespacemap; diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml index 38e8aa0bbf..a4e1b28b38 100644 --- a/doc/src/sgml/filelist.sgml +++ b/doc/src/sgml/filelist.sgml @@ -133,6 +133,7 @@ + diff --git a/doc/src/sgml/pgamcheck.sgml b/doc/src/sgml/pgamcheck.sgml new file mode 100644 index 0000000000..00643d2e58 --- /dev/null +++ b/doc/src/sgml/pgamcheck.sgml @@ -0,0 +1,493 @@ + + + + pg_amcheck + + + pg_amcheck + + + + The pg_amcheck module provides a command line interface + to the corruption checking functionality. + + + + pg_amcheck is a regular + PostgreSQL client application. You can perform + corruption checks from any remote host that has access to the database + connecting as a user with sufficient privileges to check tables and indexes. + Currently, this requires execute privileges on 's + bt_index_parent_check and verify_heapam + functions. + + + +pg_amcheck mydb + + + + Options + + + The following command-line options for controlling general program behavior + are recognized. + + + + + + + + Show pg_amcheck version number, and exit. + + + + + + + + + + Show help about pg_amcheck command line + arguments, and exit. + + + + + + + + + + Specifies verbose mode. This will cause + pg_amcheck to output more detailed information + about its activities, mostly to do with its communication with the + database. + + + Note that this does not increase the number of corruptions reported nor + the level of detail reported about each of them. + + + + + + + + The following command-line options control which datatabase objects + pg_amcheck checks and how such options + are interpreted. + + + + + + + + For indexes associated with tables being checked, check only those + indexes with names matching pattern. Multiple indexes can be + selected by writing multiple switches. The + pattern parameter is + interpreted as a pattern according to the same rules used by + psql's \d commands (see + ), so multiple indexes can also + be selected by writing wildcard characters in the pattern. When using + wildcards, be careful to quote the pattern if needed to prevent the + shell from expanding the wildcards. + + + + + + + + + + Do not check any indexes matching pattern. The pattern is interpreted + according to the same rules as for . + can be given more than once to exclude indexes + matching any of several patterns. + + + + When both and are given, the + behavior is to check just the indexes that match at least one + switch but no switches. If + appears without , then indexes + matching are excluded from what is otherwise a check + of all indexes associated with tables that are checked. + + + + + + + + + + Dump only schemas matching pattern; this selects both the + schema itself, and all its contained objects. When this option is + not specified, all non-system schemas in the target database will be + checked. Multiple schemas can be + selected by writing multiple switches. The + pattern parameter is + interpreted as a pattern according to the same rules used by + psql's \d commands + (see ), + so multiple schemas can also be selected by writing wildcard characters + in the pattern. When using wildcards, be careful to quote the pattern + if needed to prevent the shell from expanding the wildcards. + + + + + + + + + + Do not check any schemas matching pattern. The pattern is + interpreted according to the same rules as for . + can be given more than once to exclude schemas + matching any of several patterns. + + + + When both and are given, the behavior + is to check just the schemas that match at least one + switch but no switches. If appears + without , then schemas matching are + excluded from what is otherwise a check of all schemas. + + + + + + + + + + Requires that each schema + (/), table + (/) and index + (/) qualifier match at least + one schema/table/index in the database to be checked. + + + This option has no effect on + /, + /, + or . An exclude + pattern failing to match any objects is not considered an error. + + + + + + + + + + Check only tables with names matching + pattern. Multiple tables + can be selected by writing multiple switches. The + pattern parameter is + interpreted as a pattern according to the same rules used by + psql's \d commands + (see ), + so multiple tables can also be selected by writing wildcard characters + in the pattern. When using wildcards, be careful to quote the pattern + if needed to prevent the shell from expanding the wildcards. + + + + + + + + + + Do not check any tables matching pattern. The pattern is interpreted + according to the same rules as for . + can be given more than once to exclude tables + matching any of several patterns. + + + + When both and are given, the + behavior is to check just the tables that match at least one + switch but no switches. If + appears without , then tables + matching are excluded from what is otherwise a check + of all tables. + + + + + + + + The following command-line options control additional behaviors. + + + + + + + + When checking indexes, additionally verify the presence of all heap + tuples as index tuples within the index. + + + + + + + + + + Do not check blocks prior to block, which should be a non-negative + integer. (Negative values disable the option). + + + When both and + are specified, the end + block must not be less than the start block. + + + The option will be + applied to all tables that are checked, including toast tables. The + option is most useful when checking exactly one table, to focus the + checking on just specific blocks of that one table. + + + + + + + + + + Skip checking indexes for a table if the table is found to be corrupt. + + + + + + + + + + Do not check blocks after block, which should be a non-negative + integer. (Negative values disable the option). + + + When both and + are specified, the end + block must not be less than the start block. + + + The option will be + applied to all tables that are checked, including toast tables. The + option is most useful when checking exactly one table, to focus the + checking on just specific blocks of that one table. + + + + + + + + + + Stop checking database objects at the end of the first page on + which the first corruption is found. Note that even with this option + enabled, more than one corruption message may be reported. + + + + + + + + + + When checking indexes, for each tuple, perform addition verification by + re-finding the tuple on the leaf level by performing a new search from + the root page. + + + This form of verification was originally written to help in the + development of btree index features. It may be of limited or even of no + use in helping detect the kinds of corruption that occur in practice. + In any event, it is known to be a rather expensive check to perform. + + + + + + + + + + When all-visible is given, skips corruption + checking of blocks marked as all visible in the visibility map. + + + When all-frozen is given, skips corruption + checking of blocks marked as all frozen in the visibility map. + + + The default is to check blocks without regard to their marking in the + visibility map. + + + + + + + + + + Check only tables but not indexes. + + + + + + + + + + Do not check toast tables nor their indexes. + + + + + + + + + The following additional command-line options control the database + connection parameters. + + + + + + + + Specifies the name of the database to connect to. This is + equivalent to specifying dbname as the first non-option + argument on the command line. The dbname + can be a connection string. + If so, connection string parameters will override any conflicting + command line options. + + + + + + + + + + Specifies the host name of the machine on which the server is + running. If the value begins with a slash, it is used as the + directory for the Unix domain socket. The default is taken + from the PGHOST environment variable, if set, + else a Unix domain socket connection is attempted. + + + + + + + + + + Specifies the TCP port or local Unix domain socket file + extension on which the server is listening for connections. + Defaults to the PGPORT environment variable, if + set, or a compiled-in default. + + + + + + + + + + User name to connect as. + + + + + + + + + + Never issue a password prompt. If the server requires + password authentication and a password is not available by + other means such as a .pgpass file, the + connection attempt will fail. This option can be useful in + batch jobs and scripts where no user is present to enter a + password. + + + + + + + + + + Force pg_dump to prompt for a + password before connecting to a database. + + + + This option is never essential, since + pg_dump will automatically prompt + for a password if the server demands password authentication. + However, pg_dump will waste a + connection attempt finding out that the server wants a password. + In some cases it is worth typing to avoid the extra + connection attempt. + + + + + + + + + Specifies a role name to be used to create the dump. + This option causes pg_dump to issue a + SET ROLE rolename + command after connecting to the database. It is useful when the + authenticated user (specified by ) lacks privileges + needed by pg_dump, but can switch to a role with + the required rights. Some installations have a policy against + logging in directly as a superuser, and use of this option allows + dumps to be made without violating the policy. + + + + + + + -- 2.21.1 (Apple Git-122.3)