Patch for pg_upgrade to turn off autovacuum

Bruce Momjian wrote:
> Robert Haas wrote:
> > On Thu, Mar 31, 2011 at 12:11 PM, Bruce Momjian <bruce@momjian.us> wrote:
> > > Robert Haas wrote:
> > >> On Thu, Mar 31, 2011 at 11:32 AM, Heikki Linnakangas
> > >> <heikki.linnakangas@enterprisedb.com> wrote:
> > >> >> ?I think the maintenance
> > >> >> overhead of an invisible variable is too much.
> > >> >
> > >> > A simple GUC or command-line switch isn't much code.
> > >>
> > >> I like the idea of a command-line switch.
> > >
> > > If you want to do that you should gereralize it as --binary-upgrade in
> > > case we have other needs for it.
> >
> > Yeah.  Or we could do a binary_upgrade GUC which has the effect of
> > forcibly suppressing autovacuum, and maybe other things later.  I
> > think that's a lot less hazardous than fiddling with the autovacuum
> > GUC.
>
> I like the idea of a command-line flag because it forces everything to
> be affected, and cannot be turned on and off in sessions --- if you are
> doing a binary upgrade, locked-down is good. :-)

The attached patch adds a new postmaster/postgres binary upgrade mode
(-b) which disables autovacuum, allows only super-user connections, and
prevents pg_upgrade_support oid assignment when not in upgrade mode.
It also modifies pg_upgrade to use this new mode rather than play with
trying to stop autovacuum.

This does fix a very rare bug that could happen if
autovacuum_freeze_max_age were set to maximum by the user.

I think this should be applied to PG 9.1.

--
  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/server.c b/contrib/pg_upgrade/server.c
new file mode 100644
index 2a0f50e..df2f2db
*** 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,216 ****
       * 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
!      * a gap of 2000000000 from the current xid counter, so autovacuum will
!      * not touch them.
       */
      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 */
--- 198,212 ----
       * 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'.
!      * Use binary upgrade mode on the server (-b), if supported.
       */
      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,
!              (GET_MAJOR_VERSION(cluster->major_version) >= 901) ? "-b" : "",
!              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/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;