Skip to main content
Version: Latest

SPI Configuration

This document contains specifics for the a9s PostgreSQL SPI.

General information about the a9s SPIs are described in the a9s SPI documentation.

Archive Timeout​

a9s PostgreSQL supports continuous archiving, making it possible to enable or disable this backup approach via custom parameters. This means that the user can configure the value for archive_timeout during instance creation.

It is possible for the operator to specify the default value for archive_timeout, as well as minimum and maximum possible values for this property. These properties are described below.

Properties​

1. Default Archive Timeout

Configurable through the property postgresql-spi.continuous_archiving.default_archive_timeout, receives an integer for the default timeout in seconds. Default value is 1h (3600s).

2. Minimum Archive Timeout

Configurable through the property postgresql-spi.continuous_archiving.min_archive_timeout, receives an integer bigger than 0 for the minimum possible value a user can configure archive_timeout during instance creation. It is always possible to set the archive timeout to 0, which means no timeout. Default value is 10 minutes (600s).

3. Maximum Archive Timeout

Configurable through the property postgresql-spi.continuous_archiving.max_archive_timeout, receives an integer bigger than 0 for the maximum possible value a user can configure archive_timeout during instance creation. Default value is 2h (7200s).

Role Privileges​

a9s PostgreSQL supports role privileges configuration, making it possible for a user to specify which privileges the user has by default or on a per credentials basis. However, privileges must be permitted according to the Platform Operator's decision.

Possible configurable privilege values are CREATEROLE and REPLICATION. The operator must keep in mind that permitting and enabling privileges might expose the service, cause data loss, and even impact the service replication. There is a small and not exhaustive list of considerations below. Before permitting any privilege, read the official PostgreSQL documentation:

  • CREATEROLE: Gives the user permission to create, delete, and alter the attributes of a role. NOCREATEROLE is the default. A role with this privilege can create new users with a weak password, therefore exposing the service. It is also able to create new roles with different privileges (except SUPERUSER). For example, a user could create another user with REPLICATION privileges. This privilege must not be used to create other users and roles; the Application Developer must use cf service-bind/create-service-key instead.
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.

By default, this feature is enabled in a9s PostgreSQL. You can configure the reserved roles using the postgresql-ha.supautils.supautils_reserved_membership_roles property.

Note that in a9s-pg, this feature is disabled by default.

  • REPLICATION: Gives the role permission to create and drop replication slots and connect to the service in replication mode. NOREPLICATION is the default. Misusing a user with this privilege can cause problems to replication, continuous archiving, and internal storage usage, which might cause a9s Parachute to stop the service.

Properties​

1. Default Privileges

Configurable through the property postgresql-spi.service_instance.privileges.default, it receives an array of possible permitted roles. For example:

postgresql-spi:
  service_instance:
    privileges:
      default:
      - CREATEROLE

The specified default value is validated according to the permitted privileges when creating the instance.

Default is empty.

2. Permitted Privileges

Configurable through the property postgresql-spi.service_instance.privileges.permitted, it receives an array of permitted privileges. A user can only configure the role privilege if it is permitted under this section. For example:

postgresql-spi:
  service_instance:
    privileges:
      permitted:
      - CREATEROLE

By default, no privilege configuration is permitted.

Custom Parameter Configuration​

The a9s PostgreSQL SPI handles custom parameters for all Service Instances. It configures and validates custom parameters.

The a9s PostgreSQL SPI defines default values for certain custom parameters. Also, it defines whether a custom parameter can be set by the application developer.

The Platform Operator can overwrite those choices by changing the a9s PostgreSQL SPI configuration. The Platform Operator has to configure the property postgresql-spi.service_instance.custom_param_config. The property value must be a hash. Every key in that hash stands for a custom parameter and the value for every key contains the configuration for that particular parameter. The configuration for a particular param is a hash consisting of the following optional keys: default_value and modifiable. The key default_value specifies the default value for that parameter and modifiable specifies whether the Application Developer is allowed to set that custom parameter. For example:

