Re: pg_upgrade using appname to lock out other users - Mailing list pgsql-hackers

Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > I have researched this and need feedback.  Initially I wanted to use a
> > single -p port flag to be used by the old and new clusters.  However,
> > pg_upgrade allows --check mode while the old server is running, so we
> > need to allow you to use the current old postmaster port number and a
> > different port number to test the new server.  That kills the idea of
> > using a single -p flag, so -p and -P are needed.
>
> I am confused as to the point of allowing checks to be done on a "live"
> server.  Presumably, whatever you are checking could be invalidated as
> soon as you turn your back, so the checks have to be done again anyway
> as soon as you shut the old server down.  So while there might be a
> use-case for a "check" mode against the existing server, there isn't any
> need to combine that with checking the new server in the same run.

Right, we will re-check at the time they do the actual upgrade.  This
was requested so people can prepare for the real upgrade without having
to stop their live server.  You do need to check the old and new servers
in the same pg_upgrade run to make sure their values match, like
pg_controldata.  This was particularly useful for the 8.3 to 8.4
upgrades where we checked for imcompatible indexes, etc, and is useful
for checking that all the contrib shared objects are in place in the new
server, etc.  I guess we could check many of them without starting the
new server, but there are some that need it, so we just start it and do
a full check.

> Furthermore, ISTM that there isn't any need to be running both servers
> at once --- and, indeed, in entirely-plausible configurations you won't
> be *able* to do that because of SHMMAX; so pg_upgrade must not depend on
> being able to do so.

Again, this happens with --check on a live server, as outlined above.

> So on the whole I don't see the reason why two port numbers would be
> needed.  Just run the two servers serially on a single nondefault port
> number.

See above.

Requiring old and new port numbers, and the documentation wasn't as bad
as I thought.  Patch attached for review.

--
  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 8153e30..40f31ad
*** a/contrib/pg_upgrade/option.c
--- b/contrib/pg_upgrade/option.c
*************** parseCommandLine(int argc, char *argv[])
*** 60,67 ****
      os_info.progname = get_progname(argv[0]);

      /* Process libpq env. variables; load values here for usage() output */
!     old_cluster.port = getenv("PGPORT") ? atoi(getenv("PGPORT")) : DEF_PGPORT;
!     new_cluster.port = getenv("PGPORT") ? atoi(getenv("PGPORT")) : DEF_PGPORT;

      os_user_effective_id = get_user_info(&os_info.user);
      /* we override just the database user name;  we got the OS id above */
--- 60,67 ----
      os_info.progname = get_progname(argv[0]);

      /* Process libpq env. variables; load values here for usage() output */
