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

Question

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 
●くろまる [email protected] - PostgreSQL Cluster 10-main
 Loaded: loaded (/lib/systemd/system/[email protected]; 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/[email protected]
 ├─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

• Daniel Vérité: Connection refused happens probably because postgresql is not listening to remote connections, which is the default security. Configure it through the listen_addresses parameter. The rest looks normal, it's just the systemd output that is hard to interpret.

Answer 1

It is possible to have multiple postgres clusters running on the same machine. The main thing you need to check is the postgres port number, which you want to connect to.

Check if postgres instance on your port is running or not. Then check the server details from where you want to connect via ssh is listed in your pg_hba.conf file. If these are done, you should be able to connect to the remote postgres cluster.

• Floran Gmehlin: Thanks for your reply, I posted as a guest so I cannot edit to provide more details. The port is 5432 and is open and accessible (netstat shows the correct output). Firewall is disabled, postgres.conf has what it needs (listen_addresses, port) and I allowed everything in pg_hba. Also, I don't own the server so I cannot really check things on that side, but it's supposed to work (other people can connect) :(.
• Lohit Gupta: Can you provide the psql command being used to connect. It coud be that the command is wrong, if others are able to connect. Mainly the setting in pg_hba file has to be checked on the remote server you want to access. So you will have to get them checked somehow.
• Floran Gmehlin: psql -U <username> -d <dbname> -h <host> -p 5432 I think it's correct as I've used it in the past. But i'm still not quite understanding why I have these two services that run. I've purged postgres and freshly reinstalled via apt-get and still i can't connect. I previously had two version in parallel (9.6 and 10), maybe that's for something.

Answer 2

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

Answer 3

I have faced the same issue when I changed the pg_hba.conf file.I am using Linux VM in order to change the pg_hba.conf file I had to change the /car/lib/pgsql/15/data permission but after the changes, I forgot to give the correct permission to this folder.

sudo chmod 0700 data