Re: TODO item for pg_ctl and server detection

Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > While I am working on pg_ctl, I saw this TODO item:
> > Have the postmaster write a random number to a file on startup that
> > pg_ctl checks against the contents of a pg_ping response on its initial
> > connection (without login)
>
> > This will protect against connecting to an old instance of the
> > postmaster in a different or deleted subdirectory.
>
> > http://archives.postgresql.org/pgsql-bugs/2009-10/msg00110.php
> > http://archives.postgresql.org/pgsql-bugs/2009-10/msg00156.php
>
> > Based on our new PQPing(), do we ever want to implement this or should I
> > remove the TODO item? It seems this would require a server connection,
> > which is something we didn't want to force pg_ctl -w to do in case
> > authentication is broken.
>
> Well, rereading that old thread makes me realize that what you just
> implemented is still pretty far short of what was discussed. In
> particular, this implementation entirely fails to cope with the
> possibility that a Windows postmaster is using a specialized
> listen_addresses setting that has to be taken into account in order to
> get a TCP connection. I wonder whether we should revert this patch and
> have another go at the idea of a separate postmaster.ports status file
> with a line for each active port.

I had forgotten about having to use TCP and needing to honor
listen_address restrictions. We only need one valid listen_address so I
went ahead and added a line to the postmaster.pid file.

I am not sure what a separate file will buy us except additional files
to open/manage.

> The business with a magic number can't be implemented unless we actually
> add a new separate pg_ping protocol. PQping() has removed a lot of the
> pressure to have that, namely all the authentication-failure problem
> cases. I'm not sure that the case where you're looking at an inactive
> data directory but there's a live postmaster someplace else with the
> same port number is important enough to justify new protocol all by
> itself.

Yes, that was my calculus too. I realized that we create session ids by
merging the process id and backend start time, so I went ahead and added
the postmaster start time epoch to the postmaster.pid file. While there
is no way to pass back the postmaster start time from PQping, I added
code to pg_ctl to make sure the time in the postmaster.pid file is not
_before_ pg_ctl started running. We only check PQping() after we have
started the postmaster ourselves, so it fits our needs.

Patch attached.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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