RustProof Labs: blogging for education (logo)
My book Mastering PostGIS and OpenStreetMap is available!

Exploring PgConfig comparison tool

By Ryan Lambert -- Published December 27, 2019

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.

postgresql.conf comparison (pgconfig) logo)

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

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.

Screenshot showing postgresql.conf comparison website results for PostgreSQL 11 to 12)

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!

Screenshot showing Compare Your Config feature in PgConfig comparison tool)

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'


cluster_name = '12/main'


# - 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.

Screenshot showing summary of configuration differences between above example and the defaults for Postgres 12)

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.

parameter 12 custom history
listen_addresses 'localhost' '*'
password_encryption md5 scram-sha-256
ssl off on
ssl_min_protocol_version 'TLSv1' 'TLSv1.2'
shared_buffers 128MB 1GB
checkpoint_timeout 5min 15min
checkpoint_completion_target 0.5 0.9
log_line_prefix '%m [%p] ' '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
shared_preload_libraries '' 'pg_stat_statements'

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:

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.

Screenshot showing invalid parameter detected in custom configuration vs Postgres 12 default configuration)

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.

parameter 9.2 9.3 9.4 9.5 9.6 10 11 12
min_parallel_relation_size NaN NaN NaN NaN 8MB NaN NaN NaN

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!

By Ryan Lambert
Published December 27, 2019
Last Updated December 27, 2019