In Zammad’s default package configuration, it will connect to the local default PostgreSQL socket. You can verify this by various methods, some of which are listed below. Zammad (and other applications) will usually only know of a default socket (or the default TCP/IP port) and will try to connect to that. If you have a pending PostgreSQL package upgrade, there is (still) the default port/socket of your running database, as well as an alternate port/socket for the newly installed database version. After you’ve finished migrating data and finishing the upgrade, the alternate port/socket of the new database will switch to become the new default port/socket.
For example, here is a (shortened) ps auxfww processlisting of the current PostgreSQL processes. I have opened two extra psql client connections, one on the IPv6 localhost address and one on the IPv4 localhost address (username: harrie connecting to database testdb), so you can see the difference between socket and TCP/IP client connection slots.
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
postgres 716 0.0 0.3 219668 27784 ? Ss Jun16 1:10 /usr/lib/postgresql/15/bin/postgres -D /var/lib/postgresql/15/main -c config_file=/etc/postgresql/15/main/postgresql.conf
postgres 728 0.0 0.2 219796 19576 ? Ss Jun16 0:10 \_ postgres: 15/main: checkpointer
postgres 729 0.0 0.1 219812 11316 ? Ss Jun16 0:15 \_ postgres: 15/main: background writer
postgres 748 0.0 0.1 219668 9432 ? Ss Jun16 2:27 \_ postgres: 15/main: walwriter
postgres 749 0.0 0.0 221256 8016 ? Ss Jun16 0:15 \_ postgres: 15/main: autovacuum launcher
postgres 750 0.0 0.0 221240 6304 ? Ss Jun16 0:00 \_ postgres: 15/main: logical replication launcher
postgres 763245 0.0 0.2 223188 18832 ? Ss Jul17 0:09 \_ postgres: 15/main: zammad zammad [local] idle
postgres 763246 0.0 0.2 222560 19384 ? Ss Jul17 0:02 \_ postgres: 15/main: zammad zammad [local] idle
postgres 763249 0.0 0.2 222560 18552 ? Ss Jul17 0:00 \_ postgres: 15/main: zammad zammad [local] idle
postgres 763260 0.0 0.2 222560 18532 ? Ss Jul17 0:00 \_ postgres: 15/main: zammad zammad [local] idle
postgres 763262 0.0 0.2 222560 22208 ? Ss Jul17 0:04 \_ postgres: 15/main: zammad zammad [local] idle
postgres 763265 0.0 0.3 222728 24488 ? Ss Jul17 0:00 \_ postgres: 15/main: zammad zammad [local] idle
postgres 763267 0.0 0.2 222560 22700 ? Ss Jul17 0:00 \_ postgres: 15/main: zammad zammad [local] idle
postgres 763269 0.0 0.3 224844 29268 ? Ss Jul17 0:05 \_ postgres: 15/main: zammad zammad [local] idle
postgres 781960 0.0 0.2 222560 18640 ? Ss Jul18 0:00 \_ postgres: 15/main: zammad zammad [local] idle
postgres 804515 0.0 0.2 222248 16340 ? Ss 11:12 0:00 \_ postgres: 15/main: harrie testdb 127.0.0.1(35664) idle
postgres 804532 0.0 0.1 221764 15632 ? Ss 11:13 0:00 \_ postgres: 15/main: harrie testdb ::1(54038) idle
You can use lsof to check for connected sockets, or established TCP/IP connections. This is a list of connected sockets where the users are either postgres or zammad:
root@zammad:~# lsof -a -U -upostgres -uzammad|grep CONNECTED
ruby 762905 zammad 1u unix 0x00000000a3403c8b 0t0 3409990 type=STREAM (CONNECTED)
ruby 762905 zammad 2u unix 0x00000000a3403c8b 0t0 3409990 type=STREAM (CONNECTED)
ruby 762907 zammad 1u unix 0x00000000f0bc9ea9 0t0 3409986 type=STREAM (CONNECTED)
ruby 762907 zammad 2u unix 0x00000000f0bc9ea9 0t0 3409986 type=STREAM (CONNECTED)
ruby 762909 zammad 1u unix 0x00000000b6750a5f 0t0 3409994 type=STREAM (CONNECTED)
ruby 762909 zammad 2u unix 0x00000000b6750a5f 0t0 3409994 type=STREAM (CONNECTED)
ruby 762909 zammad 6u unix 0x0000000048a1b756 0t0 3410398 type=STREAM (CONNECTED)
ruby 762909 zammad 8u unix 0x00000000dd30b4d8 0t0 3411161 type=STREAM (CONNECTED)
ruby 762909 zammad 9u unix 0x00000000f533f31c 0t0 3411166 type=STREAM (CONNECTED)
ruby 762909 zammad 10u unix 0x00000000736b67d2 0t0 3410409 type=STREAM (CONNECTED)
ruby 762909 zammad 11u unix 0x0000000008c9d59a 0t0 3410411 type=STREAM (CONNECTED)
ruby 762909 zammad 12u unix 0x0000000096fa9576 0t0 3411182 type=STREAM (CONNECTED)
ruby 762909 zammad 13u unix 0x000000006c24eedb 0t0 3410422 type=STREAM (CONNECTED)
ruby 762909 zammad 14u unix 0x0000000018575749 0t0 3410424 type=STREAM (CONNECTED)
ruby 762909 zammad 15u unix 0x00000000d87a258c 0t0 3541462 type=STREAM (CONNECTED)
postgres 763245 postgres 8u unix 0x0000000051f66b89 0t0 3410399 /var/run/postgresql/.s.PGSQL.5432 type=STREAM (CONNECTED)
postgres 763246 postgres 8u unix 0x00000000587fc077 0t0 3410402 /var/run/postgresql/.s.PGSQL.5432 type=STREAM (CONNECTED)
postgres 763249 postgres 8u unix 0x00000000f71a217d 0t0 3410405 /var/run/postgresql/.s.PGSQL.5432 type=STREAM (CONNECTED)
postgres 763260 postgres 8u unix 0x0000000058fb321a 0t0 3410410 /var/run/postgresql/.s.PGSQL.5432 type=STREAM (CONNECTED)
postgres 763262 postgres 8u unix 0x00000000f1f70b81 0t0 3410412 /var/run/postgresql/.s.PGSQL.5432 type=STREAM (CONNECTED)
postgres 763265 postgres 8u unix 0x000000007eb20fe1 0t0 3410421 /var/run/postgresql/.s.PGSQL.5432 type=STREAM (CONNECTED)
postgres 763267 postgres 8u unix 0x000000007fbc8515 0t0 3410423 /var/run/postgresql/.s.PGSQL.5432 type=STREAM (CONNECTED)
postgres 763269 postgres 8u unix 0x00000000940dbe42 0t0 3410425 /var/run/postgresql/.s.PGSQL.5432 type=STREAM (CONNECTED)
postgres 781960 postgres 8u unix 0x00000000316f9ac2 0t0 3541463 /var/run/postgresql/.s.PGSQL.5432 type=STREAM (CONNECTED)
This is a list of established TCP/IP connections where the users are either postgres or zammad. You can see my IPv4 and IPv6 database connections in the list. The connections to redis are obviously not relevant here:
root@zammad:~# lsof -a -i -upostgres -uzammad|grep ESTABLISHED
ruby 762905 zammad 7u IPv4 3411160 0t0 TCP localhost:41412->localhost:redis (ESTABLISHED)
ruby 762907 zammad 7u IPv4 3410393 0t0 TCP localhost:41398->localhost:redis (ESTABLISHED)
ruby 762909 zammad 7u IPv4 3411157 0t0 TCP localhost:41390->localhost:redis (ESTABLISHED)
psql 804513 postgres 3u IPv4 3642604 0t0 TCP localhost:35664->localhost:postgresql (ESTABLISHED)
postgres 804515 postgres 8u IPv4 3642605 0t0 TCP localhost:postgresql->localhost:35664 (ESTABLISHED)
postgres 804532 postgres 8u IPv6 3642627 0t0 TCP localhost:postgresql->localhost:54038 (ESTABLISHED)
You can also check which connection slots are in use by query’ing it from the database. Here you can also see the difference between clients which are connected via a socket, as well as clients which have connected via TCP/IP (check the client_addr and client_port columns):
postgres=# SELECT * FROM pg_stat_activity ;
datid | datname | pid | leader_pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | state_change | wait_event_type | wait_event | state | backend_xid | backend_xmin | query_id | query | backend_type
-------+----------+--------+------------+----------+----------+-----------------------------+-------------+-----------------+-------------+-------------------------------+------------------------------+-------------------------------+-------------------------------+-----------------+---------------------+--------+-------------+--------------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------
| | 750 | | 10 | postgres | | | | | 2023-06-16 01:29:52.431354+02 | | | | Activity | LogicalLauncherMain | | | | | | logical replication launcher
| | 749 | | | | | | | | 2023-06-16 01:29:52.433256+02 | | | | Activity | AutoVacuumMain | | | | | | autovacuum launcher
16389 | zammad | 763245 | | 16388 | zammad | script/background-worker.rb | | | -1 | 2023-07-17 21:15:35.115622+02 | | 2023-07-19 11:34:06.185107+02 | 2023-07-19 11:34:06.185151+02 | Client | ClientRead | idle | | | | UPDATE "delayed_jobs" SET locked_at = '2023-07-19 09:34:06.184570', locked_by = 'host:zammad pid:762909' WHERE id IN (SELECT "delayed_jobs"."id" FROM "delayed_jobs" WHERE (((run_at <= '2023-07-19 09:34:06.184251' AND (locked_at IS NULL OR locked_at < '2023-07-12 09:34:06.184262')) OR locked_by = 'host:zammad pid:762909') AND failed_at IS NULL) ORDER BY priority ASC, run_at ASC LIMIT 1 FOR UPDATE) RETURNING * | client backend
16389 | zammad | 763246 | | 16388 | zammad | script/background-worker.rb | | | -1 | 2023-07-17 21:15:35.119651+02 | | 2023-07-19 11:34:01.391621+02 | 2023-07-19 11:34:01.391682+02 | Client | ClientRead | idle | | | | SELECT "schedulers".* FROM "schedulers" WHERE "schedulers"."active" = $1 ORDER BY "schedulers"."prio" ASC | client backend
16389 | zammad | 763249 | | 16388 | zammad | script/background-worker.rb | | | -1 | 2023-07-17 21:15:35.15163+02 | | 2023-07-17 21:15:35.175234+02 | 2023-07-17 21:15:35.179372+02 | Client | ClientRead | idle | | | | COMMIT | client backend
16389 | zammad | 763260 | | 16388 | zammad | script/background-worker.rb | | | -1 | 2023-07-17 21:15:36.135799+02 | | 2023-07-17 21:15:36.156834+02 | 2023-07-17 21:15:36.156858+02 | Client | ClientRead | idle | | | | SELECT "channels".* FROM "channels" WHERE (active = TRUE AND area LIKE '%::Account') | client backend
16389 | zammad | 763262 | | 16388 | zammad | script/background-worker.rb | | | -1 | 2023-07-17 21:15:37.136198+02 | | 2023-07-19 11:33:46.774424+02 | 2023-07-19 11:33:46.774442+02 | Client | ClientRead | idle | | | | SELECT "schedulers".* FROM "schedulers" WHERE "schedulers"."id" = $1 LIMIT $2 | client backend
16389 | zammad | 763265 | | 16388 | zammad | script/background-worker.rb | | | -1 | 2023-07-17 21:15:38.136523+02 | | 2023-07-19 11:30:52.751694+02 | 2023-07-19 11:30:52.751712+02 | Client | ClientRead | idle | | | | SELECT "schedulers".* FROM "schedulers" WHERE "schedulers"."id" = $1 LIMIT $2 | client backend
16389 | zammad | 763267 | | 16388 | zammad | script/background-worker.rb | | | -1 | 2023-07-17 21:15:39.144142+02 | | 2023-07-19 11:30:52.747265+02 | 2023-07-19 11:30:52.747285+02 | Client | ClientRead | idle | | | | SELECT "schedulers".* FROM "schedulers" WHERE "schedulers"."id" = $1 LIMIT $2 | client backend
16389 | zammad | 763269 | | 16388 | zammad | script/background-worker.rb | | | -1 | 2023-07-17 21:15:40.14449+02 | | 2023-07-19 11:29:10.339064+02 | 2023-07-19 11:29:10.341471+02 | Client | ClientRead | idle | | | | COMMIT | client backend
16389 | zammad | 781960 | | 16388 | zammad | script/background-worker.rb | | | -1 | 2023-07-18 12:32:49.84389+02 | | 2023-07-18 12:32:49.857598+02 | 2023-07-18 12:32:49.861408+02 | Client | ClientRead | idle | | | | COMMIT | client backend
18652 | testdb | 804515 | | 18650 | harrie | psql | 127.0.0.1 | | 35664 | 2023-07-19 11:12:43.658244+02 | | | 2023-07-19 11:12:43.670095+02 | Client | ClientRead | idle | | | | | client backend
18652 | testdb | 804532 | | 18650 | harrie | psql | ::1 | | 54038 | 2023-07-19 11:13:03.724122+02 | | | 2023-07-19 11:13:03.734204+02 | Client | ClientRead | idle | | | | | client backend
5 | postgres | 804944 | | 10 | postgres | psql | | | -1 | 2023-07-19 11:32:51.315059+02 | 2023-07-19 11:34:07.31679+02 | 2023-07-19 11:34:07.31679+02 | 2023-07-19 11:34:07.316791+02 | | | active | | 313195 | | SELECT * FROM pg_stat_activity ; | client backend
| | 729 | | | | | | | | 2023-06-16 01:29:52.365232+02 | | | | Activity | BgWriterHibernate | | | | | | background writer
| | 728 | | | | | | | | 2023-06-16 01:29:52.365541+02 | | | | Activity | CheckpointerMain | | | | | | checkpointer
| | 748 | | | | | | | | 2023-06-16 01:29:52.431954+02 | | | | Activity | WalWriterMain | | | | | | walwriter
(17 rows)