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;
Warning : the column is named 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.

Note : on Debian, upgrading from Stretch to Buster, the glibc locale data has been upgraded so this changes the way Postgres sorts data indexes. To avoid problems, you must first run:
$ reindexdb --all

There 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

Useful links