Exploring PgConfig comparison tool

PgConfig is a RustProof Labs hosted tool that makes it easy to compare configuration changes (postgresql.conf) between major PostgreSQL versions. The initial version of this tool was the result of a lazy Sunday at home, and since then has seen multiple enhancements. This post goes over how I have been using the tool over the past few months.

The current version of PgConfig has three (3) main functions:

• Differences between version X and Y
• Single parameter history
• Compare your configuration to version defaults

This post is part of the series PostgreSQL: From Idea to Database.

Versions available

The tool has default configuration files loaded for major PostgreSQLs versions starting with Pg 9.2 through the latest version, currently Postgres 12. As the upcoming version of Postgres enters Beta stages (features mostly complete, not quite production ready) I will load the Beta configurations into the tool to enable early planning / testing of upgrades.

I do not intend to load versions prior to Postgres 9.2. After all, 48 days after publishing this post Pg 9.4 will be EOL...

Differences between major versions

This first feature, the ability to see what changed over major Postgres versions, was the main reason I created the PgConfig tool. I wanted something to help identify the changes to the default configuration as I prepared for Postgres 12 upgrades. The following screenshot shows the differences in configuration between Postgres 11 and Postgres 12, the summary at the top identifies:

24 new

3 updated

1 removed

The table under the summary information outlines the changes detected between V1 and V2 selected. The link available in the "history" column links to the single parameter history for that parameter. More on that feature in a bit.

Note: The NaN values indicates the option did not exist in that version.

Compare custom configuration

The Compare Your Config feature is the newest addition to the tool. Select your version of Postgres (e.g. 12) and paste in the contents from any postgresql.conf file. The tool parses your configuration and compares it to the selected version's defaults. This can quickly highlight what has changed from the defaults, and even find invalid options in your configuration!

The best way to illustrate how this works is to use a real configuration from a server that you aren't 100% certain how it is configured. The following contents from postgresql.conf were pulled from one of my oldest development VMs. This particular VM was initially configured via Ansible followed by months of manual changes, testing workloads, and testing upgrades. In other words, who knows what is is left behind in that current configuration.

This scenario is not uncommon when exploring newly discovered Postgres servers, such as a developer's machine or a QA server.

Your goal in this scenario is to quickly understand how the server is currently configured and to identify potential issues.

Example postgresql.conf

The following block is a minimized version from the postgresql.conf file for a working Postgres 12 server, upgraded recently from Postgres 11 and upgraded from Postgres 9.6 months prior.

# -----------------------------
# Minimal PostgreSQL configuration file
# -----------------------------

listen_addresses = '*'
max_connections = 100
password_encryption = scram-sha-256

ssl = on
ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'

ssl_prefer_server_ciphers = on
ssl_ecdh_curve = 'prime256v1'
ssl_min_protocol_version = 'TLSv1.2'

shared_buffers = 1GB

min_parallel_relation_size = 8MB
max_parallel_workers_per_gather = 2

wal_level = replica

checkpoint_timeout = 15min
max_wal_size = 1GB
min_wal_size = 80MB
checkpoint_completion_target = 0.9

archive_mode = False
archive_command = ''

max_wal_senders = 4
wal_keep_segments = 8

hot_standby = True

log_checkpoints = True
log_connections = True
log_disconnections = True
log_duration = True
#log_error_verbosity = default          # terse, default, or verbose messages
log_hostname = on
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '  # Set for pgbadger

log_timezone = 'Etc/UTC'

#------------------------------------------------------------------------------
# PROCESS TITLE
#------------------------------------------------------------------------------

cluster_name = '12/main'

#------------------------------------------------------------------------------
# STATISTICS
#------------------------------------------------------------------------------

# - Query and Index Statistics Collector -

track_io_timing = True
track_functions = all                 # none, pl, all

shared_preload_libraries = 'pg_stat_statements'

Comparison Results

Paste and submit the configuration to see the results.

• 23 updated
• 1 invalid
• 244 set to default

Updated from defaults

Below the summary on the results page is a tabbed group of the parsed parameters, the Updated tab is selected by default. The Updated table shows parameters that have been updated from their default values for the selected version. The default and custom values are both displayed.

The following table is an excerpt of the results when using the above configuration example. To point out a couple changes, password_encryption has been updated to use the stronger SCRAM option, and the new ssl_min_protocol_version has been updated to use newer, more secure protocols (TLSv1.2) than the default.

Please do not use TLS v1.0! "According to NIST, there are no fixes or patches that can adequately repair SSL or early TLS. Therefore, it is critically important that organizations upgrade to a secure alternative as soon as possible"

Invalid parameters

The check for invalid parameters detects two types of error in your configuration:

• Typos, e.g. shared_bufferss (note the extra "s")
• Parameter is valid in other version(s)

The example configuration provided above happens to have an invalid parameter of the latter type. In this case min_parallel_relation_size is set but is not a valid parameter for Postgres 12. We will explore this further in the next section.

Single parameter history

The single parameter history simply lines up default values for Postgres versions 9.2 through 12. In the above example, min_parallel_relation_size was flagged as an invalid option for Postgres 12. Clicking the link under "history" from those results shows that parameter was only valid in Postgres 9.6 and removed from the configuration in Postgres 10. This illustrates that I missed removing the configuration from the file when I upgraded this server from Postgres 9.6 to Postgres 11.

What's next?

The PgConfig project started as a weekend impulse, fueled by curiosity, and enabled by a bit of free time. I have already found this tool to be helpful on multiple occasions and will continue to make improvements as time permits. One improvement I expect to make soon is to detect when a parameter is defined in a configuration more than once. This is another common error when a manual editor goes directly to the end of the file to overwrite a previously defined value.

I hope others find this tool useful too!

Need help with your PostgreSQL servers or databases? Contact us to start the conversation!