postgresql-spi:
  service_instance:
    custom_param_config:
      metric_pg_database_size:
        default_value: 600
        modifiable: false

Default Custom Parameter Configuration​

The following table contains custom parameters that are configurable by the Platform Operator. The meaning of those custom parameters can be read in the Application Developer documentation for a9s PostgreSQL.

NameDefault valueIs modifiable
log_min_duration_statement-1true
metric_pg_database_size300true
metric_pg_replication10true
metric_pg_stat_all_indexes0true
metric_pg_stat_all_tables0true
metric_pg_statio_all_indexes0true
metric_pg_statio_all_tables0true
metric_pg_stat_archiver_table0true
metric_pg_stat_database0true
metric_pg_stat_database_conflicts0true
ssl_ciphersHIGH:MEDIUM:+3DES:!aNULLtrue
ssl_min_protocol_versionTLSv1true
wal_level_logicalfalsetrue
wal_writer_delay200true
max_connections128true
max_replication_slots0true
max_wal_senders0true
synchronous_commitontrue
temp_file_limit-1true
continuous_archivingdisabledtrue
max_standby_archive_delay30000false
max_standby_streaming_delay30000false
wal_receiver_timeout60000false
autovacuum_analyze_scale_factor0.1false
autovacuum_max_workers3false
autovacuum_vacuum_cost_delay20false
autovacuum_vacuum_insert_scale_factor0.2false
autovacuum_vacuum_scale_factor0.2false
autovacuum_work_mem-1false
checkpoint_completion_target0.5false
checkpoint_timeout300false
default_statistics_target100false
log_autovacuum_min_duration-1false
log_checkpointsofffalse
log_lock_waitsofffalse
max_wal_size1GBfalse
min_wal_size80MBfalse
vacuum_cost_limit300false

log_min_duration_statement​

The parameter log_min_duration_statement corresponds to the PostgreSQL configuration parameter log_min_duration_statement.

WARNINGS:

  • Allowing the end user to set this value not equal to -1 can result in filling up the logs rather quickly. This disk might run out of disk space.
  • Confidential data could be logged.
  • The Application Developer does not have access to the log files directly, but only via the custom parameter syslog.

ssl_ciphers​

The parameter ssl_ciphers corresponds to the PostgreSQL configuration parameter ssl_ciphers.

a9s PostgreSQL SPI does no validation for the ssl_ciphers value except that it must be of type string or null (use default value).

ssl_min_protocol_version​

The parameter ssl_min_protocol_version corresponds to the PostgreSQL configuration parameter ssl_min_protocol_version. Valid values are currently: TLSv1, TLSv1.1, TLSv1.2, TLSv1.3.

The ssl_min_protocol_version has currently no effect. PostgreSQL > 11 introduces this option to set the minimum SSL/TLS protocol version to use.

wal_level_logical​

The parameter wal_level_logical sets the PostgreSQL configuration parameter wal_level to the value logical. The default value depends on the topology of the instance. To know more about this, check the wal_level_logical Custom Parameter documentation.

Valid values are currently the boolean values: true, false.

wal_writer_delay​

The parameter wal_writer_delay sets the PostgreSQL configuration parameter wal_writer_delay to the configured number.

Valid values are integers between 1 and 10_000.

max_connections​

Important Note

Please carefully read the Sync Property Update Feature section before updating this value, as it may have an impact.

The parameter max_connections sets the PostgreSQL configuration parameter max_connections to the configured number.

There are some important details about the max_connections. To know more about this, check the max_connections Custom Parameter documentation.

The default value for most service plans is 128.

It is recommended to use a different approach for a9s-pg and configure this value manually, based on the usage of the clients. To configure this parameter, use the iaas.a9s_pg.max_connections property in the IaaS configuration file.

note

It makes sense to configure this parameter per plan.

