From 088dbf3bc40bd866da71e07bef8963c81dbb14b9 Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Mon, 26 Jul 2021 22:38:14 -0400 Subject: [PATCH] pg_resetxlog: add option to set oldest xid & use by pg_upgrade Add pg_resetxlog -u option to set the oldest xid in pg_control. Previously -x set this value be -2 billion less than the -x value. However, this causes the server to immediately scan all relation's relfrozenxid so it can advance pg_control's oldest xid to be inside the autovacuum_freeze_max_age range, which is inefficient and might disrupt diagnostic recovery. pg_upgrade will use this option to better create the new cluster to match the old cluster. Reported-by: Jason Harvey, Floris Van Nee Discussion: https://postgr.es/m/20190615183759.GB239428@rfd.leadboat.com, 87da83168c644fd9aae38f546cc70295@opammb0562.comp.optiver.com Author: Bertrand Drouvot Backpatch-through: 9.6 --- doc/src/sgml/ref/pg_resetxlog.sgml | 19 +++++++++++++++ src/bin/pg_resetxlog/pg_resetxlog.c | 37 ++++++++++++++++++----------- src/bin/pg_upgrade/controldata.c | 17 ++++++++++++- src/bin/pg_upgrade/pg_upgrade.c | 7 ++++++ src/bin/pg_upgrade/pg_upgrade.h | 1 + 5 files changed, 66 insertions(+), 15 deletions(-) diff --git a/doc/src/sgml/ref/pg_resetxlog.sgml b/doc/src/sgml/ref/pg_resetxlog.sgml index 9758fa652a2..7a87350d7d4 100644 --- a/doc/src/sgml/ref/pg_resetxlog.sgml +++ b/doc/src/sgml/ref/pg_resetxlog.sgml @@ -247,6 +247,25 @@ PostgreSQL documentation + + + + + Manually set the oldest unfrozen transaction ID. + + + + A safe value can be determined by looking for the numerically smallest + file name in the directory pg_xact under the data directory + and then multiplying by 1048576 (0x100000). Note that the file names are in + hexadecimal. It is usually easiest to specify the option value in + hexadecimal too. For example, if 0007 is the smallest entry + in pg_xact, -u 0x700000 will work (five + trailing zeroes provide the proper multiplier). + + + + xid diff --git a/src/bin/pg_resetxlog/pg_resetxlog.c b/src/bin/pg_resetxlog/pg_resetxlog.c index 3e79482ca22..71f2391925d 100644 --- a/src/bin/pg_resetxlog/pg_resetxlog.c +++ b/src/bin/pg_resetxlog/pg_resetxlog.c @@ -63,6 +63,7 @@ static XLogSegNo newXlogSegNo; /* new XLOG segment # */ static bool guessed = false; /* T if we had to guess at any values */ static const char *progname; static uint32 set_xid_epoch = (uint32) -1; +static TransactionId set_oldest_xid = 0; static TransactionId set_xid = 0; static TransactionId set_oldest_commit_ts_xid = 0; static TransactionId set_newest_commit_ts_xid = 0; @@ -116,7 +117,7 @@ main(int argc, char *argv[]) } - while ((c = getopt(argc, argv, "c:D:e:fl:m:no:O:x:")) != -1) + while ((c = getopt(argc, argv, "c:D:e:fl:m:no:O:u:x:")) != -1) { switch (c) { @@ -149,6 +150,21 @@ main(int argc, char *argv[]) } break; + case 'u': + set_oldest_xid = strtoul(optarg, &endptr, 0); + if (endptr == optarg || *endptr != '\0') + { + fprintf(stderr, _("invalid argument for option %s"), "-u"); + fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname); + exit(1); + } + if (!TransactionIdIsNormal(set_oldest_xid)) + { + fprintf(stderr, _("oldest transaction ID (-u) must be greater or equal to %u"), FirstNormalTransactionId); + exit(1); + } + break; + case 'x': set_xid = strtoul(optarg, &endptr, 0); if (endptr == optarg || *endptr != '\0') @@ -370,23 +386,15 @@ main(int argc, char *argv[]) if (set_xid_epoch != -1) ControlFile.checkPointCopy.nextXidEpoch = set_xid_epoch; - if (set_xid != 0) + if (set_oldest_xid != 0) { - ControlFile.checkPointCopy.nextXid = set_xid; - - /* - * For the moment, just set oldestXid to a value that will force - * immediate autovacuum-for-wraparound. It's not clear whether adding - * user control of this is useful, so let's just do something that's - * reasonably safe. The magic constant here corresponds to the - * maximum allowed value of autovacuum_freeze_max_age. - */ - ControlFile.checkPointCopy.oldestXid = set_xid - 2000000000; - if (ControlFile.checkPointCopy.oldestXid < FirstNormalTransactionId) - ControlFile.checkPointCopy.oldestXid += FirstNormalTransactionId; + ControlFile.checkPointCopy.oldestXid = set_oldest_xid; ControlFile.checkPointCopy.oldestXidDB = InvalidOid; } + if (set_xid != 0) + ControlFile.checkPointCopy.nextXid = set_xid; + if (set_oldest_commit_ts_xid != 0) ControlFile.checkPointCopy.oldestCommitTsXid = set_oldest_commit_ts_xid; if (set_newest_commit_ts_xid != 0) @@ -1243,6 +1251,7 @@ usage(void) printf(_(" -n no update, just show what would be done (for testing)\n")); printf(_(" -o OID set next OID\n")); printf(_(" -O OFFSET set next multitransaction offset\n")); + printf(_(" -u XID set oldest transaction ID\n")); printf(_(" -V, --version output version information, then exit\n")); printf(_(" -x XID set next transaction ID\n")); printf(_(" -?, --help show this help, then exit\n")); diff --git a/src/bin/pg_upgrade/controldata.c b/src/bin/pg_upgrade/controldata.c index 2f7ba40dc98..81777779398 100644 --- a/src/bin/pg_upgrade/controldata.c +++ b/src/bin/pg_upgrade/controldata.c @@ -44,6 +44,7 @@ get_control_data(ClusterInfo *cluster, bool live_check) bool got_oid = false; bool got_multi = false; bool got_oldestmulti = false; + bool got_oldestxid = false; bool got_mxoff = false; bool got_nextxlogfile = false; bool got_float8_pass_by_value = false; @@ -306,6 +307,17 @@ get_control_data(ClusterInfo *cluster, bool live_check) cluster->controldata.chkpnt_nxtmulti = str2uint(p); got_multi = true; } + else if ((p = strstr(bufin, "Latest checkpoint's oldestXID:")) != NULL) + { + p = strchr(p, ':'); + + if (p == NULL || strlen(p) <= 1) + pg_fatal("%d: controldata retrieval problem\n", __LINE__); + + p++; /* remove ':' char */ + cluster->controldata.chkpnt_oldstxid = str2uint(p); + got_oldestxid = true; + } else if ((p = strstr(bufin, "Latest checkpoint's oldestMultiXid:")) != NULL) { p = strchr(p, ':'); @@ -524,7 +536,7 @@ get_control_data(ClusterInfo *cluster, bool live_check) /* verify that we got all the mandatory pg_control data */ if (!got_xid || !got_oid || - !got_multi || + !got_multi || !got_oldestxid || (!got_oldestmulti && cluster->controldata.cat_ver >= MULTIXACT_FORMATCHANGE_CAT_VER) || !got_mxoff || (!live_check && !got_nextxlogfile) || @@ -552,6 +564,9 @@ get_control_data(ClusterInfo *cluster, bool live_check) cluster->controldata.cat_ver >= MULTIXACT_FORMATCHANGE_CAT_VER) pg_log(PG_REPORT, " latest checkpoint oldest MultiXactId\n"); + if (!got_oldestxid) + pg_log(PG_REPORT, " latest checkpoint oldestXID\n"); + if (!got_mxoff) pg_log(PG_REPORT, " latest checkpoint next MultiXactOffset\n"); diff --git a/src/bin/pg_upgrade/pg_upgrade.c b/src/bin/pg_upgrade/pg_upgrade.c index 02078c03573..8bbb86630c8 100644 --- a/src/bin/pg_upgrade/pg_upgrade.c +++ b/src/bin/pg_upgrade/pg_upgrade.c @@ -408,6 +408,13 @@ copy_clog_xlog_xid(void) /* copy old commit logs to new data dir */ copy_subdir_files("pg_clog"); + prep_status("Setting oldest XID for new cluster"); + exec_prog(UTILITY_LOG_FILE, NULL, true, + "\"%s/pg_resetxlog\" -f -u %u \"%s\"", + new_cluster.bindir, old_cluster.controldata.chkpnt_oldstxid, + new_cluster.pgdata); + check_ok(); + /* set the next transaction id and epoch of the new cluster */ prep_status("Setting next transaction ID and epoch for new cluster"); exec_prog(UTILITY_LOG_FILE, NULL, true, diff --git a/src/bin/pg_upgrade/pg_upgrade.h b/src/bin/pg_upgrade/pg_upgrade.h index d4f6e0fe085..a7f6e4920e8 100644 --- a/src/bin/pg_upgrade/pg_upgrade.h +++ b/src/bin/pg_upgrade/pg_upgrade.h @@ -215,6 +215,7 @@ typedef struct uint32 chkpnt_nxtmulti; uint32 chkpnt_nxtmxoff; uint32 chkpnt_oldstMulti; + uint32 chkpnt_oldstxid; uint32 align; uint32 blocksz; uint32 largesz; -- 2.30.2