PostgreSQL Extensions

Create or Drop Extensions

It is possible to install PostgreSQL extensions with the Cloud Foundry CLI and additional configuration parameters:

Commands

cf create-service a9s-postgresql PLAN_NAME INSTANCE_NAME -c '{"install_plugins": ["<plugin_name>"]}'

cf update-service INSTANCE_NAME -c '{"install_plugins": ["<plugin_name>"]}'

cf update-service INSTANCE_NAME -c '{"delete_plugins": ["<plugin_name>"]}'

Available Extensions

The following PostgreSQL extensions are available:

• citext
• fuzzystrmatch
• hstore
• ltree
• pg_stat_statements
• pg_squeeze (Please see pg_squeeze Requirements and Usage)
• pg_trgm
• pgcrypto
• postgis
• uuid-ossp
• vector

Default Extensions

There are PostgreSQL extensions that are always installed and it's also not possible to delete them. The following PostgreSQL extensions are set as default extensions:

• citext
• pgcrypto

These two extensions are needed to have a functional replication.

pg_squeeze Requirements and Usage

info

a9s PostgreSQL 13 version does not support pg_squeeze extension.

Usage of the pg_squeeze extension demands logical decoding permissions, and at least one available replication slot. To ensure that it functions correctly, the a9s PostgreSQL Service Instance must be created with the following Custom Parameters:

• max_replication_slots: To ensure this plugin functions correctly, it is recommended to increase the current value of the max_replication_slots parameter by 1. For additional information, see max_replication_slots. To find the current value:
  • If the max_replication_slots value was previously set, please refer to the Getting a Custom Parameter for a Service Instance documentation to retrieve this information.
  • If the max_replication_slots value was not set, the a9s PostgreSQL Service Instance will be using the default value. For more information, see Custom Parameters - max_replication_slots.
• wal_level_logical: This parameter must be set to true. For more information, see Custom Parameters - wal_level_logical.

caution

If these custom parameters are not set as above, the pg_squeeze extension will not work as expected.

The custom parameters should be set at instance creation following the instructions above. For example:

# If the `max_replication_slots` was 0.

cf cs <service-type> <service-plan> <service-name> \
  -c '{"install_plugins": ["pg_squeeze"], "max_replication_slots": 1, "wal_level_logical": true }'

To use the squeeze feature, it is necessary to call the squeeze.fn_squeezer function, like in the following this:

"SELECT squeeze.fn_squeezer('<SCHEMA>','<TABLE_NAME>');"

Where <TABLE_NAME> is the name of the table to be compressed and the <SCHEMA> is the schema when of the table.

Example:

"SELECT squeeze.fn_squeezer((SELECT SPLIT_PART(setting, ', ',2) FROM pg_settings WHERE name = 'search_path'), 'table');"

The fn_squeezer function is a wrapper on the squeeze_table function provided by pg_squeeze the schema and table can be provided. The squeeze_table function should not be called directly. Use the fn_squeezer function instead.

The extension uses the squeeze schema to store the fn_squeezer function and some tables with internal data like usage and error logs. This schema should not be used by the user.