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