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 !


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