Thread: pg_upgrade - add config directory setting

pg_upgrade - add config directory setting

From
Steve Crawford
Date:
It would perhaps be useful to add optional --old-confdir and 
--new-confdir parameters to pg_upgrade. If these parameters are absent 
then pg_upgrade would work as it does now and assume that the config 
files are in the datadir.

The reason for this suggestion is that packages for Ubuntu (and I 
suppose Debian and possibly others) place the config files in a 
different directory than the data files.

The Ubuntu packaging, for example, puts all the configuration files in 
/etc/postgresql/VERSION/main/.

If I set the data-directories to /var/lib/postgresql/VERSION/main then 
pg_upgrade complains about missing config files.

If I set the data directories to /etc/postgresql/VERSION/main/ then 
pg_upgrade complains that the "base" subdirectory is missing.

Temporarily symlinking postgresql.conf and pg_hba.conf from the config 
directory to the data directory allowed the upgrade to run successfully 
but is a bit more kludgey and non-obvious.

Cheers,
Steve





Re: pg_upgrade - add config directory setting

From
"Mr. Aaron W. Swenson"
Date:
On Tue, Sep 27, 2011 at 04:13:41PM -0700, Steve Crawford wrote:
> It would perhaps be useful to add optional --old-confdir and
> --new-confdir parameters to pg_upgrade. If these parameters are absent
> then pg_upgrade would work as it does now and assume that the config
> files are in the datadir.
>
> The reason for this suggestion is that packages for Ubuntu (and I
> suppose Debian and possibly others) place the config files in a
> different directory than the data files.
>
> The Ubuntu packaging, for example, puts all the configuration files in
> /etc/postgresql/VERSION/main/.
>
> If I set the data-directories to /var/lib/postgresql/VERSION/main then
> pg_upgrade complains about missing config files.
>
> If I set the data directories to /etc/postgresql/VERSION/main/ then
> pg_upgrade complains that the "base" subdirectory is missing.
>
> Temporarily symlinking postgresql.conf and pg_hba.conf from the config
> directory to the data directory allowed the upgrade to run successfully
> but is a bit more kludgey and non-obvious.
>
> Cheers,
> Steve

I was just about to submit this suggestion. We do the same on Gentoo, as a
default anyway. (Users can pick their own locations for the configuration files
and data directories.) It would simplify the upgrade process by eliminating two
to four steps. (Symlink/copy configuration files in /etc/postgresql-${SLOT}
to /var/lib/postgresql-${SLOT}, same to $version++, pg_upgrade, remove symlinks.)

--
Mr. Aaron W. Swenson
Pseudonym: TitanOfOld
Gentoo Developer

Re: pg_upgrade - add config directory setting

From
Peter Eisentraut
Date:
On tis, 2011-09-27 at 16:13 -0700, Steve Crawford wrote:
> It would perhaps be useful to add optional --old-confdir and 
> --new-confdir parameters to pg_upgrade. If these parameters are absent
> then pg_upgrade would work as it does now and assume that the config 
> files are in the datadir. 

It should work the same way the postmaster itself works: If the given
directory is not a data directory, look for the postgresql.conf file and
look there for the location of the data directory.




Re: pg_upgrade - add config directory setting

From
Alvaro Herrera
Date:
Excerpts from Peter Eisentraut's message of mié sep 28 04:49:43 -0300 2011:
> On tis, 2011-09-27 at 16:13 -0700, Steve Crawford wrote:
> > It would perhaps be useful to add optional --old-confdir and 
> > --new-confdir parameters to pg_upgrade. If these parameters are absent
> > then pg_upgrade would work as it does now and assume that the config 
> > files are in the datadir. 
> 
> It should work the same way the postmaster itself works: If the given
> directory is not a data directory, look for the postgresql.conf file and
> look there for the location of the data directory.

So we need a postmaster switch:

postmaster --parse-config-and-report=data_directory

-- 
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support


Re: pg_upgrade - add config directory setting

