Skip to content

Commit 695b4a1

Browse files
bmomjianbmomjian
committed
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
1 parent 611e424 commit 695b4a1

File tree

5 files changed

+85
-32
lines changed

5 files changed

+85
-32
lines changed

‎doc/src/sgml/ref/pg_resetwal.sgml

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -297,6 +297,26 @@ PostgreSQL documentation
297297
</listitem>
298298
</varlistentry>
299299

300+
<varlistentry>
301+
<term><option>-u <replaceable class="parameter">xid</replaceable></option></term>
302+
<term><option>--oldest-transaction-id=<replaceable class="parameter">xid</replaceable></option></term>
303+
<listitem>
304+
<para>
305+
Manually set the oldest unfrozen transaction ID.
306+
</para>
307+
308+
<para>
309+
A safe value can be determined by looking for the numerically smallest
310+
file name in the directory <filename>pg_xact</filename> under the data directory
311+
and then multiplying by 1048576 (0x100000). Note that the file names are in
312+
hexadecimal. It is usually easiest to specify the option value in
313+
hexadecimal too. For example, if <filename>0007</filename> is the smallest entry
314+
in <filename>pg_xact</filename>, <literal>-u 0x700000</literal> will work (five
315+
trailing zeroes provide the proper multiplier).
316+
</para>
317+
</listitem>
318+
</varlistentry>
319+
300320
<varlistentry>
301321
<term><option>-x <replaceable class="parameter">xid</replaceable></option></term>
302322
<term><option>--next-transaction-id=<replaceable class="parameter">xid</replaceable></option></term>

‎src/bin/pg_resetwal/pg_resetwal.c

Lines changed: 41 additions & 31 deletions
Original file line numberDiff line numberDiff line change
@@ -64,6 +64,7 @@ static XLogSegNo newXlogSegNo; /* new XLOG segment # */
6464
static bool guessed = false; /* T if we had to guess at any values */
6565
static const char *progname;
6666
static uint32 set_xid_epoch = (uint32) -1;
67+
static TransactionId set_oldest_xid = 0;
6768
static TransactionId set_xid = 0;
6869
static TransactionId set_oldest_commit_ts_xid = 0;
6970
static TransactionId set_newest_commit_ts_xid = 0;
@@ -101,6 +102,7 @@ main(int argc, char *argv[])
101102
{"dry-run", no_argument, NULL, 'n'},
102103
{"next-oid", required_argument, NULL, 'o'},
103104
{"multixact-offset", required_argument, NULL, 'O'},
105+
{"oldest-transaction-id", required_argument, NULL, 'u'},
104106
{"next-transaction-id", required_argument, NULL, 'x'},
105107
{"wal-segsize", required_argument, NULL, 1},
106108
{NULL, 0, NULL, 0}
@@ -135,7 +137,7 @@ main(int argc, char *argv[])
135137
}
136138

137139

138-
while ((c = getopt_long(argc, argv, "c:D:e:fl:m:no:O:x:", long_options, NULL)) != -1)
140+
while ((c = getopt_long(argc, argv, "c:D:e:fl:m:no:O:u:x:", long_options, NULL)) != -1)
139141
{
140142
switch (c)
141143
{
@@ -168,6 +170,21 @@ main(int argc, char *argv[])
168170
}
169171
break;
170172

173+
case 'u':
174+
set_oldest_xid = strtoul(optarg, &endptr, 0);
175+
if (endptr == optarg || *endptr != '\0')
176+
{
177+
pg_log_error("invalid argument for option %s", "-u");
178+
fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
179+
exit(1);
180+
}
181+
if (!TransactionIdIsNormal(set_oldest_xid))
182+
{
183+
pg_log_error("oldest transaction ID (-u) must be greater or equal to %u", FirstNormalTransactionId);
184+
exit(1);
185+
}
186+
break;
187+
171188
case 'x':
172189
set_xid = strtoul(optarg, &endptr, 0);
173190
if (endptr == optarg || *endptr != '\0')
@@ -176,9 +193,9 @@ main(int argc, char *argv[])
176193
fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
177194
exit(1);
178195
}
179-
if (set_xid == 0)
196+
if (!TransactionIdIsNormal(set_xid))
180197
{
181-
pg_log_error("transaction ID (-x) must not be 0");
198+
pg_log_error("transaction ID (-x) must be greater or equal to %u", FirstNormalTransactionId);
182199
exit(1);
183200
}
184201
break;
@@ -428,25 +445,17 @@ main(int argc, char *argv[])
428445
FullTransactionIdFromEpochAndXid(set_xid_epoch,
429446
XidFromFullTransactionId(ControlFile.checkPointCopy.nextXid));
430447

