/pg-rds-params-sync

Compare PostgreSQL settings between two databases to detect drift.

Primary LanguagePythonMIT LicenseMIT

PostgreSQL RDS Parameters Sync

Compare PostgreSQL settings between two databases to detect drift.

Features

Audit particular settings

Example:

$ pgrdsparamsync audit --parameters=max_wal_size

It will get all the databases in the account and region and display their max_wal_size value. Since this can be an expensive operation, a local 1 hour cache is used for each parameter group.

If only a subset of databases is of interest, --db-name-like can be used to filter based on the database name.

Example:

$ pgrdsparamsync audit --parameters=max_wal_size,min_wal_size --db-name-like=production

It will audit only the databases that contain the string "production" in their name and display max_wal_size and min_wal_size.

For reporting, we can also print out the above table in CSV format.

Example:

$ pgrdsparamsync audit --parameters=max_wal_size,min_wal_size --fmt=csv

Compare a database to another or a parameter group

Example:

$ pgrdsparamsync rds-compare --target-db="users-production" --other-db="orders-production"

It will print all the settings that differ between the two databases.

If the goal is to set a standard against the entire database fleet, it is possible to compare a database to a parameter group that represents that standard.

Example:

$ pgrdsparamsync rds-compare --target-db="users-production" --parameter-group="pg-11-standard"

It will print all the settings that differ between the target database and the parameter group.

Compare two databases directly

Example:

$ pgrdsparamsync pg-compare \
--target-db-url="postgres://user:password@users-production.rds.awsamazon.com" \
--other-db-url="postgres://user:password@orders-production.rds.awsamazom.com"

RDS parameter groups use formulas to calculate certain settings (e.g. shared_buffers, effective_cache_size, etc.) by default. Sometimes, it is useful to know the actual value. This will connect to the databases directly, query pg_settings, and print the settings that differ.

Caching

Fetching parameter groups for 100s of databases is long and expensive. We added a local cache with a 1h TTL. It's stored in /tmp/pgrdsparamsync. The first run of the application will be slow, but subsequent runs will be much faster. To increase the TTL or bust the cache, add an environment variable CACHE_TTL with a value that's not 1h, for example $ export CACHE_TTL=7200 which will set the TTL to 2 hours (7200 seconds).