Re: Patch for pg_upgrade to turn off autovacuum

Bruce Momjian wrote:
> > > Well, consider that this also locks out non-super users so I figured it
> > > would be good to run the old and new in the same binary upgrade mode.
> > > Again, we can do just the new cluster for 9.1.   I can also control the
> > > behavior based on the catalog version number, which seems the most
> > > logical.
> >
> > I think you are over-engineering this. Just use it for the new cluster
> > only, full stop, and you'll be right as rain.
>
> I thought some more about this and I don't want autovacuum to run on the
> old server.  This is because pg_dumpall --binary-upgrade --schema-only
> grabs the datfrozenxid for all the databases at the start, then connects
> to each database to gets the relfrozenxids.  I don't want to risk any
> advancement of either of those during the pg_dumpall run.
>
> FYI, the existing code already doesn't allow autovacuum to run on the old
> or new cluster by setting autovacuum off and autovacuum_freeze_max_age
> very high, so this is not a behavior change --- just a more formalized
> way of turning off autovacuum.
>
> The attached patch uses catalog version to test;  we use catalog version
> checking already for tablespace subdirectories.

OK, thinking even more, it seems I have to keep the old vacuum disable
flag for pre-9.1 old clusters.  This new patch does that.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +
diff --git a/contrib/pg_upgrade/check.c b/contrib/pg_upgrade/check.c
new file mode 100644
index d1dc5db..7623c5e
*** a/contrib/pg_upgrade/check.c
--- b/contrib/pg_upgrade/check.c
*************** check_cluster_compatibility(bool live_ch
*** 264,270 ****

      /* Is it 9.0 but without tablespace directories? */
      if (GET_MAJOR_VERSION(new_cluster.major_version) == 900 &&
!         new_cluster.controldata.cat_ver < TABLE_SPACE_SUBDIRS)
          pg_log(PG_FATAL, "This utility can only upgrade to PostgreSQL version 9.0 after 2010-01-11\n"
                 "because of backend API changes made during development.\n");
  }
--- 264,270 ----

      /* Is it 9.0 but without tablespace directories? */
      if (GET_MAJOR_VERSION(new_cluster.major_version) == 900 &&
!         new_cluster.controldata.cat_ver < CAT_VER_TABLE_SPACE_SUBDIRS)
          pg_log(PG_FATAL, "This utility can only upgrade to PostgreSQL version 9.0 after 2010-01-11\n"
                 "because of backend API changes made during development.\n");
  }
diff --git a/contrib/pg_upgrade/pg_upgrade.h b/contrib/pg_upgrade/pg_upgrade.h
new file mode 100644
index 5ca570e..70d74ba
*** a/contrib/pg_upgrade/pg_upgrade.h
--- b/contrib/pg_upgrade/pg_upgrade.h
***************
*** 58,64 ****
  #define atooid(x)  ((Oid) strtoul((x), NULL, 10))

  /* OID system catalog preservation added during PG 9.0 development */
! #define TABLE_SPACE_SUBDIRS 201001111

  /*
   * Each relation is represented by a relinfo structure.
--- 58,66 ----
  #define atooid(x)  ((Oid) strtoul((x), NULL, 10))

  /* OID system catalog preservation added during PG 9.0 development */
! #define CAT_VER_TABLE_SPACE_SUBDIRS 201001111
! /* postmaster/postgres -b (binary_upgrade) flag added during PG 9.1 development */
! #define CAT_VER_BINARY_UPGRADE_SERVER_FLAG 201104221

  /*
   * Each relation is represented by a relinfo structure.
diff --git a/contrib/pg_upgrade/server.c b/contrib/pg_upgrade/server.c
new file mode 100644
index 2a0f50e..ca4a957
*** a/contrib/pg_upgrade/server.c
--- b/contrib/pg_upgrade/server.c
*************** start_postmaster(ClusterInfo *cluster, b
*** 173,178 ****
--- 173,183 ----
      const char *datadir;
      unsigned short port;
      bool        exit_hook_registered = false;
+ #ifndef WIN32
+     char        *output_filename = log_opts.filename;
+ #else
+     char        *output_filename = DEVNULL;
+ #endif

      bindir = cluster->bindir;
      datadir = cluster->pgdata;
*************** start_postmaster(ClusterInfo *cluster, b
*** 193,199 ****
       * same file because we get the error: "The process cannot access the file
       * because it is being used by another process." so we have to send all
       * other output to 'nul'.
-      *
       * Using autovacuum=off disables cleanup vacuum and analyze, but freeze
       * vacuums can still happen, so we set autovacuum_freeze_max_age to its
       * maximum.  We assume all datfrozenxid and relfrozen values are less than
--- 198,203 ----
*************** start_postmaster(ClusterInfo *cluster, b
*** 202,216 ****
       */
      snprintf(cmd, sizeof(cmd),
               SYSTEMQUOTE "\"%s/pg_ctl\" -l \"%s\" -D \"%s\" "
!              "-o \"-p %d -c autovacuum=off "
!              "-c autovacuum_freeze_max_age=2000000000\" "
!              "start >> \"%s\" 2>&1" SYSTEMQUOTE,
!              bindir,
! #ifndef WIN32
!              log_opts.filename, datadir, port, log_opts.filename);
! #else
!              DEVNULL, datadir, port, DEVNULL);
! #endif
      exec_prog(true, "%s", cmd);

      /* wait for the server to start properly */
