List PostgreSQL Extensions Per Database: A Practical Guide

Hey guys! Ever found yourself needing to figure out which PostgreSQL extensions are being used in each of your databases? If you're managing a PostgreSQL server with multiple databases, like our friend with 30 databases on Postgres 9.5, this can be a real head-scratcher. You want a quick, efficient way to get this info, ideally with a query you can easily use in pgAdmin. Well, you've come to the right place! Let’s dive into how you can achieve this in one go. We'll break it down step by step, making it super easy to follow, even if you're not a PostgreSQL guru.

The Challenge: Identifying Extensions Across Multiple Databases

When dealing with multiple databases in PostgreSQL, it’s crucial to understand the extensions each database utilizes. Extensions add functionalities like new data types, functions, and operators, making PostgreSQL highly versatile. However, knowing which extensions are installed and used in which database is essential for maintenance, upgrades, and troubleshooting. Imagine having to manually connect to each of your 30 databases just to check their extensions – sounds like a total time-sink, right? This is where a streamlined approach becomes invaluable. We need a method that can consolidate this information efficiently, preferably through a single query. This not only saves time but also reduces the chances of human error. By centralizing this information, you can ensure consistency across your database environment, making it easier to manage and optimize your PostgreSQL setup. Think of it as having a bird's-eye view of your entire extension landscape, allowing you to make informed decisions about your database infrastructure. So, how do we tackle this challenge? Let’s explore the solution that combines PostgreSQL's system catalogs with some clever SQL to get the job done.

Crafting the SQL Query to List Extensions

The heart of our solution lies in crafting the right SQL query. To list PostgreSQL extensions per database, we'll leverage PostgreSQL's system catalogs, specifically the pg_extension and pg_database catalogs. These catalogs contain metadata about extensions and databases, respectively. The pg_extension catalog stores information about installed extensions, such as their names and versions, while the pg_database catalog holds details about each database, including their names and connection limits. By joining these catalogs with some filtering, we can create a query that outputs the desired information. The query essentially iterates through each database, checks its installed extensions, and presents the results in a clear, readable format. It's like having a detective that goes from one database to another, collecting clues about the extensions in use. This approach is both efficient and comprehensive, ensuring that you don't miss any extension in any database. So, what does this magic query look like? Let’s break it down and see how each part contributes to the final result.

SELECT
    d.datname AS database_name,
    e.extname AS extension_name
FROM
    pg_database d
CROSS JOIN
    pg_extension e
WHERE
    e.extnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public')
    AND d.datistemplate = false
    AND has_database_privilege(d.datname, 'CONNECT')
ORDER BY
    d.datname, e.extname;

Let's break this query down piece by piece so you understand exactly what's going on:

Dissecting the SQL Query: Understanding Each Component

The SQL query we've crafted is designed to be both efficient and informative, giving you a clear picture of which extensions are used in each database. The first key part is the SELECT statement, where we specify the data we want to retrieve. We're selecting d.datname, which represents the name of the database, and e.extname, which stands for the name of the extension. These are the two critical pieces of information we need: the database and its extensions. Next, we have the FROM clause, where we define the tables we're pulling data from. Here, we're using pg_database (aliased as d) and pg_extension (aliased as e). These are PostgreSQL's system catalogs that hold information about databases and extensions, respectively. The CROSS JOIN clause is a crucial part of this query. It creates a Cartesian product of the pg_database and pg_extension tables. This means that every row from pg_database is paired with every row from pg_extension. While this might sound like overkill, it ensures that we consider all possible combinations of databases and extensions. The WHERE clause is where we apply filters to narrow down our results. The first condition, e.extnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public'), filters the extensions to only include those in the public namespace. This is a common practice, as most user-installed extensions reside in the public namespace. The second condition, d.datistemplate = false, excludes template databases. Template databases are used as blueprints for creating new databases, and we typically don't want to include them in our list of active databases. The third condition, has_database_privilege(d.datname, 'CONNECT'), ensures that we only list databases that the current user has the CONNECT privilege for. This is a security measure to prevent unauthorized access to database information. Finally, the ORDER BY clause sorts the results first by database name (d.datname) and then by extension name (e.extname). This makes the output easy to read and helps you quickly find the information you need. By understanding each part of this query, you can appreciate its efficiency and how it brings together information from different system catalogs to provide a comprehensive view of your PostgreSQL extensions.

Running the Query in pgAdmin: Step-by-Step

Now that we have our query, let's get it running in pgAdmin. This is where the rubber meets the road, and you'll see the fruits of our SQL labor. First things first, fire up pgAdmin and connect to your PostgreSQL server. Once you're connected, navigate to the database you want to query, or simply connect at the server level to query across all databases. Open the Query Tool by right-clicking on the database (or server) and selecting