DEV Community

Talha Munir 🇵🇸
Talha Munir 🇵🇸

Posted on

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>;
Enter fullscreen mode Exit fullscreen mode

Step 2:

Use your Postgres client of choice to load the extensions.

psql <biganimal_connection_string> -f createExtension.sql
Enter fullscreen mode Exit fullscreen mode

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/

Top comments (0)