Heroku Data Labs: Extensions on Any Schema for Heroku Postgres
Last updated May 12, 2023
Table of Contents
By default, Heroku Postgres extensions are installed to the heroku_ext schema. You can enable this Heroku Data Labs feature on existing databases to install PostgreSQL extensions on schemas other than heroku_ext.
You can install extensions on new non-Essential-tier databases at provision time.
For Essentials-tier databases, you can only install extensions in the heroku_ext schema.
Features added through Heroku Data Labs are experimental and subject to change.
Prerequisites
- A Standard, Premium, Private, or Shield tier Heroku Postgres database
- The latest version of the Heroku Data CLI plugin installed
Enable Extensions on Any Schema on an Existing Heroku Postgres Database
To enable this feature, run the command:
$ heroku data:labs:enable extensions-on-any-schema --addon DATABASE_URL
Enabling extensions-on-any-schema on postgresql-trapezoidal-57207...... done
This feature doesn’t modify any of your existing extensions and only applies to new extensions you install. Dropping an existing extension and recreating it installs the extension to the first schema in your search_path, typically public.
Disable Extensions on Any Schema on an Existing Heroku Postgres Database
To disable this feature, run the command:
$ heroku data:labs:disable extensions-on-any-schema --addon DATABASE_URL
Disabling extensions-on-any-schema on postgresql-trapezoidal-57207...... done
Disabling this feature doesn’t change the schema on any existing extensions, and only forces new extensions to install to the heroku_ext schema.
Show Feature Status
To see if this feature is enabled or disabled, run the command:
$ heroku data:labs:list --addon DATABASE_URL
=== Experimental Features Available for YOUR_ADDON_NAME:
[+] Extensions on Any Schema Enable extensions on any schema
A green cross within the box indicates that the feature is enabled on your add-on, while an empty box indicates that it’s disabled.
View Your Extensions
To see which schemas your extensions are installed in, open a session with heroku pg:psql and run the \dx command:
intense-mesa-15917::DATABASE=> \dx
List of installed extensions
Name | Version | Schema | Description
--------------------+---------+------------+------------------------------------------------------------------------
pg_stat_statements | 1.10 | heroku_ext | track planning and execution statistics of all SQL statements executed
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
xml2 | 1.1 | public | XPath querying and XSLT
(3 rows)