Postgres extensions

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

Installing extensions

Use the CREATE EXTENSION command to install extensions, except for the EDB Postgres Tuner (pg_tuner) extension. You enable edb_pg_tuner on the DB Configuration tab of the Create or Edit Cluster page of the BigAnimal portal.

You need superuser privileges to install most extensions on BigAnimal. The table in Postgres extensions available by deployment indicates whether an extension requires superuser privileges.

Example: Installing multiple extensions

One way of installing multiple extensions simultaneously is to:

  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.

    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:

    psql <biganimal_connection_string> -f create_extensions.sql

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

Extensions requiring superuser privileges

Certain extensions require superuser privileges to use the extension.

Extensions requiring superuser to useNotes
amcheckCan be granted to others
pg_buffercachesuperuser or pg_monitor role
pg_freespacemapsuperuser or pg_stat_scan_tables role
pgrowlockssuperuser or pg_stat_scan_tables role
pgstattuplesuperuser or pg_stat_scan_tables role
pg_visibilitysuperuser or pg_stat_scan_tables role

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.