Re: Bug with pg_ctl -w/wait and config-only directories

On Sat, Oct 01, 2011 at 02:08:33PM -0400, Bruce Momjian wrote:
> In researching pg_ctl -w/wait mode for pg_upgrade, I found that pg_ctl
> -w's handling of configuration-only directories is often incorrect. For
> example, 'pg_ctl -w stop' checks for the postmaster.pid file to
> determine when the server is shut down, but there is no postmaster.pid
> file in the config directory, so it fails, i.e. does nothing. What is
> interesting is that specifying the real data directory does work.
>
> Similarly, pg_ctl references these data directory files:
>
> snprintf(postopts_file, MAXPGPATH, "%s/postmaster.opts", pg_data);
> snprintf(backup_file, MAXPGPATH, "%s/backup_label", pg_data);
> snprintf(recovery_file, MAXPGPATH, "%s/recovery.conf", pg_data);
> snprintf(promote_file, MAXPGPATH, "%s/promote", pg_data);
>
> I assume things that use these files also don't work for config-only
> directories.
>
> You might think that you can always just specify the real data
> directory, but that doesn't work if the server has to be started because
> you need to point to postgresql.conf. pg_ctl -w restart is a classic
> case of something that needs both the config directory and the real data
> directory. Basically, this stuff all seems broken and needs to be fixed
> or documented.
>
> What is even worse is that pre-9.1, pg_ctl start would read ports from
> the pg_ctl -o command line, but in 9.1 we changed this to force reading
> the postmaster.pid file to find the port number and socket directory
> location --- meaning, new in PG 9.1, 'pg_ctl -w start' doesn't work for
> config-only directories either. And, we can't easily connect to the
> server to get the 'data_directory' because we need to read
> postmaster.pid to get the connection settings. :-(
>
> I think this points to the need for a command-line tool to output the
> data directory location; I am not sure what to do about the new 9.1
> breakage.
>
> pg_upgrade can work around these issues by starting using the config
> directory and stopping using the real data directory, but it cannot work
> around the 9.1 pg_ctl -w start problem for config-only directories.
>
> --
> Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + It's impossible for everything to be true. +

I went through several iterations trying to find a command that can work
the way we'd like it to. (Essentially is works the way you're describing
it should.) So, in Gentoo, for the initscript, we have this really ugly
command to start the server:

su -l postgres \
-c "env PGPORT=\"${PGPORT}\" ${PG_EXTRA_ENV} \
/usr/lib/postgresql-9.0/bin/pg_ctl \
start ${WAIT_FOR_START} -t ${START_TIMEOUT} -s -D ${DATA_DIR} \
-o '-D ${PGDATA} --data-directory=${DATA_DIR} \
--silent-mode=true ${PGOPTS}'"

And to stop the server:

su -l postgres \
-c "env PGPORT=\"${PGPORT}\" ${PG_EXTRA_ENV} \
/usr/lib/postgresql-9.0/bin/pg_ctl \
stop ${WAIT_FOR_STOP} -t ${NICE_TIMEOUT} -s -D ${DATA_DIR} \
-m smart"

The default values for these are:

PGPORT='5432'
PG_EXTRA_ENV=''
WAIT_FOR_START='-w'
START_TIMEOUT='60'
WAIT_FOR_STOP='-w'
NICE_TIMEOUT='60'
DATA_DIR='/var/lib/postgresql/9.0/data'
PGDATA='/etc/postgresql-9.0'
PGOPTS=''

We don't use 'pg_ctl restart', instead we stop and then start the
server. So, I don't have an answer for that. I'd imagine passing '-D
${DATA_DIR}' would do the trick there as well.

Of course, simplifying this a bit would be welcome.

--
Mr. Aaron W. Swenson
Gentoo Linux Developer
Email : titanofold(at)gentoo(dot)org
GnuPG FP : 2C00 7719 4F85 FB07 A49C 0E31 5713 AA03 D1BB FDA0
GnuPG ID : D1BBFDA0