a9s PostgreSQL SPI Configuration
This document describes a9s PostgreSQL SPI configuration.
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. We describe these
properties 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 mean 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 CREATEDB
, CREATEROLE
, and REPLICATION
. The
operator must keep in mind that permitting and enabling privileges might expose the service,
cause data loss, and even impact on the service replication. There is a small and not exhaustive
list of considerations below. Before permitting any privilege, read the
official PostgreSQL documentation:
CREATEDB
: Gives the user permission to create and drop new databases.NOCREATEDB
is the default. A user with this privilege can cause data loss and even impact on the automation of your cluster. For example, deleting thepostgres
database can cause backup and restore to fail, and deleting the current default database causes the user to lose the data and not being able to access the instance. If this is permitted and it is used to creating databases, the owner must ensure that the owner of the database iscfuser
(ALTER DATABASE <dbname> OWNER TO cfuser;
) and that all objects in all databases must belong tocfuser
.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 (exceptSUPERUSER
). For example, a user could create another user withCREATEDB
privileges. This privilege must not be used to create other users and roles, the application developer must usecf service-bind/create-service-key
instead.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
- CREATEDB
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
- CREATEDB
By default, no privilege configuration is permitted.
Custom Parameter Configuration
The a9s PostgreSQL SPI handles custom parameters for all service instances. It does configuration and validation of 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.
Name | Default value | Is modifiable |
---|---|---|
log_min_duration_statement | -1 | true |
metric_pg_database_size | 300 | true |
metric_pg_replication | 10 | true |
metric_pg_stat_all_indexes | 0 | true |
metric_pg_stat_all_tables | 0 | true |
metric_pg_statio_all_indexes | 0 | true |
metric_pg_statio_all_tables | 0 | true |
metric_pg_stat_archiver_table | 0 | true |
metric_pg_stat_database | 0 | true |
metric_pg_stat_database_conflicts | 0 | true |
ssl_ciphers | HIGH:MEDIUM:+3DES:!aNULL | true |
ssl_min_protocol_version | TLSv1 | true |
wal_level_logical | false | true |
wal_writer_delay | 200 | true |
max_connections | 128 | true |
max_replication_slots | 0 | true |
max_wal_senders | 0 | true |
synchronous_commit | on | true |
temp_file_limit | -1 | true |
continuous_archiving | disabled | true |
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 if disk space.
- Confidential data could be logged.
- Application developer do 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
The parameter max_connections
sets the PostgreSQL configuration parameter
max_connections
to the configured number.
You cannot decrease max_connections
as this will break clustering.
The default value for most service plans is 128
.
We recommend 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 on your IaaS File.
IMPORTANT: 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.
We also recommend leaving around 512MB for other process, this way for a 4GB RAM VM we have:
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 that this value will be added to necessary number for proper cluster replication. To know more about this, check the max_replication_slots Custom Parameter documentation.
Valid values are any integer
bigger than 0
.
max_wal_senders
The parameter max_wal_senders
sets the PostgreSQL configuration parameter
max_wal_senders
to the configured number.
Note that this value will be added to necessary number for proper cluster replication. To know more about this, check the max_wal_senders Custom Parameter documentation.
Valid values are any integer
bigger than 0
.
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
.
Example Configuration
Let's build an imaginary 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
Take a look at Service Plans documentation if you want to configure the custom parameters per plan.
Notes
Additional custom parameters will be made available over time. We do not make all available custom parameters the SPIs knows about available to the platform operator from the beginning.
During the SPI startup, the configuration the platform operator made will be checked. When it's invalid, the SPI will not start up.
postgresql-spi.protocols
For more details on this property, please refer to the a9s Framework TLS configuration documentation.