Overview

Because Datica CPaaS does not expose postgresql.conf to database services, customers wanting to adjust PostgreSQL configuration settings must make use of the pg_settings view and the ALTER SYSTEM, SHOW, and SET commands to make changes to PostgreSQL runtime parameters.

Refer to the PostgreSQL documentation for Setting Parameters for more information.

Applying Changes

To correctly apply a change to a configuration variable, you must understand the context in which the variable exists. The contexts are detailed in the pg_settings PostgreSQL documentation. There are several possible values of context. In order of decreasing difficulty of changing the setting, they are:

datica redeploy <service>

select pg_reload_conf();
\c

select pg_reload_conf();
\c

select pg_reload_conf();

* Unsupported in CPaaS

Example

In the following example we will enable logging for all connections and disconnections.

Check the current configuration values.

-- Use regular expression to search for specific configuration variables
select name
    ,setting
    ,unit
    ,context
    ,source
    ,sourcefile
    ,pending_restart
from pg_settings
where name ~ 'log.*connections'
;

OUTPUT

        name        | setting | unit |      context      | source  | sourcefile | pending_restart
--------------------+---------+------+-------------------+---------+------------+-----------------
 log_connections    | off     |      | superuser-backend | default |            | f
 log_disconnections | off     |      | superuser-backend | default |            | f
(2 rows)

Assuming they are set to off (the default) let's turn them on using the ALTER SYSTEM command. Apply the change given the context of the variable (see table in Apply Changes).

-- Alter the variables
ALTER SYSTEM SET log_connections = on;
ALTER SYSTEM SET log_disconnections = on;

-- Apply the changes
select pg_reload_conf();
\c

Let's view the changes of all variables modified by the ALTER SYSTEM command. Only the variables we changed above will be displayed, but it's helpful to know how your configuration has changed from the default provided by Datica.

-- Show changes applied with the ALTER SYSTEM command
select name
    ,setting
    ,unit
    ,context
    ,source
    ,sourcefile
    ,pending_restart
from pg_settings
where sourcefile ~ '.*postgresql.auto.conf'
;

OUTPUT

       name         | setting | unit |      context      |       source       |                  sourcefile                    | pending_restart
--------------------+---------+------+-------------------+--------------------+------------------------------------------------+-----------------
 log_connections    | on      |      | superuser-backend | configuration file | /data/postgresql/9.6/main/postgresql.auto.conf | f
 log_disconnections | on      |      | superuser-backend | configuration file | /data/postgresql/9.6/main/postgresql.auto.conf | f
(2 rows)

Finally, let's reset the values back to their default. 

-- Reset the variables
ALTER SYSTEM RESET log_connections;
ALTER SYSTEM RESET log_disconnections;

-- Apply the changes
select pg_reload_conf();
\c