Compliant Kubernetes Service documentation has moved

Please note: You are not reading Kubernetes documentation. If you're looking for Compliant Kubernetes Service documentation, it has moved. Read more here.

Purpose

PostgreSQL supports a number of additional supplied modules which extend the core functionality of the PostgreSQL service. The libraries for the modules have been installed on the base image of your service. 

The modules are typically loaded by creating an extension; however, some modules require additional steps. We will look at a simple example of loading a module by creating an extension and loading a more complicated module which requires shared_preload_libraries to be set.

 

Loading by creating an extension

This example will load the citext module. For example:

catalyzeDB=# create extension citext;

CREATE EXTENSION

Next, you can verify the citext module has been loaded by executing the example provided in the module's documentation.

CREATE TABLE users (
    nick CITEXT PRIMARY KEY,
    pass TEXT   NOT NULL
);

INSERT INTO users VALUES ( 'larry',  md5(random()::text) );
INSERT INTO users VALUES ( 'Tom',    md5(random()::text) );
INSERT INTO users VALUES ( 'Damian', md5(random()::text) );
INSERT INTO users VALUES ( 'NEAL',   md5(random()::text) );
SELECT * FROM users WHERE nick = 'Larry';

OUTPUT

 nick | pass
-------+----------------------------------
larry | 0fd79f64980f746567bb0e09aa5fef02
(1 row)

That's it! The citext module is working. 

 

Loading shared libraries

The module pg_stat_statements is a very popular module for analyzing query performance. It's also not possible to load simply by creating an extension, as demonstrated below.

catalyzeDB=# create extension pg_stat_statements ;

CREATE EXTENSION

catalyzeDB=# select count(*) from pg_stat_statements;

ERROR: pg_stat_statements must be loaded via shared_preload_libraries

It turns out the documentation for pg_stat_statements states it must be added to the shared_preload_libraries system variable. You can read more about configuring system variables in Configuring PostgreSQL in CPaaS.

Run the following to add pg_stat_statements to the shared_preload_libraries system variable.

catalyzeDB=# alter system set shared_preload_libraries to 'pg_stat_statements' ;

ALTER SYSTEM

Finally, use the Datica CLI to redeploy the database service (this will cause a downtime for active connections). 

user@laptop:~$ datica -E ExampleEnv redeploy database-1

Redeploying service database-1 (ID = 5e516388-da17-477d-bffa-21f6d5600bf2) in environment ExampleEnv (ID = 97d68698-c508-443a-b4cb-f66dc8e60c5c)
Redeploy successful! Check the status with "datica status" and your logging dashboard for updates

Now that you've created the extension, added the shared_preload_libraries, and restarted the service, the module should be running and you can query the pg_stat_statements view.

catalyzeDB=# select count(*) from pg_stat_statements;

count
-------
8
(1 row)