Re: pg_upgrade - add config directory setting - Mailing list pgsql-hackers

Bruce Momjian wrote:
> OK, I have modified the postmaster in PG 9.2 to allow output of the data
> directory, and modified pg_ctl to use that, so starting in PG 9.2 pg_ctl
> will work cleanly for config-only directories.
>
> I will now work on pg_upgrade to also use the new flag to find the data
> directory from a config-only install.  However, this is only available
> in PG 9.2, and it will only be in PG 9.3 that you can hope to use this
> feature (if old is PG 9.2 or later).  I am afraid the symlink hack will
> have to be used for several more years, and if you are supporting
> upgrades from pre-9.2, perhaps forever.
>
> I did find that it is possible to use pg_ctl -w start on a config-only
> install using this trick:
>
>   su -l postgres \
>     -c "env PGPORT=\"5432\" /usr/lib/postgresql-9.1/bin/pg_ctl start -w \
>         -t 60 -s -D /var/lib/postgresql/9.1/data/ \
>          -o '-D /etc/postgresql-9.1/ \
>             --data-directory=/var/lib/postgresql/9.1/data/ \
>             --silent-mode=true'"
>
> Unfortunately pg_upgrade doesn't support the -o option which would make
> this possible for pg_upgrade.
>
> One idea would be to add -o/-O options to pg_upgrade 9.2 to allow this
> to work even with old installs, but frankly, this is so confusing I am
> not sure we want to encourage people to do things like this.  Of course,
> the symlink hack is even worse, so maybe there is some merit to this.

OK, the attached patch adds -o/-O options to pg_upgrade to mimick pg_ctl
-o, and documents the 'Gentoo method' for allowing pg_upgrade to handle
pre-9.2 upgrades for config-only installs.  I think this closes the
issue, with no backpatching required for it to work for new PG 9.2.
Users will have to continue using the symlink method for new 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/option.c b/contrib/pg_upgrade/option.c
new file mode 100644
index 3ab1b5c..9892b97
*** a/contrib/pg_upgrade/option.c
--- b/contrib/pg_upgrade/option.c
*************** parseCommandLine(int argc, char *argv[])
*** 39,44 ****
--- 39,46 ----
          {"new-datadir", required_argument, NULL, 'D'},
          {"old-bindir", required_argument, NULL, 'b'},
          {"new-bindir", required_argument, NULL, 'B'},
+         {"old-options", required_argument, NULL, 'o'},
+         {"new-options", required_argument, NULL, 'O'},
          {"old-port", required_argument, NULL, 'p'},
          {"new-port", required_argument, NULL, 'P'},

*************** parseCommandLine(int argc, char *argv[])
*** 93,99 ****

      getcwd(os_info.cwd, MAXPGPATH);

