Custom Parameters
As mentioned in Getting Started - Custom Parameters as an end user, an Application Developer has the opportunity to customize their Service Instance by using custom parameters.
data_checksums
Use checksums on data pages to help detect corruption by the I/O system that would otherwise be silent. This option can only be set during creation of the Service Instance, and cannot be changed later. Enabling checksums may incur a noticeable performance penalty.
The default value is enabled.
The allowed values are enabled and disabled.
max_connections
Please carefully read the Sync Property Update Feature section before updating this value, as it may have an impact.
Determines the maximum number of concurrent connections to the database server.
You cannot decrease the value of max_connections in the a9s PostgreSQL 13 as this will break clustering.
If max_connections is not explicitly specified, a default value will be used based on the service plan.
This default value is 128 for most service plans.
The Platform Operator should specify this parameter for each service plan.
This setup will be enough for most cases but if a different value is needed for a Service Instance, you can configure it with:
cf create-service <service> <plan> <service-instance-name> -c '{"max_connections": <max_connections_value>}'
When creating a service.
When updating an existing Service Instance, you can use the following:
cf update-service mypg -c '{"max_connections": <max_connections_value>}'
Once you set the value for max_connections, you cannot reset it to the default value using the null value.
The max_connections of an existing cluster deployment can not be updated to
a smaller value than the current one due to the fact that a standby node
can not connect to a primary that has a greater value for this property.
effective_cache_size
Please note, that our BOSH release includes an algorithm which calculates a usable value for this property on the fly based on the resources of the VM running.
The property effective_cache_size can be specified (e.g. 2GB) or be
omitted. If effective_cache_size is not set, it's value is 75% of the total memory.
If the total memory is less than 4GB, it will use 50% of the total memory
with a floor of 256MB. When specifying this value, make sure you set a value that
leads to a configuration that is higher than shared_buffers because shared buffers
are included in the PostgreSQL query execution planner.
An integer value greater than 0 is expected. The units of the property are MB
and no unit should be appended to the value. For example, to set 1GB of effective_cache_size
the parameter value should be 1024, and for 512MB the value would be 512.
shared_buffers
Sets the amount of memory the database server uses for shared memory buffers.
The property shared_buffers can be specified (e.g. 2048 or "2048MB" or "2GB") or be omitted.
If shared_buffers is not set, its value is 25% of the total memory if the total memory is greater than 1GB.
If the total memory is less than 1GB, the shared_buffers are 15% of the total memory.
It floors at 128MB.
To specify the shared_buffers, It can be set it in two distinct formats integer or string.
When is integer format, an integer value greater than 0 is expected. The units of the property are MB
and no unit should be appended to the value. For example, to set 1GB of shared_buffers
the parameter value should be 1024, and for 512MB the value would be 512.
When is string format, an string value with number greater than 0 plus unit(MB or GB) is expected.
For example, to set 1GB of shared_buffers the parameter value should be "1024MB" or "1GB",
and for 512MB the value would be "512MB".
When updating an existing Service Instance, you can use the following:
Commands
cf update-service mypg -c '{"shared_buffers": 1024}'
# or
cf update-service mypg -c '{"shared_buffers": "1024MB"}'
# or
cf update-service mypg -c '{"shared_buffers": "1GB"}'
work_mem
The default unit is kilobytes but you can also specify MB or GB. The
default value is 8 MB. For example, to set 1 MB of work_mem the
parameter value can be 1024 or 1 MB.
maintenance_work_mem
Specifies the maximum amount of memory to be used by maintenance operations, such as VACUUM, CREATE INDEX,
and ALTER TABLE ADD FOREIGN KEY. See PostgreSQL Documentation
The default unit is kilobytes but you can also specify MB or GB. The
default value is 64 MB. For example, to set 1 MB of maintenance_work_mem the
parameter value can be 1024 or 1 MB.
Note that when autovacuum runs, up to autovacuum_max_workers times of this memory may be allocated.
Therefore, you need to be careful not to set the default value too high.
Due to the default value of three, the memory could then be allocated three times simultaneously.
temp_file_limit
An integer value greater than 0 is expected. Alternatively -1 means no limit. The default value is -1.
track_io_timing
Enables timing of database I/O calls.
By default this parameter is off.
WARNING: When it is on it will repeatedly query the operating system for the current time, which may cause significant overhead on some platforms.
archive_timeout
Enforces the server to switch to a new WAL segment file periodically.
By default the value is 1h.
The following values are allowed:
| Value | Meaning |
|---|---|
null | Reset custom value and use default one, so 1h |
0 | Disables archive_timeout |
ìnteger bigger than 59 seconds | Sets archive_timeout to the provided value |
statement_timeout
The default value is 0 (off).
Only integer values bigger or equal 0 are allowed.
idle_in_transaction_session_timeout
The default value is 0 (disabled).
Only integer values bigger or equal 0 are allowed.
role_privileges
Configures default privileges for the credentials.
By default, it is not possible to specify a value for this parameter. However, default and permitted values are configured on the SPI by the Platform Operator. Check with him which privileges are available.
Possible configurable values are:
| Privilege | Meaning | Default value |
|---|---|---|
CREATEDB | Gives the user permission to create and drop new databases. | NOCREATEDB |
CREATEROLE | Gives the user permission to create, delete, and alter the attributes of a role. | NOCREATEROLE |
REPLICATION | Gives the role permission to create and drop replication slots and connect to the service in replication mode. | NOREPLICATION |
This parameter receives one of these values in the following format:
| Value | Type | Meaning |
|---|---|---|
null | - | Reset custom values to the default specified by the Platform Operator. |
[] | array | Removes all privileges. |
['CREATEDB', 'CREATEROLE', 'REPLICATION'] | array | The default and permitted values are defined by the Platform Operator. Any privilege not specified is disabled. |
This parameter can be specified during instance creation (cf create-service and
cf update-service) and credentials creation (cf bind-service and cf create-service-key).
When specifying during instance creation, the privileges are applied to the cfuser role. All other
users inherit this role by default. For example:
cf create-service a9s-postgresql postgresql-single-small my-postgresql-service \
-c '{ "role_privileges": ["CREATEROLE"]}'
If specified during credentials creation, the privileges are applied to the created user only and
are enabled when using the user role instead of the cfuser role.
cf create-service-key my-postgresql-service \
-c '{ "role_privileges": ["CREATEROLE"]}'
You must ensure that for every new database the owner is the cfuser role
(ALTER DATABASE <dbname> OWNER TO cfuser;) and all objects stored in all databases belongs
to the cfuser.
The CREATEROLE privilege automation was designed to grant a user the ability to use
ALTER ROLE to change default
values for run-time parameters for a user using SET.
New users must be created using the instructions above.
Be careful when configuring privileges in your Service Instances. CREATEROLE, for example, gives a user permission to
create new users with a weak password, avoid doing this in favor of using the cf CLI. CREATEDB gives permission also
to delete databases, and misusing REPLICATION can cause issues with the storage space, continuous archiving, and the
node replication of your cluster.
For security reasons, starting with anynines-deployment v56.0.0, certain roles in a9s PostgreSQL 13 or greater are reserved exclusively for the admin user, even if the CREATEROLE permission is enabled. Please contact the Platform Operator for more details on these restrictions.
To know more about the a9s PostgreSQL user configuration, see the Internal Workings of a9s PostgreSQL section.
wal_level_logical
This parameter receives a boolean, where true configures the wal_level as logical and false
fallback to the default configuration.
The default value is false, in this case, the wal_level is configured as minimal for single
instances, and replica for the clusters. For any case, if the instance has
continuous archiving the wal_level is configured as replica.
The allowed values are true and false.
wal_writer_delay
Configures how often the WAL writer flushes WAL.
This parameter receives an integer between 1 and 10000.
It is taken as milliseconds.
The default is 200.
| Value | Meaning |
|---|---|
null | Reset custom value and use the default value. |
ìnteger between 1 and 10_000 | Sets wal_writer_delay to the given number. |
We do not allow to set the custom parameter with a value that includes the unit ms. We expect just an integer
(in milliseconds).
max_replication_slots
Configures the maximum amount of replication slots.
This parameter receives an integer bigger or equal than 0.
The default value for the max_replication_slots custom parameter is 0.
The maximum number of replication slots in the a9s PostgreSQL Service Instance depend on whether the Service Instance is a single or a replica Service Instance. The formula is as follows:
- Single Service Instances: The PostgreSQL maximum number of replication slots will be set to the value of the
max_replication_slots. - Replica Service Instances (High Availability): The PostgreSQL maximum number of replication slots will be the
value of the
max_replication_slotsplus two. These additional two replication slots are automatically added by the underlying automation, and are reserved for the two standby nodes in order to ensure high availability.
Examples:
When max_replication_slots = 0
- Single Instance: Maximum number of replication slots =
0 - Replica Instance: Maximum number of replication slots =
0 + 2=2
When max_replication_slots = 2
- Single Instance: Maximum number of replication slots =
2 - Replica Instance: Maximum number of replication slots =
2 + 2=4
| Value | Meaning |
|---|---|
null | Reset custom value and use the default value. |
Integer bigger or equal than 0 | Sets max_replication_slots to the given value, plus the number of standby nodes. |
max_replication_slotsUpdating this property to a value lower than the current number of replication slots is not possible, and any attempts to do so will fail.
max_wal_senders
Please carefully read the Sync Property Update Feature section before updating this value, as it may have an impact.
Configures the maximum amount of WAL sender fore each node of the Service Instance.
This parameter receives an integer bigger or equal than 0.
The default is 0 for single Service Instances. For cluster Service Instances, the default is the number of standby
nodes times 4. For any case, if the instance has continuous archiving
enabled 4 more are added to the WAL senders.
| Value | Meaning |
|---|---|
null | Reset custom value and use the default value. |
integer bigger or equal than 0 | Sets max_wal_senders to the given number, plus the number of WAL senders needed by the cluster |
When running a cluster Service Instance, the necessary number of WAL senders to ensure correct replication and backup is added to the value given as parameter.
You cannot decrease the value of max_wal_senders in a9s PostgreSQL 13, as this will break clustering.
The default wal_level for the single instance is minimal, in this case, max_wal_senders can not be configured, as
PostgreSQL fails to start. To change that, you can use wal_level_logical or enable continuous
archiving.
synchronous_commit
Configures synchronous_commit.
This value specifies how much WAL processing must complete before the database server returns a 'success' indication to the client.
The default value is on. Possible configurable values are on, off, local,
remote_write, and remote_apply.
On a9s PostgreSQL instances the synchronous_standby_names is always empty .
max_standby_archive_delay
Configures max_standby_archive_delay.
The Platform Operator must allow this parameter to be configured. It controls how long the standby server waits
(when WAL data is being read from the WAL archive) before canceling conflicting standby queries when applying WAL entries.
An integer value greater than 0 is expected. The unit is in milliseconds. Alternatively, -1 means no limit.
The default value is 30000 (30 seconds).
A value that is too high may allow replication lag on replicas and/or data usage on the primary server, increasing the occupied disk usage, which can cause a9s Parachute to be triggered.
max_standby_streaming_delay
Configures max_standby_streaming_delay.
The Platform Operator must allow this parameter to be configured. It controls how long the standby server waits
(when WAL data is being received via streaming replication) before canceling conflicting standby queries when applying
WAL entries. An integer value greater than 0 is expected. The unit is in milliseconds. Alternatively, -1 means
no limit. The default value is 30000 (30 seconds).
A value that is too high may allow replication lag on replicas and/or data usage on the primary server, increasing the occupied disk usage, which can cause a9s Parachute to be triggered.
wal_receiver_timeout
Configures wal_receiver_timeout.
The Platform Operator should specify this parameter. It controls when to terminate replication connections that are
inactive for longer than this amount of time. This is useful for the receiving standby server to detect a primary node
crash or network outage. An integer value greater than 0 is expected.
A value that is too high may impact the replica resources (CPU or memory) as PostgreSQL waits longer to receive the WAL files.
Logging parameters
Logging in a9s PostgreSQL is reduced to a minimum by default. This is, to give most of the performance and especially diskspace, to the customer. Additionally it reduces the problems that can appear if queries are failing for any reasons.
The parameters that can be configured are the following:
client_min_messages
Controls which message levels are sent to the client. Valid values (in order of details) are:
| Log-Level | Verbosity |
|---|---|
DEBUG5 | Most |
DEBUG4 | |
DEBUG3 | |
DEBUG2 | |
DEBUG1 | |
LOG | |
NOTICE | default for a9s PostgreSQL |
WARNING | |
ERROR | |
FATAL | |
PANIC | Least |
pg_log_min_messages
Controls which message levels are written to the server log. Valid values are DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, INFO, NOTICE, WARNING, ERROR, LOG, FATAL and PANIC. Each level includes all the levels that follow it. The later the level, the fewer messages are sent to the log.
The default for a9s PostgreSQL is WARNING.
pg_log_min_error_statement
Controls which SQL statements that cause an error condition are recorded in the server log. The current SQL statement is included in the log entry for any message of the specified severity or higher. Valid values are DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, INFO, NOTICE, WARNING, ERROR, LOG, FATAL and PANIC.
The default for a9s PostgreSQL is ERROR, which means statements causing errors, log messages, fatal errors, or panics will be logged. To effectively turn off logging of failing statements, set this parameter to PANIC.
repmgr_loglevel
Log level: possible values are DEBUG, INFO, NOTICE, WARNING, ERR, ALERT, CRIT or EMERG.
The default for a9s PostgreSQL is NOTICE.
loglevel
Configures the overall loglevel for the control scripts and components from anynines. Valid levels are INFO or DEBUG.
The default for a9s PostgreSQL is DEBUG.
temp_files
Controls logging of temporary file names and sizes. Temporary files can be created for sorts, hashes, and temporary query results. A log entry is made for each temporary file when it is deleted. A value of zero logs all temporary file information, while positive values log only files whose size is greater than or equal to the specified number of kilobytes. The default for a9s PostgreSQL is -1, which disables such logging.
log_statement
Controls which SQL statements are logged. Valid values are none (off), ddl, mod, and all (all statements). ddl logs all data definition statements, such as CREATE, ALTER and DROP statements. mod logs all ddl statements, plus data-modifying statements such as INSERT, UPDATE, DELETE, TRUNCATE, and COPY FROM. PREPARE, EXECUTE and EXPLAIN ANALYZE statements are also logged if their contained command is of an appropriate type. For clients using extended query protocol, logging occurs when an Execute message is received, and values of the Bind parameters are included (with any embedded single-quote marks doubled).
The default for a9s PostgreSQL is none.
log_min_duration_statement
Causes the duration of each completed statement to be logged if the statement ran for at least the specified amount of time.
An integer value greater than or equal to -1 is expected.
The value -1 disables logging statement durations.
The value 0 prints all statement durations.
The default value is -1.
log_error_verbosity
Controls the amount of detail written in the server log for each message that is logged. Valid values are TERSE,
DEFAULT and VERBOSE, each adding more fields to displayed messages. TERSE excludes the logging of DETAIL,
HINT, QUERY, and CONTEXT error information. VERBOSE output includes the SQLSTATE error code and the source
code file name, function name, and line number that generated the error.
The default for a9s PostgreSQL is TERSE.
More information about logging can be found in the Error Reporting and Logging section of the PostgreSQL Documentation.
Common Considerations
Sync Property Update Feature
Read carefully before updating the parameters.
This feature manages the synchronization, in cluster Service Instances, for the following custom parameters:
max_connectionsmax_wal_senders
When updating these parameters in an a9s PostgreSQL cluster Service Instance, a synchronization process is triggered by the Sync Property Update feature.
This process updates the nodes in a specific order, which varies depending on whether the parameter's value is increased or decreased.
This feature is necessary because PostgreSQL requires the primary node to always have values for max_connections and
max_wal_senders that are equal to or greater than those on the standby nodes. Without this feature, if the nodes are
updated in the wrong order, the update process could fail or cause unexpected problems.
This functionality is supported for a9s PostgreSQL 15 and later.
In case you need to retrieve the parameters' current values, see the Custom Parameters documentation.
Please, check the important notes addressed below:
Please, consider the following:
- Starting from version
v68.0.0, this process is not executed during the first update, as all nodes must first be updated to the same version of the internal a9s PostgreSQL components, to ensure correct functionality. - The feature synchronization process will be skipped when the cluster is not healthy, in order to avoid worsening
an already degraded state. When the process is skipped, the system behaves inconsistently, which may result in the
following:
- Downtime, when increasing the parameter value.
- Update failure, when decreasing the parameter value, requiring manual intervention.
- Decreasing parameter values will result in unavoidable downtime. This occurs because, during the synchronization process, the primary node and certain standby nodes must be restarted. The cluster will remain unavailable until the primary and the affected standby nodes are fully back online and the cluster has regained quorum.
- If the update fails while using this feature:
- If the cluster is healthy, try running the update again with the same parameters. The synchronization should complete successfully.
- If the cluster is not healthy, contact your Platform Operator and provide details about the issue.