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:
Context | Applied At | Command |
internal | Server Build | NA* |
postmaster | Server Restart | Using the Datica CLI:
datica redeploy <service> |
superuser-backend | SIGHUP &Session Reconnect | Using the psql prompt:
select pg_reload_conf(); |
backend | SIGHUP &Session Reconnect | Using the psql prompt:
select pg_reload_conf(); |
sighup | SIGHUP | Using the psql prompt:
select pg_reload_conf(); |
superuser | Immediate | NA |
user | Immediate | NA |
* Unsupported in CPaaS
Example
In the following example we will enable logging for all connections and disconnections.
First, get a console session to a PostgreSQL DB running in a Datica CPaaS environment using the CLI.
datica -E '<environment_name>' console <pg_service_name>
After you're connected, check the current configuration values for logging connections.
-- 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