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(at)hjp(dot)at
> <mailto: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

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(dot)klaver(at)aklaver(dot)com