From
Steve Crawford
Date:
On 09/28/2011 12:49 AM, Peter Eisentraut wrote:
> On tis, 2011-09-27 at 16:13 -0700, Steve Crawford wrote:
>> It would perhaps be useful to add optional --old-confdir and
>> --new-confdir parameters to pg_upgrade. If these parameters are absent
>> then pg_upgrade would work as it does now and assume that the config
>> files are in the datadir.
> It should work the same way the postmaster itself works: If the given
> directory is not a data directory, look for the postgresql.conf file and
> look there for the location of the data directory.
>
>
>
>
That would make sense to me (I actually tried setting the datadirs based 
on that assumption). It would require adding that feature to pg_upgrade 
and tweaking the docs for --XXX-datadir but would not require any new 
parameters.

Cheers,
Steve


Re: pg_upgrade - add config directory setting

From
Peter Eisentraut
Date:
On ons, 2011-09-28 at 11:53 -0300, Alvaro Herrera wrote:
> Excerpts from Peter Eisentraut's message of mié sep 28 04:49:43 -0300 2011:
> > On tis, 2011-09-27 at 16:13 -0700, Steve Crawford wrote:
> > > It would perhaps be useful to add optional --old-confdir and 
> > > --new-confdir parameters to pg_upgrade. If these parameters are absent
> > > then pg_upgrade would work as it does now and assume that the config 
> > > files are in the datadir. 
> > 
> > It should work the same way the postmaster itself works: If the given
> > directory is not a data directory, look for the postgresql.conf file and
> > look there for the location of the data directory.
> 
> So we need a postmaster switch:
> 
> postmaster --parse-config-and-report=data_directory

Perhaps.  That might have some use for pg_ctl as well.



Re: pg_upgrade - add config directory setting

From
Bruce Momjian
Date:
Peter Eisentraut wrote:
> On ons, 2011-09-28 at 11:53 -0300, Alvaro Herrera wrote:
> > Excerpts from Peter Eisentraut's message of mi? sep 28 04:49:43 -0300 2011:
> > > On tis, 2011-09-27 at 16:13 -0700, Steve Crawford wrote:
> > > > It would perhaps be useful to add optional --old-confdir and 
> > > > --new-confdir parameters to pg_upgrade. If these parameters are absent
> > > > then pg_upgrade would work as it does now and assume that the config 
> > > > files are in the datadir. 
> > > 
> > > It should work the same way the postmaster itself works: If the given
> > > directory is not a data directory, look for the postgresql.conf file and
> > > look there for the location of the data directory.
> > 
> > So we need a postmaster switch:
> > 
> > postmaster --parse-config-and-report=data_directory
> 
> Perhaps.  That might have some use for pg_ctl as well.

FYI, unless this is backpatched, which I doubt, it is only going to be
available for the _new_ cluster.

You are right that while pg_upgrade doesn't care about the location of
postgresql.conf and pg_hba.conf, we have to point to those to start the
server, and pg_upgrade does need to access some data files, so it also
needs to know about the data location.

I am unclear how to proceed with this, particularly with the backpatch
requirement.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +


Re: pg_upgrade - add config directory setting

From
Bruce Momjian
Date:
Bruce Momjian wrote:
> Peter Eisentraut wrote:
> > On ons, 2011-09-28 at 11:53 -0300, Alvaro Herrera wrote:
> > > Excerpts from Peter Eisentraut's message of mi? sep 28 04:49:43 -0300 2011:
> > > > On tis, 2011-09-27 at 16:13 -0700, Steve Crawford wrote:
> > > > > It would perhaps be useful to add optional --old-confdir and 
> > > > > --new-confdir parameters to pg_upgrade. If these parameters are absent
> > > > > then pg_upgrade would work as it does now and assume that the config 
> > > > > files are in the datadir. 
> > > > 
> > > > It should work the same way the postmaster itself works: If the given
> > > > directory is not a data directory, look for the postgresql.conf file and
> > > > look there for the location of the data directory.
> > > 
> > > So we need a postmaster switch:
> > > 
> > > postmaster --parse-config-and-report=data_directory
> > 
> > Perhaps.  That might have some use for pg_ctl as well.
> 
> FYI, unless this is backpatched, which I doubt, it is only going to be
> available for the _new_ cluster.
> 
> You are right that while pg_upgrade doesn't care about the location of
> postgresql.conf and pg_hba.conf, we have to point to those to start the
> server, and pg_upgrade does need to access some data files, so it also
> needs to know about the data location.
> 
> I am unclear how to proceed with this, particularly with the backpatch
> requirement.

