postgresql.conf#

TPA creates a conf.d directory with various .conf files under it, and uses include_dir in the main postgresql.conf to use these additional configuration files.

The Postgres configuration files (postgresql.conf, pg_ident.conf, and pg_hba.conf) and the included files under conf.d are always stored in postgres_conf_dir . This is the same as postgres_data_dir by default, but you can set it to a different location if you wish to keep the configuration separate from the data directory.

The main configuration mechanism is to set variables directly:

cluster_vars:
  temp_buffers: 16MB
  log_connections: on
  autovacuum_vacuum_cost_limit: -1
  effective_cache_size: 4GB
  max_connections: 300
  max_wal_senders: 32

TPA splits the configuration up into multiple files. The two main files are 0000-tpa.conf and 0001-tpa_restart.conf . These contain settings that require a server reload or restart to change, respectively. During deployment, TPA will write any changes to the correct file and reload or restart Postgres as required.

TPA may use other files in certain circumstances (e.g., to configure optional extensions), but you do not ordinarily need to care where exactly a given parameter is set.

You should never edit any of the files under conf.d , because the changes may be overwritten when you next run tpaexec deploy .

postgres_port#

The Postgres port can be overwritten from its default value to another valid port number.

cluster_vars:
  postgres_port: 5433

This method is preferred over setting the port via postgres_conf_settings , since postgres_port ensures that the value is correctly shared and used across all components relying on this value to interact with the postgres cluster.

postgres_conf_settings#

TPA provides variables like temp_buffers and maintenance_work_mem that you can set directly for many, but not all, available postgresql.conf settings.

You can use postgres_conf_settings to set any parameters, whether recognised by TPA or not. You need to quote the value exactly as it would appear in postgresql.conf :

cluster_vars:
  effective_cache_size: 2GB
  postgres_conf_settings:
    effective_cache_size: 4GB
    authentication_timeout: 1min
    synchronous_standby_names: >-
      any 2 ("first", "second", "third")
    bdr.global_lock_statement_timeout: 60s

This is most useful with settings that TPA does not recognise natively, but you can use it for any parameter (e.g., effective_cache_size can be set as a variable, but authentication_timeout cannot).

These settings will be written to conf.d/9900-role-settings.conf , and therefore take priority over variables set in any other way.

If you make changes to values under postgres_conf_settings , TPA has no way to know whether the a reload is sufficient to effect the changes, or if a restart is required. Therefore it will always restart the server to activate the changes. This is why it’s always preferable to use variables directly whenever possible.

shared_buffers#

By default, TPA will set shared_buffers to 25% of the available memory (this is just a rule of thumb, not a recommendation). You can override this default by setting shared_buffers_ratio: 0.35 to use a different proportion, or by setting shared_buffers_mb: 796 to a specific number of MB, or by specifying an exact value directly, e.g., shared_buffers: "2GB" .

effective_cache_size#

By default, TPA will set effective_cache_size to 50% of the available memory. You can override this default by setting effective_cache_size_ratio: 0.35 to use a different proportion, or by setting effective_cache_size_mb: 796 to a specific number of MB, or by specifying an exact value directly, e.g., effective_cache_size: "8GB" .

shared_preload_libraries#

TPA maintains an internal list of extensions that require entries in shared_preload_libraries to work, and if you include any such extensions in postgres_extensions , it will automatically update shared_preload_libraries for you.

If you are using unrecognised extensions that require preloading, you can add them to preload_extensions :

cluster_vars:
  preload_extensions:
  - myext
  - otherext

Now if you add myext to postgres_extensions , shared_preload_libraries will include myext .

By default, shared_preload_libraries is set in conf.d/8888-shared_preload_libraries.conf .

Setting shared_preload_libraries directly as a variable is not supported. You should not normally need to set it, but if unavoidable, you can set a fully-quoted value under postgres_conf_settings . In this case, the value is set in conf.d/9900-tpa_postgres_conf_settings.conf .

Postgres logging#

By default, TPA configures the Postgres log_destination GUC as syslog and configures rsyslog to write Postgres logs to /var/log/postgres/postgres.log .

You can change these defaults using the following cluster variables.

