0

I'm having problems with PostgreSQL on Ubuntu 20.04. After server is rebooted, the database doesn't work and I need to switch the user to postgres and call /usr/lib/postgresql/12/bin/pg_ctl restart -D /var/lib/postgresql/12/main to restart it.

EDIT

So it looks like that the night this started, these upgrades have been automatically installed:

tart-Date: 2021-11-12  06:35:51
Commandline: /usr/bin/unattended-upgrade
Upgrade: libtdb1:amd64 (1.4.2-3build1, 1.4.3-0ubuntu0.20.04.1)
End-Date: 2021-11-12  06:35:51

Start-Date: 2021-11-12  06:35:59
Commandline: /usr/bin/unattended-upgrade
Upgrade: libpq5:amd64 (12.8-0ubuntu0.20.04.1, 12.9-0ubuntu0.20.04.1), libpq-dev:amd64 (12.8-0ubuntu0.20.04.1, 12.9-0ubuntu0.20.04.1)
End-Date: 2021-11-12  06:35:59

Start-Date: 2021-11-12  06:36:07
Commandline: /usr/bin/unattended-upgrade
Upgrade: postgresql-client-12:amd64 (12.8-0ubuntu0.20.04.1, 12.9-0ubuntu0.20.04.1)
End-Date: 2021-11-12  06:36:08

Start-Date: 2021-11-12  06:36:16
Commandline: /usr/bin/unattended-upgrade
Upgrade: postgresql-12:amd64 (12.8-0ubuntu0.20.04.1, 12.9-0ubuntu0.20.04.1)
End-Date: 2021-11-12  06:36:19

----------------------------------------------------------------------------------------------------------

I absolutely don't know where the problem is, can you take a look at it? These are steps:

(Step 4 - postgresql not working, step 8 - it works)

*1 REBOOT

*2

master@myproject-ubuntu:~$ sudo service postgresql status

● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Tue 2021-11-16 15:16:22 CST; 29s ago
    Process: 713 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 713 (code=exited, status=0/SUCCESS)

Nov 16 15:16:22 myproject-ubuntu systemd[1]: Starting PostgreSQL RDBMS...
Nov 16 15:16:22 myproject-ubuntu systemd[1]: Finished PostgreSQL RDBMS.

*3

sudo grep "postgres" /var/log/syslog

Nov 16 15:16:22 myproject-ubuntu kernel: [    3.939501] systemd[1]: Created slice system-postgresql.slice.
Nov 16 15:16:22 myproject-ubuntu postgresql@12-main[712]: Error: Could not open logfile /var/log/postgresql/postgresql-12-main.log
Nov 16 15:16:22 myproject-ubuntu postgresql@12-main[638]: Error: /usr/lib/postgresql/12/bin/pg_ctl /usr/lib/postgresql/12/bin/pg_ctl start -D /var/lib/postgresql/12/main -l /var/log/postgresql/postgresql-12-main.log -s -o  -c config_file="/etc/postgresql/12/main/postgresql.conf"  exited with status 1:
Nov 16 15:16:22 myproject-ubuntu systemd[1]: postgresql@12-main.service: Can't open PID file /run/postgresql/12-main.pid (yet?) after start: Operation not permitted
Nov 16 15:16:22 myproject-ubuntu systemd[1]: postgresql@12-main.service: Failed with result 'protocol'.
Nov 16 15:16:49 myproject-ubuntu gunicorn[984]:   File "/home/master/.virtualenvs/arcounty_django/lib/python3.8/site-packages/django/db/backends/postgresql/base.py", line 187, in get_new_connection

*4

pg_lsclusters

Ver Cluster Port Status Owner     Data directory              Log file
12  main    5432 down   <unknown> /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log

*5

CMD: sudo service postgresql restart
CMD: pg_lsclusters
Ver Cluster Port Status Owner     Data directory              Log file
12  main    5432 down   <unknown> /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log

*6

