After upgrade 14.04 to 16.04.1 Postgresql server does not start

Question

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?

Solution 1

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 [email protected]

Stop: systemctl stop [email protected]

Status: systemctl status [email protected]

Enable autostart on boot: systemctl enable [email protected]

Disable autostart on boot: systemctl disable [email protected]

foo@postgres:~$ systemctl status [email protected]
●  [email protected] - PostgreSQL Cluster 9.5-main
   Loaded: loaded (/lib/systemd/system/[email protected]; disabled; vendor preset: enabled)
   Active: active (running) since Fri 2017-03-31 17:44:46 CEST; 59s ago
   Main PID: 4546 (postgres)
   CGroup: /system.slice/system-postgresql.slice/[email protected]
           ├ ─ 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.

Solution 2

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: /system.slice/postgresql.service
           ├─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.

Solution 3

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?

Comments

[Auspex]

That's got to be wrong. Systemd does work. It's been starting my 9.3 cluster just fine; it just won't start my 9.5 cluster. It's not an invalid service script, it's a target script, that they've called a service to make it reloadable. Ugly hacks don't seem to be the right way to do it either, but it really should cause systemctl _action_ postgresql@_version_ to run

[Artur Eshenbrener]

Can prove that this is work. How can I setup systemd to autostart this?

[decibyte]

I don't know if this is an ugly hack (it probably is), but I had the same problem with postfix and this answer seems to solve my proplem.

[malte]

systemctl enable [email protected] is all you need.

[Artur Eshenbrener]

Just upgraded postgresql from 9.6 to 10.0, and hit this issue again. Tried your solution and can prove: it works.

[Kemin Zhou]

At thesystemctl enable postgresql I got the following error: postgresql.service is not a native service, redirecting to systemd-sysv-install Executing /lib/systemd/systemd-sysv-install enable postgresql insserv: fopen(.depend.stop): Permission denied insserv: fopen(.depend.stop): Permission denied. Apparently this is not working with my setup.

[GreenReaper]

Extracted from Kemin's answer below: you can use e.g. systemctl edit [email protected] to create an override file with systemd directives such as: [Service] Nice=15 IOSchedulingClass=2 IOSchedulingPriority=7