List PostgreSQL extensions per database

Question

I have a Postgres 9.5 server with 30 databases.

I would like to find out which database uses which extension(s) in one go, preferably in a query that is usable in pgAdmin.

I know that SELECT * FROM pg_extension gives me the extensions for the database I am connected to.

I also know that SELECT * FROM pg_available_extensions lists, well, all available extensions.

I also know that SELECT * FROM pg_database lists all existing databases.

Question

How can I create a list that includes the database name and the extensions used in each database?

• cedricdlb: Possibly the query on the following page can be adapted to get the data you seek: tapoueh.org/blog/2019/11/…

Answer 1

You connect to each database in turn and query pg_extension.

• pablete: Is there away to do this in an automated way? like a script to give to psql or something

Answer 2

I'm not sure if this can be automated directly in psql, as AFAIK you can't do cross-database queries.

But you can easily automate this with some Bash. Here is quick script, which will list all databases in all PostgreSQL versions (clusters) and their extensions. Output is in Markdown format, so it is easier to share/store if needed.

echo "# Extensions in PostgreSQL databases"
while IFS= read -r cluster; do
 echo "## ${cluster}"
 while IFS= read -r dbname; do
 echo "### ${dbname}"
 psql --cluster="${cluster}" -U postgres -qAtc "SELECT CONCAT('- ', extname, ' ', extversion) FROM pg_extension;" "${dbname}"
 echo
 done < <(psql --cluster="${cluster}" -U postgres -qAtc "SELECT datname FROM pg_database WHERE datname != 'template0';")
 echo
done < <(pg_lsclusters --no-header | awk '{ print 1ドル "/" 2ドル }')

Output looks like this:

## 9.5/main
### template1
- plpgsql 1.0
### postgres
- plpgsql 1.0
### somedb
- plpgsql 1.0
## 14/main
### postgres
- plpgsql 1.0
### template1
- plpgsql 1.0
### gitlab
- plpgsql 1.0
- btree_gist 1.6
- pg_trgm 1.6

• Gert van den Berg: Cross-db queries are possible, but painful.