1

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 postgresql@10-main.service failed because the service did not take the steps required by its unit configuration.
See "systemctl status postgresql@10-main.service" and "journalctl -xe" for details.
root@ip_address:/# systemctl status postgresql@10-main.service
● postgresql@10-main.service - 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]: postgresql@10-main.service: 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]: postgresql@10-main.service: 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 postgresql@10-main.service has begun start-up
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
--
-- Unit postgresql@10-main.service 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]: postgresql@10-main.service: 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]: postgresql@10-main.service: Failed with result 'protocol'.
Mar 08 17:46:07 vps584959 systemd[1]: Failed to start PostgreSQL Cluster 10-main.
-- Subject: Unit postgresql@10-main.service has failed
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
--
-- Unit postgresql@10-main.service 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 postgresql@10-main.service failed because the service did not take the steps required by its unit configuration.
See "systemctl status postgresql@10-main.service" and "journalctl -xe" for details.
root@myserver:~# systemctl status postgresql@10-main.service
● postgresql@10-main.service - 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 postgresql@10-main.service
● postgresql@10-main.service - 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 postgresql@10-main.service has begun start-up
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
--
-- Unit postgresql@10-main.service 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]: postgresql@10-main.service: 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]: postgresql@10-main.service: Failed with result 'protocol'.
Mar 14 13:55:43 vps584959 systemd[1]: Failed to start PostgreSQL Cluster 10-main.
-- Subject: Unit postgresql@10-main.service has failed
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
--
-- Unit postgresql@10-main.service 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:~#
3
  • Postgres is complaining because /etc/postgresql/10/main is not owned neither by root nor postgres. – Piotr P. Karwasz Mar 8 '20 at 20:24
  • so how do I approach this problem? – Jin Lim Mar 8 '20 at 22:36
  • Do a recursive chown to postgres:postgres for both /etc/postgres and /var/lib/postgres. – Piotr P. Karwasz Mar 9 '20 at 5:28
3

systemctl status postgresql is not useful to check for your postgresql instances, as it's just an umbrella service. You want systemctl status postgresql@10-main.service 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. "postgresql@9.3-main". 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
11
  • so I did this.►►► 'sudo chown -R deploy:deploy /var/lib/postgresql/10/ && sudo chmod -R u=rwX,go= /var/lib/postgresql/10/' - Anything I can do for this more? I assume Ubuntu update made this all mess... :( – Jin Lim Mar 9 '20 at 23:50
  • 1
    @JinLim: The files that you have chowned to deploy:deploy must be chowned to postgres:postgres instead. – Daniel Vérité Mar 10 '20 at 0:27
  • Nope. Did not work. ►►►'sudo chown -R postgres:postgres /var/lib/postgresql/10/ && sudo chmod -R u=rwX,go= /var/lib/postgresql/10/' – Jin Lim Mar 10 '20 at 19:50
  • @JinLim: That's not sufficient. The configuration files in /etc/postgresql/10/main need to be reassigned to postgres as well. Otherwise there will still be the first error you mentioned: Error: Config owner (deploy:1003) and data owner (postgres:114) do not match, and config owner is not root – Daniel Vérité Mar 10 '20 at 23:13
  • please tell me how I do it. or some reference doc I can follow? – Jin Lim Mar 11 '20 at 0:57
-2

Yes. Its Cause of permission issues to postgresql folders on your system. try with 0777

1
  • 4
    No, Never use 777 for anything, ever, not even testing. It is too destructive. Ignore anyone who recommends it because they are mistaken. – Michael Hampton Aug 29 '20 at 19:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.