pg-replica-auditor
A tool to compare a PostgreSQL logical replica to its primary. It can help detect data inconsistencies.
Features
Assumptions
These tests assume that id and updated_at (or column specified in --lag-column) columns exist and have indexes for efficient querying and that the table exists on both databases.
Row comparison
Runs row comparisons between primary and replica using two methods:
- select 8128 rows (or number of rows given to
--rows) at random betweenMIN(id)andMAX(id) - select the last 1000 rows.
Replica lag
Checks for "replica lag" by comparing MAX(updated_at) on the given table on both databases.
MinMax
Checks that the minimum id and the maximum id match on both replica and primary. These can drift a little because of replica lag.
Bulk 1000 Sum
Take the sum of the id column in chunks of 1000 and compare it between databases. This assumes that retrieving rows in bulk is easier than at random and runs faster than the row comparison and can scan more rows.
Count all rows
Counts all the rows using COUNT(lag_column) to make sure row counts match on both replica and primary. Very slow, since it has to do a full scan (index or table). Adjust --count-before to count all columns before a timestamp on --lag-column, or updated_at by default.
Missing Sequential Records
Go throught the table with a step size of MAX(id) * --step-size=0.01. The assumption is that if records will be missing, they will be missing in bulk, grouped together.
Requirements
- Python 3
- Postgres development files (required by psycopg2). On Mac OS, use
brew install postgresql. On Ubuntu, installlibpq-dev.
Installation
Development
Using virtualenv, pip install -r requirements.txt
Production
Using Pypi, pip install pg-replica-auditor.
Usage
This script requires three arguments:
--primary, any acceptable Postgres connection string (incl. DSN),--replica, same as--primarybut for the replica database,
Optional arguments:
--exclude-tables, excludes the comma-separated tables from the scan,--table, only scans this table,--debug, will print debugging information,--rows, will scan this many rows in the row comparisons check,--lag-column, will use this column for the replica lag check,--show-skipped, will print the skipped rows in the Last 1000 check,--count-before, will count all rows in the table created/updated before this timestamp,--step-size, will decrease the step size for missing sequential records search.
Example:
$ pgreplicaauditor --primary=postgres://primary-db.amazonaws.com:5432/my_db --replica=postgres://replica-db.amazonaws.com:5432/my_db --table=immutable_items --lag-column=created_at --count-before="2020-04-06"
1 maintainer
Metadata
1 maintainer
Release files for pg-replica-auditor 0.15
For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.
Source distribution (sdist)
| File | Size | Uploaded | |
|---|---|---|---|
| pg-replica-auditor-0.15.tar.gz | 6.6 kB | Details |
Built distribution (wheel)
| File | Interpreter | ABI | Platform | Reset |
|---|---|---|---|---|
| pg_replica_auditor-0.15-py3-none-any.whl | Python 3 | none | any | Details |
Total release size: 15.0 kB
Release files / pg-replica-auditor-0.15.tar.gz
| Download URL | pg-replica-auditor-0.15.tar.gz |
|---|---|
| Size | 6.6 kB |
| Tags | Source |
|
SHA-256 checksum How to use checksums |
e80d4139cf62d236e02d7dc4802caf4ba6551f5029c5e9331d18c0efbf388e52
|
|
BLAKE2b-256 checksum How to use checksums |
6a3443acfe16d408382d3954936ecbeaecd73d878ae5b880e2ddd6dc2d6bb001
|
| Upload date | |
|
Uploaded using Trusted Publishing? What is trusted publishing? |
No |
| Uploaded via |
twine/3.1.1 pkginfo/1.5.0.1 requests/2.22.0 setuptools/42.0.2 requests-toolbelt/0.9.1 tqdm/4.45.0 CPython/3.7.5
|
Release files / pg_replica_auditor-0.15-py3-none-any.whl
| Download URL | pg_replica_auditor-0.15-py3-none-any.whl |
|---|---|
| Size | 8.3 kB |
| Tags | Python 3 |
|
SHA-256 checksum How to use checksums |
e7cce4c73cb26eb94081ad96cc31f1ae90dc6adf6314d54dd69888476948d358
|
|
BLAKE2b-256 checksum How to use checksums |
e4ceed49b09ac356720231d58da42f596dbb315f2e310338c1ebcb2c3031242b
|
| Upload date | |
|
Uploaded using Trusted Publishing? What is trusted publishing? |
No |
| Uploaded via |
twine/3.1.1 pkginfo/1.5.0.1 requests/2.22.0 setuptools/42.0.2 requests-toolbelt/0.9.1 tqdm/4.45.0 CPython/3.7.5
|
