Re: [HACKERS] pg_upgrade - add config directory setting

2011-10-10 Thread Bruce Momjian
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  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] pg_upgrade - add config directory setting

2011-10-08 Thread Bruce Momjian
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  br...@momjian.ushttp://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, --linklink instead of copying files to new cluster\n\
-l, --logfile=FILENAMElog 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=OLDPORTold cluster port number (default %d)\n\
-P, --new-port=NEWPORTnew 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 

Re: [HACKERS] pg_upgrade - add config directory setting

2011-10-07 Thread Bruce Momjian
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  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] pg_upgrade - add config directory setting

2011-10-06 Thread Bruce Momjian
Bruce Momjian wrote:
 Bruce Momjian wrote:
  Tom Lane wrote:
   Bruce Momjian br...@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  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] pg_upgrade - add config directory setting

2011-10-06 Thread Bruce Momjian
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  br...@momjian.ushttp://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 

Re: [HACKERS] pg_upgrade - add config directory setting

2011-10-01 Thread Bruce Momjian
Bruce Momjian wrote:
 Tom Lane wrote:
  Bruce Momjian br...@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  br...@momjian.ushttp://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 

Re: [HACKERS] pg_upgrade - add config directory setting

2011-09-29 Thread Bruce Momjian
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  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] pg_upgrade - add config directory setting

2011-09-29 Thread Bruce Momjian
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  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] pg_upgrade - add config directory setting

2011-09-29 Thread Alvaro Herrera

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 alvhe...@commandprompt.com
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] pg_upgrade - add config directory setting

2011-09-29 Thread Mr. Aaron W. Swenson
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


pgpRbF13InZSg.pgp
Description: PGP signature


Re: [HACKERS] pg_upgrade - add config directory setting

2011-09-29 Thread Bruce Momjian
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  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] pg_upgrade - add config directory setting

2011-09-29 Thread Steve Crawford

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] pg_upgrade - add config directory setting

2011-09-29 Thread Bruce Momjian
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  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] pg_upgrade - add config directory setting

2011-09-29 Thread Tom Lane
Bruce Momjian br...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] pg_upgrade - add config directory setting

2011-09-29 Thread Bruce Momjian
Tom Lane wrote:
 Bruce Momjian br...@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  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] pg_upgrade - add config directory setting

2011-09-29 Thread Tom Lane
Bruce Momjian br...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] pg_upgrade - add config directory setting

2011-09-29 Thread Bruce Momjian
Tom Lane wrote:
 Bruce Momjian br...@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  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] pg_upgrade - add config directory setting

2011-09-28 Thread Peter Eisentraut
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.



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] pg_upgrade - add config directory setting

2011-09-28 Thread Alvaro Herrera

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 alvhe...@commandprompt.com
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] pg_upgrade - add config directory setting

2011-09-28 Thread Steve Crawford

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] pg_upgrade - add config directory setting

2011-09-28 Thread Peter Eisentraut
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.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


[HACKERS] pg_upgrade - add config directory setting

2011-09-27 Thread Steve Crawford
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




--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] pg_upgrade - add config directory setting

2011-09-27 Thread Mr. Aaron W. Swenson
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


pgpA97Tt6jV2d.pgp
Description: PGP signature