431-
if (set_xid != 0)
448+
if (set_oldest_xid != 0)
432449
{
450+
ControlFile.checkPointCopy.oldestXid = set_oldest_xid;
451+
ControlFile.checkPointCopy.oldestXidDB = InvalidOid;
452+
}
453+
454+
if (set_xid != 0)
433455
ControlFile.checkPointCopy.nextXid =
434456
FullTransactionIdFromEpochAndXid(EpochFromFullTransactionId(ControlFile.checkPointCopy.nextXid),
435457
set_xid);
436458

437-
/*
438-
* For the moment, just set oldestXid to a value that will force
439-
* immediate autovacuum-for-wraparound. It's not clear whether adding
440-
* user control of this is useful, so let's just do something that's
441-
* reasonably safe. The magic constant here corresponds to the
442-
* maximum allowed value of autovacuum_freeze_max_age.
443-
*/
444-
ControlFile.checkPointCopy.oldestXid = set_xid - 2000000000;
445-
if (ControlFile.checkPointCopy.oldestXid < FirstNormalTransactionId)
446-
ControlFile.checkPointCopy.oldestXid += FirstNormalTransactionId;
447-
ControlFile.checkPointCopy.oldestXidDB = InvalidOid;
448-
}
449-
450459
if (set_oldest_commit_ts_xid != 0)
451460
ControlFile.checkPointCopy.oldestCommitTsXid = set_oldest_commit_ts_xid;
452461
if (set_newest_commit_ts_xid != 0)
@@ -1209,20 +1218,21 @@ usage(void)
12091218
printf(_("Usage:\n %s [OPTION]... DATADIR\n\n"), progname);
12101219
printf(_("Options:\n"));
12111220
printf(_(" -c, --commit-timestamp-ids=XID,XID\n"
1212-
" set oldest and newest transactions bearing\n"
1213-
" commit timestamp (zero means no change)\n"));
1214-
printf(_(" [-D, --pgdata=]DATADIR data directory\n"));
1215-
printf(_(" -e, --epoch=XIDEPOCH set next transaction ID epoch\n"));
1216-
printf(_(" -f, --force force update to be done\n"));
1217-
printf(_(" -l, --next-wal-file=WALFILE set minimum starting location for new WAL\n"));
1218-
printf(_(" -m, --multixact-ids=MXID,MXID set next and oldest multitransaction ID\n"));
1219-
printf(_(" -n, --dry-run no update, just show what would be done\n"));
1220-
printf(_(" -o, --next-oid=OID set next OID\n"));
1221-
printf(_(" -O, --multixact-offset=OFFSET set next multitransaction offset\n"));
1222-
printf(_(" -V, --version output version information, then exit\n"));
1223-
printf(_(" -x, --next-transaction-id=XID set next transaction ID\n"));
1224-
printf(_(" --wal-segsize=SIZE size of WAL segments, in megabytes\n"));
1225-
printf(_(" -?, --help show this help, then exit\n"));
1221+
" set oldest and newest transactions bearing\n"
1222+
" commit timestamp (zero means no change)\n"));
1223+
printf(_(" [-D, --pgdata=]DATADIR data directory\n"));
1224+
printf(_(" -e, --epoch=XIDEPOCH set next transaction ID epoch\n"));
1225+
printf(_(" -f, --force force update to be done\n"));
1226+
printf(_(" -l, --next-wal-file=WALFILE set minimum starting location for new WAL\n"));
1227+
printf(_(" -m, --multixact-ids=MXID,MXID set next and oldest multitransaction ID\n"));
1228+
printf(_(" -n, --dry-run no update, just show what would be done\n"));
1229+
printf(_(" -o, --next-oid=OID set next OID\n"));
1230+
printf(_(" -O, --multixact-offset=OFFSET set next multitransaction offset\n"));
1231+
printf(_(" -u, --oldest-transaction-id=XID set oldest transaction ID\n"));
1232+
printf(_(" -V, --version output version information, then exit\n"));
1233+
printf(_(" -x, --next-transaction-id=XID set next transaction ID\n"));
1234+
printf(_(" --wal-segsize=SIZE size of WAL segments, in megabytes\n"));
1235+
printf(_(" -?, --help show this help, then exit\n"));
12261236
printf(_("\nReport bugs to <%s>.\n"), PACKAGE_BUGREPORT);
12271237
printf(_("%s home page: <%s>\n"), PACKAGE_NAME, PACKAGE_URL);
12281238
}

