After upgrade 14.04 to 16.04.1 Postgresql server does not start

Just upgraded my system from 14.04. LTS to 16.04.1 LTS, postgresql does not start on systemd:

/etc/init.d/postgresql start
[ ok ] Starting 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 Вт 2016-08-09 13:40:51 MSK; 3min 23s ago Process: 23142 ExecStart=/bin/true (code=exited, status=0/SUCCESS) Main PID: 23142 (code=exited, status=0/SUCCESS)Seems that 

Seems that systemd script contains wrong data:

# cat /lib/systemd/system/postgresql.service
# systemd service for managing all PostgreSQL clusters on the system. This
# service is actually a systemd target, but we are using a service since
# targets cannot be reloaded.
[Unit]
Description=PostgreSQL RDBMS
[Service]
Type=oneshot
ExecStart=/bin/true
ExecReload=/bin/true
RemainAfterExit=on
[Install]
WantedBy=multi-user.target

What should be correct script for postgresql?

4 Answers

Systemd should use script from /etc/init.d. Instead, systemd uses some kind of template file. To fix that, follow that steps:

1. Remove invalid service script:

   # rm /lib/systemd/system/postgresql.service

2. Reload daemon scripts:

   # systemctl daemon-reload

3. Enable postgresql service:

   # systemctl enable postgresql

After that, you would be able to start postgres with any preferred variants: sudo systemctl start postgresql, sudo service postgresql start, or sudo /etc/init.d/postgresql start. To check that postgresql actually run, please check the status of service: sudo systemctl status postgresql. It sould looks like:

% sudo systemctl status postgresql
● postgresql.service - LSB: PostgreSQL RDBMS server Loaded: loaded (/etc/init.d/postgresql; bad; vendor preset: enabled) Active: active (running) since Пт 2016-08-12 10:13:43 MSK; 1h 37min ago Docs: man:systemd-sysv-generator(8) CGroup: / ├─4086 /usr/lib/postgresql/9.5/bin/postgres -D /var/lib/postgresql/9.5/main -c config_file=/etc/postgresql/9.5/main/postgresql.conf ├─4099 postgres: checkpointer process ├─4100 postgres: writer process ├─4101 postgres: wal writer process ├─4102 postgres: autovacuum launcher process └─4103 postgres: stats collector process
авг 12 10:13:40 ubuntu-precise systemd[1]: Starting LSB: PostgreSQL RDBMS server...
авг 12 10:13:40 ubuntu-precise postgresql[4009]: * Starting PostgreSQL 9.5 database server
авг 12 10:13:43 ubuntu-precise postgresql[4009]: ...done.
авг 12 10:13:43 ubuntu-precise systemd[1]: Started LSB: PostgreSQL RDBMS server.

This should work out of the box if you provide the correct version and cluster name.

Lets say you're running version 9.5 and the cluster is called main:

Start: systemctl start postgresql@9.5-main

Stop: systemctl stop postgresql@9.5-main

Status: systemctl status postgresql@9.5-main

Enable autostart on boot: systemctl enable postgresql@9.5-main

Disable autostart on boot: systemctl disable postgresql@9.5-main

foo@postgres:~$ systemctl status postgresql@9.5-main
● - PostgreSQL Cluster 9.5-main Loaded: loaded (/lib/systemd/system/postgresql@.service; disabled; vendor preset: enabled) Active: active (running) since Fri 2017-03-31 17:44:46 CEST; 59s ago Main PID: 4546 (postgres) CGroup: / ├ ─ 4546 /usr/lib/postgresql/9.5/bin/postgres -D /var/lib/postgresql/9.5/main -c config_file=/etc/postgresql/9.5/main/postgresql.conf ├ ─ 4548 postgres: checkpointer process ├ ─ 4549 postgres: writer process ├ ─ 4550 postgres: wal writer process ├ ─ 4551 postgres: autovacuum launcher process └ ─ 4552 postgres: stats collector process
Mar 31 17:44:44 postgres postgres[4546]: [1-2] 2017-03-31 17:44:44 CEST [4546] @ HINT: Future log output will go to log destination "syslog".
Mar 31 17:44:44 postgres postgres[4547]: [2-1] 2017-03-31 17:44:44 CEST [4547] @ LOG: database system was shut down at 2017-03-31 17:44:43 CEST
Mar 31 17:44:44 postgres postgres[4547]: [3-1] 2017-03-31 17:44:44 CEST [4547] @ LOG: MultiXact member wraparound protections are now enabled
Mar 31 17:44:44 postgres postgres[4546]: [2-1] 2017-03-31 17:44:44 CEST [4546] @ LOG: database system is ready to accept connections
Mar 31 17:44:44 postgres postgres[4551]: [2-1] 2017-03-31 17:44:44 CEST [4551] @ LOG: autovacuum launcher started
Mar 31 17:44:45 postgres postgres[4553]: [3-1] 2017-03-31 17:44:45 CEST [4553] [unknown]@[unknown] LOG: incomplete startup packet
Mar 31 17:44:46 postgres systemd[1]: Started PostgreSQL Cluster 9.5-main.
Mar 31 17:44:47 postgres systemd[1]: Reloading PostgreSQL Cluster 9.5-main.
Mar 31 17:44:47 postgres postgres[4546]: [3-1] 2017-03-31 17:44:47 CEST [4546] @ LOG: received SIGHUP, reloading configuration files
Mar 31 17:44:47 systemd[1]: Reloaded PostgreSQL Cluster 9.5-main.

I had the same problem after purging postges9 and installed 10 on my ubuntu16. Initially I edited the /lib/systemd/system/postgresql.service file with the following content:

[Unit]
Description=PostgreSQL RDBMS
[Service]
Type=notify
User=postgres
ExecStart=/usr/lib/postgresql/10/bin/postgres -D /analysis2/postgresql/data
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=0
[Install]
WantedBy=multi-user.target

And I can use systemctl restart/stop/start to control the postgresql service. However, for some reason, the above file got overwritten (maybe after system update) and I could not run systemctl command to start and stop the postgresql server any more. After some reading, I realized that you are not supposed to edite the above file. Instead you should use systemctl edit foo to overwrite the defaults. After following the solution suggested by the following link, my system seems to be working properly with postgresql.

How do I override or configure systemd services?

Not the nicest solution, but this solves the problem without having to dive into anything. Add this to cron:

@reboot sleep 5; systemctl start postgresql