Thinking some more, I don't need to know the data directory while the
server is down --- I already am starting it. pg_upgrade starts both old
and new servers during its check phase, and it could look up the
data_directory GUC variable and use that value when accessing files. 
That would work for old and new servers.  However, I assume that is
something we would not backpatch to 9.1.  

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +


Re: pg_upgrade - add config directory setting

From
Alvaro Herrera
Date:
Excerpts from Bruce Momjian's message of jue sep 29 09:56:09 -0300 2011:
> 
> Thinking some more, I don't need to know the data directory while the
> server is down --- I already am starting it. pg_upgrade starts both old
> and new servers during its check phase, and it could look up the
> data_directory GUC variable and use that value when accessing files. 
> That would work for old and new servers.  However, I assume that is
> something we would not backpatch to 9.1.  

Why not?  We've supported separate data/config dirs for a long time now,
so it seems to me that pg_upgrade not coping with them is a bug.  If
pg_upgrade starts postmaster, it seems simple to grab the data_directory
setting, is it not?

-- 
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support


Re: pg_upgrade - add config directory setting

From
"Mr. Aaron W. Swenson"
Date:
On Thu, Sep 29, 2011 at 10:44:29AM -0300, Alvaro Herrera wrote:
>
> Excerpts from Bruce Momjian's message of jue sep 29 09:56:09 -0300 2011:
> >
> > Thinking some more, I don't need to know the data directory while the
> > server is down --- I already am starting it. pg_upgrade starts both old
> > and new servers during its check phase, and it could look up the
> > data_directory GUC variable and use that value when accessing files.
> > That would work for old and new servers.  However, I assume that is
> > something we would not backpatch to 9.1.
>
> Why not?  We've supported separate data/config dirs for a long time now,
> so it seems to me that pg_upgrade not coping with them is a bug.  If
> pg_upgrade starts postmaster, it seems simple to grab the data_directory
> setting, is it not?

I was going to say. I'd view this as bringing the behavior of pg_upgrade
to a consistent state with postgres. I vote for it being backpatched to
9.0 even. For whatever my vote is worth.

--
Mr. Aaron W. Swenson
Pseudonym: TitanOfOld
Gentoo Developer

Re: pg_upgrade - add config directory setting

From
Bruce Momjian
Date:
Mr. Aaron W. Swenson wrote:
-- Start of PGP signed section.
> On Thu, Sep 29, 2011 at 10:44:29AM -0300, Alvaro Herrera wrote:
> > 
> > Excerpts from Bruce Momjian's message of jue sep 29 09:56:09 -0300 2011:
> > > 
> > > Thinking some more, I don't need to know the data directory while the
> > > server is down --- I already am starting it. pg_upgrade starts both old
> > > and new servers during its check phase, and it could look up the
> > > data_directory GUC variable and use that value when accessing files. 
> > > That would work for old and new servers.  However, I assume that is
> > > something we would not backpatch to 9.1.  
> > 
> > Why not?  We've supported separate data/config dirs for a long time now,
> > so it seems to me that pg_upgrade not coping with them is a bug.  If
> > pg_upgrade starts postmaster, it seems simple to grab the data_directory
> > setting, is it not?
> 
> I was going to say. I'd view this as bringing the behavior of pg_upgrade
> to a consistent state with postgres. I vote for it being backpatched to
> 9.0 even. For whatever my vote is worth.

Well, I would call it more of a limitation of pg_upgrade, rather than a
bug --- perhaps documenting the limitation in the back branches is
sufficient.  I think the only big argument for backpatching the feature
is that 9.1 is the first release that packagers are going to use
pg_upgrade, and fixing it now makes sense because it avoids packagers
from implementing a workaround on every platform that will go away with
9.2.

So, there are four options:

1  document the limitation and require users to use symlinks
2  add a --old/new-configdir parameter to pg_upgrade
3  have pg_upgrade find the real data dir by starting the server
4  add a flag to some tool to return the real data dir, and backpatch  that

#4 has the problem of backpatching.  I looked at #3 and the first thing
pg_upgrade does is to read PG_VERSION, and the second thing is to call
pg_controldata, both of which need the real data dir, so it is going to
require some major code ordering adjustments to do #3.

One interesting trick would be to start the old and new servers to pull
the data dir at the start only if we don't see PG_VERSION in the
specified data directory --- that would limit the overhead of starting
the servers, and limit the risk for backpatching.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +


