How to Check if a PostgreSQL Extension is Installed in YugabyteDB

Overview

In YugabyteDB, PostgreSQL extensions enhance database functionality by providing additional features such as advanced indexing, performance monitoring, and more. To determine whether a specific extension is installed, you can query the system catalog tables.

Checking Installed Extensions

To list all installed extensions in YugabyteDB, use the following SQL query:

SELECT oid, extname, extversion FROM pg_extension;

Explanation:

• oid - Object identifier for the extension.
• extname - Name of the installed extension.
• extversion - Version of the installed extension.

This query will return all extensions currently installed in your YugabyteDB instance.

Alternatively, we can also run \dx command to list the installed extensions.

Checking Available Extensions

To check which extensions are available for installation, use the following query:

SELECT name, default_version, installed_version FROM pg_available_extensions;

Explanation:

• name - Name of the extension.
• default_version - The default version of the extension.
• installed_version - The currently installed version, if applicable.

If the installed_version column is NULL, it means the extension is available but not yet installed.

Example Output

Installed Extensions:

 oid | extname | extversion 
-------+--------------------+------------
13227 | plpgsql | 1.0
13232 | pg_stat_statements | 1.6-yb-1.0
16640 | hypopg | 1.3.2
(3 rows)

yugabyte=# \dx
List of installed extensions
Name | Version | Schema | Descripti
on 
--------------------+------------+------------+---------------------------------
--------------------------
pg_stat_statements | 1.6-yb-1.0 | pg_catalog | track execution statistics of al
l SQL statements executed
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)

Available Extensions:

       name       | default_version | installed_version
-----------------+----------------+------------------
  pg_stat_statements | 1.8  | 1.8  
  pgcrypto          | 1.3  | 1.3  
  hstore            | 1.7  | NULL  
(3 rows)

In this example, pg_stat_statements and pgcrypto are installed, while hstore is available but not yet installed.

Conclusion

By using these queries, you can easily verify which PostgreSQL extensions are installed in YugabyteDB and check for available extensions. If you need to install an extension, use the CREATE EXTENSION command:

CREATE EXTENSION hstore;

Ensure that the extension is supported in YugabyteDB before attempting to install it.

https://docs.yugabyte.com/preview/explore/ysql-language-features/pg-extensions/