大网数据广告活动
服务器运维问答

Postgresql 10 and Ubuntu - Unable to postgresql server UP

· 52CE 我爱测

One day, My Postgresql server stopped working. Checked log. It was shutdown somehow. 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…

问题原文

原帖链接 · Jin Lim · CC BY-SA 4.0

One day, My Postgresql server stopped working. Checked log. It was shutdown somehow.

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

When I run,

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"?

It complained that there are no files and directory. so I checked if my postgresql running.

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.

It was running. but, if I check postgresql cluster.

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

It was DOWN

so I tried

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

I wasn't able to make it work. then I tried.

sudo chown -R deploy:postgres /var/lib/postgresql/10/ && sudo chmod -R u=rwX,go= /var/lib/postgresql/10/

try again.

root@ip_address:/# pg_ctlcluster 10 main start
Job for [email omitted] failed because the service did not take the steps required by its unit configuration.
See "systemctl status [email omitted]" and "journalctl -xe" for details.
root@ip_address:/# systemctl status [email omitted]
● [email omitted] - PostgreSQL Cluster 10-main
   Loaded: loaded (/lib/systemd/system/postgresql@.service; 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 omitted]: 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 omitted]: Failed with result 'protocol'.
Mar 08 16:59:53 vps584959 systemd[1]: Failed to start PostgreSQL Cluster 10-main.

Don't know what to do more. Is anybody had the same problem?

More infos.

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 omitted] has begun start-up
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
--
-- Unit [email omitted] 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 omitted]: 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 omitted]: Failed with result 'protocol'.
Mar 08 17:46:07 vps584959 systemd[1]: Failed to start PostgreSQL Cluster 10-main.
-- Subject: Unit [email omitted] has failed
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
--
-- Unit [email omitted] 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

Update - still not working.

root@myserver:~# chown -R postgres:postgres /etc/postgresql/10/main/
root@myserver:~#
root@myserver:~#
root@myserver:~# pg_ctlcluster 10 main start
Job for [email omitted] failed because the service did not take the steps required by its unit configuration.
See "systemctl status [email omitted]" and "journalctl -xe" for details.
root@myserver:~# systemctl status [email omitted]
● [email omitted] - PostgreSQL Cluster 10-main
   Loaded: loaded (/lib/systemd/system/postgresql@.service; 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 omitted]
● [email omitted] - PostgreSQL Cluster 10-main
   Loaded: loaded (/lib/systemd/system/postgresql@.service; 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)

Update 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 omitted] has begun start-up
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
--
-- Unit [email omitted] 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 omitted]: 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 omitted]: Failed with result 'protocol'.
Mar 14 13:55:43 vps584959 systemd[1]: Failed to start PostgreSQL Cluster 10-main.
-- Subject: Unit [email omitted] has failed
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
--
-- Unit [email omitted] has failed.
--
-- The result is RESULT.

and,

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:~#

已采纳解答

原帖链接 · Daniel Vérité · CC BY-SA 4.0

systemctl status postgresql is not useful to check for your postgresql instances, as it's just an umbrella service. You want systemctl status [email omitted] in your case. It would correctly show the status as inactive when the instance is down.

As explained at the start of /lib/systemd/system/postgresql@.service:

$ head /lib/systemd/system/postgresql@.service
# systemd service template for PostgreSQL clusters. The actual instances will
# be called "postgresql@version-cluster", e.g. "[email omitted]". 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

As for the original problem of the server not starting, it's because postgres should own both the configuration files and the data directory and everything in it. For some reason, you/someone/something reassigned these files to a deploy user, and that can't work with the way PosgreSQL is set up in Ubuntu/Debian. Just leave the permissions and owner as they're originally set.


Pper comments about the SSL key file: The following error shows that the permissions and ownership must be restored on the SSL private key file:

PostgreSQL error:

FATAL:  private key file "/etc/ssl/private/ssl-cert-snakeoil.key" must be owned by the database user or root

Commands to restore ownership and permissions:

$ sudo chown root:ssl-cert /etc/ssl/private/ssl-cert-snakeoil.key
$ sudo chmod 640 /etc/ssl/private/ssl-cert-snakeoil.key

摘自公开归档,保留原文语言,版式经整理,外部媒体与邮箱地址已移除。

资料来源与适用范围

资料来源
Server Fault
适用范围
以原帖系统、软件版本与发布日期为准(2020-03-08)
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY-SA 4.0

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。