ADPG configuration parameters

Elena Kostyuchenko

To configure the service, use the following configuration parameters in ADCM.

NOTE

  • Some of the parameters become visible in the ADCM UI after the Advanced flag has been set.

  • The parameters that are set in the Custom group will overwrite the existing parameters even if they are read-only.
Parameter Description Default value

Data directory

Directories that are used to store data on the ADPG hosts

/pg_data1

Custom postgresql.conf

In this section you can define values for custom parameters that are not displayed in ADCM UI, but are allowed in the configuration file postgresql.conf

 — 

Custom pg_hba.conf

In this section you can define values for custom parameters that are not displayed in ADCM UI, but are allowed in the configuration file pg_hba.conf

 
host    all             all             0.0.0.0/0               scram-sha-256
host    all             all             ::/0                    scram-sha-256
ADPG configurations
Parameter Description Default value

listen_addresses

Specifies the TCP/IP address(es) on which the server is to listen for connections from client applications (requires a restart when changed)

*

port

TCP port the server listens on

5432

max_connections

Determines the maximum number of concurrent connections to the server. For a replica host, the value of this parameter must be greater than or equal to the value on the leader host. If this requirement is not met, the replica host will reject all requests

300

shared_buffers(MB)

Sets the amount of memory for the shared memory buffer. The higher the value of this parameter, the less the load on the host hard drives will be

128MB

max_worker_processes

Sets the maximum number of background processes that the system can support

8

max_parallel_workers

Sets the maximum number of workers that the system can support for parallel operations

8

max_parallel_workers_per_gather

Sets the maximum number of workers that can be started by a single Gather or Gather Merge node

2

max_parallel_maintenance_workers

Sets the maximum number of parallel workers that can be started by a single utility command

2

effective_cache_size(MB)

Sets the planner’s assumption about the effective size of the disk cache that is available to a single query. This is taken into account when estimating the cost of using the index. A higher value makes it more likely index scans will be used, a lower value makes it more likely sequential scans will be applied. When setting this parameter, you should consider both PostgreSQL shared buffers and the portion of the kernel’s disk cache that will be used for PostgreSQL data files, though some data might exist in both places. Also, take into account the expected number of concurrent queries on different tables, since they will have to share the available space. This parameter does not affect the size of shared memory allocated by PostgreSQL, and it does not reserve kernel disk cache. It is used only for estimation purposes. The system also does not assume data remains in the disk cache between queries. If this value is specified without units, it is taken as blocks, that is BLCKSZ bytes, typically 8 KB

4096MB

maintenance_work_mem(MB)

Specifies the maximum amount of memory to be used by maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY. If this value is specified without units, it is taken as kilobytes. Since only one of these operations can be executed at a time by a session, and they are usually not executed in parallel in a cluster, it is safe to set this value significantly larger than work_mem. Larger settings might improve performance of vacuuming and restoring database dumps

64MB

work_mem(MB)

Sets the base maximum amount of memory to be used by a query operation (such as a sort or hash table) before writing to temporary disk files. Note that for a complex query, several sort or hash operations might be running in parallel. Each operation will be allowed to use as much memory as this value specifies before it starts to write data into temporary files. Several running sessions can also do such operations concurrently. Therefore, the total memory used can be many times greater than the value of work_mem. If this value is specified without units, it is taken as kilobytes

4MB

min_wal_size(MB)

Until WAL disk usage stays below the min_wal_size value, old WAL files are recycled for future use at a checkpoint instead of removing. This ensures that enough WAL space is reserved to handle spikes in WAL usage, for example, when running large batch jobs

80MB

max_wal_size(MB)

Sets the memory limit to which the log size can grow between automatic checkpoints. Increasing this setting may increase the recovery time after a failure. The specified limit can be exceeded automatically with a high load on ADPG

1024MB

wal_keep_size(MB)

Sets the minimum size of segments retained in the pg_wal directory, in case a standby server needs to fetch them for streaming replication. If a standby server connected to the sending server falls behind by more than wal_keep_size megabytes, the sending server might remove a WAL segment still needed by the standby server. In this case, the replication connection is terminated. Downstream connections also fail as a result. If WAL archiving is enabled, the standby server can fetch the segment from the archive and recover. The wal_keep_size parameter sets only the minimum size of segments retained in pg_wal. The system might need to retain more segments for WAL archival or to recover from a checkpoint. If wal_keep_size=0, the system does not keep any extra segments for standby purposes. In the Enterprise version, if the wal_keep_size value is set to less than 16 MB, Patroni uses 128 MB instead in its configuration. The value in the ADCM UI is not changed

0MB

huge_pages

Defines whether huge pages can be requested for the main shared memory area. The following values are valid:

  • Try — the server tries to request huge pages. If this operation fails, the server falls back to the default page.

  • On — if a huge page request fails, the server does not start.

  • Off — huge pages are not used.

try

superuser_reserved_connections

Determines the number of connection "slots" that are reserved for PostgreSQL superuser connections

3

logging_collector

Enables the logging collector. The logging collector is a background process that captures log messages sent to stderr and redirects them into log files

true

log_directory

Determines the directory that contains log files. It can be specified as an absolute path or relative to the cluster data directory

log
(the absolute path is /pg_data1/adpg16/log)

log_filename

Specifies the log file name pattern. The value can include strftime % — the escapes to define time-varying file names. If you specify a file name pattern without escapes, use a log rotation utility to save disk space

postgresql-%a.log

log_rotation_age

Determines the maximum period of time to use a log file, after which a new log file is created. If this value is specified without units, it is taken as minutes. Set log_rotation_age to 0 to disable time-based log file creation

1d

log_rotation_size

Determines the maximum size of a log file. After a log file reaches the specified size, a new log file is created. If the value is set without units, it is taken as kilobytes. Set log_rotation_size to 0 to disable size-based log file creation

0

log_min_messages

Specifies the minimum severity level of messages that are written to a log file. Valid values are debug5, debug4, debug3, debug2, debug1, info, notice, warning, error, log, fatal, and panic (see Severity levels table). Messages with the specified severity or higher are included in the log file. For example, if you set log_min_messages to warning, the log file will include the warning, error, log, fatal, and panic messages

warning

log_min_error_statement

Specifies which SQL statements that cause errors are logged. Valid values are debug5, debug4, debug3, debug2, debug1, info, notice, warning, error, log, fatal, and panic (see Severity levels table). The log file includes SQL statements for messages of the specified severity or higher. To disable the logging of failed statements, set log_min_error_statement to panic

error
Found a mistake? Seleсt text and press Ctrl+Enter to report it