Exact procedure of online restart of pgbouncer 1.20 with help of so_reuseport

[MaximBoguk]

I yet can not understand how to perform online restart of new pgbouncer 1.20 WITHOUT losing long living client connections.

so_reuseport in my understanding can not help with transfer already established client tcp connections from the old pgbouncer to new one, so killing old pgbouncer lead to established db connection break as application concerned (and not every apps, especially legacy able to reconnect in case of connection to the database lost, not talking about graceful reconnect without producing user visible anomalies).

[JelteF]

Thanks a lot for the question. To clarify: We purposefully deprecated the -R flag of pgbouncer, without actually disabling it in 1.20. So you can still use it for now if it worked well enough for you so far. The main reason for deprecating was to get some feedback on how it was used in production by people. Because due to its TLS connection limitation, it did not seem very useful for production usage these days. So, some questions back:

1. Am I correct in assuming you are using the -R flag?
2. Do use use TLS connections?
3. How long do these long lived connections actually live? e.g. up to 2/5/10/20 minutes or indefinitely.
4. Can you trigger a controlled reconnect of these connections? e.g. by restarting the application

Other than that I tried to create some steps for you, but it turned out that what I had in mind didn't work as well as I anticipated. So I created a PR that fixes the issues I ran into: #902 Could you let me know if that PR and the steps in the description work for your usecase?

[MaximBoguk]

Hi,

1. Yes
2. No (one of reasons - TLS overhead, second - problem with online restarts)
3. Close to indefinitely in some cases, many hours-days usually without adequate control on my side (big legacy systems in their best)
4. Without serious visible downtime - no (what's worse in many cases app cannot reconnect automatically if connection lost).

It's technically possible to wait until connection to the old pgbouncer instance finally graceful ended (few days should be enough most time), but so far there are no way to prevent new connections going to the old instance of pgbouncer with so_reuseport.

PS: about #902 - going to check it.

[JelteF]

Were you able to try out #902 ? Feedback on it is definitely appreciated. I just updated it a bit (added tests, added admin command and improved the docs).

[MaximBoguk]

Unfortunately #902 not much help in our case. It's close to impossible to do "Cause all clients reconnect." It require a complete shutdown of whole project with large amount of independent services/servers (e.g. it cannot be done gracefully in reasonable amount of time and without long downtime). But it's better than nothing.
In our case #902 can be done via waiting a few days to allow most/all applications finally close connection to pgbouncer instance A and then follow recommended rolling upgrade sequence. I'll try it someday in the next moths when time allow.