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

On 2/17/20 7:17 AM, Jason Swails wrote:
> 
> 
> On Sun, Feb 16, 2020 at 8:51 AM Peter J. Holzer <hjp-pgsql@hjp.at 
> <mailto:hjp-pgsql@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 

Without a conf file the server will not start(the ok notwithstanding):

sudo /etc/init.d/postgresql12 start
Starting PostgreSQL: ok

postmaster: could not access the server configuration file 
"/usr/local/pgsql12/data/postgresql.conf": No such file or directory

ps ax | grep postmaster

Returns nothing

Whereas with conf file:

sudo /etc/init.d/postgresql12 start
Starting PostgreSQL: ok

--2020-02-17 08:35:05.026 PST-0LOG:  starting PostgreSQL 12.1 on 
x86_64-pc-linux-gnu, compiled by gcc (SUSE Linux) 7.4.1 20190905 
[gcc-7-branch revision 275407], 64-bit
--2020-02-17 08:35:05.026 PST-0LOG:  listening on IPv4 address 
"0.0.0.0", port 5432
--2020-02-17 08:35:05.026 PST-0LOG:  listening on IPv6 address "::", 
port 5432
--2020-02-17 08:35:05.075 PST-0LOG:  listening on Unix socket 
"/tmp/.s.PGSQL.5432"
--2020-02-17 08:35:05.131 PST-0LOG:  redirecting log output to logging 
collector process
--2020-02-17 08:35:05.131 PST-0HINT:  Future log output will appear in 
directory "pg_log".


ps ax | grep postmaster

/usr/local/pgsql12/bin/postmaster -D /usr/local/pgsql12/data


> "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.


What is the actual error message?

> 
> 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.

You can also use pg_hba.conf to restrict access:

https://www.postgresql.org/docs/11/auth-pg-hba-conf.html

> 
> Thanks,
> Jason
> 
> -- 
> Jason M. Swails


-- 
Adrian Klaver
adrian.klaver@aklaver.com