!     old_cluster.port = atoi(getenv("OLDPGPORT");
!     new_cluster.port = atoi(getenv("NEWPGPORT");

      os_user_effective_id = get_user_info(&os_info.user);
      /* we override just the database user name;  we got the OS id above */
*************** parseCommandLine(int argc, char *argv[])
*** 214,219 ****
--- 214,223 ----
      validateDirectoryOption(&new_cluster.pgdata, "NEWDATADIR", "-D",
                              "new cluster data resides");

+     if (old_cluster.port == 0 || new_cluster.port == 0)
+         pg_log(PG_FATAL, "You must specify both old and new port numbers\n"
+                "They can be the same, except when running check on an active old server\n");
+
      get_pkglibdirs();
  }

diff --git a/doc/src/sgml/pgupgrade.sgml b/doc/src/sgml/pgupgrade.sgml
new file mode 100644
index a505707..c56165f
*** a/doc/src/sgml/pgupgrade.sgml
--- b/doc/src/sgml/pgupgrade.sgml
***************
*** 105,111 ****
       <varlistentry>
        <term><option>-k</option></term>
        <term><option>--link</option></term>
!       <listitem><para>link instead of copying files to new cluster</para></listitem>
       </varlistentry>

       <varlistentry>
--- 105,111 ----
       <varlistentry>
        <term><option>-k</option></term>
        <term><option>--link</option></term>
!       <listitem><para>use hard links instead of copying files to the new cluster</para></listitem>
       </varlistentry>

       <varlistentry>
***************
*** 117,131 ****
       <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
!       variable <envar>PGPORT</></para></listitem>
       </varlistentry>

       <varlistentry>
        <term><option>-P</option> <replaceable>new_port_number</></term>
        <term><option>--new-port=</option><replaceable>new_portnum</></term>
!       <listitem><para>the new cluster port number; environment
!       variable <envar>PGPORT</></para></listitem>
       </varlistentry>

       <varlistentry>
--- 117,131 ----
       <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 variable
!       <envar>OLDPGPORT</></para></listitem>
       </varlistentry>

       <varlistentry>
        <term><option>-P</option> <replaceable>new_port_number</></term>
        <term><option>--new-port=</option><replaceable>new_portnum</></term>
!       <listitem><para>the new cluster port number; environment variable
!       <envar>NEWPGPORT</></para></listitem>
       </varlistentry>

       <varlistentry>
*************** mv /usr/local/pgsql /usr/local/pgsql.old
*** 201,209 ****
      <title>Install the new PostgreSQL binaries</title>

      <para>
!      Install the new server's binaries and support files. You can use the
!      same port numbers for both clusters, typically 5432, because the old and
!      new clusters will not be running at the same time.
      </para>

      <para>
--- 201,207 ----
      <title>Install the new PostgreSQL binaries</title>

      <para>
!      Install the new server's binaries and support files.
      </para>

      <para>
*************** gmake prefix=/usr/local/pgsql.new instal
*** 256,263 ****
       so you might want to set authentication to <literal>trust</> in
       <filename>pg_hba.conf</>, or if using <literal>md5</> authentication,
       use a <filename>~/.pgpass</> file (see <xref linkend="libpq-pgpass">)
!      to avoid being prompted repeatedly for a password.  Also make sure
!      pg_upgrade is the only program that can connect to the clusters.
      </para>
     </step>

--- 254,260 ----
       so you might want to set authentication to <literal>trust</> in
       <filename>pg_hba.conf</>, or if using <literal>md5</> authentication,
       use a <filename>~/.pgpass</> file (see <xref linkend="libpq-pgpass">)
!      to avoid being prompted repeatedly for a password.
      </para>
     </step>

*************** NET STOP pgsql-8.3  (<productname>Postgr
*** 293,303 ****
      <para>
       Always run the <application>pg_upgrade</> binary of the new server, not the old one.
       <application>pg_upgrade</> requires the specification of the old and new cluster's
!      data and executable (<filename>bin</>) directories. You can also specify separate
!      user and port values, and whether you want the data linked instead of
!      copied (the default). If you use linking, the upgrade will be much
!      faster (hard link data files rather than copying them), but you
!      will no longer be able to access your old cluster once you start
       the new cluster after the upgrade.  Link mode also requires that the
       old and new cluster data directories be in the same file system.
       See <literal>pg_upgrade --help</> for a full list of options.
--- 290,312 ----
      <para>
       Always run the <application>pg_upgrade</> binary of the new server, not the old one.
       <application>pg_upgrade</> requires the specification of the old and new cluster's
!      data and executable (<filename>bin</>) directories, and their port
!      numbers.  When checking a running old server, specify the current
!      old server's port number and specify an unused port number greater
!      than 1023 for the new cluster.  Specifying an unused port number
!      helps prevent users from accidentally connecting to the server
!      during the check.  For actual upgrades, specify a similar unused
!      port number for the old and new servers;  you can use the same
!      port number for both because the old and new servers never
!      run at the same time during an upgrade.  Again, use an unused
!      port number to avoid accidental user connections.
!     </para>
!
!     <para>
!      You can also specify a user name and whether you want the data
!      linked instead of copied (the default). If you use link mode,
!      the upgrade will be much faster (no file copying), but you
!      will not be able to access your old cluster once you start
       the new cluster after the upgrade.  Link mode also requires that the
       old and new cluster data directories be in the same file system.
       See <literal>pg_upgrade --help</> for a full list of options.
*************** pg_upgrade.exe
*** 320,325 ****
--- 329,336 ----
          --new-datadir "C:/Program Files/PostgreSQL/9.0/data"
          --old-bindir "C:/Program Files/PostgreSQL/8.4/bin"
          --new-bindir "C:/Program Files/PostgreSQL/9.0/bin"
+         --old-port 5723
+         --new-port 5723
  </programlisting>

       Once started, <command>pg_upgrade</> will verify the two clusters are compatible