!     while ((option = getopt_long(argc, argv, "d:D:b:B:cgG:kl:p:P:u:v",
                                   long_options, &optindex)) != -1)
      {
          switch (option)
--- 95,101 ----

      getcwd(os_info.cwd, MAXPGPATH);

!     while ((option = getopt_long(argc, argv, "d:D:b:B:cgG:kl:o:O:p:P:u:v",
                                   long_options, &optindex)) != -1)
      {
          switch (option)
*************** parseCommandLine(int argc, char *argv[])
*** 141,146 ****
--- 143,161 ----
                  log_opts.filename = pg_strdup(optarg);
                  break;

+             case 'o':
+                 old_cluster.pgopts = pg_strdup(optarg);
+                 break;
+
+             case 'O':
+                 new_cluster.pgopts = pg_strdup(optarg);
+                 break;
+
+             /*
+              * Someday, the port number option could be removed and
+              * passed using -o/-O, but that requires postmaster -C
+              * to be supported on all old/new versions.
+              */
              case 'p':
                  if ((old_cluster.port = atoi(optarg)) <= 0)
                  {
*************** Options:\n\
*** 242,247 ****
--- 257,264 ----
    -G, --debugfile=FILENAME      output debugging activity to file\n\
    -k, --link                    link instead of copying files to new cluster\n\
    -l, --logfile=FILENAME        log session activity to file\n\
+   -o, --old-options=OPTIONS     old cluster options to pass to the server\n\
+   -O, --new-options=OPTIONS     new cluster options to pass to the server\n\
    -p, --old-port=OLDPORT        old cluster port number (default %d)\n\
    -P, --new-port=NEWPORT        new cluster port number (default %d)\n\
    -u, --user=NAME               clusters superuser (default \"%s\")\n\
diff --git a/contrib/pg_upgrade/pg_upgrade.h b/contrib/pg_upgrade/pg_upgrade.h
new file mode 100644
index 0fb16ed..b7e4ea5
*** a/contrib/pg_upgrade/pg_upgrade.h
--- b/contrib/pg_upgrade/pg_upgrade.h
*************** typedef struct
*** 189,194 ****
--- 189,195 ----
      char       *pgdata;            /* pathname for cluster's $PGDATA directory */
      char       *pgconfig;        /* pathname for cluster's config file directory */
      char       *bindir;            /* pathname for cluster's executable directory */
+     char       *pgopts;            /* options to pass to the server, like pg_ctl -o */
      unsigned short port;        /* port number where postmaster is waiting */
      uint32        major_version;    /* PG_VERSION of cluster */
      char        major_version_str[64];    /* string PG_VERSION of cluster */
diff --git a/contrib/pg_upgrade/server.c b/contrib/pg_upgrade/server.c
new file mode 100644
index d512ef3..9c4f2d6
*** a/contrib/pg_upgrade/server.c
--- b/contrib/pg_upgrade/server.c
*************** start_postmaster(ClusterInfo *cluster)
*** 168,179 ****
       */
      snprintf(cmd, sizeof(cmd),
               SYSTEMQUOTE "\"%s/pg_ctl\" -w -l \"%s\" -D \"%s\" "
!              "-o \"-p %d %s\" start >> \"%s\" 2>&1" SYSTEMQUOTE,
               cluster->bindir, log_opts.filename2, cluster->pgconfig, cluster->port,
               (cluster->controldata.cat_ver >=
                BINARY_UPGRADE_SERVER_FLAG_CAT_VER) ? "-b" :
               "-c autovacuum=off -c autovacuum_freeze_max_age=2000000000",
!              log_opts.filename2);

      /*
       * Don't throw an error right away, let connecting throw the error because
--- 168,179 ----
       */
      snprintf(cmd, sizeof(cmd),
               SYSTEMQUOTE "\"%s/pg_ctl\" -w -l \"%s\" -D \"%s\" "
!              "-o \"-p %d %s %s\" start >> \"%s\" 2>&1" SYSTEMQUOTE,
               cluster->bindir, log_opts.filename2, cluster->pgconfig, cluster->port,
               (cluster->controldata.cat_ver >=
                BINARY_UPGRADE_SERVER_FLAG_CAT_VER) ? "-b" :
               "-c autovacuum=off -c autovacuum_freeze_max_age=2000000000",
!              cluster->pgopts ? cluster->pgopts : "", log_opts.filename2);

      /*
       * Don't throw an error right away, let connecting throw the error because
*************** void
*** 207,233 ****
  stop_postmaster(bool fast)
  {
      char        cmd[MAXPGPATH];
!     const char *bindir;
!     const char *configdir;

      if (os_info.running_cluster == &old_cluster)
!     {
!         bindir = old_cluster.bindir;
!         configdir = old_cluster.pgconfig;
!     }
      else if (os_info.running_cluster == &new_cluster)
!     {
!         bindir = new_cluster.bindir;
!         configdir = new_cluster.pgconfig;
!     }
      else
!         return;                    /* no cluster running */

      snprintf(cmd, sizeof(cmd),
!              SYSTEMQUOTE "\"%s/pg_ctl\" -w -l \"%s\" -D \"%s\" %s stop >> "
!              "\"%s\" 2>&1" SYSTEMQUOTE,
!              bindir, log_opts.filename2, configdir, fast ? "-m fast" : "",
!              log_opts.filename2);

      exec_prog(fast ? false : true, "%s", cmd);

--- 207,227 ----
  stop_postmaster(bool fast)
  {
      char        cmd[MAXPGPATH];
!     ClusterInfo *cluster;

      if (os_info.running_cluster == &old_cluster)
!         cluster = &old_cluster;
      else if (os_info.running_cluster == &new_cluster)
!         cluster = &new_cluster;
      else
!         return;        /* no cluster running */

      snprintf(cmd, sizeof(cmd),
!              SYSTEMQUOTE "\"%s/pg_ctl\" -w -l \"%s\" -D \"%s\" -o \"%s\" "
!              "%s stop >> \"%s\" 2>&1" SYSTEMQUOTE,
!              cluster->bindir, log_opts.filename2, cluster->pgconfig,
!              cluster->pgopts ? cluster->pgopts : "",
!             fast ? "-m fast" : "", log_opts.filename2);

      exec_prog(fast ? false : true, "%s", cmd);

diff --git a/doc/src/sgml/pgupgrade.sgml b/doc/src/sgml/pgupgrade.sgml
new file mode 100644
index 0d08424..460d06b
*** a/doc/src/sgml/pgupgrade.sgml
--- b/doc/src/sgml/pgupgrade.sgml
***************
*** 115,120 ****
--- 115,134 ----
       </varlistentry>

       <varlistentry>
+       <term><option>-o</option> <replaceable class="parameter">options</replaceable></term>
+       <term><option>--old-options</option> <replaceable class="parameter">options</replaceable></term>
+       <listitem><para>options to be passed directly to the
+       old <command>postgres</command> command</para></listitem>
+      </varlistentry>
+
+      <varlistentry>
+       <term><option>-O</option> <replaceable class="parameter">options</replaceable></term>
+       <term><option>--new-options</option> <replaceable class="parameter">options</replaceable></term>
+       <listitem><para>options to be passed directly to the
+       new <command>postgres</command> command</para></listitem>
+      </varlistentry>
+
+      <varlistentry>
        <term><option>-p</option> <replaceable>old_port_number</></term>
        <term><option>--old-port=</option><replaceable>old_portnum</></term>
        <listitem><para>the old cluster port number; environment
*************** psql --username postgres --file script.s
*** 560,565 ****
--- 574,587 ----
    </para>

    <para>
+    If you are upgrading a pre-<productname>PostgreSQL</> 9.2 cluster
+    that uses a configuration-file-only directory, you must pass the
+    real data directory location to <application>pg_upgrade</>, and
+    pass the configuration directory location to the server, e.g.
+    <literal>-d /real-data-directory -o '-D /configuration-directory'</>.
+   </para>
+
+   <para>
     If you want to use link mode and you don't want your old cluster
     to be modified when the new cluster is started, make a copy of the
     old cluster and upgrade that with link mode. To make a valid copy