Configuration Parameters

The following parameters allow you to customize the behavior of your a9s PostgreSQL Service Instance. These settings can be passed as custom parameters when creating or updating a Service Instance. Unless stated otherwise, parameters can be modified at any time and will take effect after the instance is updated.

archive_timeout

Enforces the server to switch to a new WAL segment file periodically. For more information, see the official PostgreSQL documentation.

By default the value is 1h. The following values are allowed:

Value	Description
null	Resets custom value and uses the default one.
0	Disables archive_timeout.
integer bigger than 59 seconds	Sets archive_timeout to the provided value.

data_checksums

Checksums, on data pages, help detect corruption by the I/O system that would otherwise be silent. For more information, see the official PostgreSQL documentation.

caution

• 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.

effective_cache_size

Sets the planner's assumption about the effective size of the disk cache that is available to a single query. For more information, see the official PostgreSQL documentation.

Please note, that the a9s PostgreSQL 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 to 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.

idle_in_transaction_session_timeout

Terminate any session with an open transaction that has been idle for longer than the specified duration (in milliseconds). For more information, see the official PostgreSQL documentation.

The default value is 0 (disabled). Only integer values bigger or equal 0 are allowed.

maintenance_work_mem

Specifies the maximum amount of memory to be used by maintenance operations, such as:

• ALTER TABLE ADD FOREIGN KEY
• CREATE INDEX
• VACUUM

For more information, see the official PostgreSQL Documentation.

The default unit is kilobytes, but MB or GB can also be specified. 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.

caution

Note that when autovacuum runs, up to autovacuum_max_workers times of this memory may be allocated. Therefore, it is important to not to set the default value too high.

Due to the default value of three, the memory could then be allocated three times simultaneously.

max_connections

Important Note

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. For more information, see the official PostgreSQL documentation.

note

The value of max_connections cannot be decreased in 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 it can be configured, when creating a service, with:

cf create-service <service> <plan> <service-instance-name> -c '{"max_connections": <max_connections_value>}'

When updating an existing Service Instance, the following command can be used:

cf update-service mypg -c '{"max_connections": <max_connections_value>}'

note

Once the value for max_connections is set, it cannot be reset to the default value using the null value.

The max_connections of an existing cluster deployment cannot be updated to a smaller value than the current one due to the fact that a standby node cannot connect to a primary that has a greater value for this property.

max_replication_slots

Configures the maximum amount of replication slots. For more information, see the official PostgreSQL documentation.

This parameter receives an integer bigger or equal than 0.

The default value for the max_replication_slots custom parameter is 0.

note

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_slots plus 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	Description
null	Removes the custom value and sets the default value.
Integer bigger or equal than 0	Sets max_replication_slots to the given value, plus the number of standby nodes.

When decreasing the max_replication_slots

Updating 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

Important Note

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 for each node of the Service Instance. For more information, see the official PostgreSQL documentation.

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.

caution

You cannot decrease the value of max_wal_senders in a9s PostgreSQL 13, as this will break clustering.

caution

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. This can be changed either by using wal_level_logical or enabling continuous archiving.

max_standby_archive_delay

Configures max_standby_archive_delay. For more information, see the official PostgreSQL documentation.

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).

caution

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. For more information, see the official PostgreSQL documentation.

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).

caution

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.

role_privileges

Configures default privileges for the credentials. For more information, see the official PostgreSQL documentation.

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.

Possible configurable values are:

Privilege	Description	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	Description
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 Service Instance creation (cf create-service and cf update-service) and credentials creation (cf bind-service and cf create-service-key).

When specifying during Service 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.

caution

Be careful when configuring privileges in the 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 the cluster.

CREATEROLE restrictions

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.

shared_buffers

Sets the amount of memory the database server uses for shared memory buffers. For more information, see the official PostgreSQL documentation.

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, the following command can be used:

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"}'

statement_timeout

Sets the time that any statement can run before being aborted. This causes any statement that takes more than the specified number of milliseconds, starting from the time the command arrives at the server from the client, to be aborted. For more information, see the official PostgreSQL documentation.

The default value is 0 (off). Only integer values bigger or equal 0 are allowed.

synchronous_commit

This value specifies how much WAL processing must complete before the database server returns a 'success' indication to the client. For more information, see the official PostgreSQL documentation.

The default value is on. Possible configurable values are:

• on
• off
• local
• remote_write
• remote_apply

On a9s PostgreSQL instances the synchronous_standby_names is always empty .

temp_file_limit

Specifies the maximum amount of disk space that a process can use for temporary files, such as sort and hash temporary files, or the storage file for a held cursor. For more information, see the official PostgreSQL Documentation.

The accepted values are integer values greater than 0 and , alternatively, -1 to define no limit.

The default value is -1.

track_io_timing

Enables timing of database I/O calls. For more information, see the official PostgreSQL Documentation.

By default this parameter is off.

caution

When it is on it will repeatedly query the operating system for the current time, which may cause significant overhead on some platforms.

wal_level_logical

This parameter accepts a boolean, where true configures the wal_level as logical and false fallback to the default configuration. For more information, see the official PostgreSQL Documentation.

The default value is false, in this case, the wal_level is configured as minimal for single Service 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_receiver_timeout

Controls when to terminate replication connections that are inactive for longer than the set amount of time. This is useful for the receiving standby server to detect a primary node crash or network outage. For more information, see the official PostgreSQL Documentation.

The allowed values are integer greater than 0.

caution

A value that is too high may impact the replica resources (CPU or memory) as PostgreSQL waits longer to receive the WAL files.

wal_writer_delay

Configures how often the WAL writer flushes WAL. For more information, see the official PostgreSQL Documentation.

This parameter receives an integer between 1 and 10000. It is taken as milliseconds.

The default is 200.

Value	Description
null	Reset custom value and use the default value.
integer between 1 and 10_000	Sets wal_writer_delay to the given number.

caution

The value of this custom parameter should be set just as an integer, in milliseconds, but without the unit ms. Thus, the value for, as an example, 500 ms would be 500.

work_mem

Specifies the amount of memory to be used by internal sort operations and hash tables before writing to temporary disk files. For more information, see the official PostgreSQL Documentation.

The default unit is kilobytes, but MB or GB can also be specified. The default value is 8 MB. For example, to set 1 MB of work_mem the parameter value can be 1024 or 1 MB.

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_connections
• max_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:

Sync Property Update Feature - Caveats

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.