14

[Ubuntu 16.04] I installed postgresql 9.5 along with dependencies:

sudo sh -c "echo 'deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main' > /etc/apt/sources.list.d/pgdg.list"
wget --quiet -O - http://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql-common
sudo apt-get install postgresql-9.5 libpq-dev

When I want to run psql then I get:

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

But /var/run/postgresql/ is empty. When I restart posgresql everything appears to be fine:

$ /etc/init.d/postgresql restart
[ ok ] Restarting postgresql (via systemctl): postgresql.service.

$ /etc/init.d/postgresql status
● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
   Active: active (exited) since wto 2016-09-27 16:18:26 CEST; 1min 15s ago
  Process: 3076 ExecReload=/bin/true (code=exited, status=0/SUCCESS)
  Process: 3523 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
 Main PID: 3523 (code=exited, status=0/SUCCESS)

but if check ps aux there is not such PID (why??)

Total reinstalation doesn't help at all. How can I fix it?

  • What does the /var/log/posgtresql/postgresql-9.5-main.log file show? – ubfan1 Sep 27 '16 at 15:23
  • this file is empty – mike927 Sep 27 '16 at 15:50
14

This is an idiosyncrasy of the systemd integration of PostgreSQL in Xenial.

The postgresql service unit installed by the postgresql-common package is just a dummy service which causes the actual service postgresql@9.6-main to be started via a dependency. You can see that dependency by running the command

systemctl list-dependencies postgresql

That dependency is not permanent, but generated during system boot by the systemd generator /lib/systemd/system-generators/postgresql-generator which also comes with the postgresql-common package. The generator checks whether the startup mode in the file /etc/postgresql/9.6/main/start.conf is set to auto, and if so, sets up the dependency that subsequently causes instance 9.6-main to be started.

(More precisely, it checks all configuration subdirectories /etc/postgresql/*/* and will create dependencies for all instances that are configured for automatic startup, but in a default installation there will be just the one instance.)

Due to the limitations of systemd generators (see man systemd.generator) this process may fail, causing the dependencies to be absent after a reboot. Systemd will then start only the dummy service, writing

systemd[1]: Starting PostgreSQL RDBMS...
systemd[1]: Started PostgreSQL RDBMS.

to the log but otherwise doing nothing. Attempting to start the service manually by

systemctl start postgresql

will just reproduce that result. Running the command

systemctl daemon-reload

manually as root will re-run the generator and in most cases fix the problem until the next reboot.

To solve the problem permanently you'll have to find the reason why the generator fails during boot. Possible causes can be found in the systemd.generator manpage. In my case it was the PostgreSQL config file /etc/postgresql/9.6/main/postgresql.conf which was symlinked to a different filesystem that wasn't available yet when the generator ran early during boot. postgresql-generator checks the existence of that file even though it doesn't otherwise need it.

  • Feel free to edit your answer when you manage to solve the issue :) – storm Mar 30 '17 at 16:16
9

Extending on the answer of Tilman, but not enough Kudos to comment...

If you do not need the service to be called postgresql and do not care about the wrapper dummy service, it should work to just to control the real service directly. It's name is : postgresql@$version-$cluster.service In your case it should be postgresql-9.5-main in short. Like to start

systemctl start postgresql@9.5-main

and to stop:

systemctl stop postgresql@9.5-main

Status will also give you much better and accurate information than on the auto generated wrapper service.

systemctl status postgresql@9.5-main

For 9.6 it looks like this:

● postgresql@9.6-main.service - PostgreSQL Cluster 9.6-main
   Loaded: loaded (/lib/systemd/system/postgresql@.service; disabled; vendor preset: enabled)
   Active: active (running) since Wed 2017-09-13 00:41:50 CEST; 7h ago
  Process: 10235 ExecStop=/usr/bin/pg_ctlcluster --skip-systemctl-redirect -m fast %i stop (code=exited, status=2)
  Process: 10676 ExecStart=postgresql@%i --skip-systemctl-redirect %i start (code=exited, status=0/SUCCESS)
 Main PID: 10683 (postgres)
   CGroup: /system.slice/system-postgresql.slice/postgresql@9.6-main.service
           ├─10683 /usr/lib/postgresql/9.6/bin/postgres -D /var/lib/postgresql/9.6/main -c config_file=/etc/postgresql/9.6/main/postgresql.conf
           ├─10685 postgres: 9.6/main: checkpointer process
           ├─10686 postgres: 9.6/main: writer process
           ├─10748 postgres: 9.6/main: wal writer process
           ├─10749 postgres: 9.6/main: autovacuum launcher process
           ├─10750 postgres: 9.6/main: archiver process   last was 000000020000000000000082
           ├─10751 postgres: 9.6/main: stats collector process
4

In my case this was related to incorrectly configured locales.

I've found the solution in this dba.stackexchange.com answer:

  1. Use sudo dpkg-reconfigure locales to generate the necessary locales
  2. Drop the existing database cluster via sudo pg_dropcluster 9.5 main (this will erase all the data in the cluster!)
  3. Re-create the cluster via sudo pg_createcluster 9.5 main --start
  4. Restart PostgreSQL via sudo service postgresql restart
1

it would better to use systemd startup scripts with ubuntu 16.04 , init scripts might not work properly these days. Postgres 9.5 is already in the ubuntu repos so try that instead , it should have systemd startup.

  • using standard ubuntu repo I get the same result – mike927 Sep 28 '16 at 8:02
  • thats a shame , looks like the postgres folks have not got the hang of systemd yet. You should probably raise a bug against the postgres package or ask on their mailing list about systemd support. I don't use postgres much but some open source projects have taken a stance against systemd or maybe tangled in internal debates about supporting it. – Amias Sep 28 '16 at 9:54
  • when I run systemctl it returns "postgresql@9.5-main.service loaded failed failed PostgreSQL Cluster 9.5-main". Why is it failed? – mike927 Sep 28 '16 at 13:17
  • Well in this case it's the systemd startup scripts which do not work properly so the advice is not exactly helpful. – Tilman Jul 24 '18 at 16:03
1

Another "got bitten by this".

The pg_upgradecluster actually left the target version (9.6) in "manual" mode on port 5433 and source version (9.5) at port 5432.

Even after pg_dropcluster 9.5. Editing the start.conf file didn't help, but the hint was to use systemctl daemon-reload, since the generator decides based on this configuration file whether to symlink the service file:

for conf in /etc/postgresql/*/*/postgresql.conf; do
    # trimmed for brevity
    [ "$start" = "auto" ] || continue
    ln -s "$pgservice" "$wantdir/postgresql@$version-$cluster.service"
done

So if the cluster you want started does not have the word "auto" in start.conf, you need to do a system-reload (or reboot) to have it enabled at boot time.

Still have to verify this with a reboot, but given the above pretty confident that was the issue.

1

I disabled the magic "super service" like this:

root@server# systemctl disable postgresql

Then I activated the concrete service:

root@server:~# systemctl enable postgresql@9.5-main.service 

After rebooting everything worked again.

0

Had the same error, wasted hours, simple solution. Check this SO question and my answer, Which is:

sudo service postgresql restart
0

I had this problem due to a different reason: directory permissions. I had a full-sweep chmod like this:

chmod -R 644 /etc/postgresql/10/main

This sets the directory as non-executable, which prevents postgres from reading it.

0

I had this same problem upon checking found issue with ssl-cert-snakeoil.key permission .

Set Ownership

chown root:ssl-cert ssl-cert-snakeoil.key chmod 640 ssl-cert-snakeoil.key

and did a clean restart .

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.