Description

The pgpro_tune utility is a command-line tool for automatic tuning. Optimal values for different configuration parameters of Postgres Pro depend on the hardware configuration. The pgpro_tune utility collects information about the system and transforms it into a set of parameters written into the configuration file.

Usage

The use of pgpro_tune is enabled in initdb by default. The utility runs automatically during initialization of a new database cluster, ensuring that the cluster is configured with hardware-appropriate baseline parameters. Two initdb options control this behavior:

The utility can also be run manually at any time after cluster initialization by executing the following command:

pgpro_tune -P preset_name

This is useful after hardware upgrades, e.g., adding RAM or CPUs, or changes in DBMS workload characteristics. After manually running the utility, apply the updated configuration parameters. Depending on which configuration parameters were modified, either call the pg_reload_conf function, or restart the server.

By default, pgpro_tune applies the default.tune preset first. Additional presets can be specified using the -P or --preset option, or by providing the preset name as the last command-line argument. Multiple presets may be used simultaneously; they are applied sequentially in the order specified in -P or --preset. The default.tune preset is always applied first unless --no-default is specified. If the same preset appears multiple times, it is applied repeatedly. Configuration parameters from presets are modified incrementally, and for transparency, all intermediate changes are written to the postgresql.conf configuration file.

Non-default presets take precedence because they are applied after default.tune, and only the final values written to postgresql.conf take effect.

The following default presets are currently available:

Additional options may be passed via -O or --options parameter for any preset, but for the default preset --default-options should be used.

Additional environment variables can be set for presets with the --set parameter. These values will be the same both for the default preset and all custom presets.

By default, the presets are placed in the share directory, but you can specify their location manually in the --preset-dir parameter.

By default, pgpro_tune works with the postgresql.conf configuration file located in the data directory. The data directory can be specified with -D or --pgdata, or provided with environment variable PGDATA. When runing pgpro_tune from initdb, the tool will use the data directory of initdb.

Configuration file can also be specified with --config-file.

The pgpro_tune tool writes its changes into a separate block at the end of the configuration file, which is commented as added by pgpro_tune. For details, see the example of the preset.

Options

Environment variables

pgpro_tune sets the following environment variables before running presets.

Writing presets

A preset is an executable, usually a shell script, that transforms the information passed through environment variables into the set of configuration parameters.

The expected output of the preset is the set of specifically formatted lines. The first non-whitespace character is the indicator of the line content interpretation for pgpro_tune. The indicator should be one of the following:

work_mem = 4MB # Default value
shared_preload_libraries = 'plantuner'
search_path = '"$user",wrong_schema,public'

echo "# Adding new configuration parameters."
#Replace configuration parameter value by a new one
echo "work_mem = 8MB"
#Append to the start of existing value
echo "shared_preload_libraries += pg_stat_statements"
#Append to the end of existing value
echo "shared_preload_libraries =+ pg_prewarm"
#Withdraw from existing value
echo "search_path -= 'wrong_schema'"

pgpro_tune --config-file=/path/to/test.conf -P/path/to/test.tune --no-default

#------------------------------------------------------------------------------
# The following settings were added by pgpro_tune.
# pgpro_tune was run with the following options:
# --config-file=/path/to/test/conf -P/path/to/test/tune --no-default
# The following presets were used:
#	"/path/to/test/tune"
# At DD.MM.YYYY HH:MM:SS
#------------------------------------------------------------------------------
# Adding new configuration parameters.
work_mem = 8MB
shared_preload_libraries = 'pg_stat_statements, plantuner'
shared_preload_libraries = 'pg_stat_statements, plantuner, pg_prewarm'
search_path = '"$user", public'
#------------------------------------------------------------------------------
# End of settings added by pgpro_tune at DD.MM.YYYY HH:MM:SS
#------------------------------------------------------------------------------