Re: pg_upgrade - add config directory setting

From
Steve Crawford
Date:
On 09/29/2011 08:20 AM, Bruce Momjian wrote:
> ...
> 1  document the limitation and require users to use symlinks
> 2  add a --old/new-configdir parameter to pg_upgrade
> 3  have pg_upgrade find the real data dir by starting the server
> 4  add a flag to some tool to return the real data dir, and backpatch
>     that
5. (really 3a). Have pg_upgrade itself check the specified --XXX-datadir 
for postgresql.conf and use the data_directory setting therein using the 
same rules as followed by the server.

This would mean that there are no new options to pg_upgrade and that 
pg_upgrade operation would not change when postgresql.conf is in the 
data-directory. This would also make it consistent with PostgreSQL's 
notion of file-locations:

"If you wish to keep the configuration files elsewhere than the data 
directory, the postgres -D command-line option or PGDATA environment 
variable must point to the directory containing the configuration files, 
and the data_directory parameter must be set in postgresql.conf..."

So for backporting, it could just be considered a "bug fix" that aligns 
pg_upgrade's interpretation of datadir to that of the server.

Cheers,
Steve


Re: pg_upgrade - add config directory setting

From
Bruce Momjian
Date:
Steve Crawford wrote:
> On 09/29/2011 08:20 AM, Bruce Momjian wrote:
> > ...
> > 1  document the limitation and require users to use symlinks
> > 2  add a --old/new-configdir parameter to pg_upgrade
> > 3  have pg_upgrade find the real data dir by starting the server
> > 4  add a flag to some tool to return the real data dir, and backpatch
> >     that
> 5. (really 3a). Have pg_upgrade itself check the specified --XXX-datadir 
> for postgresql.conf and use the data_directory setting therein using the 
> same rules as followed by the server.
> 
> This would mean that there are no new options to pg_upgrade and that 
> pg_upgrade operation would not change when postgresql.conf is in the 
> data-directory. This would also make it consistent with PostgreSQL's 
> notion of file-locations:
> 
> "If you wish to keep the configuration files elsewhere than the data 
> directory, the postgres -D command-line option or PGDATA environment 
> variable must point to the directory containing the configuration files, 
> and the data_directory parameter must be set in postgresql.conf..."
> 
> So for backporting, it could just be considered a "bug fix" that aligns 
> pg_upgrade's interpretation of datadir to that of the server.

pg_upgrade is not about to start reading through postgresql.conf looking
for a definition for data_directory --- there are too many cases where
this could go wrong.  It would need a full postgresql.conf parser.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +


Re: pg_upgrade - add config directory setting

From
Tom Lane
Date:
Bruce Momjian <bruce@momjian.us> writes:
> pg_upgrade is not about to start reading through postgresql.conf looking
> for a definition for data_directory --- there are too many cases where
> this could go wrong.  It would need a full postgresql.conf parser.

Yeah.  I think the only sensible way to do this would be to provide an
operating mode for the postgres executable that would just parse the
config file and spit out requested values.  We've had requests for that
type of functionality before, IIRC.  The --describe-config option does
something related, but not what's needed here.
        regards, tom lane


Re: pg_upgrade - add config directory setting

From
Bruce Momjian
Date:
Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > pg_upgrade is not about to start reading through postgresql.conf looking
> > for a definition for data_directory --- there are too many cases where
> > this could go wrong.  It would need a full postgresql.conf parser.
> 
> Yeah.  I think the only sensible way to do this would be to provide an
> operating mode for the postgres executable that would just parse the
> config file and spit out requested values.  We've had requests for that
> type of functionality before, IIRC.  The --describe-config option does
> something related, but not what's needed here.

That would certainly solve the problem, though it would have to be
backpatched all the way back to 8.4, and it would require pg_upgrade
users to be on newer minor versions of Postgres.  We could minimize that
by using this feature only if postgresql.conf exists in the specified
data directory but PG_VERSION does not.

Adding this features is similar to this TODO item:
Allow configuration files to be independently validated 

This still seems like a lot to backpatch.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +


Re: pg_upgrade - add config directory setting