There is no silver bullet to calculate max_connections. The optimum value for this property depends on the configured value for work_mem and the total available memory as each sort operation allocates the specified work_mem. It is important to notice that it is possible that multiple sort operations can happen during a query, so each query can use an undefined amount of memory.

A reasonable value to assume is that in average, each connection uses 3 sort operations.

max_connections = (RAM - shared_buffers - memory left to the operating system) / (work_mem * 3)

By default, a9s PostgreSQL configures shared_buffers with 25% of the RAM and the default work_mem is 8MB. It is also recommended to leave around 512MB for other processes. For example, for a 4GB RAM VM:

round((4096 - 1024 - 512) / ( 8 * 3)) = 107

max_replication_slots​

The parameter max_replication_slots sets the PostgreSQL configuration parameter max_replication_slots to the configured number.

note

This value will be added to the fixed number required for proper cluster replication. For more information, see the Custom Parameter - max_replication_slots page.

Valid values are any integer bigger than 0.

max_wal_senders​

Important Note

Please carefully read the Sync Property Update Feature section before updating this value, as it may have an impact.

The parameter max_wal_senders sets the PostgreSQL configuration parameter max_wal_senders to the configured number.

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.

ValueMeaning
nullReset custom value and use the default value.
integer bigger or equal than 0Sets 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.

synchronous_commit​

The parameter synchronous_commit corresponds to the PostgreSQL configuration parameter synchronous_commit.

Specifies how much WAL processing must complete before the database server returns a “success” indication to the client.

Valid values are on, remote_write, remote_write, local and off.

temp_file_limit​

The parameter temp_file_limit corresponds to the PostgreSQL configuration parameter 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.

Valid value are any integer or a memory size.

continuous_archiving​

The parameter continuous_archiving enables or disables Continuous Archiving and PITR backup approach for PostgreSQL.

If this parameter is disabled, a9s PostgreSQL uses the normal logical backups approach.

Valid values are enabled or disabled.

max_standby_archive_delay​

The parameter max_standby_archive_delay corresponds to the PostgreSQL configuration parameter max_standby_archive_delay.

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.

Valid values are -1, 0, or any positive integer. A value that is too high may allow replication lag on replicas and/or data usage on the primary server.

max_standby_streaming_delay​

The parameter max_standby_streaming_delay corresponds to the PostgreSQL configuration parameter max_standby_streaming_delay.

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.

Valid values are -1, 0, or any positive integer. A value that is too high may allow replication lag on replicas and/or data usage on the primary server.

wal_receiver_timeout​

The parameter wal_receiver_timeout corresponds to the PostgreSQL configuration parameter wal_receiver_timeout.

It determines how long an inactive replication connection is kept before being terminated.

Valid values are 0 or any positive integer. A value that is too high may impact replica resources (CPU or memory), as it waits for a longer period to receive WAL files.

autovacuum_analyze_scale_factor​

The parameter autovacuum_analyze_scale_factor sets the PostgreSQL configuration parameter autovacuum_analyze_scale_factor to the configured value.

It specifies a fraction of the table size to add to autovacuum_analyze_threshold when deciding whether to trigger an ANALYZE.

Its type is number and valid values are between 0.0 and 100.0.

autovacuum_max_workers​

The parameter autovacuum_max_workers sets the PostgreSQL configuration parameter autovacuum_max_workers to the configured number.

It specifies the maximum number of autovacuum worker processes that may run at the same time.

Its type is integer and valid values are between 1 and 262143.

autovacuum_vacuum_cost_delay​

The parameter autovacuum_vacuum_cost_delay sets the PostgreSQL configuration parameter autovacuum_vacuum_cost_delay to the configured value, in milliseconds.

It specifies the cost delay value used in automatic VACUUM operations.

Its type is number and valid values are -1 (use vacuum_cost_delay) or a value between 0.0 and 100.0.

autovacuum_vacuum_insert_scale_factor​

The parameter autovacuum_vacuum_insert_scale_factor sets the PostgreSQL configuration parameter autovacuum_vacuum_insert_scale_factor to the configured value.

