Incorrect Port is already in use error when restarting PostgreSQL

[urkle]

Steps to recreate.

1. start a PG server
2. connect to it via some tool (e.g. I'm using a JDBC driver via DataGrip)
3. in PGApp stop the server and then start the server

--- ERROR.. "Port 5432 is already in use"

4. look at netstat output

tcp4       0      0  127.0.0.1.5432         127.0.0.1.49565        FIN_WAIT_2
tcp4     117      0  127.0.0.1.49565        127.0.0.1.5432         CLOSE_WAIT

-- See nothing is "LISTENING" only some connections closing up. 5) wait for those connections to close.. sometimes takes up to a minute or longer.. 6) now start PG server -- It works!

The listening detection code in PG app should only be checking for listening sockets not FIN_WAIT_2 or CLOSE_WAIT sockets. as those do not impede the ability of the pgsql to start up.

[tbussmann]

Thanks for the detailed report. This seems to be related to earlier reports in #655 and maybe #611 - so far, we have not yet been able to reproduce a client holding the port open. Meanwhile, I've now managed to do so: (note that by default, psql will connect using a UNIX-domain socket and not a TCP socket, thus not triggering the issue)

• start a PostgreSQL server on port 5432
• in one terminal run watch -n1 -d "netstat -anvp tcp | grep 5432"
• see the port in state LISTEN
• in another terminal, run psql -h 127.0.0.1
• see the connection in state ESTABLISHED
• stop the server in Postgres.app
• see the LISTEN ports disappear and the connections change to FIN_WAIT_2 / CLOSE_WAIT
• try to restart the server
• see the "Port in use" Error
• enter \q in psql
• see the waiting connections to disappear in netstat

However, we don't have to wait for these connections to finish closing. If we manually start the server again (circumventing the UI) /Applications/Postgres.app/Contents/Versions/15/bin/pg_ctl start -D "/Users/bussmann/Library/Application Support/Postgres/var-15b2" it starts w/o hassle and new LISTEN ports appear in netstat together with the old FIN_WAIT_2 / CLOSE_WAIT connections.

As soon as you run a server side command in the previously connected psql like SELECT 1; the dead connection is recognized (FATAL: terminating connection due to administrator command), the FIN_WAIT_2 / CLOSE_WAIT connections disappear and an attempt to reconnect is performed.

The man page of netstat has details on the status we see here:

     CLOSE_WAIT:  The socket connection has been closed by the remote peer,
     and the system is waiting for the local application to close its half of
     the connection.
     FIN_WAIT_2:  The socket connection has been closed by the local
     application, the remote peer has acknowledged the close, and the system
     is waiting for it to close its half of the connection.

When Postges.app checks if the port is in use, we try to bind to it and emit the error in case we get a EADDRINUSE (see https://github.com/PostgresApp/PostgresApp/blob/5f9dc4fa6c676e99bcdeec604baacc936ad2263a/Postgres/Server.swift#L493) obviously, this logic needs to be refined somehow.

[jakob]

This issue should be fixed in Postgres.app 2.5.10 (released yesterday).

Background: I checked the PostgreSQL source code and found out that they set the SO_REUSEADDR flag on the socket. Apparently this flag is required, because otherwise macOS doesn't let us reuse ports when connections are still in the FIN_WAIT_2 / CLOSE_WAIT state.

Anyway, thanks a lot for all the help with this issue. I'm going to close the old issues as well!