Create Extension

After installing PostgreSQL extensions, you can view them in the pg_available_extensions view. However, enabling these extensions typically requires additional steps:

1. Some extensions must be added to the shared_preload_libraries for dynamic loading, such as timescaledb and citus.
2. Most extensions need to be activated by running the SQL statement: CREATE EXTENSION <name>;. A few, like wal2json, do not require this step.

Modifying shared_preload_libraries:

• Before initializing the database cluster: You can manually specify the required libraries using the pg_libsparameter.
• After the database cluster has been initialized: You can modify the cluster configuration by directly editing the shared_preload_libraries parameter and applying the changes (no restart required).
• Typical extensions that require dynamic loading: citus, timescaledb, pg_cron, pg_net, pg_tle

Executing CREATE EXTENSION:

• Before initializing the database cluster: You can specify the required extensions in the extensions list within pg_databases.
• After the database cluster has been initialized: You can directly connect to the database and execute the SQL command, or manage extensions using other schema management tools.

Conceptually: PostgreSQL extensions usually consist of three parts: a control file (metadata, always present), an SQL file (optional SQL statements), and a .so file (optional binary shared library). Extensions that provide a .so file may need to be added to shared_preload_libraries to function properly, such as citus and timescaledb. However, many extensions do not require this, such as postgis and pgvector. Extensions that do not expose a SQL interface do not need a CREATE EXTENSION command to be executed, such as the wal2json extension, which provides CDC extraction capabilities.

To complete the extension creation, execute the CREATE EXTENSION SQL statement in the database where you wish to enable the extension.

CREATE EXTENSION vector;  -- create & enable vector extension
CREATE EXTENSION hydra;   -- create & enable columnar extension