Connection pooling
To get active connections from psql:
SELECT COUNT(*) FROM pg_stat_activity WHERE pid <> pg_backend_pid();
In case we want to get the number of connections for a specific user:
SELECT COUNT(*) FROM pg_stat_activity WHERE pid <> pg_backend_pid() AND usename = current_user;
usename and not username !
Upgrading
On Debian, to upgrade to version (say 10 to 13), both versions should be installed. Once that's done, you can run su postgres to log in as postgres user.
glibc locale data has been upgraded so this changes the way Postgres sorts data indexes. To avoid problems, you must first run:
$ reindexdb --allThere must be a cluster for each Postgres version installed. It is possible to list them with:
$ pg_lsclusters
There must also be two Systemd services (e.g. postgresql@10-main and postgresql@13-main with main being the name of the cluster).
First, to make things a bit more safe, you need to backup your configuration files and databases:
$ cp /etc/postgresql/10/* /backup/...
$ pg_dump database > /backup/database.sql # Or pg_dumpall
Then, you can delete the cluster created for the new version:
$ pg_dropcluster --stop 13 main
Finally, you can upgrade your cluster to the new version
$ pg_upgradecluster 10 main
Once, and only once, you've checked that everything is working fine on the new version, you can clean-up everything:
$ pg_dropcluster --stop 10 main
$ apt-get remove postgresql-10
$ apt autoremove