Re: [PATCH] Allow Postgres to pick an unused port to listen

Tom, Robert, Greg, Andrew,

On Thu, Apr 13, 2023 at 12:56 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> > On Wed, Apr 12, 2023 at 1:31 PM Greg Stark <stark(at)mit(dot)edu> wrote:
> >> I don't object to using the pid file as the mechanism -- but it is a
> >> bit of an awkward UI for shell scripting. I imagine it would be handy
> >> if pg_ctl had an option to just print the port number so you could get
> >> it with a simple port=`pg_ctl -D <dir> status-port`
>
> > That's not a bad idea, and would provide some additional isolation to
> > reduce direct dependency on the PID file format.
>
> Yeah. My main concern here is with limiting our ability to change
> the pidfile format in future. If we can keep the dependencies on that
> localized to code we control, it'd be much better.
>
>
Thank you all for the feedback. It's quite useful. I think it is important
to separate this into two concerns:

1. Letting Postgres pick an unused port.
2. Retrieving the port it picked.

If I get this right, there's no significant opposition to (1) as this is
common functionality we're relying on. The most contention is around (2)
because I suggested using postmaster.pid
file, which may be considered private for the most part, at least for the
time being.

With this in mind, I still think that proceeding with (1) is a good idea,
as retrieving the port being listened on is still much easier than
involving a more complex lock file script. For example, on UNIX-like
systems, `lsof` can be typically used to do this:

```
# For IPv4
lsof -a -w -FPn -p $(head -n 1 postmaster.pid) -i4TCP -sTCP:LISTEN -P -n |
tail -n 1 | awk -F: '{print $NF}'
# For IPv6
lsof -a -w -FPn -p $(head -n 1postmaster.pid) -i6TCP -sTCP:LISTEN -P -n |
tail -n 1 | awk -F: '{print $NF}'
```

(There are also other tools that can be used to achieve much of the same)

On Windows, this can be done using PowerShell (and perhaps netstat, too):

```
# IPv4
PS> Get-NetTCPConnection -State Listen -OwningProcess (Get-Content
"postmaster.pid" -First 1) | Where-Object { $_.LocalAddress -notmatch ':' }
| Select-Object -ExpandProperty LocalPort
5432
PS> Get-NetTCPConnection -State Listen -OwningProcess (Get-Content
"postmaster.pid" -First 1) | Where-Object { $_.LocalAddress -match ':' } |
Select-Object -ExpandProperty LocalPort
5432
```

The above commands can be worked on to extract multiple ports should that
ever become a feature.

The bottom line is this decouples (1) from (2), and we can resolve them
separately if there's too much (understandable) hesitation to commit to a
particular approach to it (documenting postmaster.pid, changing its format,
amending pg_ctl functionality, etc.) I will be happy to participate in the
discovery and resolution of (2) as well.

This would allow people like myself or Mark (above in the thread) to let
Postgres pick the unused port and extract it using a oneliner for the time
being. When a better approach for server introspection will be agreed on,
we can use that.

I'll be happy to address any [styling or other] issues with the currently
proposed patch.

--
http://omnigres.org
Yurii