From
Tom Lane
Date:
Bruce Momjian <bruce@momjian.us> writes:
> Tom Lane wrote:
>> Yeah.  I think the only sensible way to do this would be to provide an
>> operating mode for the postgres executable that would just parse the
>> config file and spit out requested values.

> That would certainly solve the problem, though it would have to be
> backpatched all the way back to 8.4, and it would require pg_upgrade
> users to be on newer minor versions of Postgres.

I would just say "no" to people who expect this to work against older
versions of Postgres.  I think it's sufficient if we get this into HEAD
so that it will work in the future.
        regards, tom lane


Re: pg_upgrade - add config directory setting

From
Bruce Momjian
Date:
Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > Tom Lane wrote:
> >> Yeah.  I think the only sensible way to do this would be to provide an
> >> operating mode for the postgres executable that would just parse the
> >> config file and spit out requested values.
> 
> > That would certainly solve the problem, though it would have to be
> > backpatched all the way back to 8.4, and it would require pg_upgrade
> > users to be on newer minor versions of Postgres.
> 
> I would just say "no" to people who expect this to work against older
> versions of Postgres.  I think it's sufficient if we get this into HEAD
> so that it will work in the future.

Well, it is going to work in the future only when the _old_ version is
9.2+.  Specifically, pg_upgrade using the flag could be patched to just
9.2, but the flag has to be supported on old and new backends for that
to work.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +


Re: pg_upgrade - add config directory setting

From
Bruce Momjian
Date:
Bruce Momjian wrote:
> Tom Lane wrote:
> > Bruce Momjian <bruce@momjian.us> writes:
> > > Tom Lane wrote:
> > >> Yeah.  I think the only sensible way to do this would be to provide an
> > >> operating mode for the postgres executable that would just parse the
> > >> config file and spit out requested values.
> >
> > > That would certainly solve the problem, though it would have to be
> > > backpatched all the way back to 8.4, and it would require pg_upgrade
> > > users to be on newer minor versions of Postgres.
> >
> > I would just say "no" to people who expect this to work against older
> > versions of Postgres.  I think it's sufficient if we get this into HEAD
> > so that it will work in the future.
>
> Well, it is going to work in the future only when the _old_ version is
> 9.2+.  Specifically, pg_upgrade using the flag could be patched to just
> 9.2, but the flag has to be supported on old and new backends for that
> to work.

OK, I started working on #3, which was to start the servers to find the
data_directory setting, and developed the attached patch which mostly
does this.  However, I have found serious problems with pg_ctl -w/wait
mode and config-only directories (which pg_upgrade uses), and will start
a new thread to address this issue and then continue with this once that
is resolved.

--
  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 bdb7ddb..e9c5f25
*** a/contrib/pg_upgrade/option.c
--- b/contrib/pg_upgrade/option.c
*************** parseCommandLine(int argc, char *argv[])
*** 112,121 ****
--- 112,123 ----

              case 'd':
                  old_cluster.pgdata = pg_strdup(optarg);
+                 old_cluster.pgconfig = pg_strdup(optarg);
                  break;

              case 'D':
                  new_cluster.pgdata = pg_strdup(optarg);
+                 new_cluster.pgconfig = pg_strdup(optarg);
                  break;

              case 'g':
*************** check_required_directory(char **dirpath,
*** 319,321 ****
--- 321,379 ----
  #endif
          (*dirpath)[strlen(*dirpath) - 1] = 0;
  }