CMD: sudo su - postgres 
CMD: /usr/lib/postgresql/12/bin/pg_ctl restart -D /var/lib/postgresql/12/main
pg_ctl: PID file "/var/lib/postgresql/12/main/postmaster.pid" does not exist
Is server running?
trying to start server anyway
waiting for server to start....2021-11-16 21:21:55.805 UTC [1158] LOG:  starting PostgreSQL 12.9 (Ubuntu 12.9-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
2021-11-16 21:21:55.806 UTC [1158] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2021-11-16 21:21:55.808 UTC [1158] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-11-16 21:21:55.847 UTC [1163] LOG:  database system was shut down at 2021-11-16 21:13:45 UTC
2021-11-16 21:21:55.865 UTC [1158] LOG:  database system is ready to accept connections
 done
server started

*7 exit

*8 (as normal user)

`CMD:pg_lsclusters`

Ver Cluster Port Status Owner Data directory Log file 12 main 5432 online <unknown> /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log

EDIT:

permissions & ownerships after reboot (when it's not working)

sudo ls -la /var/lib/postgresql/12/main/

master@myproject-ubuntu:~$ sudo ls -la /var/lib/postgresql/12/main/
[sudo] password for master: 
total 88
drwx------ 19 postgres postgres 4096 Nov 16 15:39 .
drwx------  3 postgres postgres 4096 Oct  6  2020 ..
-rwx------  1 postgres postgres    3 Oct  6  2020 PG_VERSION
drwx------  7 postgres postgres 4096 Jan 12  2021 base
drwx------  2 postgres postgres 4096 Nov 16 15:22 global
drwx------  2 postgres postgres 4096 Oct  6  2020 pg_commit_ts
drwx------  2 postgres postgres 4096 Oct  6  2020 pg_dynshmem
drwx------  4 postgres postgres 4096 Nov 16 15:39 pg_logical
drwx------  4 postgres postgres 4096 Oct  6  2020 pg_multixact
drwx------  2 postgres postgres 4096 Nov 16 15:21 pg_notify
drwx------  2 postgres postgres 4096 Oct  6  2020 pg_replslot
drwx------  2 postgres postgres 4096 Oct  6  2020 pg_serial
drwx------  2 postgres postgres 4096 Oct  6  2020 pg_snapshots
drwx------  2 postgres postgres 4096 Nov 16 15:39 pg_stat
drwx------  2 postgres postgres 4096 Oct  6  2020 pg_stat_tmp
drwx------  2 postgres postgres 4096 Nov  1 14:55 pg_subtrans
drwx------  2 postgres postgres 4096 Oct  6  2020 pg_tblspc
drwx------  2 postgres postgres 4096 Oct  6  2020 pg_twophase
drwx------  3 postgres postgres 4096 Nov 11 22:20 pg_wal
drwx------  2 postgres postgres 4096 Oct 27 09:13 pg_xact
-rwx------  1 postgres postgres   88 Oct  6  2020 postgresql.auto.conf
-rwx------  1 postgres postgres  130 Nov 16 15:21 postmaster.opts

sudo ls -la /var/lib/postgresql/12/

master@myproject-ubuntu:~$ sudo ls -la /var/lib/postgresql/12/
total 12
drwx------  3 postgres postgres 4096 Oct  6  2020 .
drwxr-xr-x  3 postgres postgres 4096 Dec 22  2020 ..
drwx------ 19 postgres postgres 4096 Nov 16 15:39 main

sudo cat etc/postgresql/12/main/start.conf

# Automatic startup configuration
#   auto: automatically start the cluster
#   manual: manual startup with pg_ctlcluster/postgresql@.service only
#   disabled: refuse to start cluster
# See pg_createcluster(1) for details. When running from systemd,
# invoke 'systemctl daemon-reload' after editing this file.

auto

cat /var/log/apt/history.log

Start-Date: 2021-11-09  06:34:56
Commandline: apt-get -o Dpkg::Options::=--force-confdef -o Dpkg::Options::=--force-confold -qq install -y --only-upgrade do-agent
Upgrade: do-agent:amd64 (3.11.0, 3.12.0)
End-Date: 2021-11-09  06:34:57

Start-Date: 2021-11-10  06:49:20
Commandline: /usr/bin/unattended-upgrade
Install: linux-image-5.4.0-90-generic:amd64 (5.4.0-90.101, automatic), linux-headers-5.4.0-90-generic:amd64 (5.4.0-90.101, automatic), linux-headers-5.4.0-90:amd64 (5.4.0-90.101, automatic), linux-modules-5.4.0-90-generic:amd64 (5.4.0-90.101, automatic)
Upgrade: linux-headers-generic:amd64 (5.4.0.89.93, 5.4.0.90.94), linux-virtual:amd64 (5.4.0.89.93, 5.4.0.90.94), linux-headers-virtual:amd64 (5.4.0.89.93, 5.4.0.90.94), linux-image-virtual:amd64 (5.4.0.89.93, 5.4.0.90.94)
End-Date: 2021-11-10  06:49:40

Start-Date: 2021-11-10  06:49:48
Commandline: /usr/bin/unattended-upgrade
Upgrade: linux-libc-dev:amd64 (5.4.0-89.100, 5.4.0-90.101)
End-Date: 2021-11-10  06:49:49

Start-Date: 2021-11-11  06:44:31
Commandline: /usr/bin/unattended-upgrade
Remove: linux-headers-5.4.0-88-generic:amd64 (5.4.0-88.99), linux-headers-5.4.0-88:amd64 (5.4.0-88.99)
End-Date: 2021-11-11  06:44:34

Start-Date: 2021-11-11  06:44:41
Commandline: /usr/bin/unattended-upgrade
Remove: linux-image-5.4.0-88-generic:amd64 (5.4.0-88.99)
End-Date: 2021-11-11  06:44:43

Start-Date: 2021-11-11  06:44:50
Commandline: /usr/bin/unattended-upgrade
Remove: linux-modules-5.4.0-88-generic:amd64 (5.4.0-88.99)
End-Date: 2021-11-11  06:44:50

Start-Date: 2021-11-12  06:35:51
Commandline: /usr/bin/unattended-upgrade
Upgrade: libtdb1:amd64 (1.4.2-3build1, 1.4.3-0ubuntu0.20.04.1)
End-Date: 2021-11-12  06:35:51

Start-Date: 2021-11-12  06:35:59
Commandline: /usr/bin/unattended-upgrade
Upgrade: libpq5:amd64 (12.8-0ubuntu0.20.04.1, 12.9-0ubuntu0.20.04.1), libpq-dev:amd64 (12.8-0ubuntu0.20.04.1, 12.9-0ubuntu0.20.04.1)
End-Date: 2021-11-12  06:35:59

Start-Date: 2021-11-12  06:36:07
Commandline: /usr/bin/unattended-upgrade
Upgrade: postgresql-client-12:amd64 (12.8-0ubuntu0.20.04.1, 12.9-0ubuntu0.20.04.1)
End-Date: 2021-11-12  06:36:08

Start-Date: 2021-11-12  06:36:16
Commandline: /usr/bin/unattended-upgrade
Upgrade: postgresql-12:amd64 (12.8-0ubuntu0.20.04.1, 12.9-0ubuntu0.20.04.1)
End-Date: 2021-11-12  06:36:19

Start-Date: 2021-11-16  06:29:53
Commandline: /usr/bin/unattended-upgrade
Upgrade: xxd:amd64 (2:8.1.2269-1ubuntu5.3, 2:8.1.2269-1ubuntu5.4)
End-Date: 2021-11-16  06:29:54

Start-Date: 2021-11-16  06:30:01
Commandline: /usr/bin/unattended-upgrade
Upgrade: vim-common:amd64 (2:8.1.2269-1ubuntu5.3, 2:8.1.2269-1ubuntu5.4), vim-runtime:amd64 (2:8.1.2269-1ubuntu5.3, 2:8.1.2269-1ubuntu5.4), vim:amd64 (2:8.1.2269-1ubuntu5.3, 2:8.1.2269-1ubuntu5.4), vim-tiny:amd64 (2:8.1.2269-1ubuntu5.3, 2:8.1.2269-1ubuntu5.4)
End-Date: 2021-11-16  06:30:05

Start-Date: 2021-11-17  06:57:29
Commandline: /usr/bin/unattended-upgrade
Upgrade: accountsservice:amd64 (0.6.55-0ubuntu12~20.04.4, 0.6.55-0ubuntu12~20.04.5), libaccountsservice0:amd64 (0.6.55-0ubuntu12~20.04.4, 0.6.55-0ubuntu12~20.04.5)
End-Date: 2021-11-17  06:57:30

Start-Date: 2021-11-17  07:07:27
Commandline: apt-get purge --auto-remove redis-server
Requested-By: master (1000)
Purge: lua-bitop:amd64 (1.0.2-5), libhiredis0.14:amd64 (0.14.0-6), lua-cjson:amd64 (2.1.0+dfsg-2.1), redis:amd64 (5:5.0.7-2), redis-tools:amd64 (5:5.0.7-2), liblua5.1-0:amd64 (5.1.5-8.1build4), libjemalloc2:amd64 (5.2.1-1ubuntu1), redis-server:amd64 (5:5.0.7-2)
End-Date: 2021-11-17  07:07:30

Start-Date: 2021-11-17  07:07:49
Commandline: apt-get install redis-server
Requested-By: master (1000)
Install: lua-bitop:amd64 (1.0.2-5, automatic), libhiredis0.14:amd64 (0.14.0-6, automatic), lua-cjson:amd64 (2.1.0+dfsg-2.1, automatic), redis-tools:amd64 (5:5.0.7-2, automatic), liblua5.1-0:amd64 (5.1.5-8.1build4, automatic), libjemalloc2:amd64 (5.2.1-1ubuntu1, automatic), redis-server:amd64 (5:5.0.7-2)
End-Date: 2021-11-17  07:07:52
Milano
  • 18,048
  • 37
  • 153
  • 353
  • 2
    Educated guess ... you created the problem by running postgres as the wrong user at some stage ... what are the ownerships & permissions on `/var/lib/postgresql/12/main/postmaster.pid` and the directory `/var/lib/postgresql/12/main` after the reboot? Please add the info to your question, not here in the comments. – tink Nov 16 '21 at 21:39
  • @tink I checked both with `ls -la` - the results are added to the very bottom of this question. Thanks. – Milano Nov 16 '21 at 21:43
  • Why not?: `sudo pg_ctlcluster 12 main start`. Use the postgresql-common commands. This: `sudo service postgresql restart` is doing an end run around the system which is probably why it is not working. – Adrian Klaver Nov 16 '21 at 22:09
  • I'm quite baffled I must say - those ownerships/permissions look fine; did you modify the systemd unit files in some way? Is your machine trying to launch postgres as some other user than postgres? – tink Nov 16 '21 at 22:17
  • @tink No I did not. In fact it happened after weeks of not touching anything, even not ssh logging. It's crazy. – Milano Nov 16 '21 at 22:25
  • @AdrianKlaver thanks I'll do that but why it doesn't work by default as it worked before... That's the problem – Milano Nov 16 '21 at 22:33
  • The `postgresql` service is just a stub process that starts the other Postgres services for the actual instances. That is why you will see `exited` after it is run. It starts, launches the other instances and then exits. Look in `etc/postgresql/12/main/start.conf` and see if the startup configuration has changed. The file is commented as to what settings are available. – Adrian Klaver Nov 16 '21 at 22:47
  • @AdrianKlaver I've added the content of start.conf to the bottom of the question. It hasn't changed since October 2020. – Milano Nov 17 '21 at 11:41
  • @AdrianKlaver `sudo pg_ctlcluster 12 main start` returns error: `Job for postgresql@12-main.service failed because the service did not take the steps required by its unit configuration.` – Milano Nov 17 '21 at 11:43
  • Hmm. Questions: 1) Why was the server rebooted? 2) Have there been any upgrades to software since the last time Postgres started successfully? 3) What if you do `/usr/lib/postgresql/12/bin/pg_ctl stop ...` and then use the `pg_ctlcluster` command? Add replies as update to your question. – Adrian Klaver Nov 17 '21 at 16:30
  • @AdrianKlaver Client wrote me that the server suddenly doesn't work (NGINX 504 I guess - because Django couldn't connect to Postgres). So I rebooted it. 2. No that I know of. 3. `pg_ctl: no database directory specified and environment variable PGDATA unset` – Milano Nov 17 '21 at 20:21
  • For 3) you will have to use: `/usr/lib/postgresql/12/bin/pg_ctl stop -D /var/lib/postgresql/12/main`. – Adrian Klaver Nov 17 '21 at 20:26
  • @AdrianKlaver I got `pg_ctl: could not access directory "/var/lib/postgresql/12/main": Permission denied` as a `master` user (not root nor postgres) without `sudo`. – Milano Nov 17 '21 at 20:27
  • 1
    So use `sudo`. I thought it was obvious that you repeat the command you showed in your question with `stop` instead of `restart`. As to 2) you need to ask the client and/or look at `vi /var/log/apt/history.log` to see if there any changes since the last time Postgres ran. – Adrian Klaver Nov 17 '21 at 20:31
  • @AdrianKlaver I thought you want to know if `master` has permissions. So 1. `pg_ctl: cannot be run as root` but 2. (WOW) the `/var/log/apt/history.log` looks more interesting. These problems started at night 11/12 November (USA) and there is some `postgres` update in the `apt` log. I've added it to the bottom of the question. – Milano Nov 17 '21 at 20:38
  • @AdrianKlaver Sorry I didn't submit the Q updates. You can find it at the bottom now. – Milano Nov 17 '21 at 20:44
  • 1
    Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/239332/discussion-between-adrian-klaver-and-milano). – Adrian Klaver Nov 17 '21 at 21:24

0 Answers0