有一天，我的 Postgresql 服务器停止工作了。检查日志。它不知怎么就被关闭了。
root@ip_address:/# tail /var/log/postgresql/postgresql-10-main.log
2020-02-19 06:47:49.215 CET [23497] LOG: received smart shutdown request
2020-02-19 06:47:49.477 CET [23497] LOG: worker process: logical replication launcher (PID 23512) exited with exit code 1
2020-02-19 06:47:49.482 CET [23507] LOG: shutting down
2020-02-19 06:47:49.546 CET [23497] LOG: database system is shut down
当我跑步的时候，
root@ip_address:/# psql
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
它抱怨说没有文件和目录。所以我检查了我的postgresql运行。
root@ip_address:/# systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
Active: active (exited) since Sun 2020-03-08 16:19:24 CET; 26min ago
Process: 30136 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Main PID: 30136 (code=exited, status=0/SUCCESS)
Mar 08 16:19:24 vps584959 systemd[1]: Starting PostgreSQL RDBMS...
Mar 08 16:19:24 vps584959 systemd[1]: Started PostgreSQL RDBMS.
它正在运行。但是，如果我检查 postgresql 集群。
root@ip_address:/# pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
10 main 5432 down postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log
它是向下
所以我尝试了
root@ip_address:/# pg_ctlcluster 10 main start
Error: Config owner (deploy:1003) and data owner (postgres:114) do not match, and config owner is not root
我无法让它工作。然后我尝试了。
sudo chown -R deploy:postgres /var/lib/postgresql/10/ && sudo chmod -R u=rwX,go= /var/lib/postgresql/10/
再试一次。
root@ip_address:/# pg_ctlcluster 10 main start
Job for [email protected] failed because the service did not take the steps required by its unit configuration.
See "systemctl status [email protected]" and "journalctl -xe" for details.
root@ip_address:/# systemctl status [email protected]
● [email protected] - PostgreSQL Cluster 10-main
Loaded: loaded (/lib/systemd/system/[email protected]; indirect; vendor preset: enabled)
Active: failed (Result: protocol) since Sun 2020-03-08 16:59:53 CET; 2min 52s ago
Process: 31635 ExecStart=/usr/bin/pg_ctlcluster --skip-systemctl-redirect 10-main start (code=exited, status=1/FAILURE)
Main PID: 23497 (code=exited, status=0/SUCCESS)
Mar 08 16:59:53 vps584959 systemd[1]: Starting PostgreSQL Cluster 10-main...
Mar 08 16:59:53 vps584959 postgresql@10-main[31635]: Error: /usr/lib/postgresql/10/bin/pg_ctl /usr/lib/postgresql/10/bin/pg_ctl start -D /var/lib/postgresql/10/main -l /var/log/postgre
Mar 08 16:59:53 vps584959 systemd[1]: [email protected]: Can't open PID file /var/run/postgresql/10-main.pid (yet?) after start: No such file or directory
Mar 08 16:59:53 vps584959 systemd[1]: [email protected]: Failed with result 'protocol'.
Mar 08 16:59:53 vps584959 systemd[1]: Failed to start PostgreSQL Cluster 10-main.
不知道该怎么办。有人遇到过同样的问题吗？
更多信息。
root@ip_address:/var/run/postgresql# ls
total 0
drwxrwsr-x 3 postgres postgres 60 Feb 19 06:47 .
drwxr-xr-x 28 root root 1060 Mar 8 13:58 ..
drwxr-s--- 2 postgres postgres 40 Feb 19 06:47 10-main.pg_stat_tmp
root@vps584959:~# journalctl -xe
Mar 08 17:46:07 vps584959 sudo[2154]: root : TTY=pts/0 ; PWD=/root ; USER=root ; COMMAND=/bin/systemctl start postgresql@10-main
Mar 08 17:46:07 vps584959 sudo[2154]: pam_unix(sudo:session): session opened for user root by root(uid=0)
Mar 08 17:46:07 vps584959 systemd[1]: Starting PostgreSQL Cluster 10-main...
-- Subject: Unit [email protected] has begun start-up
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
--
-- Unit [email protected] has begun starting up.
Mar 08 17:46:07 vps584959 postgresql@10-main[2157]: Error: Config owner (deploy:1003) and data owner (root:0) do not match, and config owner is not root
Mar 08 17:46:07 vps584959 systemd[1]: [email protected]: Can't open PID file /var/run/postgresql/10-main.pid (yet?) after start: No such file or directory
Mar 08 17:46:07 vps584959 systemd[1]: [email protected]: Failed with result 'protocol'.
Mar 08 17:46:07 vps584959 systemd[1]: Failed to start PostgreSQL Cluster 10-main.
-- Subject: Unit [email protected] has failed
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
--
-- Unit [email protected] has failed.
--
-- The result is RESULT.
Mar 08 17:46:07 vps584959 sudo[2154]: pam_unix(sudo:session): session closed for user root
Mar 08 17:46:08 vps584959 sshd[2152]: Invalid user ftp1 from x.x.x.x port 57060
Mar 08 17:46:08 vps584959 sshd[2152]: pam_unix(sshd:auth): check pass; user unknown
Mar 08 17:46:08 vps584959 sshd[2152]: pam_unix(sshd:auth): authentication failure; logname= uid=0 euid=0 tty=ssh ruser= rhost=x.x.x.x
Mar 08 17:46:09 vps584959 sshd[2152]: Failed password for invalid user ftp1 from 159.89.196.75 port 57060 ssh2
Mar 08 17:46:10 vps584959 sshd[2152]: Received disconnect from x.x.x.x port 57060:11: Bye Bye [preauth]
Mar 08 17:46:10 vps584959 sshd[2152]: Disconnected from invalid user ftp1 159.89.196.75 port 57060 [preauth]
Mar 08 17:46:11 vps584959 sshd[2150]: pam_unix(sshd:auth): authentication failure; logname= uid=0 euid=0 tty=ssh ruser= rhost=x.x.x.x user=root
Mar 08 17:46:12 vps584959 sshd[2159]: pam_unix(sshd:auth): authentication failure; logname= uid=0 euid=0 tty=ssh ruser= rhost=x.x.x.x user=root
Mar 08 17:46:13 vps584959 sshd[2150]: Failed password for root from xx.xx.xx.xx port 20408 ssh2
更新 - 还是行不通。
root@myserver:~# chown -R postgres:postgres /etc/postgresql/10/main/
root@myserver:~#
root@myserver:~#
root@myserver:~# pg_ctlcluster 10 main start
Job for [email protected] failed because the service did not take the steps required by its unit configuration.
See "systemctl status [email protected]" and "journalctl -xe" for details.
root@myserver:~# systemctl status [email protected]
● [email protected] - PostgreSQL Cluster 10-main
Loaded: loaded (/lib/systemd/system/[email protected]; indirect; vendor preset: enabled)
Active: failed (Result: protocol) since Thu 2020-03-12 00:09:43 CET; 7s ago
Process: 23767 ExecStart=/usr/bin/pg_ctlcluster --skip-systemctl-redirect 10-main start (code=exited, status=1/FAILURE)
root@vps584959:~# systemctl status [email protected]
● [email protected] - PostgreSQL Cluster 10-main
Loaded: loaded (/lib/systemd/system/[email protected]; indirect; vendor preset: enabled)
Active: failed (Result: protocol) since Thu 2020-03-12 00:09:43 CET; 11min ago
Process: 23767 ExecStart=/usr/bin/pg_ctlcluster --skip-systemctl-redirect 10-main start (code=exited, status=1/FAILURE)
更新 2
root@vps584959:~# journalctl -xe
--
-- Unit UNIT has finished starting up.
--
-- The start-up result is RESULT.
Mar 14 13:55:16 vps584959 systemd[31170]: Startup finished in 171ms.
-- Subject: User manager start-up is now complete
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
--
-- The user manager instance for user 0 has been started. All services queued
-- for starting have been started. Note that other services might still be starting
-- up or be started at any later time.
--
-- Startup of the manager took 171927 microseconds.
Mar 14 13:55:17 vps584959 sshd[31156]: Failed password for root from 49.88.112.111 port 29693 ssh2
Mar 14 13:55:18 vps584959 sshd[31156]: Received disconnect from 49.88.112.111 port 29693:11: [preauth]
Mar 14 13:55:18 vps584959 sshd[31156]: Disconnected from authenticating user root 49.88.112.111 port 29693 [preauth]
Mar 14 13:55:18 vps584959 sshd[31156]: PAM 2 more authentication failures; logname= uid=0 euid=0 tty=ssh ruser= rhost=49.88.112.111 user=root
Mar 14 13:55:33 vps584959 sshd[31363]: pam_unix(sshd:auth): authentication failure; logname= uid=0 euid=0 tty=ssh ruser= rhost=134.17.94.237 user=root
Mar 14 13:55:35 vps584959 sshd[31363]: Failed password for root from 134.17.94.237 port 3684 ssh2
Mar 14 13:55:35 vps584959 sshd[31363]: Received disconnect from 134.17.94.237 port 3684:11: Bye Bye [preauth]
Mar 14 13:55:35 vps584959 sshd[31363]: Disconnected from authenticating user root 134.17.94.237 port 3684 [preauth]
Mar 14 13:55:43 vps584959 systemd[1]: Starting PostgreSQL Cluster 10-main...
-- Subject: Unit [email protected] has begun start-up
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
--
-- Unit [email protected] has begun starting up.
Mar 14 13:55:43 vps584959 postgresql@10-main[31373]: Error: /usr/lib/postgresql/10/bin/pg_ctl /usr/lib/postgresql/10/bin/pg_ctl start -D /var/lib/postgresql/10/main -l /var/log/p
Mar 14 13:55:43 vps584959 postgresql@10-main[31373]: 2020-03-14 13:55:43.696 CET [31378] FATAL: private key file "/etc/ssl/private/ssl-cert-snakeoil.key" must be owned by the da
Mar 14 13:55:43 vps584959 postgresql@10-main[31373]: 2020-03-14 13:55:43.698 CET [31378] LOG: database system is shut down
Mar 14 13:55:43 vps584959 postgresql@10-main[31373]: pg_ctl: could not start server
Mar 14 13:55:43 vps584959 postgresql@10-main[31373]: Examine the log output.
Mar 14 13:55:43 vps584959 systemd[1]: [email protected]: Can't open PID file /var/run/postgresql/10-main.pid (yet?) after start: No such file or directory
Mar 14 13:55:43 vps584959 systemd[1]: [email protected]: Failed with result 'protocol'.
Mar 14 13:55:43 vps584959 systemd[1]: Failed to start PostgreSQL Cluster 10-main.
-- Subject: Unit [email protected] has failed
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
--
-- Unit [email protected] has failed.
--
-- The result is RESULT.
和，
root@vps584959:~# cat /var/log/postgresql/postgresql-10-main.log
.
.
2020-03-12 00:09:43.609 CET [23773] FATAL: private key file "/etc/ssl/private/ssl-cert-snakeoil.key" must be owned by the database user or root
2020-03-12 00:09:43.611 CET [23773] LOG: database system is shut down
pg_ctl: could not start server
Examine the log output.
2020-03-14 13:55:43.696 CET [31378] FATAL: private key file "/etc/ssl/private/ssl-cert-snakeoil.key" must be owned by the database user or root
2020-03-14 13:55:43.698 CET [31378] LOG: database system is shut down
pg_ctl: could not start server
Examine the log output.
root@vps584959:~#
答案1
systemctl status postgresql对于检查您的 postgresql 实例没有用，因为它只是一项伞状服务。您需要根据自己的情况来选择。当实例关闭时，它会正确地显示状态为非活动状态。systemctl status [email protected]
正如开头所解释的那样：/lib/systemd/system/[email protected]
$ head /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
至于服务器无法启动的原始问题，这是因为postgres应该拥有配置文件和数据目录以及其中的所有内容。出于某种原因，您/某人/某事将这些文件重新分配给用户deploy，而这无法与 Ubuntu/Debian 中设置 PosgreSQL 的方式兼容。只需保留最初设置的权限和所有者即可。
关于 SSL 密钥文件的 Pper 注释：以下错误表明必须在 SSL 私钥文件上恢复权限和所有权：
PostgreSQL 错误：
FATAL: private key file "/etc/ssl/private/ssl-cert-snakeoil.key" must be owned by the database user or root
恢复所有权和权限的命令：
$ sudo chown root:ssl-cert /etc/ssl/private/ssl-cert-snakeoil.key
$ sudo chmod 640 /etc/ssl/private/ssl-cert-snakeoil.key
答案2
是的。这是由于您系统上的 postgresql 文件夹的权限问题造成的。请尝试使用 0777