On Sun, Feb 16, 2020 at 8:51 AM Peter J. Holzer <hjp-pg...@hjp.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
