0

I ran into this problem a few months ago under different (since forgotten the details) circumstances, and here we are again. If you google this issue there are plenty of similar posts, but none really seem to reach much of a satisfactory conclusion. Unless it's simply permissions based, which I'm struggling to see that this is.

I'm stopping and starting the postgres service from a Debian init.d script, which runs if a database restore is deemed necessary.

It stops no problem:

systemctl stop postgresql

The restore operation runs smoothly (pgbackrest, stanza backup hosted remotely):

sudo -u postgres bash -c "pgbackrest --log-level-console=info --stanza=$stanza --delta restore"

Then the start operation just hangs with no debug or logging generated:

systemctl start postgresql    

If I replace the call to systemctl with a direct call to postgres:

sudo -u postgres bash -c "/usr/lib/postgresql/9.5/bin/postgres -D /var/lib/postgresql/9.5/main -c config_file=/etc/postgresql/9.5/main/postgresql.conf

I get the following error (please ignore the timestamp, it's just a test box I'm using):

2016-11-03 17:30:27 UTC [2511-3] FATAL:  could not open directory "/var/run/postgresql/9.5-main.pg_stat_tmp": No such file or directory

The next thing to point out is that both Postgres 9.5 and 9.6 co-exist on the system, and the 9.6 instance stops and starts fine via the systemctl calls (which start all configured clusters), so if it was a permissions thing why should 9.5 fail? If genuinely all it needs to do is create this file then I see no reason for the error, or is the error perhaps a Red Herring?

TEST-RIG-postgres@CIE-4096:/root$ ls -l /var/run/postgresql/
total 4
drwxr-s--- 2 postgres postgres 100 Nov  3 17:36 9.6-main.pg_stat_tmp
-rw-r--r-- 1 postgres postgres   5 Nov  3 17:20 9.6-main.pid

ls -l /var/run/
...
drwxrwsr-x  3 postgres    postgres     120 Nov  3 17:30 postgresql

I'm just a bit lost on this. Is it maybe because it's called during system init (Postgres usually starts fine)? I've added the following to the LSB section and no difference:

# Required-Start: postgresql
3

Solution: Run

mkdir -p /var/run/postgresql/9.5-main.pg_stat_tmp
chown postgres /var/run/postgresql/9.5-main.pg_stat_tmp
chgrp postgres /var/run/postgresql/9.5-main.pg_stat_tmp
  • Please explain. There is no indication from OP that it is under Docker. – Patrick Mevzek Feb 2 '18 at 15:36
  • It doesn't matter, the path suggest that PostgreSQL has been installed on linux machine. Error tell us that there is missing temporary file, so the simplest solution is to create this file and grant required permissions. – pbo Feb 4 '18 at 22:29
  • Before the edit, your answer looked like instructions in a Dockerfile... – Patrick Mevzek Feb 4 '18 at 22:30
  • Probably you were too fast for me ;) I've tested this solution two times, and I supose that in this case should help. – pbo Feb 4 '18 at 22:35
  • This worked for me, but why is the temporary file missing in the first place? What should create it? – Johan Aug 10 '18 at 8:26

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.