Postgres Extensions

StackGres has developed an innovative mechanism to load Postgres extensions on-demand, onto the otherwise immutable container images. Required extensions can be specified declaratively in the cluster definition.

Out of the box, a StackGres cluster comes stripped of all the Postgres core extensions. Only some basic extensions are installed by default, like plpgsql.

List Extensions

We can list the basic extensions by connecting to the cluster (here cluster), and executing the following SQL:

postgres=# select * from pg_available_extensions();
        name        | default_version |                           comment                            
 dblink             | 1.2             | connect to other PostgreSQL databases from within a database
 plpgsql            | 1.0             | PL/pgSQL procedural language
 pg_stat_statements | 1.7             | track execution statistics of all SQL statements executed
 plpython3u         | 1.0             | PL/Python3U untrusted procedural language
(4 rows)

Simple Extension Installation

To install other extensions, StackGres provides a mechanism to make them available on the fly by declaring them in the SGCluster. At first, we’ll have a look at a simple example, with the ltree extension.

Edit your cluster’s YAML file and add the following section inside the postgres spec:

kind: SGCluster
# ...
    - name: 'ltree'
    # ...
  # ...

After applying the change to Kubernetes (kubectl apply), the extension will be dynamically downloaded onto the container. After a few seconds, repeating the previous SQL query in the Postgres console will now show the ltree extension.

Now, you just need to run create extension ltree; on the databases that you want the extension to be installed in, and you’re ready to go.

Note that installing some extensions may require a cluster restart. A more complex example is shown below.

Complex Extension Installation

For a more complex example, we’ll add the timescaledb and postgis extensions:

kind: SGCluster
  name: cluster
  # [...]
  # [..]
    - name: postgis
    - name: timescaledb

After applying the YAML change to the cluster, the extensions will be downloaded and will become available:

# select * from pg_available_extensions;
        name        | default_version | installed_version |                               comment                               
 postgis            | 3.0.1           | 3.0.1             | PostGIS geometry, geography, and raster spatial types and functions
 timescaledb        | 2.2.0           | 2.2.0             | Enables scalable inserts and complex queries for time-series data
 pg_stat_statements | 1.6             |                   | track execution statistics of all SQL statements executed
 plpgsql            | 1.0             | 1.0               | PL/pgSQL procedural language
 dblink             | 1.2             | 1.2               | connect to other PostgreSQL databases from within a database
 plpython3u         | 1.0             | 1.0               | PL/Python3U untrusted procedural language
(5 rows)

As usual, to use an extension on a database use the create extension command like below:

postgres=# create extension postgis;

Some extensions need extra files to be installed and configured before they can be used. This varies depending on the extension and, in some cases, requires the cluster to be configured and restarted:

  • Extensions that requires to add an entry to shared_preload_libraries configuration parameter.
  • Upgrading extensions that overwrite any file that is not the extension'’s control file or extension'’s script file.
  • Removing extensions. Until the cluster is not restarted a removed extension will still be available.
  • Install of extensions that require extra mount. After installed the cluster will require to be restarted.

Update the Configuration for the New Extensions

Some extensions, such as timescale needs to update some configuration to work, as shown in the error below:

postgres=# create extension timescaledb;
FATAL:  extension "timescaledb" must be preloaded
HINT:  Please preload the timescaledb library via shared_preload_libraries.

This can be done by editing the config file at: /var/lib/postgresql/data/postgresql.conf
and adding 'timescaledb' to the list in the shared_preload_libraries config.
        # Modify postgresql.conf:
        shared_preload_libraries = 'timescaledb'

Another way to do this, if not preloading other libraries, is with the command:
        echo "shared_preload_libraries = 'timescaledb'" >> /var/lib/postgresql/data/postgresql.conf 

(Will require a database restart.)

If you REALLY know what you are doing and would like to load the library without preloading, you can disable this check with: 
        SET timescaledb.allow_install_without_preload = 'on';
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

To fix that, it is necessary to find the configuration used in the SGCluster, update it with the new values, then reload the cluster.

Editing the PostgresConfig

Assuming that my cluster name is named cluster, execute the command below to find its current postgres configuration:

$ kubectl get sgcluster/cluster -o jsonpath="{ .spec.configurations.sgPostgresConfig }"

Once found the config, edit it, adding the extra configs:

kubectl edit sgPgConfig/postgres-12-generated-from-default-1622494739858

Then add the extra param in the shared_preload_libraries, under the postgresql.conf:

kind: SGPostgresConfig
  name: postgres-12-generated-from-default-1622494739858
  namespace: default
  # ...
  # ...
    # ...
    shared_preload_libraries: pg_stat_statements,auto_explain,timescaledb

Please note that the pg_stat_statements and the auto_explain are automatically set up by the cluster creation, they are used for the monitoring and will break the node creation if removed.

Reloading and Testing

Once updated the configuration is necessary to reload the cluster to update the configuration. To do so, a restart SGDbOps can be created:

kind: SGDbOps
  name: restart-1622494739858
  namespace: default
  sgCluster: cluster
  op: restart

You can wait for the completion of the task using kubectl wait command:

$ kubectl wait sgdbops/restart-1622494739858 --for=condition=Completed condition met

Connect in the psql and run the following commands:

postgres=# show shared_preload_libraries ;
(1 row)

postgres=# create extension timescaledb;
 _____ _                               _     ____________  
|_   _(_)                             | |    |  _  \ ___ \ 
  | |  _ _ __ ___   ___  ___  ___ __ _| | ___| | | | |_/ / 
  | | | |  _ ` _ \ / _ \/ __|/ __/ _` | |/ _ \ | | | ___ \ 
  | | | | | | | | |  __/\__ \ (_| (_| | |  __/ |/ /| |_/ /
  |_| |_|_| |_| |_|\___||___/\___\__,_|_|\___|___/ \____/
               Running version 1.7.4
For more information on TimescaleDB, please visit the following links:

 1. Getting started:
 2. API reference documentation:
 3. How TimescaleDB is designed:

Note: TimescaleDB collects anonymous reports to better understand and assist our users.
For more information and how to disable, please see our docs


Available Extensions

Check the Extensions page for the complete list of available extensions.