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 https://github.com/klouddb/klouddb_tools/tree/main/postgrescfgcompareis 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_NAME=postgres 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) DB2_NAME=postgres
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 pgcfgcompare.py’
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': '18.xxx.xx.xxx', 'dbname': 'postgres', 'user': 'repusrr', 'password': 'xxx', 'port': '5432'}
{'host': '3.xxx.xx.xxx', 'dbname': 'postgres', 'user': 'repusrr', 'password': 'xxx', 'port': '5432'}
data_directory_mode : 0750 -> 0700
primary_conninfo : user=replicator password=replicator host=3.22.209.122 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=3.22.209.122 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"}}}
FAQ
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