Postgresql – How come systemctl status postgresql.service and systemctl status postgresql@10-main show different results

postgresqlUbuntu

Not sure I really understand what's going on.

Originally, I'm trying to connect to a remote host (which I can ping, access via SSH) but cannot connect through psql

Error: psql: could not connect to server: Connection refused Is the server running on host <HOST> and accepting TCP/IP connections on port 5432?

After checking a couple of things, I stumbled upon different results from postgresql.service and postgresql@10-main :

postgresql.service — active (exited)

  ~ sudo systemctl status postgresql.service
● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled;  
vendor preset: enabled)
   Active: active (exited) since Don 2018-01-04 15:24:14 CET; 2min 29s ago
  Process: 10954 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
 Main PID: 10954 (code=exited, status=0/SUCCESS)

postgresql@10-main — active (running)

  ~ systemctl status postgresql@10-main             
● postgresql@10-main.service - PostgreSQL Cluster 10-main
   Loaded: loaded (/lib/systemd/system/postgresql@.service; disabled; vendor preset: enabled)
   Active: active (running) since Don 2018-01-04 15:24:14 CET; 5min ago
  Process: 10920 ExecStop=/usr/bin/pg_ctlcluster --skip-systemctl-redirect -m fast %i stop (code=exited, status=0/SUCC
  Process: 10929 ExecStart=postgresql@%i --skip-systemctl-redirect %i start (code=exited, status=0/SUCCESS)
 Main PID: 10936 (postgres)
   CGroup: /system.slice/system-postgresql.slice/postgresql@10-main.service
           ├─10936 /usr/lib/postgresql/10/bin/postgres -D /var/lib/postgresql/10/main -c config_file=/etc/postgresql/1
           ├─10938 postgres: 10/main: checkpointer process                                                            
           ├─10939 postgres: 10/main: writer process                                                                  
           ├─10940 postgres: 10/main: wal writer process                                                              
           ├─10941 postgres: 10/main: autovacuum launcher process                                                     
           ├─10942 postgres: 10/main: stats collector process                                                         
           └─10943 postgres: 10/main: bgworker: logical replication launcher                                          

Jan 04 15:24:12 floppy systemd[1]: Starting PostgreSQL Cluster 10-main...
Jan 04 15:24:14 floppy systemd[1]: Started PostgreSQL Cluster 10-main.
  • Is this related to my problem with connecting to the remote host ?
  • Why are two different services running (showing different results) ?

PS: I'm running on Ubuntu 16.04

Thanks a lot

Best Answer

Ubuntu and Debian

If you're using Ubuntu, rather than using systemd's systemctl to gauge the status of your clusters use the distro's own pg_lsclusters. It'll tell you if they're running and the port number,

$ pg_lsclusters 
Ver Cluster Port Status Owner    Data directory               Log file
9.5 main    5433 online postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-main.log
Related Question