Hi Nikita, with your configuration you instruct PostgreSQL to 'bind()' to
not-(yet?)-assigned IP address to your computer and that is not allowed.
Please, wait with PostgreSQL until DHCP takes place or use different
configuration.

I think this is actually a systemd bug: it provides no defined way for services to wait until "all expected network interfaces are up" before starting.  If it did, I would certainly have included that dependency in the postgresql.service file.  I realize that just which things such a dependency should wait for is a squishy question, but it's failure to solve that type of question that is exactly the reason why systemd is so widely disliked for running servers.

(Disclaimer: maybe they've made things better in the several years since I had occasion to look at this.  If there is such a dependency type now, it's a postgresql packaging bug that we're not using it.)

(Disclaimer #2: there is an "After=network.target" dependency in postgresql.service; but as this bug report demonstrates, that doesn't actually do anything useful in systemd, and never has.)

(In reply to Tom Lane from comment #2)
> I think this is actually a systemd bug: it provides no defined way for
> services to wait until "all expected network interfaces are up" before
> starting.

That is right.

> If it did, I would certainly have included that dependency in the
> postgresql.service file.

Hms, for the *default* PostgreSQL service configuration we would just wait a
bit longer then we wait now.  And we would most probably not start at all for
offline boxes.

> I realize that just which things such a dependency should wait for is a
> squishy question, but it's failure to solve that type of question that is
> exactly the reason why systemd is so widely disliked for running servers.

This is valid comment, but I'm unable to help here.  There are some
disclaimers in [1] .. which seem to be valid for non-systemd servers, too.

What looks like a possible way is [2] request, but I haven't tried to write a
patch and test this, yet.  What do you think about it, Tom?

> (Disclaimer: maybe they've made things better in the several years since I
> had occasion to look at this.  If there is such a dependency type now, it's
> a postgresql packaging bug that we're not using it.)
> 
> (Disclaimer #2: there is an "After=network.target" dependency in
> postgresql.service; but as this bug report demonstrates, that doesn't
> actually do anything useful in systemd, and never has.)

I doubt there happened something useful in systemd from this POV.

Is it right that 'network.target' is more or less useless for us?  It at least
looks that it guarantees network is up during service's shutdown (for online
boxes), but I'm not sure this actually helps to something...

[1] https://www.freedesktop.org/wiki/Software/systemd/NetworkTarget/
[2] https://www.postgresql.org/message-id/20151203165520.5892.4072@wrigleys.postgresql.org

Hm, maybe I'm misunderstanding something, but it sounds to me like IP_FREEBIND would basically destroy error checking on the listen_addresses value.  That is, if you say to bind to 11.2.0.5 when you meant 10.2.0.5, you wouldn't get any complaint.  Doesn't seem like a particularly good idea, not even if the systemd people claim so.

As best I can tell, the problem here arises from Nikita's desire to specify a hard-wired IP address in listen_addresses rather than simply using '*'.  If he did the latter then it'd be just fine for the network interface to come up later.  But to use a specific IP address in the Postgres config, what he really needs is a way to write an After= spec in postgresql.service that waits for that specific interface to be ready.  If systemd has that, I don't know what it is.

The best short-term answer is probably to keep listen_addresses as '*' and rely on pg_hba.conf and/or packet filtering to reject unwanted connections.

Ah, good point, the error handling is crucial there.

Maybe we could hide IP_FREEBIND behind configuration option?  And let the
bind() be done in two steps if this option is enabled?  First bind() without
IP_FREEBIND and complain loudly if this does not succeed and the second bind()
with IP_FREEBIND.

Nikita, I bet the 'Cut the crap! How do I make network.target work for me?'
paragraph could help (from the link [1]), so it sounds like there is a way to
ensure that 'network.target' does what we expect..  but to be honest I still
need to have a look at this, too.

Anyway, that ^^ isn't completely equivalent approach as it causes unnecessary
boot delays .. Tom, does the IP_FREEBIND sound like a bad idea?  Just to be
sure that we shouldn't waste time with commitfest.

(In reply to Tom Lane from comment #4)
> Hm, maybe I'm misunderstanding something, but it sounds to me like
> IP_FREEBIND would basically destroy error checking on the listen_addresses
> value.  That is, if you say to bind to 11.2.0.5 when you meant 10.2.0.5, you
> wouldn't get any complaint.  Doesn't seem like a particularly good idea, not
> even if the systemd people claim so.
> 
> As best I can tell, the problem here arises from Nikita's desire to specify
> a hard-wired IP address in listen_addresses rather than simply using '*'. 
> If he did the latter then it'd be just fine for the network interface to
> come up later.  But to use a specific IP address in the Postgres config,
> what he really needs is a way to write an After= spec in postgresql.service
> that waits for that specific interface to be ready.  If systemd has that, I
> don't know what it is.
> 
> The best short-term answer is probably to keep listen_addresses as '*' and
> rely on pg_hba.conf and/or packet filtering to reject unwanted connections.

Yes, at this moment I use option - listen_addreseses = '*', but it's not wrong, in my opinion, for enterprise solution. I have three ethernet interfaces with different DHCP ip address's. Old version Fedora (22,23) postgresql and systemd don't have this trouble, and perhaps it's more bug in systemd than in postgresql. Well..be waiting new version systemd and postgresql, maybe this trouble will fix. Thanks for your response.

(In reply to Nikita from comment #6)
> I have three ethernet interfaces with different DHCP ip address's. Old
> version Fedora (22,23) postgresql and systemd don't have this trouble

This is interesting point.  At least I don't think something changed in
postgresql.service between F23- and F24+ from this POV.  May this be matter of
luck that the configuration in F23- worked for you?  With systemd, the
boot order might be a bit non-deterministic.

Earlier I was upgrade Fedora 21-> Fedora 22 -> Fedora 23 and postgresql normally start with listen_addresses = '10.2.0.5'. After I decide to install new version of Fedora 24 and collision this trouble. Luck config it's very rare.

(In reply to Pavel Raiskup from comment #7)
> (In reply to Nikita from comment #6)
> > I have three ethernet interfaces with different DHCP ip address's. Old
> > version Fedora (22,23) postgresql and systemd don't have this trouble
> 
> This is interesting point.  At least I don't think something changed in
> postgresql.service between F23- and F24+ from this POV.

Yeah, it's been systemd since F16 or so, and I don't think this aspect of the postgres packaging changed since then.  But it wouldn't be terribly surprising if the time of launch of the DHCP service changed relative to PG in F24 for no specific reason.

(In reply to Pavel Raiskup from comment #5)
> Ah, good point, the error handling is crucial there.
> 
> Maybe we could hide IP_FREEBIND behind configuration option?  And let the
> bind() be done in two steps if this option is enabled?  First bind() without
> IP_FREEBIND and complain loudly if this does not succeed and the second
> bind() with IP_FREEBIND.

Yeah, I think the way to present it would be a separate variable "allow_late_ip_binding" or something like that.  Try the bind normally, if that fails and the flag is on try with IP_FREEBIND, if that fails give usual error.  If it succeeds, accept the address, but maybe emit a LOG message anyway.

I'm not exactly convinced that the PG community will think this is anything but a system-specific wart, though.  IP_FREEBIND is Linux only, isn't it?

Another but much more invasive idea would be to teach the postmaster to respond to changes in the set of available interfaces by retrying its bind calls.  There have been requests to allow listen_addresses to change without a postmaster restart, which could also be satisfied by such a rewrite.  So that approach would be (much?) more work but would offer greater reward, and I think it might be an easier sell to the community.

In the end though, the listen_addresses = '*' solution corresponds exactly to one of the preferred approaches shown in [1], and it's not clear to me that it's worth expending so much work just to get to a different solution.