Custom Parameters

PostgreSQL Custom Parameters

As mentioned in Using an a9s Data Service - 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

Determines the maximum number of concurrent connections to the database server.

note

You cannot decrease max_connections 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>}'

note

Standby nodes can only connect to primary nodes when the max_connections are equal or bigger on the standby node. If you are going to increase the max_connections for the Service Instance, the standby node may differ in the max_connection configuration and will not be able to connect to the primary.

Example: We have three nodes in our Service Instance:

Node-Name	Configuration	State
pg0	max_connections = 128	Primary
pg1	max_connections = 128	Standby
pg2	max_connections = 128	Standby

While updating the max_connections to 150, the following happens:

• pg/0 is configured with max_connections = 150.
• pg/2 is still configured with max_connections = 100.
• pg/1 goes down to update

At this moment, pg/2 isn't able to join the primary because the max_connections is less than on the primary. This causes a short downtime.

If pg/1 comes up again with max_connections = 150 it is able to join pg/0 and build a quorum. At this point, the a9s PostgreSQL Database is reachable again.

An update to a smaller value than the current one is not allowed. 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 master that has a greater value for this property.

effective_cache_size

Sets the planner's assumption about the effective size of the disk cache that is available to a single query.

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

Specifies the amount of memory to be used by internal sort operations and hash tables before writing to temporary disk files.

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.

caution

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

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.

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

Abort any statement that takes more than the specified number of milliseconds, starting from the time the command arrives at the server from the client.

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

idle_in_transaction_session_timeout

Terminate any session with an open transaction that has been idle for longer than the specified duration in milliseconds.

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.

caution

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.

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.

wal_level_logical

Configures wal_level.

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.

caution

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 is 4 for single instances, and for clusters, the default is 6 for compatibility reasons.

Value	Meaning
null	Reset custom value and use the default value.
ìnteger bigger or equal than 0	Sets max_replication_slots to the given number, plus the number of standby nodes.

When running a cluster, the necessary number of replication slots necessary to ensure correct replication is added to the value given as parameter.

caution

Updating this property to a value lower than the current number of replication slots will fail.

max_wal_senders

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 instances. For clusters, 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, the necessary number of WAL senders to ensure correct replication and backup is added to the value given as parameter.

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

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.

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.

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.

caution

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.