Обсуждение: Cannot connect to postgresql-11 from another machine after boot
Hi,
I've been struggling with a strange (to me) issue for awhile. I have PostgreSQL 11.6 installed on my Ubuntu machine with the data directory living on a different drive than the one mounted on /. I was observing the same behavior when my machine was running Gentoo a month ago.
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:
swails@client ~ $ psql -U postgres -h 192.168.1.3
psql: could not connect to server: Connection refused
Is the server running on host "192.168.1.3" and accepting
TCP/IP connections on port 5432?
psql: could not connect to server: Connection refused
Is the server running on host "192.168.1.3" and accepting
TCP/IP connections on port 5432?
swails@client ~ $ ssh 192.168.1.3
swails@server ~ $ sudo systemctl restart postgresql
swails@server ~ $ sudo systemctl restart postgresql
swails@server ~ $ logout
Connection to 192.168.1.3 closed.
Connection to 192.168.1.3 closed.
swails@client ~ $ psql -U postgres -h 192.168.1.3
Password for user postgres:
Password for user postgres:
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.
Thanks,
Jason
Jason M. Swails
On 2/13/20 7:54 PM, Jason Swails wrote: > Hi, > > I've been struggling with a strange (to me) issue for awhile. I have > PostgreSQL 11.6 installed on my Ubuntu machine with the data directory > living on a different drive than the one mounted on /. I was observing > the same behavior when my machine was running Gentoo a month ago. > > 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: > > swails@client ~ $ psql -U postgres -h 192.168.1.3 > psql: could not connect to server: Connection refused > Is the server running on host "192.168.1.3" and accepting > TCP/IP connections on port 5432? > > swails@client ~ $ ssh 192.168.1.3 > > swails@server ~ $ sudo systemctl restart postgresql > > swails@server ~ $ logout > Connection to 192.168.1.3 closed. > > swails@client ~ $ psql -U postgres -h 192.168.1.3 > Password for user postgres: > > 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. > > Thanks, > Jason > > -- > Jason M. Swails -- Adrian Klaver adrian.klaver@aklaver.com
On 2/13/20 9:02 PM, Adrian Klaver wrote: > On 2/13/20 7:54 PM, Jason Swails wrote: >> Hi, >> >> I've been struggling with a strange (to me) issue for awhile. I have >> PostgreSQL 11.6 installed on my Ubuntu machine with the data directory >> living on a different drive than the one mounted on /. I was observing >> the same behavior when my machine was running Gentoo a month ago. >> >> 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: >> >> swails@client ~ $ psql -U postgres -h 192.168.1.3 >> psql: could not connect to server: Connection refused >> Is the server running on host "192.168.1.3" and accepting >> TCP/IP connections on port 5432? >> >> swails@client ~ $ ssh 192.168.1.3 >> >> swails@server ~ $ sudo systemctl restart postgresql >> >> swails@server ~ $ logout >> Connection to 192.168.1.3 closed. >> >> swails@client ~ $ psql -U postgres -h 192.168.1.3 >> Password for user postgres: >> >> 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. > >> >> Thanks, >> Jason >> >> -- >> Jason M. Swails > > -- Adrian Klaver adrian.klaver@aklaver.com
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.
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
Вложения
On Sun, Feb 16, 2020 at 8:51 AM Peter J. Holzer <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 "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
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
On 2020-02-17 10:17:41 -0500, Jason Swails wrote: > 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. After=network-online.target should be correct. However, see https://www.freedesktop.org/wiki/Software/systemd/NetworkTarget/ for an explanation why "the network is online" is not as simple as it looks and how to ensure that. hp -- _ | Peter J. Holzer | Story must make more sense than reality. |_|_) | | | | | hjp@hjp.at | -- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!"