Managing Postgres extensions

Cloud Service supports many Postgres extensions. See Postgres extensions available by deployment for the complete list.

Extensions available when using your cloud account

Installing many Postgres extensions requires superuser privileges. The table in Postgres extensions available by deployment indicates whether an extension requires superuser privileges. If you're using your cloud account, you can grant superuser privileges to edb_admin so that you can install these extensions on your cluster (see superuser).

Extensions available when using EDB Hosted Cloud Service

If you're using EDB Hosted Cloud Service account, you can install and use the following extensions.

PostgreSQL contrib extensions/modules:

• auth_delay
• bloom
• btree_gin (PostgreSQL v12 only)
• btree_gist (PostgreSQL v12 only)
• citext (PostgreSQL v12 only)
• cube (PostgreSQL v12 only)
• earthdistance
• fuzzystrmatch
• hstore (PostgreSQL v12 only)
• hstore_plperl
• intagg
• intarray (PostgreSQL v12 only)
• isn (PostgreSQL v12 only)
• jsonb_plperl (PostgreSQL v12 only)
• ltree (PostgreSQL v12 only)
• old_snapshot (PostgreSQL and EDB Postgres Advanced Server v14 and v15 only)
• pageinspect
• pg_buffercache
• pg_freespacemap
• pg_prewarm
• pg_stat_statements
• pg_surgery (PostgreSQL and EDB Postgres Advanced Server v14 and v15 only)
• pg_trgm (PostgreSQL v12 and EDB Postgres Advanced Server v15 only)
• pg_visibility
• pgaudit (PostgreSQL only)
• pgcrypto (PostgreSQL v12 only)
• pglogical3 (PostgreSQL v12 and v13 only)
• pgrowlocks
• pgstattupple
• postgis
• postgres_fdw
• seg (PostgreSQL v12 only)
• sslinfo
• tablefunc (PostgreSQL v12 only)
• tcn (PostgreSQL v12 only)
• tsm_system_rows (PostgreSQL v12 only)
• tsm_system_time (PostgreSQL v12 only)
• unaccent (PostgreSQL v12 only)
• uuid-ossp (PostgreSQL v12 only)
• xml2
• spi - autoinc
• spi - refint
• spi - insert_username
• spi - moddatetime

EDB extensions:

• edb_dbo
• sql-profiler
• query_advisor
• refdata
• autocluster
• edb_pg_tuner

Installing extensions

Use the CREATE EXTENSION command to install most extensions. You must enable certain extensions, including the EDB Postgres Tuner (pg_tuner) extension and PostGIS on the DB Configuration tab of the Create or Edit Cluster page of the Console.

Example: Installing multiple extensions

This example shows one way of installing multiple extensions simultaneously.

1. Create a text file containing the CREATE EXTENSION command for each of the extensions you want to install. In this example, the file is named create_extensions.sql.

   → WrapCopy

   CREATE EXTENSION <extension_name_1> SCHEMA <schema_name>;
   CREATE EXTENSION <extension_name_2> SCHEMA <schema_name>;
   CREATE EXTENSION <extension_name_3> SCHEMA <schema_name>;
   CREATE EXTENSION <extension_name_4> SCHEMA <schema_name>;

2. Use your Postgres client of choice to load the extensions. For example, using psql:

   → WrapCopy

   psql <biganimal_connection_string> -f create_extensions.sql

   For more information about connecting to your cluster using a client, see Connecting to your cluster.

Working with extensions

Use the DROP EXTENSION command to remove extensions.

Use the pg_available_extensions view to see a list of all PostreSQL extensions.

The catalog_pg_extension catalog stores information about the installed extensions.