It specifies a fraction of the table size to add to autovacuum_vacuum_insert_threshold when deciding whether to trigger a VACUUM.

Its type is number and valid values are between 0.0 and 100.0.

autovacuum_vacuum_scale_factor​

The parameter autovacuum_vacuum_scale_factor sets the PostgreSQL configuration parameter autovacuum_vacuum_scale_factor to the configured value.

It specifies a fraction of the table size to add to autovacuum_vacuum_threshold when deciding whether to trigger a VACUUM.

Its type is number and valid values are between 0.0 and 100.0.

autovacuum_work_mem​

The parameter autovacuum_work_mem sets the PostgreSQL configuration parameter autovacuum_work_mem to the configured value.

It specifies the maximum amount of memory to be used by each autovacuum worker process.

Its type is a memory size and valid values are -1 (fall back to maintenance_work_mem) or a memory size.

checkpoint_completion_target​

The parameter checkpoint_completion_target sets the PostgreSQL configuration parameter checkpoint_completion_target to the configured value.

It specifies the target for checkpoint completion as a fraction of the total time between checkpoints.

Its type is number and valid values are between 0.0 and 1.0.

checkpoint_timeout​

The parameter checkpoint_timeout sets the PostgreSQL configuration parameter checkpoint_timeout to the configured number of seconds.

It sets the maximum time between automatic WAL checkpoints.

Its type is integer and valid values are between 30 and 86400.

default_statistics_target​

The parameter default_statistics_target sets the PostgreSQL configuration parameter default_statistics_target to the configured value.

It sets the default statistics target for table columns that do not have a column-specific statistics target.

Its type is integer and valid values are between 1 and 10000.

log_autovacuum_min_duration​

The parameter log_autovacuum_min_duration sets the PostgreSQL configuration parameter log_autovacuum_min_duration to the configured number of milliseconds.

It causes each action executed by autovacuum to be logged if it ran for at least the configured duration.

Its type is integer and valid values are -1 (disabled) or a value between 0 and 2147483647.

log_checkpoints​

The parameter log_checkpoints sets the PostgreSQL configuration parameter log_checkpoints to the configured value.

It controls whether checkpoints and restartpoints are logged in the server log.

Its type is a string and valid values are on and off.

log_lock_waits​

The parameter log_lock_waits sets the PostgreSQL configuration parameter log_lock_waits to the configured value.

It controls whether a log message is produced when a session waits longer than deadlock_timeout to acquire a lock.

Its type is a string and valid values are on and off.

max_wal_size​

The parameter max_wal_size sets the PostgreSQL configuration parameter max_wal_size to the configured value.

It sets the maximum size to let the WAL grow during automatic checkpoints.

Its type is a memory size.

min_wal_size​

The parameter min_wal_size sets the PostgreSQL configuration parameter min_wal_size to the configured value.

It controls how much WAL can remain available for recycling at future checkpoints before old WAL files are removed.

Its type is a memory size.

vacuum_cost_limit​

The parameter vacuum_cost_limit sets the PostgreSQL configuration parameter vacuum_cost_limit to the configured value.

It sets the accumulated cost that causes the vacuuming process to sleep.

Its type is integer and valid values are between 1 and 10000.

Example Configuration​

Consider the following example.

The default value for the metric_pg_replication custom parameter should be 600. The custom parameter metric_pg_database_size should also have a default value of 600 and the Application Developer should not be allowed to change it.

Then the configuration must look the following way:

postgresql-spi:
  service_instance:
    custom_param_config:
      metric_pg_replication:
        default_value: 600
        modifiable: true
      metric_pg_database_size:
        default_value: 600
        modifiable: false

Per Plan Configuration​

See the Service Plans documentation to configure the custom parameters per plan.

Notes​

Additional custom parameters will be made available over time. Not all custom parameters that the SPI knows about are made available to the Platform Operator from the beginning.

During the SPI startup, the configuration the Platform Operator made will be checked. When it is invalid, the SPI will not start up.

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.