+
+ /*
+  * adjust_data_dir
+  *
+  * If a configuration-only directory was specified, find the real data dir
+  * by quering the running server.  This has limited checking because we
+  * can't check for a running server because we can't find postmaster.pid.
+  */
+ void
+ adjust_data_dir(ClusterInfo *cluster)
+ {
+     char        filename[MAXPGPATH];
+     FILE       *fd;
+     PGconn       *conn;
+     PGresult   *res;
+
+     /* If there is no postgresql.conf, it can't be a config-only dir */
+     snprintf(filename, sizeof(filename), "%s/postgresql.conf", cluster->pgconfig);
+     if ((fd = fopen(filename, "r")) == NULL)
+         return;
+     fclose(fd);
+
+     /* If PG_VERSION exists, it can't be a config-only dir */
+     snprintf(filename, sizeof(filename), "%s/PG_VERSION", cluster->pgconfig);
+     if ((fd = fopen(filename, "r")) != NULL)
+     {
+         fclose(fd);
+         return;
+     }
+
+     /* Must be a configuration directory, so query the server. */
+
+     prep_status("Finding the real data directory for the %s cluster",
+                 CLUSTER_NAME(cluster));
+
+     start_postmaster(cluster);
+
+     conn = connectToServer(cluster, "template1");
+
+     res = executeQueryOrDie(conn,
+                             "SELECT setting "
+                             "FROM pg_settings "
+                             "WHERE name = 'data_directory'");
+     assert(PQntuples(res) == 1);
+
+     pg_free(cluster->pgdata);
+     cluster->pgdata = pg_strdup(PQgetvalue(res, 0, 0));
+
+     PQclear(res);
+
+     PQfinish(conn);
+
+     stop_postmaster(false);
+
+     check_ok();
+ }
diff --git a/contrib/pg_upgrade/pg_upgrade.c b/contrib/pg_upgrade/pg_upgrade.c
new file mode 100644
index 0568aca..273561e
*** a/contrib/pg_upgrade/pg_upgrade.c
--- b/contrib/pg_upgrade/pg_upgrade.c
*************** main(int argc, char **argv)
*** 68,73 ****
--- 68,76 ----

      parseCommandLine(argc, argv);

+     adjust_data_dir(&old_cluster);
+     adjust_data_dir(&new_cluster);
+
      output_check_banner(&live_check);

      setup(argv[0], live_check);
diff --git a/contrib/pg_upgrade/pg_upgrade.h b/contrib/pg_upgrade/pg_upgrade.h
new file mode 100644
index 46aed74..0fb16ed
*** a/contrib/pg_upgrade/pg_upgrade.h
--- b/contrib/pg_upgrade/pg_upgrade.h
*************** typedef struct
*** 187,192 ****
--- 187,193 ----
      ControlData controldata;    /* pg_control information */
      DbInfoArr    dbarr;            /* dbinfos array */
      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 */
      unsigned short port;        /* port number where postmaster is waiting */
      uint32        major_version;    /* PG_VERSION of cluster */
