Managing Postgres extensions

Big Animal supports many Postgres extensions. Some of the extensions can be viewed over here

Extensions in BigAnimal:

Extensions are available when you use your cloud account.
Installing Postgres extensions requires superuser privileges.
If you use your cloud account you can grant superuser privileges to edb_admin so that you can install these extensions on your cluster.

List of extensions:

A list of extensions that can be installed using your cloud account is given below:

• 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
• 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

Installing extensions:

Use the create_extension command to install extensions. You can enable edb_pg_tuner on the DB configuration tab of the Create or Edit Cluster page of the big animal portal. The following example shows how you can install multiple extensions simultaneously.

Step 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 createExtension.sql

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>;

Step 2:

Use your Postgres client of choice to load the extensions.

psql <biganimal_connection_string> -f createExtension.sql

Removing Extensions:

You can remove extensions using Drop Extensions command.

Retrieving list of extensions:

You can view the list of all Postgres extensions using pg_available_extension

Pg extension catalog:

The Catalog_pg_extension catalog stores all the information about the installed extensions.

References:

1. https://www.enterprisedb.com/docs/biganimal/latest/using_cluster/extensions/
2. https://www.postgresql.org/docs/