Compare Postgres Config Using Simple DB Utility

As a postgres DBA one of the common maintenance tasks is to alter postgres config . Below are some of the scenarios that we would like to bring to your notice :

  • Lets say we have a 3 node streaming replication cluster , we make a quick change on primary node to fix some urgent issue but forget to update other two nodes , this could cause an issue in the future (when a failover occurs)
  • You are supporting 30 prod postgres nodes and not all of them are consistent in terms of postgres config. This could cause maintenance issues
  • You migrated a server from test to pre-prod and wanted to make sure if both have same config before handing it over to app teams
  • You have a team of 5 DBAs and want to make sure that all DBAs are following the same standards (for config changes)

It is always better to automate config comparison so that we are alerted when a drift happens. Here is a simple script that can compare config for two servers the location of the script

How do we execute this script ?

You need to populate below environment variables . You can put all of below variables in a hidden file called .env and source it . You can issue a command like ‘echo $DB1_HOSTNAME’ to see if the variables are properly initialized

DB1_HOSTNAME=Hostname/ip of first hostname
DB1_USERNAME= username of first hostname
DB1_PASSWORD= password of first hostname
DB1_PORT=5432 (change port depending on your config)
DB2_HOSTNAME=Hostname/ip of second hostname
DB2_USERNAME=username of first hostname
DB2_PASSWORD= password of second hostname
DB2_PORT=5432(change port depending on your config)

Sample config file (NOTE : Actual hostnames are masked here . You need to replace with appropriate hosts)

Once above environment variables are loaded please execute the script ‘python3’

Sample output :

Below is a sample output . Differences between below two hosts are displayed . Some differences like transaction_read_only and primary_conninfo can be ignored as one of them is replica . Replicas have read_only enabled and primary_conninfo values which is totally fine . But the other differences needs to be addressed

{'host': '', 'dbname': 'postgres', 'user': 'repusrr', 'password': 'xxx', 'port': '5432'}
{'host': '', 'dbname': 'postgres', 'user': 'repusrr', 'password': 'xxx', 'port': '5432'}
data_directory_mode : 0750 -> 0700
primary_conninfo : user=replicator password=replicator host= port=5432 
server_version : 12.4 -> 12.7
transaction_read_only : on -> off

JSON output :

You will also get a JSON output in addition to above format

{"total_difference": 5, "timestamp": "2021-07-06T19:38:40.496656", "diffs": {"data_directory_mode": {"host_1": "0750", "host_2": "0700"}, "primary_conninfo": {"host_1": "user=replicator password=replicator host= port=5432 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any", "host_2": ""}, "server_version": {"host_1": "12.4", "host_2": "12.7"}, "server_version_num": {"host_1": "120004", "host_2": "120007"}, "transaction_read_only": {"host_1": "on", "host_2": "off"}}}


Currently this script only compares two hosts . We might add multi host compare in the near future

You need to add email automation code and schedule it on cron (or any other scheduler)

Please reach out to us . We will try our best to address the issue

Primary_conn_info and transaction_read_only variables are expected to be different (Primary Vs Replica) and we will be adding code to exclude the know differences very soon. For now you need to customize the given code to suit your needs

Yes . We have plans to upgrade this to add more features

Share this Post :