*************** void print_maps(FileNameMap *maps, int n
*** 361,366 ****
--- 362,368 ----
  /* option.c */

  void        parseCommandLine(int argc, char *argv[]);
+ void        adjust_data_dir(ClusterInfo *cluster);

  /* relfilenode.c */

diff --git a/contrib/pg_upgrade/server.c b/contrib/pg_upgrade/server.c
new file mode 100644
index 8c4aec9..d12ff64
*** a/contrib/pg_upgrade/server.c
--- b/contrib/pg_upgrade/server.c
*************** start_postmaster(ClusterInfo *cluster)
*** 164,175 ****
       * 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\" -w -l \"%s\" -D \"%s\" "
               "-o \"-p %d %s\" start >> \"%s\" 2>&1" SYSTEMQUOTE,
!              cluster->bindir, log_opts.filename2, cluster->pgdata, cluster->port,
               (cluster->controldata.cat_ver >=
                BINARY_UPGRADE_SERVER_FLAG_CAT_VER) ? "-b" :
               "-c autovacuum=off -c autovacuum_freeze_max_age=2000000000",
--- 164,176 ----
       * 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.  When we are starting the server to find the real data
!      * directory, controldata.cat_ver is zero, meaning we assume an older server.
       */
      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",
*************** stop_postmaster(bool fast)
*** 210,215 ****
--- 211,221 ----
      const char *bindir;
      const char *datadir;

+     /*
+      * Oddly, for config-only directories, pg_ctl _stop_ needs the real
+      * data directory to check postmasteer.pid, not the config directory.
+      */
+
      if (os_info.running_cluster == &old_cluster)
      {
          bindir = old_cluster.bindir;
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
new file mode 100644
index a18f14a..b2f8239
*** a/src/backend/utils/misc/postgresql.conf.sample
--- b/src/backend/utils/misc/postgresql.conf.sample
***************
*** 46,52 ****
                      # (change requires restart)

  # If external_pid_file is not explicitly set, no extra PID file is written.
! #external_pid_file = '(none)'        # write an extra PID file
                      # (change requires restart)


--- 46,52 ----
                      # (change requires restart)

  # If external_pid_file is not explicitly set, no extra PID file is written.
! #external_pid_file = ''        # write an extra PID file
                      # (change requires restart)



Re: pg_upgrade - add config directory setting

From
Bruce Momjian
Date:
Bruce Momjian wrote:
> Bruce Momjian wrote:
> > Tom Lane wrote:
> > > Bruce Momjian <bruce@momjian.us> writes:
> > > > Tom Lane wrote:
> > > >> Yeah.  I think the only sensible way to do this would be to provide an
> > > >> operating mode for the postgres executable that would just parse the
> > > >> config file and spit out requested values.
> > > 
> > > > That would certainly solve the problem, though it would have to be
> > > > backpatched all the way back to 8.4, and it would require pg_upgrade
> > > > users to be on newer minor versions of Postgres.
> > > 
> > > I would just say "no" to people who expect this to work against older
> > > versions of Postgres.  I think it's sufficient if we get this into HEAD
> > > so that it will work in the future.
> > 
> > Well, it is going to work in the future only when the _old_ version is
> > 9.2+.  Specifically, pg_upgrade using the flag could be patched to just
> > 9.2, but the flag has to be supported on old and new backends for that
> > to work.
> 
> OK, I started working on #3, which was to start the servers to find the
> data_directory setting, and developed the attached patch which mostly
> does this.  However, I have found serious problems with pg_ctl -w/wait
> mode and config-only directories (which pg_upgrade uses), and will start
> a new thread to address this issue and then continue with this once that
> is resolved.

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.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +


Re: pg_upgrade - add config directory setting

From
Bruce Momjian
Date:
Bruce Momjian wrote:
> 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.

The attached patch uses "postmaster -C data_directory" to allow
config-only upgrades.  It will allow a normal 9.1 cluster to be upgraded
to a 9.2 config-only cluster.

--
  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 bdb7ddb..3ab1b5c
*** a/contrib/pg_upgrade/option.c
--- b/contrib/pg_upgrade/option.c
*************** parseCommandLine(int argc, char *argv[])
*** 112,121 ****
--- 112,123 ----

              case 'd':
                  old_cluster.pgdata = pg_strdup(optarg);
+                 old_cluster.pgconfig = pg_strdup(optarg);
                  break;

              case 'D':
                  new_cluster.pgdata = pg_strdup(optarg);
+                 new_cluster.pgconfig = pg_strdup(optarg);
                  break;

              case 'g':
*************** check_required_directory(char **dirpath,
*** 319,321 ****
--- 321,381 ----
  #endif
          (*dirpath)[strlen(*dirpath) - 1] = 0;
  }
+
+ /*
+  * adjust_data_dir
+  *
+  * If a configuration-only directory was specified, find the real data dir
+  * by quering the running server.  This has limited checking because we
+  * can't check for a running server because we can't find postmaster.pid.
+  */
+ void
+ adjust_data_dir(ClusterInfo *cluster)
+ {
+     char        filename[MAXPGPATH];
+     char        cmd[MAXPGPATH], cmd_output[MAX_STRING];
+     FILE       *fd, *output;
+
+     /* If there is no postgresql.conf, it can't be a config-only dir */
+     snprintf(filename, sizeof(filename), "%s/postgresql.conf", cluster->pgconfig);
+     if ((fd = fopen(filename, "r")) == NULL)
+         return;
+     fclose(fd);
+
+     /* If PG_VERSION exists, it can't be a config-only dir */
+     snprintf(filename, sizeof(filename), "%s/PG_VERSION", cluster->pgconfig);
+     if ((fd = fopen(filename, "r")) != NULL)
+     {
+         fclose(fd);
+         return;
+     }
+
+     /* Must be a configuration directory, so find the real data directory. */
+
+     prep_status("Finding the real data directory for the %s cluster",
+                 CLUSTER_NAME(cluster));
+
+     /*
+      * We don't have a data directory yet, so we can't check the PG
+      * version, so this might fail --- only works for PG 9.2+.   If this
+      * fails, pg_upgrade will fail anyway because the data files will not
+      * be found.
+      */
+     snprintf(cmd, sizeof(cmd), "\"%s/postmaster\" -D \"%s\" -C data_directory",
+              cluster->bindir, cluster->pgconfig);
+
+     if ((output = popen(cmd, "r")) == NULL ||
+         fgets(cmd_output, sizeof(cmd_output), output) == NULL)
+         pg_log(PG_FATAL, "Could not get data directory using %s: %s\n",
+         cmd, getErrorText(errno));
+
+     pclose(output);
+
+     /* Remove trailing newline */
+     if (strchr(cmd_output, '\n') != NULL)
+         *strchr(cmd_output, '\n') = '\0';
+
+     cluster->pgdata = pg_strdup(cmd_output);
+
+     check_ok();
+ }
diff --git a/contrib/pg_upgrade/pg_upgrade.c b/contrib/pg_upgrade/pg_upgrade.c
new file mode 100644
index 0568aca..273561e
*** a/contrib/pg_upgrade/pg_upgrade.c
--- b/contrib/pg_upgrade/pg_upgrade.c
*************** main(int argc, char **argv)
*** 68,73 ****
--- 68,76 ----

      parseCommandLine(argc, argv);

+     adjust_data_dir(&old_cluster);
+     adjust_data_dir(&new_cluster);
+
      output_check_banner(&live_check);

      setup(argv[0], live_check);
diff --git a/contrib/pg_upgrade/pg_upgrade.h b/contrib/pg_upgrade/pg_upgrade.h
new file mode 100644
index 46aed74..0fb16ed
*** a/contrib/pg_upgrade/pg_upgrade.h
--- b/contrib/pg_upgrade/pg_upgrade.h
*************** typedef struct
*** 187,192 ****
--- 187,193 ----
      ControlData controldata;    /* pg_control information */
      DbInfoArr    dbarr;            /* dbinfos array */
      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 */
      unsigned short port;        /* port number where postmaster is waiting */
      uint32        major_version;    /* PG_VERSION of cluster */
*************** void print_maps(FileNameMap *maps, int n
*** 361,366 ****
--- 362,368 ----
  /* option.c */

  void        parseCommandLine(int argc, char *argv[]);
+ void        adjust_data_dir(ClusterInfo *cluster);

  /* relfilenode.c */

diff --git a/contrib/pg_upgrade/server.c b/contrib/pg_upgrade/server.c
new file mode 100644
index 8c4aec9..d512ef3
*** a/contrib/pg_upgrade/server.c
--- b/contrib/pg_upgrade/server.c
*************** start_postmaster(ClusterInfo *cluster)
*** 169,175 ****
      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->pgdata, cluster->port,
               (cluster->controldata.cat_ver >=
                BINARY_UPGRADE_SERVER_FLAG_CAT_VER) ? "-b" :
               "-c autovacuum=off -c autovacuum_freeze_max_age=2000000000",
--- 169,175 ----
      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",
*************** stop_postmaster(bool fast)
*** 208,224 ****
  {
      char        cmd[MAXPGPATH];
      const char *bindir;
!     const char *datadir;

      if (os_info.running_cluster == &old_cluster)
      {
          bindir = old_cluster.bindir;
!         datadir = old_cluster.pgdata;
      }
      else if (os_info.running_cluster == &new_cluster)
      {
          bindir = new_cluster.bindir;
!         datadir = new_cluster.pgdata;
      }
      else
          return;                    /* no cluster running */
--- 208,224 ----
  {
      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 */
*************** stop_postmaster(bool fast)
*** 226,232 ****
      snprintf(cmd, sizeof(cmd),
               SYSTEMQUOTE "\"%s/pg_ctl\" -w -l \"%s\" -D \"%s\" %s stop >> "
               "\"%s\" 2>&1" SYSTEMQUOTE,
!              bindir, log_opts.filename2, datadir, fast ? "-m fast" : "",
               log_opts.filename2);

      exec_prog(fast ? false : true, "%s", cmd);
--- 226,232 ----
      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);

Re: pg_upgrade - add config directory setting

From
Bruce Momjian
Date:
Bruce Momjian wrote:
> Bruce Momjian wrote:
> > 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.
> 
> The attached patch uses "postmaster -C data_directory" to allow
> config-only upgrades.  It will allow a normal 9.1 cluster to be upgraded
> to a 9.2 config-only cluster.

Applied.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +


Re: pg_upgrade - add config directory setting

From
Bruce Momjian
Date:
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

Re: pg_upgrade - add config directory setting

From
Bruce Momjian
Date:
Bruce Momjian wrote:
> 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.

Applied to head.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +