Hi Christoph et al,

Some services cope badly when their postgresql connection goes down.
Arguably, software should work in that scenario, but practically that's
not the case. For instance, debci-collector just hangs and passenger
stops processing requests. When these run on the same machine as
postgresql, we may mitigate the damage by restarting them.
Unfortunately, that's difficult to do with the current systemd units.

We have a postgresql.service meta unit and several postgresql@*.service
instances. What is being restarted by package upgrades and by
needrestart is the instances. The meta unit remains started. Declaring
dependencies on the right instance is difficult as it changes with every
distribution upgrade. The meta unit solves this using a
systemd-generator.

Ideally, I could add an postgresql@.service.d override indicating that
all instances should propagate restarts to another unit. Unfortunately,
that's not implemented[1] in systemd. The target unit could
Requires=postgresl@15-main.service, but we may not set up RequiredBy as
that's only valid in the [Install] section.

If postgresql.service were to declare Requires for each instance instead
of Wants, then postgresql.service were restarted whenever any instance
is restarted. Then other services could Requires=postgresql.service to
propagate those restarts. Does that sound viable? I'm not sure what
other implications this has. Thanks for considering.

Helmut

[1] https://github.com/systemd/systemd/issues/32382

Re: Helmut Grohne

Would that mean that restarting one instance would trigger restarting
the other instances on that machine?

Christoph

Hi Christoph,

Yes, indeed. postgresql@.service declares PartOf=postgresql.service such
that when restarting postgresql.service all instances are restarted.
Thanks for your attention to detail.

Obviously such a circular restart triggering is not what we want here.
So I guess we'd need two meta-units to implement what I am asking for.
Restarting postgresql.service should continue to restart all instances.

Say we were to add a postgresql-running.target and then have the
postgresql-generator also generate a drop-in for that target making it
PartOf=postgresql@INST.service for each instance. Then other services
could Require=postgresql-running.target to be restarted when any
postgresql instance restarts.

That said, I'm not super convinced that this is a good solution. Maybe
spending more effort on the debci side is warranted. In principle, debci
should work with a remote postgresql server and then no such
notification can happen.

Helmut

Re: Helmut Grohne

This sounds like a very complicated solution for a problem that lies
elsewhere. The other service should be able to say
"InterestedInRestartsOf=postgresql@*.service" with a pattern match.
(No idea if that exists.)

That said, there is probably room for improvement here. I remember
that when I wrote postgresql.service, I had to choose between
synchronous start (postgresql.service waits for postgresql@*.service
to start) and synchronous stop (same on shutdown). There was no way to
make both wait. On to of that, postgresql.service should rather be
postgresql.target, but back then, targets didn't support reloading.

I admit that it's been a while since I last tried to understand the
differences between Wants After PartOf and the like...

You are not alone. Apps freaking out after database restarts is still
widely seen.

Christoph

Control: reassign -1 debci-collector
Control: retitle -1 debci-collector should handle a postgresql connection failure in a better way

I'm reassigning the bug to debci-collector as there is no useful thing
postgresql can do to support the use case. What follows is context for
debci maintainers.

When restarting postgresql (and thus closing existing connections),
debci-collector gets stuck. You get this:

| E, [2025-05-31T06:44:25.008155 #1704] ERROR -- #<Bunny::Session:0x938 guest@ci.example.com:5671, vhost=/, addresses=[ci.example.com:5671]>: Uncaught exception from consumer #<Bunny::Consumer:1355380 @channel_id=1 @queue=debci_results> @consumer_tag=bunny-1747892779000-472546353095>: #<ActiveRecord::StatementInvalid: PG::ConnectionBad: PQconsumeInput() FATAL:  terminating connection due to administrator command
| server closed the connection unexpectedly
|         This probably means the server terminated abnormally
|         before or while processing the request.
| > @ /usr/share/rubygems-integration/all/gems/activerecord-6.1.7.10/lib/active_record/connection_adapters/postgresql_adapter.rb:687:in `exec_prepared'

And then for every further result being processed, you get this:

| E, [2025-05-31T06:45:05.396999 #1704] ERROR -- #<Bunny::Session:0x938 guest@ci.example.com:5671, vhost=/, addresses=[ci.example.com:5671]>: Uncaught exception from consumer #<Bunny::Consumer:1355380 @channel_id=1 @queue=debci_results> @consumer_tag=bunny-1747892779000-472546353095>: #<ActiveRecord::StatementInvalid: PG::ConnectionBad: PQsocket() can't get socket descriptor> @ /usr/share/rubygems-integration/all/gems/activerecord-6.1.7.10/lib/active_record/connection_adapters/postgresql_adapter.rb:687:in `exec_prepared'

This is due to how debci in general uses ActiveRecord. Looking into
lib/debci/db.rb, we may see that the last line is:

| Debci::DB.establish_connection

I understand this as one connection being opened at program startup and
its kept for the entire process lifetime. When it is closed, stuff just
fails.

It's not clear to me how to fix this, but ActiveRecord does have
ActiveRecord::Base.connection_pool.with_connection. I guess a first step
would be wrapping all database interactions with this such that
ActiveRecord can keep track of when connections are leased and released.
Then, we may request that the pool closes idle connections, but I
wouldn't know how.

The key complaint in this bug report is the failure mode. I suggest that
it becomes resilient to connection failure, but another way of dealing
with this is propagating the exception and terminating the
debci-collector process such that systemd can restart it. Solving it
that way would be a reasonable thing to do from my point of view.
Unfortunately, I did not figure out where that exception is caught and
logged rather than propagated.

Any ideas on how to move forward here?

Helmut

Hi,

Thanks for the bug report.

I agree that this is a problem, and we have been bitten by it on
ci.debian.net a few times. However, I and kanashiro tried to reproduce
this for some time and failed. We just realized that your logs show a
stable system, and thus activerecord 6.x, while we tried with trixie,
activerecord 7.2, which AFAICT reestablishes the connection on its own.

ci.debian.net has been upgraded to trixie since a few weeks, and all is
well there, so I have little incentive to hunt this down. It may very
well be that this is fixed by just upgrading.

When you upgrade, if you remember, please update the bug here.