☰

重启后，postgresql 仅在本地主机上监听（重新启动 postgresql 可解决问题）

tag-icon • tag-icon networking systemd postgresql
重启后，postgresql 仅在本地主机上监听（重新启动 postgresql 可解决问题）

我看到这种奇怪的行为，重新启动后，通过postgresql@11-main（systemd）启动的PG仅监听127.0.0.1:5432PG conf文件，其中明确包含以下内容：

listen_addresses = '[EXTERNAL IP ADDRESS - REDACTED], 127.0.0.1'

一旦我重新启动它，systemctl restart postgresql@11-main它就会在两个 IP 地址上监听 5432。

附言：在看到一些帖子说魔法会导致问题/混乱后，我已禁用魔法postgresql.service。然而，这并没有解决问题。

/lib/systemd/system/[email protected]

# systemd service template for PostgreSQL clusters. The actual instances will
# be called "postgresql@version-cluster", e.g. "[email protected]". The
# variable %i expands to "version-cluster", %I expands to "version/cluster".
# (%I breaks for cluster names containing dashes.)

[Unit]
Description=PostgreSQL Cluster %i
AssertPathExists=/etc/postgresql/%I/postgresql.conf
RequiresMountsFor=/etc/postgresql/%I /var/lib/postgresql/%I
PartOf=postgresql.service
ReloadPropagatedFrom=postgresql.service
Before=postgresql.service

[Service]
Type=forking
# -: ignore startup failure (recovery might take arbitrarily long)
# the actual pg_ctl timeout is configured in pg_ctl.conf
ExecStart=-/usr/bin/pg_ctlcluster --skip-systemctl-redirect %i start
# 0 is the same as infinity, but "infinity" needs systemd 229
TimeoutStartSec=0
ExecStop=/usr/bin/pg_ctlcluster --skip-systemctl-redirect -m fast %i stop
TimeoutStopSec=1h
ExecReload=/usr/bin/pg_ctlcluster --skip-systemctl-redirect %i reload
PIDFile=/var/run/postgresql/%i.pid
SyslogIdentifier=postgresql@%i
# prevent OOM killer from choosing the postmaster (individual backends will
# reset the score to 0)
OOMScoreAdjust=-900
# restarting automatically will prevent "pg_ctlcluster ... stop" from working,
# so we disable it here. Also, the postmaster will restart by itself on most
# problems anyway, so it is questionable if one wants to enable external
# automatic restarts.
#Restart=on-failure
# (This should make pg_ctlcluster stop work, but doesn't:)
#RestartPreventExitStatus=SIGINT SIGTERM

[Install]
WantedBy=multi-user.target

systemctl status

# systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; disabled; vendor preset: enabled)
   Active: inactive (dead)


# systemctl status postgresql@11-main
● [email protected] - PostgreSQL Cluster 11-main
   Loaded: loaded (/lib/systemd/system/[email protected]; indirect; vendor preset: enabled)
   Active: active (running) since Mon 2019-02-18 09:10:43 CET; 5min ago
  Process: 1121 ExecStop=/usr/bin/pg_ctlcluster --skip-systemctl-redirect -m fast 11-main stop (code=exited, status=0/SUCCESS)
  Process: 1128 ExecStart=/usr/bin/pg_ctlcluster --skip-systemctl-redirect 11-main start (code=exited, status=0/SUCCESS)
 Main PID: 1134 (postgres)
    Tasks: 10 (limit: 4915)
   CGroup: /system.slice/system-postgresql.slice/[email protected]
           ├─1134 /usr/lib/postgresql/11/bin/postgres -D /var/lib/postgresql/11/main -c config_file=/etc/postgresql/11/main/postgresql.conf
           ├─1136 postgres: 11/main: checkpointer   
           ├─1137 postgres: 11/main: background writer   
           ├─1138 postgres: 11/main: walwriter   
           ├─1139 postgres: 11/main: autovacuum launcher   
           ├─1140 postgres: 11/main: stats collector   
           ├─1141 postgres: 11/main: pglogical supervisor   
           ├─1142 postgres: 11/main: logical replication launcher   
           ├─1184 postgres: 11/main: b2b b2b [REDACTED](32854) idle
           └─1210 postgres: 11/main: b2b b2b [REDACTED](41872) idle

Feb 18 09:10:41 prod-dataserver systemd[1]: Starting PostgreSQL Cluster 11-main...
Feb 18 09:10:43 prod-dataserver systemd[1]: Started PostgreSQL Cluster 11-main.

Ubuntu 版本

# cat /etc/lsb-release 
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=18.04
DISTRIB_CODENAME=bionic
DISTRIB_DESCRIPTION="Ubuntu 18.04.1 LTS"

systemd 版本

# systemd --version
systemd 237
+PAM +AUDIT +SELINUX +IMA +APPARMOR +SMACK +SYSVINIT +UTMP +LIBCRYPTSETUP +GCRYPT +GNUTLS +ACL +XZ +LZ4 +SECCOMP +BLKID +ELFUTILS +KMOD -IDN2 +IDN -PCRE2 default-hierarchy=hybrid

答案1

这与https://serverfault.com/questions/787426/postgresql-not-listening-on-local-eth1-after-reboot-- postgres systemd 配置文件有缺陷，在启动 postgres 之前没有等待网络启动。

解决方案https://serverfault.com/a/788044/117598解决了这个问题。

警告：请编辑 中的文件。将新文件放置到/lib/systemd/system/[email protected]/etc/systemd/system/[email protected] 应该有效，但不起作用

答案2

我也遇到了同样的问题。我发现在 postgresql.conf 中替换显式 IP 地址

listen_addresses = '[EXTERNAL IP ADDRESS - REDACTED], 127.0.0.1'

任何 IP 地址也有帮助：

listen_addresses = '*'

相关内容