Re: Cannot connect to postgresql-11 from another machine after boot

On Sun, Feb 16, 2020 at 8:51 AM Peter J. Holzer <hjp-pgsql(at)hjp(dot)at> wrote:

> On 2020-02-13 21:03:48 -0800, Adrian Klaver wrote:
> > On 2/13/20 9:02 PM, Adrian Klaver wrote:
> > > On 2/13/20 7:54 PM, Jason Swails wrote:
> > > > The problem is that after my machine boots, I'm unable to connect to
> > > > the server from anywhere except localhost. Running a simple
> > > > "systemctl restart postgresql" fixes the problem and allows me to
> > > > connect from anywhere on my LAN. Here is an example of this
> > > > behavior:
> [...]
> > > >
> > > > So the first connection attempt fails. But when I restart the
> > > > service and try again (doing nothing else in between), the
> > > > connection attempt succeeds. My workaround has been to simply
> > > > restart the service every time my machine reboots, but I'd really
> > > > like to have a more reliable startup.
> > > >
> > > > Any ideas how to start hunting down the root cause? I think this
> > > > started happening after I moved the data directory to another drive.
> > >
> > > I would start by looking in the system log to see what it records when
> > > the service tries to start on reboot.
> >
> > Hit send to soon. At a guess the Postgres service is starting before the
> > drive is mounted.
>
> I don't think this has anything to do with the drive. If the drive
> wasn't mounted he couldn't connect from localhost either.
>
> What is probably happening is that postgresql is configured to listen on
> localhost and the IP address of the ethernet interface and is starting
> before the etherned interface is ready. So it is listening only on
> localhost (there should be an error message regarding the other address
> in the log). When he restarts postgresql some time later, the interface
> is ready.
>
> It should be possible to solve this by adding the right dependencies to
> systemd.
>

I actually think the problem was both of these. The postgresql.conf file
was on the non-root drive that probably wasn't mounted before postgresql
started up -- I think the "default" listen_addresses when no conf file is
available is just "localhost". To fix this, I added "After=home.mount" to
the postgresql systemd service. Once I did that, I started seeing the
error message regarding the other address in the log, so I suspected
exactly what you mentioned here.

I then added "network.target", "networking.service", and
"network-online.target" to the After line of the postgresql.service systemd
file, but it still didn't fix the problem. I ultimately had to change
listen_addresses from "localhost,192.168.1.3" to "*". It's certainly not
my favorite approach as the former is stricter and therefore more secure.
But I don't have port forwarding set up for the postgres port, so my router
should serve as a suitable firewall for my small-scale home database setup.

Thanks,
Jason

--
Jason M. Swails