--- 206,218 ----
       */
      snprintf(cmd, sizeof(cmd),
               SYSTEMQUOTE "\"%s/pg_ctl\" -l \"%s\" -D \"%s\" "
!              "-o \"-p %d %s\" start >> \"%s\" 2>&1" SYSTEMQUOTE,
!              bindir, output_filename, datadir, port,
!              (cluster->controldata.cat_ver >=
!                 CAT_VER_BINARY_UPGRADE_SERVER_FLAG) ? "-b" :
!                 "-c autovacuum=off -c autovacuum_freeze_max_age=2000000000",
!              log_opts.filename);
!
      exec_prog(true, "%s", cmd);

      /* wait for the server to start properly */
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
new file mode 100644
index 8c5670f..870ddba
*** a/src/backend/catalog/heap.c
--- b/src/backend/catalog/heap.c
*************** heap_create_with_catalog(const char *rel
*** 1051,1057 ****
           * Use binary-upgrade override for pg_class.oid/relfilenode, if
           * supplied.
           */
!         if (OidIsValid(binary_upgrade_next_heap_pg_class_oid) &&
              (relkind == RELKIND_RELATION || relkind == RELKIND_SEQUENCE ||
               relkind == RELKIND_VIEW || relkind == RELKIND_COMPOSITE_TYPE ||
               relkind == RELKIND_FOREIGN_TABLE))
--- 1051,1058 ----
           * Use binary-upgrade override for pg_class.oid/relfilenode, if
           * supplied.
           */
!         if (IsBinaryUpgrade &&
!             OidIsValid(binary_upgrade_next_heap_pg_class_oid) &&
              (relkind == RELKIND_RELATION || relkind == RELKIND_SEQUENCE ||
               relkind == RELKIND_VIEW || relkind == RELKIND_COMPOSITE_TYPE ||
               relkind == RELKIND_FOREIGN_TABLE))
*************** heap_create_with_catalog(const char *rel
*** 1059,1065 ****
              relid = binary_upgrade_next_heap_pg_class_oid;
              binary_upgrade_next_heap_pg_class_oid = InvalidOid;
          }
!         else if (OidIsValid(binary_upgrade_next_toast_pg_class_oid) &&
                   relkind == RELKIND_TOASTVALUE)
          {
              relid = binary_upgrade_next_toast_pg_class_oid;
--- 1060,1067 ----
              relid = binary_upgrade_next_heap_pg_class_oid;
              binary_upgrade_next_heap_pg_class_oid = InvalidOid;
          }
!         else if (IsBinaryUpgrade &&
!                  OidIsValid(binary_upgrade_next_toast_pg_class_oid) &&
                   relkind == RELKIND_TOASTVALUE)
          {
              relid = binary_upgrade_next_toast_pg_class_oid;
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
new file mode 100644
index c79402c..a662cfc
*** a/src/backend/catalog/index.c
--- b/src/backend/catalog/index.c
*************** index_create(Relation heapRelation,
*** 790,796 ****
           * Use binary-upgrade override for pg_class.oid/relfilenode, if
           * supplied.
           */
!         if (OidIsValid(binary_upgrade_next_index_pg_class_oid))
          {
              indexRelationId = binary_upgrade_next_index_pg_class_oid;
              binary_upgrade_next_index_pg_class_oid = InvalidOid;
--- 790,797 ----
           * Use binary-upgrade override for pg_class.oid/relfilenode, if
           * supplied.
           */
!         if (IsBinaryUpgrade &&
!             OidIsValid(binary_upgrade_next_index_pg_class_oid))
          {
              indexRelationId = binary_upgrade_next_index_pg_class_oid;
              binary_upgrade_next_index_pg_class_oid = InvalidOid;
diff --git a/src/backend/catalog/pg_enum.c b/src/backend/catalog/pg_enum.c
new file mode 100644
index 08d8aa1..61a9322
*** a/src/backend/catalog/pg_enum.c
--- b/src/backend/catalog/pg_enum.c
***************
*** 21,26 ****
--- 21,27 ----
  #include "catalog/pg_enum.h"
  #include "catalog/pg_type.h"
  #include "storage/lmgr.h"
+ #include "miscadmin.h"
  #include "utils/builtins.h"
  #include "utils/fmgroids.h"
  #include "utils/rel.h"
*************** restart:
*** 311,317 ****
      }

      /* Get a new OID for the new label */
!     if (OidIsValid(binary_upgrade_next_pg_enum_oid))
      {
          /*
           * Use binary-upgrade override for pg_enum.oid, if supplied. During
--- 312,318 ----
      }

      /* Get a new OID for the new label */
!     if (IsBinaryUpgrade && OidIsValid(binary_upgrade_next_pg_enum_oid))
      {
          /*
           * Use binary-upgrade override for pg_enum.oid, if supplied. During
diff --git a/src/backend/catalog/pg_type.c b/src/backend/catalog/pg_type.c
new file mode 100644
index 9e35e73..80c1bfc
*** a/src/backend/catalog/pg_type.c
--- b/src/backend/catalog/pg_type.c
*************** TypeShellMake(const char *typeName, Oid
*** 125,131 ****
      tup = heap_form_tuple(tupDesc, values, nulls);

      /* Use binary-upgrade override for pg_type.oid, if supplied. */
!     if (OidIsValid(binary_upgrade_next_pg_type_oid))
      {
          HeapTupleSetOid(tup, binary_upgrade_next_pg_type_oid);
          binary_upgrade_next_pg_type_oid = InvalidOid;
--- 125,131 ----
      tup = heap_form_tuple(tupDesc, values, nulls);

      /* Use binary-upgrade override for pg_type.oid, if supplied. */
!     if (IsBinaryUpgrade && OidIsValid(binary_upgrade_next_pg_type_oid))
      {
          HeapTupleSetOid(tup, binary_upgrade_next_pg_type_oid);
          binary_upgrade_next_pg_type_oid = InvalidOid;
*************** TypeCreate(Oid newTypeOid,
*** 430,436 ****
          if (OidIsValid(newTypeOid))
              HeapTupleSetOid(tup, newTypeOid);
          /* Use binary-upgrade override for pg_type.oid, if supplied. */
!         else if (OidIsValid(binary_upgrade_next_pg_type_oid))
          {
              HeapTupleSetOid(tup, binary_upgrade_next_pg_type_oid);
              binary_upgrade_next_pg_type_oid = InvalidOid;
--- 430,436 ----
          if (OidIsValid(newTypeOid))
              HeapTupleSetOid(tup, newTypeOid);
          /* Use binary-upgrade override for pg_type.oid, if supplied. */
!         else if (IsBinaryUpgrade && OidIsValid(binary_upgrade_next_pg_type_oid))
          {
              HeapTupleSetOid(tup, binary_upgrade_next_pg_type_oid);
              binary_upgrade_next_pg_type_oid = InvalidOid;
diff --git a/src/backend/catalog/toasting.c b/src/backend/catalog/toasting.c
new file mode 100644
index 85fe57f..362d26d
*** a/src/backend/catalog/toasting.c
--- b/src/backend/catalog/toasting.c
*************** create_toast_table(Relation rel, Oid toa
*** 157,163 ****
       * creation even if it seems not to need one.
       */
      if (!needs_toast_table(rel) &&
!         !OidIsValid(binary_upgrade_next_toast_pg_class_oid))
          return false;

      /*
--- 157,164 ----
       * creation even if it seems not to need one.
       */
      if (!needs_toast_table(rel) &&
!         (!IsBinaryUpgrade ||
!          !OidIsValid(binary_upgrade_next_toast_pg_class_oid)))
          return false;

      /*
*************** create_toast_table(Relation rel, Oid toa
*** 202,208 ****
          namespaceid = PG_TOAST_NAMESPACE;

      /* Use binary-upgrade override for pg_type.oid, if supplied. */
!     if (OidIsValid(binary_upgrade_next_toast_pg_type_oid))
      {
          toast_typid = binary_upgrade_next_toast_pg_type_oid;
          binary_upgrade_next_toast_pg_type_oid = InvalidOid;
--- 203,209 ----
          namespaceid = PG_TOAST_NAMESPACE;

      /* Use binary-upgrade override for pg_type.oid, if supplied. */
!     if (IsBinaryUpgrade && OidIsValid(binary_upgrade_next_toast_pg_type_oid))
      {
          toast_typid = binary_upgrade_next_toast_pg_type_oid;
          binary_upgrade_next_toast_pg_type_oid = InvalidOid;
diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c
new file mode 100644
index 1a20b0d..b3b6dc2
*** a/src/backend/commands/typecmds.c
--- b/src/backend/commands/typecmds.c
*************** AssignTypeArrayOid(void)
*** 1550,1556 ****
      Oid            type_array_oid;

      /* Use binary-upgrade override for pg_type.typarray, if supplied. */
!     if (OidIsValid(binary_upgrade_next_array_pg_type_oid))
      {
          type_array_oid = binary_upgrade_next_array_pg_type_oid;
          binary_upgrade_next_array_pg_type_oid = InvalidOid;
--- 1550,1556 ----
      Oid            type_array_oid;

      /* Use binary-upgrade override for pg_type.typarray, if supplied. */
!     if (IsBinaryUpgrade && OidIsValid(binary_upgrade_next_array_pg_type_oid))
      {
          type_array_oid = binary_upgrade_next_array_pg_type_oid;
          binary_upgrade_next_array_pg_type_oid = InvalidOid;
diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c
new file mode 100644
index 3f7d499..838d6eb
*** a/src/backend/commands/user.c
--- b/src/backend/commands/user.c
*************** CreateRole(CreateRoleStmt *stmt)
*** 388,394 ****
       * pg_largeobject_metadata contains pg_authid.oid's, so we use the
       * binary-upgrade override, if specified.
       */
!     if (OidIsValid(binary_upgrade_next_pg_authid_oid))
      {
          HeapTupleSetOid(tuple, binary_upgrade_next_pg_authid_oid);
          binary_upgrade_next_pg_authid_oid = InvalidOid;
--- 388,394 ----
       * pg_largeobject_metadata contains pg_authid.oid's, so we use the
       * binary-upgrade override, if specified.
       */
!     if (IsBinaryUpgrade && OidIsValid(binary_upgrade_next_pg_authid_oid))
      {
          HeapTupleSetOid(tuple, binary_upgrade_next_pg_authid_oid);
          binary_upgrade_next_pg_authid_oid = InvalidOid;
diff --git a/src/backend/postmaster/postmaster.c b/src/backend/postmaster/postmaster.c
new file mode 100644
index 6e7f664..c0cf033
*** a/src/backend/postmaster/postmaster.c
--- b/src/backend/postmaster/postmaster.c
*************** PostmasterMain(int argc, char *argv[])
*** 529,535 ****
       * tcop/postgres.c (the option sets should not conflict) and with the
       * common help() function in main/main.c.
       */
!     while ((opt = getopt(argc, argv, "A:B:c:D:d:EeFf:h:ijk:lN:nOo:Pp:r:S:sTt:W:-:")) != -1)
      {
          switch (opt)
          {
--- 529,535 ----
       * tcop/postgres.c (the option sets should not conflict) and with the
       * common help() function in main/main.c.
       */
!     while ((opt = getopt(argc, argv, "A:B:bc:D:d:EeFf:h:ijk:lN:nOo:Pp:r:S:sTt:W:-:")) != -1)
      {
          switch (opt)
          {
*************** PostmasterMain(int argc, char *argv[])
*** 541,546 ****
--- 541,551 ----
                  SetConfigOption("shared_buffers", optarg, PGC_POSTMASTER, PGC_S_ARGV);
                  break;

+             case 'b':
+                 /* Undocumented flag used for binary upgrades */
+                 IsBinaryUpgrade = true;
+                 break;
+
              case 'D':
                  userDoption = optarg;
                  break;
*************** ServerLoop(void)
*** 1480,1487 ****
          if (WalWriterPID == 0 && pmState == PM_RUN)
              WalWriterPID = StartWalWriter();

!         /* If we have lost the autovacuum launcher, try to start a new one */
!         if (AutoVacPID == 0 &&
              (AutoVacuumingActive() || start_autovac_launcher) &&
              pmState == PM_RUN)
          {
--- 1485,1497 ----
          if (WalWriterPID == 0 && pmState == PM_RUN)
              WalWriterPID = StartWalWriter();

!         /*
!          *    If we have lost the autovacuum launcher, try to start a new one.
!          *    We don't want autovacuum to run in binary upgrade mode because
!          *    autovacuum might update relfrozenxid for empty tables before
!          *    the physical files are put in place.
!          */
!         if (!IsBinaryUpgrade && AutoVacPID == 0 &&
              (AutoVacuumingActive() || start_autovac_launcher) &&
              pmState == PM_RUN)
          {
*************** reaper(SIGNAL_ARGS)
*** 2413,2419 ****
               */
              if (WalWriterPID == 0)
                  WalWriterPID = StartWalWriter();
!             if (AutoVacuumingActive() && AutoVacPID == 0)
                  AutoVacPID = StartAutoVacLauncher();
              if (XLogArchivingActive() && PgArchPID == 0)
                  PgArchPID = pgarch_start();
--- 2423,2429 ----
               */
              if (WalWriterPID == 0)
                  WalWriterPID = StartWalWriter();
!             if (!IsBinaryUpgrade && AutoVacuumingActive() && AutoVacPID == 0)
                  AutoVacPID = StartAutoVacLauncher();
              if (XLogArchivingActive() && PgArchPID == 0)
                  PgArchPID = pgarch_start();
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
new file mode 100644
index 59b7666..a07661f
*** a/src/backend/tcop/postgres.c
--- b/src/backend/tcop/postgres.c
*************** process_postgres_switches(int argc, char
*** 3238,3244 ****
       * postmaster/postmaster.c (the option sets should not conflict) and with
       * the common help() function in main/main.c.
       */
!     while ((flag = getopt(argc, argv, "A:B:c:D:d:EeFf:h:ijk:lN:nOo:Pp:r:S:sTt:v:W:-:")) != -1)
      {
          switch (flag)
          {
--- 3238,3244 ----
       * postmaster/postmaster.c (the option sets should not conflict) and with
       * the common help() function in main/main.c.
       */
!     while ((flag = getopt(argc, argv, "A:B:bc:D:d:EeFf:h:ijk:lN:nOo:Pp:r:S:sTt:v:W:-:")) != -1)
      {
          switch (flag)
          {
*************** process_postgres_switches(int argc, char
*** 3250,3255 ****
--- 3250,3260 ----
                  SetConfigOption("shared_buffers", optarg, ctx, gucsource);
                  break;

+             case 'b':
+                 /* Undocumented flag used for binary upgrades */
+                 IsBinaryUpgrade = true;
+                 break;
+
              case 'D':
                  if (secure)
                      userDoption = strdup(optarg);
diff --git a/src/backend/utils/init/globals.c b/src/backend/utils/init/globals.c
new file mode 100644
index 984ffd0..c4c4154
*** a/src/backend/utils/init/globals.c
--- b/src/backend/utils/init/globals.c
*************** pid_t        PostmasterPid = 0;
*** 85,90 ****
--- 85,91 ----
   */
  bool        IsPostmasterEnvironment = false;
  bool        IsUnderPostmaster = false;
+ bool        IsBinaryUpgrade = false;

  bool        ExitOnAnyError = false;

diff --git a/src/backend/utils/init/postinit.c b/src/backend/utils/init/postinit.c
new file mode 100644
index a4c5d4c..1f6fba5
*** a/src/backend/utils/init/postinit.c
--- b/src/backend/utils/init/postinit.c
*************** InitPostgres(const char *in_dbname, Oid
*** 626,631 ****
--- 626,641 ----
      }

      /*
+      * Binary upgrades only allowed super-user connections
+      */
+     if (IsBinaryUpgrade && !am_superuser)
+     {
+             ereport(FATAL,
+                     (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+             errmsg("must be superuser to connect in binary upgrade mode")));
+     }
+
+     /*
       * The last few connections slots are reserved for superusers. Although
       * replication connections currently require superuser privileges, we
       * don't allow them to consume the reserved slots, which are intended for
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
new file mode 100644
index 53c684a..22926b0
*** a/src/include/catalog/catversion.h
--- b/src/include/catalog/catversion.h
***************
*** 53,58 ****
   */

  /*                            yyyymmddN */
! #define CATALOG_VERSION_NO    201104181

  #endif
--- 53,58 ----
   */

  /*                            yyyymmddN */
! #define CATALOG_VERSION_NO    201104221

  #endif
diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h
new file mode 100644
index aa8cce5..9d19417
*** a/src/include/miscadmin.h
--- b/src/include/miscadmin.h
*************** do { \
*** 124,129 ****
--- 124,130 ----
  extern pid_t PostmasterPid;
  extern bool IsPostmasterEnvironment;
  extern PGDLLIMPORT bool IsUnderPostmaster;
+ extern bool IsBinaryUpgrade;

  extern bool ExitOnAnyError;