‎src/bin/pg_upgrade/controldata.c

Lines changed: 16 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -44,6 +44,7 @@ get_control_data(ClusterInfo *cluster, bool live_check)
4444
bool got_oid = false;
4545
bool got_multi = false;
4646
bool got_oldestmulti = false;
47+
bool got_oldestxid = false;
4748
bool got_mxoff = false;
4849
bool got_nextxlogfile = false;
4950
bool got_float8_pass_by_value = false;
@@ -312,6 +313,17 @@ get_control_data(ClusterInfo *cluster, bool live_check)
312313
cluster->controldata.chkpnt_nxtmulti = str2uint(p);
313314
got_multi = true;
314315
}
316+
else if ((p = strstr(bufin, "Latest checkpoint's oldestXID:")) != NULL)
317+
{
318+
p = strchr(p, ':');
319+
320+
if (p == NULL || strlen(p) <= 1)
321+
pg_fatal("%d: controldata retrieval problem\n", __LINE__);
322+
323+
p++; /* remove ':' char */
324+
cluster->controldata.chkpnt_oldstxid = str2uint(p);
325+
got_oldestxid = true;
326+
}
315327
else if ((p = strstr(bufin, "Latest checkpoint's oldestMultiXid:")) != NULL)
316328
{
317329
p = strchr(p, ':');
@@ -544,7 +556,7 @@ get_control_data(ClusterInfo *cluster, bool live_check)
544556

545557
/* verify that we got all the mandatory pg_control data */
546558
if (!got_xid || !got_oid ||
547-
!got_multi ||
559+
!got_multi || !got_oldestxid ||
548560
(!got_oldestmulti &&
549561
cluster->controldata.cat_ver >= MULTIXACT_FORMATCHANGE_CAT_VER) ||
550562
!got_mxoff || (!live_check && !got_nextxlogfile) ||
@@ -575,6 +587,9 @@ get_control_data(ClusterInfo *cluster, bool live_check)
575587
cluster->controldata.cat_ver >= MULTIXACT_FORMATCHANGE_CAT_VER)
576588
pg_log(PG_REPORT, " latest checkpoint oldest MultiXactId\n");
577589

590+
if (!got_oldestxid)
591+
pg_log(PG_REPORT, " latest checkpoint oldestXID\n");
592+
578593
if (!got_mxoff)
579594
pg_log(PG_REPORT, " latest checkpoint next MultiXactOffset\n");
580595

‎src/bin/pg_upgrade/pg_upgrade.c

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -467,6 +467,13 @@ copy_xact_xlog_xid(void)
467467
GET_MAJOR_VERSION(new_cluster.major_version) <= 906 ?
468468
"pg_clog" : "pg_xact");
469469

470+
prep_status("Setting oldest XID for new cluster");
471+
exec_prog(UTILITY_LOG_FILE, NULL, true, true,
472+
"\"%s/pg_resetwal\" -f -u %u \"%s\"",
473+
new_cluster.bindir, old_cluster.controldata.chkpnt_oldstxid,
474+
new_cluster.pgdata);
475+
check_ok();
476+
470477
/* set the next transaction id and epoch of the new cluster */
471478
prep_status("Setting next transaction ID and epoch for new cluster");
472479
exec_prog(UTILITY_LOG_FILE, NULL, true, true,

‎src/bin/pg_upgrade/pg_upgrade.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -207,6 +207,7 @@ typedef struct
207207
uint32 chkpnt_nxtmulti;
208208
uint32 chkpnt_nxtmxoff;
209209
uint32 chkpnt_oldstMulti;
210+
uint32 chkpnt_oldstxid;
210211
uint32 align;
211212
uint32 blocksz;
212213
uint32 largesz;

0 commit comments

Comments
 (0)