Which command to restart postgresql 12 service

Question

I am about to restart postgresql 12 service running on Ubuntu 18. First, I check it's status : sudo service postgresql status. Its says :

postgresql.service - PostgreSQL RDBMS Loaded: loaded (/lib/systemd/system/postgresql.service; disabled; vendor pres Active: inactive (dead) lines 1-3/3 (END)...skipping... postgresql.service - PostgreSQL RDBMS Loaded: loaded (/lib/systemd/system/postgresql.service; disabled; vendor preset: enabled) Active: inactive (dead) 

Although it appears to be inactive, but I still can connect.

Then I run another checking : systemctl status postgresql@12-main. it says :

root@db-services:/# systemctl status postgresql@12-main ● [email protected] - PostgreSQL Cluster 12-main Loaded: loaded (/etc/systemd/system/[email protected]; enabled; vendor preset: enabled) Drop-In: /etc/systemd/system/[email protected] └─override.conf Active: active (running) since Mon 2022-06-13 09:02:25 WIB; 5 months 27 days ago Main PID: 5807 (postgres) Tasks: 86 (limit: 4915) CGroup: /system.slice/system-postgresql.slice/[email protected] ├─ 1586 postgres: 12/main: postgres db_sync 10.100.3.248(61414) idle ├─ 1588 postgres: 12/main: postgres db_sync 10.100.3.248(61415) idle ├─ 1606 postgres: 12/main: docreg db_docreg 10.100.8.150(18385) idle ├─ 1607 postgres: 12/main: docreg db_docreg 10.100.8.150(18399) idle ├─ 1608 postgres: 12/main: docreg db_docreg 10.100.8.150(18401) idle ├─ 1609 postgres: 12/main: docreg db_docreg 10.100.8.150(18421) idle ├─ 2592 postgres: 12/main: docudigtl db_docudigtl 10.100.8.112(34956) idle ├─ 3518 postgres: 12/main: docudigtl db_docudigtl 127.0.0.1(48290) idle ├─ 5150 postgres: 12/main: docudigtl db_docudigtl 10.100.7.114(61504) idle ├─ 5807 /usr/lib/postgresql/12/bin/postgres -D /data/postgresql/12/main -c config_file=/etc/postgresql/12/main/postgresql.co ├─ 5883 postgres: 12/main: docudigtl db_docudigtl 127.0.0.1(48626) idle ├─ 6316 postgres: 12/main: docudigtl db_docudigtl 127.0.0.1(48704) idle ├─ 7049 postgres: 12/main: docudigtl db_docudigtl 127.0.0.1(48870) idle ├─ 7986 postgres: 12/main: docudigtl db_docudigtl 10.100.7.114(56240) idle ├─ 8146 postgres: 12/main: docudigtl db_docudigtl 10.100.8.114(47432) idle ├─ 8183 postgres: 12/main: docudigtl db_docudigtl 127.0.0.1(49022) idle ├─ 8360 postgres: 12/main: docudigtl db_docudigtl 127.0.0.1(49036) idle ├─ 8445 postgres: 12/main: docudigtl db_docudigtl 10.100.8.114(47478) idle ├─ 9590 postgres: 12/main: docudigtl db_docudigtl 10.100.8.106(56094) idle ├─ 9687 postgres: 12/main: docudigtl db_docudigtl 10.100.8.112(50980) idle ├─10148 postgres: 12/main: docudigtl db_docudigtl 127.0.0.1(40646) idle ├─10324 postgres: 12/main: docudigtl db_docudigtl 127.0.0.1(40680) idle ├─11079 postgres: 12/main: docudigtl db_docudigtl 127.0.0.1(49496) idle ├─11602 postgres: 12/main: docudigtl db_docudigtl 127.0.0.1(58152) idle ├─11886 postgres: 12/main: docudigtl db_docudigtl 127.0.0.1(44674) idle ├─12106 postgres: 12/main: docudigtl db_docudigtl 127.0.0.1(49618) idle ├─13112 postgres: 12/main: docudigtl db_docudigtl 10.100.6.145(55014) idle ├─13122 postgres: 12/main: docudigtl db_docudigtl 10.100.6.145(55015) idle ├─13145 postgres: 12/main: docudigtl db_mysapk 10.100.6.145(55016) idle ├─13202 postgres: 12/main: docudigtl db_docudigtl 10.100.7.114(62073) idle ├─13945 postgres: 12/main: checkpointer ├─13946 postgres: 12/main: background writer ├─13947 postgres: 12/main: walwriter ├─13948 postgres: 12/main: autovacuum launcher ├─13949 postgres: 12/main: stats collector ├─13950 postgres: 12/main: logical replication launcher 

So it is actually active !

1. Whats the difference between the 2 commands?
2. Which one should I use for restart : systemctl restart postgresql@12-main OR sudo service postgresql restart ?

Answer 1

There are special systemd services called template services.

A template service can take an argument like this: template_service@argument.

A template service is never started in itself, but it can be instantiated with a given argument. This means that the same (template) service may be running in multiple instances, each identified by its argument. For example:

systemctl start template_service@a
systemctl start template_service@b
systemctl start template_service@c

In Debian and some derivative distributions (such as Ubuntu):

Every PostgreSQL instance (cluster) belongs to an instantiated postgresql@ template service. The argument to the postgresql@ service takes the form of version-clustername.

So for example postgresql@12-main is a systemd service which represents a PostgreSQL 12 server instance (cluster), with the cluster name main (which is the default).

There is another systemd service called simply postgresql. This is a kind of a catch-all service. If you start or stop it, all PostgreSQL instances will be started or stopped (this behavior can be disabled in start.conf).

You cannot, however, use systemctl status postgresql to check whether any PostgreSQL services are running, even if you only have a single PostgreSQL server set up in your system. You should always check the status of the particular instantiated service for a meaningful status report.

The service command was used for the old init system (SysV init), before systemd came along. It still works for backward compatibility, but you really should use the systemctl command on systems where systemd is available.

Answer 2

Commands:

• systemctl restart postgresql@12-main
• service postgresql@12-main restart

are the same.

On Debian /usr/sbin/service is a sh wrapper the would translate arguments into systemctl call. One can argue that direct systemctl command will be faster (maybe few ms) but the effect will be the same.

Use pg_lsclusters to see available clusters

$ pg_lsclusters Ver Cluster Port Status Owner Data directory Log file 12 main 5432 online postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log 13 main 5433 online postgres /var/lib/postgresql/13/main /var/log/postgresql/postgresql-13-main.log 14 dev 5434 online postgres /var/lib/postgresql/14/dev /var/log/postgresql/postgresql-14-dev.log 

And then pass {version}-{cluster name} to after @:

systemctl status postgresql@12-main systemctl status postgresql@13-main systemctl status postgresql@14-dev