postgres_log_file is the path to the log file. Defaults to /var/log/postgres/postgres.log .
postgres_log_file_mode is the mode of the log file. Defaults to 0640 .
postgres_log_directory_mode is the mode of the log directory. Defaults to 0700 .
log_destination sets the Postgres GUC of the same name. Defaults to syslog .
logging_collector sets the Postgres GUC of the same name. Defaults to off if log_destination is syslog , otherwise on .

If you select a log_destination other than syslog TPA will set up the Postgres logging collector to write the logs. In all cases, TPA will take care of creating the directories and configuring log rotation.

The following example tells TPA to log to the selected location using rsyslog .

cluster_vars:
  [...]
  postgres_log_file: /srv/fantastic_logs/pg_server.log

This example, logs to the same location, but using the Postgres logging collector to write JSON logs, and permitting read and execute on the log directory for group members.

cluster_vars:
  [...]
  postgres_log_file: /srv/fantastic_logs/pg_server.log
  postgres_log_directory_mode: 0750
  log_destination: jsonlog

rather than from TPA.

If you need to access the exact path of the log file, including the final extension—for example as part of a hook—this is stored in the Ansible fact postgres_log_file_with_extension . This cannot be set directly.

By default, TPA will generate a private key and a self-signed TLS certificate which are used by Postgres as the ssl_key_file and ssl_cert_file respectively. The files are named using the TPA cluster name (cluster_name.key and cluster_name.crt ) and located in /etc/tpa , resulting in the following default configuration in 0001-tpa_restart.conf :

ssl_key_file=/etc/tpa/cluster_name.key
ssl_cert_file=/etc/tpa/cluster_name.crt

This is sufficient to ensure that traffic between clients and server is encrypted in transit.

To provide your own certificates, upload them to the target nodes as Uploading artifacts , then set the path by specifying the following cluster variables:

cluster_vars:
  ...
  artifacts:
  - type: file
    dest: /path/to/your_key.key
    src: /local/path/to/your_key.key
    owner: root
    group: root
    mode: "0644"
  - type: file
    dest: /path/to/your_cert.crt
    src: /local/path/to/your_cert.crt
    owner: root
    group: root
    mode: "0600"
  ssl_key_file: /path/to/your_key.key
  ssl_cert_file: /path/to/your_cert.crt

Alternatively, if you upload your key and certificate to the default location, TPA will use them instead of generating its own, and you do not need to specify ssl_key_file or ssl_cert_file . Note, however, that you must explicitly create /etc/tpa because it doesn’t exist at the time artifacts are uploaded. The permissions and ownership of these files will be adjusted by TPA when the postgres user is created during deployment.

cluster_vars:
  ...
  artifacts:
  - type: path
    path: /etc/tpa
    state: directory
    owner: root
    group: root
    mode: "0755"
  - type: file
    dest: /etc/tpa/cluster_name.key
    src: /local/path/to/your_key.key
    owner: root
    group: root
    mode: "0644"
  - type: file
    dest: /etc/tpa/cluster_name.crt
    src: /local/path/to/your_cert.crt
    owner: root
    group: root
    mode: "0600"

There are two ways you can override anything in the TPA-generated configuration.

The first (and recommended) option is to use ALTER SYSTEM , which always takes precedence over anything in the configuration files:

ALTER SYSTEM SET bdr.global_lock_statement_timeout TO 60s;

You can also edit conf.d/9999-override.conf :

echo "bdr.global_lock_statement_timeout=60s" >> conf.d/9999-override.conf

All other files under conf.d are subject to be overwritten during deployment if the configuration changes, but TPA will never change 9999-override.conf after initially creating the empty file.

Depending on which settings you change, you may need to execute SELECT pg_reload_conf() or restart the server for the changes to take effect.

Generating postgresql.conf from scratch#

By default, TPA will leave the default (i.e., initdb -generated) postgresql.conf file alone other than adding the include_dir . You should not ordinarily need to override this behaviour, but you can set postgres_conf_template to do so:

cluster_vars:
  postgres_conf_template: pgconf.j2

Now the templates/pgconf.j2 in your cluster directory will be used to generate postgresql.conf.

postgresql.conf

Contents