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

diff --git a/doc/src/sgml/ref/pg_resetwal.sgml b/doc/src/sgml/ref/pg_resetwal.sgml
index defaf170dc67a9df1ada930dd41e5e4896bebe6c..89ddca47e90ab41f4528a8a2168d69b98fe8226b 100644 (file)
--- a/doc/src/sgml/ref/pg_resetwal.sgml
+++ b/doc/src/sgml/ref/pg_resetwal.sgml
@@ -247,6 +247,25 @@ PostgreSQL documentation
     </listitem>
    </varlistentry>
 
+   <varlistentry>
+    <term><option>-u <replaceable class="parameter">xid</replaceable></option></term>
+    <listitem>
+     <para>
+      Manually set the oldest unfrozen transaction ID.
+     </para>
+
+     <para>
+      A safe value can be determined by looking for the numerically smallest
+      file name in the directory <filename>pg_xact</filename> 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 <filename>0007</filename> is the smallest entry
+      in <filename>pg_xact</filename>, <literal>-u 0x700000</literal> will work (five
+      trailing zeroes provide the proper multiplier).
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry>
     <term><option>-x</option> <replaceable class="parameter">xid</replaceable></term>
     <listitem>

diff --git a/src/bin/pg_resetwal/pg_resetwal.c b/src/bin/pg_resetwal/pg_resetwal.c
index 0e6d790246fc1722ac6696d7bd3ebed26cdf618e..b3827931888920aa1cef077055c16c03dee0a44d 100644 (file)
--- a/src/bin/pg_resetwal/pg_resetwal.c
+++ b/src/bin/pg_resetwal/pg_resetwal.c
@@ -62,6 +62,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;
@@ -115,7 +116,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)
        {
@@ -148,6 +149,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')
@@ -369,23 +385,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)
@@ -1239,6 +1247,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 7a8619b5d48c4b1c6cd5df5501a65c54f43410ac..fcbc28946dbb1db48b176bcee7f6b0f7be478490 100644 (file)
--- 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;
@@ -312,6 +313,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, ':');
@@ -530,7 +542,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) ||
@@ -561,6 +573,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 9b4bc88149973b2c0566d50729fc7a9b1aae11cd..5562327016895efce4b9fce8341c519bca129577 100644 (file)
--- a/src/bin/pg_upgrade/pg_upgrade.c
+++ b/src/bin/pg_upgrade/pg_upgrade.c
@@ -421,6 +421,13 @@ copy_xact_xlog_xid(void)
                      GET_MAJOR_VERSION(new_cluster.major_version) <= 906 ?
                      "pg_clog" : "pg_xact");
 
+   prep_status("Setting oldest XID for new cluster");
+   exec_prog(UTILITY_LOG_FILE, NULL, true, true,
+             "\"%s/pg_resetwal\" -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, true,

diff --git a/src/bin/pg_upgrade/pg_upgrade.h b/src/bin/pg_upgrade/pg_upgrade.h
index 7080922da74f3efcd1829a043471cfef732d312c..56cb3fdf51debc40cc9a8baab2c6cd51f1fa361e 100644 (file)
--- 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;