Enable or disable a PostgreSQL extension
To use a PostgreSQL extension its necessary to first enable the extension in the PostgreSQL cluster and then instantiate the extension in a desired database.
Enabling an extension is when a user adds the extension’s required permissions and configuration parameters to the PostgreSQL configuration.
Instantiating an extension means to run the PostgreSQL command
|
1 |
CREATE EXTENSION <extension name>; |
in a desired database by a user with CREATE permissions.
Enable an Extension
Some extensions can’t be instantiated by the user and need special enablement. This can be done at cluster provisioning time or via the API or console at a later time. Note that enablement after the cluster is created may require support to perform a manual restart on your cluster to complete the setup.
Via Console
1. To enable an extension, first, navigate to the PostgreSQL Extensions page of your PostgreSQL cluster. From the PostgreSQL Extensions page, click the Enable button. You will be redirected to the form to enable available extensions.
2. Select the extension that you want to enable by pressing on the corresponding checkbox and then press on the Enable button.
Via API
PostgreSQL extensions can also be enabled via Update PostgreSQL cluster details endpoint from provisioning API. You can find the how to access and use Instaclustr’s provisioning API here.
Example request
|
1 2 3 4 5 6 |
{ "extensions": ["PG_CRON"], "dataCentres": [ ... ] } |
Disable an Extension
Via Console
1. To disable an extension, first, navigate to the PostgreSQL Extensions page of your PostgreSQL cluster. From the PostgreSQL Extensions page, click the Disable button on the extension that you want to disable. You will be redirected to the form to disable that particular extension.
2. Review that this is the extension that you want to disable and the press the button Disable Extension.
Via API
PostgreSQL extensions can also be disabled via Update PostgreSQL cluster details endpoint from provisioning API. You can find the how to access and use Instaclustr’s provisioning API here .
Example request
|
1 2 3 4 5 6 |
{ "extensions": ["PG_CRON"], "dataCentres": [ ... ] } |
Instantiate an Extension
To instantiate an extension in a database, open the psql terminal, connect to your desired database and run:
|
1 |
CREATE EXTENSION <extension name>; |
Note that you will need to be authenticated as a user with CREATE permissions, on the database that you instantiate the extension into. For further details, see the official documentation.
PG Cron
PG Cron allow for running SQL on a schedule. It uses the same syntax as the cron unix command. It allows these jobs to run SQL in a specified database as the user which create the Cron job.
As part of the enablement for PG Cron the extension will created in the ‘postgres’ database and the icpostgresql user appropriate access.
You can check this by running the following command in a psql terminal in the ‘postgres database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
postgres=> \dx List of installed extensions Name | Version | Schema | Description --------------------+---------+------------+------------------------------------------------------------------------ pg_cron | 1.6 | pg_catalog | Job scheduler for PostgreSQL pg_stat_statements | 1.10 | public | track planning and execution statistics of all SQL statements executed plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language (3 rows) |
Scheduling jobs in other databases can be performed with the cron.schedule_in_database function.
|
1 |
SELECT cron.schedule_in_database (‘Unique name for the job', '*/10 * * * *', $$<SQL Statement>$$, 'Database Name'); |
Note: ‘*/10 * * * *’ above refers to the interval in which the statement will run. This example is for every 10 minutes. Users should be aware that the frequency and number of Cron jobs will will have impact on cluster performance. These jobs will be run in worker threads, and we configure limits based on node size.
By Instaclustr Support