pg_comparator(1)

📦
version 2.3.2 · 6 versions
, in ubuntu resolute, ubuntu stonking

💖 pg_comparator(1) 💖

the manual page bestie, but make it iconic — it lives in the User Contributed Perl Documentation section, and it's called pg_comparator. yeah. the name is right there. we're already slaying.

✨ NAME ✨

pg_comparator — efficient table content comparison and synchronization 🦋

✨ SYNOPSIS ✨

pg_comparator [options as --help --option --man] conn1 conn2

✨ DESCRIPTION ✨

okay so. this script does a network-efficient and time-efficient comparison or synchronization of two possibly HUGE tables in PostgreSQL, MySQL or SQLite databases. its whole job is to detect inserted, updated or deleted tuples between those tables. and the algorithm? it eats. especially when the differences you're expecting are relatively small. lowkey elegant.

the implementation is super generic, like it does not care about your drama:

  • multi-column keys — but bestie, there must be a key! that's non-negotiable 💀
  • no assumptions about data types, other than that they can be cast to text
  • you can compare a subset of columns
  • NULL values get handled
  • and more...

this script is obsessed with the comparison algorithm itself, which is why there are So Many Options. the fact that it might also do something genuinely useful — like checking that a replication tool actually replicates your data, or synchronizing tables — is, and I quote, a mere side effect. main character energy honestly. 👑

✨ OPTIONS ✨

options let you ask for help, or tweak some internal parameters. short one-letter options also exist, usually using the first letter of the option name. cute right?

  • --aggregate=(sum|xor) or -a (sum|xor) — the aggregation function used for summaries. either xor or sum. it has to operate on the result of the checksum function. for PostgreSQL and SQLite, the xor aggregate needs to be loaded. also there's a signed/unsigned issue on the key hash when you use xor to compare tables on MySQL or SQLite vs PostgreSQL. we provide a new ISUM aggregate for SQLite because both SUM and TOTAL do some incompatible handling of integer overflows. 😭 default is sum because it's available by default and it works in mixed mode.
  • --ask-pass — ask for passwords interactively. see also the --env-pass option below. 🙈 default is not to ask for passwords.
  • --asynchronous or -A, --no-asynchronous or -X — whether to run asynchronous queries. this gives you some parallelism, however the two connections are more or less synchronized per query. 🏃♀️ default is to use asynchronous queries to enable some parallelism.
  • --checksum-computation=(create|insert) or --cc=... — how to create the checksum table. create uses a CREATE ... AS SELECT ... query, insert uses a CREATE ...; INSERT ... SELECT ... query. the former will need an additional counting to get the table size, so in the end there are two queries anyway. heads up: there's a type size issue with the insert strategy on MySQL — the cumulated key string length must be under 64 bytes. ⚠️ default is create because it always works for both databases.
  • --checksum-function=fun or --cf=fun or -c fun — checksum function to use: ck, fnv or md5. for PostgreSQL, MySQL and SQLite the provided ck and fnv checksum functions must be loaded into the target databases. choosing md5 doesn't come free either: the provided cast functions must be loaded into the target databases, and the computation is more expensive. 💸 default is ck, which is fast — especially if the operation is cpu-bound and the bandwidth is reasonably high.
  • --checksum-size=n or --check-size=n or --cs=n or -z n — tuple checksum size. must be 2, 4 or 8 bytes. the key checksum size is always 4 bytes long, no arguing. default is 8, so that the false negative probability is very low. there should be no reason to change that. fr fr.
  • --cleanup — drop checksum and summary tables beforehand. useful after a run with --no-temp and --no-clear, typically used for debugging. 🧹 default is not to drop because it's not needed.
  • --clear — drop checksum and summary tables explicitly after the computation. note that they're dropped implicitly by default when the connection is closed, because they're temporary — see the -(-no)-temporary option. this option is useful for debugging. default is not to clear the checksum and summary tables explicitly, as it's not needed.
  • --debug or -d — set debug mode. repeat it for higher debug levels. see also --verbose. beware that some safe guards about option settings are skipped under debug, so as to allow testing under different conditions. 👀 default is not to run in debug mode.
  • --env-pass='var' — take the password from environment variables var1, var2 or var for connection one, two, or both. this is tried before asking interactively if --ask-pass is also set. default is not to look for passwords from environment variables.
  • --expect n or -e n — total number of differences to expect (updates, deletes and inserts). this option is only used for non regression tests. see the TESTS section.
  • --folding-factor=7 or -f 7 — folding factor: log2 of the number of rows grouped together at each stage, starting from the leaves so that the first round always groups as many records as possible. the power of two lets you use masked computations. the minimum value of 1 builds a binary tree. 🌳 default folding factor log2 is 7, i.e. size 128 folds. this default value was chosen after some basic tests on medium-size cases with medium or low bandwidth. values from 4 to 8 should be a reasonable choice for most settings.
  • --help or -h — show short help. 🫶
  • --key-checksum='kcs' or --kcs=... — use the key checksum attribute of this name, which must already be available in the tables to compare. this option also requires the --tuple-checksum option. see also the EXAMPLES section below for how to set a checksum trigger. consider --use-key instead if you already have a reasonably distributed integer primary key. default is to build both key and tuple checksums on the fly.
  • --lock, --no-lock — whether to lock tables. setting the option explicitly overrides the default one way or another. for PostgreSQL, this option requires --transaction, which is enabled by default. 🔒 default depends on the current operation: the table is not locked for a comparison, but it is locked for a synchronization.
  • --long-read-len=0 or -L 0 — set max size for fetched binary large objects. well, it seems to be ignored at least by the PostgreSQL driver. 🤷♀️ default is to keep the default value set by the driver.
  • --man or -m — show the manual page interactively in the terminal.
  • --max-ratio=0.1 — maximum relative search effort. the search is stopped if the number of results goes above this threshold, expressed relatively to the table size. use 2.0 for no limit (all tuples were deleted and new ones are inserted). default is 0.1, i.e. an overall 10% difference is allowed before giving up.
  • --max-report=n — maximum absolute search effort. the search is stopped if the number of differences goes beyond this threshold. if set, the previous --max-ratio option is ignored, otherwise the effort is computed with the ratio once the table size is known. default is to compute the maximum number of reported differences based on the --max-ratio option, with a minimum of 100 differences allowed.
  • --max-levels=0 — maximum number of levels used. lets you cut-off folding. 0 means no cut-off. setting a value of 1 would only use the checksum table, without summaries. a value of 3 or 4 would be raisonable, as the last levels of the tree are nice for the theoretical complexity formula, but do not improve performance in practice. (yes, "raisonable" — we keep it exactly as written, don't @ me 💅) default is 0.
  • --null='text' — how to handle NULL values. either hash to hash all values, where NULL has one special hash value, or text where NULL values are substituted by the NULL string. default is text because it is faster.
  • --option or -o — show option summary.
  • --pg-text-cast — with PostgreSQL, add explicit TEXT casts to work around some typing issues.
  • --pg-copy=128 — experimental option to use PostgreSQL's COPY instead of INSERT/UPDATE when synchronizing, by chunks of the specified size. 🧪
  • --prefix='pgc_cmp' — name prefix, possibly schema qualified, used for generated comparison tables by appending numbers to it. consider changing the prefix if you expect several comparisons to run concurrently against the same database. default is pgc_cmp. the checksum table is named pgc_cmp_1_0 and pgc_cmp_2_0, and summary tables are named by increasing the last number.
  • --report, --no-report — report differing keys to stdout as they are found. default is to report.
  • --separator='|' or -s '|' — separator string or character used when concatenating key columns for computing checksums. defaults to the pipe | character.
  • --size=n — assume this value as the table size. it's sufficient for the algorithm to perform well that this size is in the order of magnitude of the actual table size. default is to query the table sizes — which is skipped if this option is set.
  • --source-1='DBI:...', --source-2='...' or -1 '...', -2 '...' — take full control of the DBI data source specification, and mostly ignore the comparison authentication part of the source or target URLs. you can connect with DBI:Pg:service=backup, use an alternate driver, set any option allowed by the driver... see the DBD::Pg and DBD:mysql manuals for the various options that can be set through the DBI data source specification. however, the database server specified in the URL must be consistent with this source specification so that the queries' syntax is the right one. ⚠️ default is to rely on the two URL arguments.
  • --skip-inserts, --skip-updates, --skip-deletes — when synchronizing, do not perform these operations. 🙅♀️ default under --synchronize is to do all operations.
  • --stats=(txt|csv) — show various statistics about the comparison performed in this format. also, the --stats-name option gives the test a name, useful to generate csv files that will be processed automatically. default is not to show statistics, because it requires additional synchronizations and is not necessarily interesting to the user.
  • --synchronize or -S — actually perform operations to synchronize the second table wrt the first. well... not really, it's only a dry run. it is actually done if you add --do-it or -D. save your data before attempting anything like that! 😭 default is not to synchronize.
  • --temporary, --no-temporary — do we use temp tables or not? If you say no, the tables just... stay there at the end, chillin' in your database forever until you delete them by hand 😭. Hit --clear if you want a cleanup. Honestly this one is mostly a debugging vibe.

Default is yes to temporary tables, and they get automatically yeeted when the connection closes. Slay.

  • --unlogged, --no-unlogged — use unlogged tables to stash the checksums. These bad boys are not transactional, so they might speed things up a lil bit. But — and this is the important part bestie — they are NOT automatically cleaned up at the end. So again, --clear if you want them gone.

Default is not to use unlogged tables.

  • --threads or -T, --no-threads or -N — Highly EXPERIMENTAL feature. Like, be careful, okay?

It tries to use threads to do computations in parallel, with some hocus-pocus 🪄 because the perl thread model does not really work well with DBI. Perl threads are rather heavy and slow — honestly they're more like communicating processes than lightweight threads. It's giving overworked.

This does NOT work at all with PostgreSQL. Not even a little. It works partially with MySQL, but the price is turning off --transaction.

Default is not to use threads, because it doesn't work for all databases. Periodt.

  • --timeout n — timeout the comparison after n seconds ⏰.

Default is no timeout. Be patient, bestie 🧘

  • --transaction, --no-transaction — wrap the whole algorithm in a single transaction or nah?

Default is to use a wrapping transaction, because apparently it's both faster and safer that way. We love a win-win ✨

  • --tuple-checksum='tcs' or --tcs=... — use the tuple checksum attribute with this name, which must already exist in the tables you're comparing. This option requires you to also set either --use-key or --key-checksum=... (those were up above). The checksum attributes you provide must not appear in the lists of key and value columns. Peep the EXAMPLES section below for how to set up a checksum trigger 🔥

Default is to build both key and tuple checksums on the fly.

  • --use-key or -u — do we straight-up use the value of the key to distribute tuples among branches? The key must be simple, integer, not NULL, and evenly distributed. If you've got a reasonably spread integer primary key, consider using this to dodge half of the checksum table hash computations. Efficiency icon 💅

Default is to hash the key, so it handles any type, composition and distribution. No gatekeeping.

  • --use-null, --no-use-null — use the info that a column is declared NOT NULL to simplify computations by avoiding calls to COALESCE to handle NULL values? Sneaky but valid.

Default is to use this information, at the price of querying table metadata.

  • --verbose or -v — be verbose about what's happening. The more you ask, the more verbose. Yes, it stacks. Go off.

Default is to be quiet, so that possible warnings or errors actually stand out 👀

  • --version or -V — show version information and exit. Bye 👋
  • --where=... — a SQL boolean condition on table tuples for a partial comparison. Super useful to reduce the load if you already know the expected differences are in some part of your data, say those timestamps from today... ⏳ The same condition is passed on both sides, so both tables must be pretty similar for it to work. Usually they are, so we move.

Default is to compare whole tables.

👀 ARGUMENTS

The two arguments describe database connections using this URL-like syntax, where square brackets mean "optional part". Lots of parts are optional and have defaults. The minimum syntactically correct specification is / — but bestie, that doesn't necessarily mean anything useful 💀

[driver://][login[:pass]@][host][:port]/[base/[[schema.]table[?key[:cols]]]]

Check the EXAMPLES section below, and also the --source-* options up above.

Heads up: some default values used by DBI drivers can be changed with driver-specific environment variables, and DBI also brings its own defaults and overrides. So what actually happens may not always be clear. Not our fault. 🙈 Default values for the second URL are mostly taken from the first URL.

driver

Database driver to use. pgsql for PostgreSQL, mysql for MySQL, sqlite for SQLite. Heterogeneous databases may be compared and synchronized — but beware, subtle typing, encoding and casting issues may stop heterogeneous comparisons or synchronizations from succeeding. It's giving chaos. Default is pgsql for the first connection, and same as first for the second.

For SQLite, the authentication part of the URL (login, pass, host, port) is expected to be empty, so the full URL should look like:

sqlite:///base.db/table?key,col:other,columns

Also, setting the PGC_SQLITE_LOAD_EXTENSION environment variable with ":"-separated shared object files loads these into SQLite. Cute 🎀

login

Login to use when connecting to the database. Default is the username for the first connection, and same as the first connection for the second.

pass

Password to use when connecting to the database. Note that it is a bad idea to put a password as a command argument. Like, fr, don't. Default is none for the first connection, and the same password as the first connection for the second — if the connection targets the same host, port and uses the same login. See also --ask-pass and --env-pass options.

host

Hostname or IP to connect to. Default is the empty string, which means connecting to the database on localhost with a UNIX socket. Cozy 🌸

port

TCP-IP port to connect to. Default is 5432 for PostgreSQL and 3306 for MySQL.

base

Database catalog to connect to. Default is the username for the first connection. Default is same as first connection for the second connection. For SQLite, provide the database file name. The path is relative by default, but you can make it absolute by prepending an extra /:

sqlite:////var/cache/sqlite/base.db/table?...

schema.table

The possibly schema-qualified table to use for comparison. No default for the first connection. Default is same as first connection for the second connection.

Note that MySQL does not have schemas, so the schema part must be empty. However — and this is the weird part — strangely enough, their database concept is just like a schema, so one could say that MySQL really does not have databases, although there is something of that name. Am I clear? 😭 Bestie I'm just the messenger.

keys

Comma-separated list of key columns. Default is the table primary key for the first connection. Default is same as first connection for the second connection. The key cannot be empty. If you have no way of identifying your tuples, then there is no point in looking for differences. Facts.

cols

Comma-separated list of columns to compare. May be empty. Default is all columns but keys for the first connection. Default is same as first connection for the second connection. Beware that ...?key: means an empty cols, while ...?key sets the default by querying table metadata. Sneaky difference, don't get caught out 🫶

✨ EXAMPLES ✨

Compare tables calvin and hobbes in database family on localhost, with key id and columns c1 and c2:

./pg_comparator /family/calvin?id:c1,c2 /family/hobbes

Compare table calvin in the default database on localhost and the same table in the default database on sablons, with key id and column data:

./pg_comparator localhost/family/calvin?id:data sablons/

Synchronize the "user" table in database "wikipedia" from MySQL on "server1" to PostgreSQL on "server2":

./pg_comparator -S -D --ask-pass \
    mysql://calvin@server1/wikipedia/user pgsql://hobbes@server2/

For PostgreSQL, you may add trigger-maintained key and tuple checksums like this:

-- TABLE Foo(id SERIAL PRIMARY KEY, data ... NOT NULL);
-- add a key and tuple checksum attributes
-- the key checksum can be skipped if you use --use-key,
-- for which the key must be a simple NOT NULL integer.
ALTER TABLE Foo
  ADD COLUMN key_cs INT4 NOT NULL DEFAULT 0,
  ADD COLUMN tup_cs INT8 NOT NULL DEFAULT 0;
-- function to update the tuple checksum
-- if some attributes may be NULL, they must be coalesced
CREATE FUNCTION foo_cs() RETURNS TRIGGER AS $$
  BEGIN
    -- compute key checksum
    NEW.key_cs = cksum4(NEW.id);
    -- compute tuple checksum
    NEW.tup_cs = cksum8(NEW.id || '|' || NEW.data);
    RETURN NEW;
  END; $$ LANGUAGE plpgsql;
-- set trigger to call the checksum update function
CREATE TRIGGER foo_cs_trigger
  BEFORE UPDATE OR INSERT ON Foo
  FOR EACH ROW EXECUTE PROCEDURE foo_cs();
-- if table Foo is not initially empty,
-- update its contents to trigger checksum computations
UPDATE Foo SET id=id;

Then a fast comparison, which does not need to compute the initial checksum table, can be requested with:

./pg_comparator --tcs=tup_cs --kcs=key_cs \
    admin@server1/app/Foo?id:data hobbes@server2/

Since the primary key is a simple integer, the key_cs could be left out and the comparison launched with:

./pg_comparator --tcs=tup_cs --use-key \
    admin@server1/app/Foo?id:data hobbes@server2/

💅 OUTPUT

The output of the command is a bunch of lines describing the differences found between the two tables. They're expressed in terms of insertions, updates or deletes, and of tuple keys. Here's the whole cast:

  • UPDATE k — key k tuple is updated from table 1 to table 2. It exists in both tables, just with different values. Glow-up detected ✨
  • INSERT k — key k tuple does not appear in table 2, only in table 1. It must be inserted into table 2 to synchronize it wrt table 1.
  • DELETE k — key k tuple appears in table 2, but not in table 1. It must be deleted from 2 to synchronize it wrt table 1. Bye girl 👋
In case of tuple checksum collisions, false negative results may occur 💀 Changing the checksum function would help in such cases. See the ANALYSIS sub-section.

🛠️ INSTALL 🛠️

okay bestie, this bit is all about installing extensions — and by extensions we mean functions, casts and aggregates — that pg_comparator needs on whatever target database you're pointing it at. Different DBs, different vibes, different setup. Let's go 💅

First things first: go get the pg_comparator sources from http://www.coelho.net/pg_comparator/pg_comparator-2.3.2.tgz.

🐘 PostgreSQL

For installing on PostgreSQL, you gotta make sure that the pg_config command chilling in your path is the one belonging to your target PostgreSQL server — not some other rando version lurking around — and that the development packages are installed. no cap, this trips people up constantly 😭

Then compile and install the extensions' shared objects:

sh> make pgsql_install

To load the extension files into the target DB database, where ... are your connection options:

sh> psql ... -c 'CREATE EXTENSION pgcmp' DB

And when you wanna uninstall:

sh> psql ... -c 'DROP EXTENSION pgcmp' DB
sh> make pgsql_uninstall

🐬 MySQL

For installing on MySQL, same energy: make sure the mysql_config command found in your path is the one of the target MySQL server, and that development packages are installed ✨

Then compile and install the extensions' shared objects:

sh> make mysql_install

And load the extension files into the database:

sh> mysql ... < PATH-TO-EXTENSION/mysql_casts.sql
sh> mysql ... < PATH-TO-EXTENSION/mysql_checksum.sql

Peep mysql_config --plugindir to find the extension directory path. On some systems PATH-TO-EXTENSION might be /usr/lib/mysql/contrib.

To uninstall:

sh> make mysql_uninstall

🪶 SQLite

For installing with SQLite, you need the corresponding development package. that's the whole prerequisite, she's low maintenance 🎀

First, compile and install the extensions' shared objects — and yes you can tweak the SQLITE.libdir make variable to change the target directory, which is by default /usr/local/lib:

sh> make sqlite_install

Then load the extension by executing this (and if you want it to happen always, just append the line to your .sqliterc file, iconic behavior):

SELECT load_extension('/usr/local/lib/sqlite_checksum.so');

To uninstall:

sh> make sqlite_uninstall

✨ DEPENDENCES ✨

Three support functions are needed on the database, bestie. Three. Count them 🫶

  1. The COALESCE function takes care of NULL values in columns.
  2. A checksum function must be used to reduce and distribute key and column values. You can swap it with the --checksum option. Its size can be picked with the --checksize option (currently 2, 4 or 8 bytes). The checksums also require casts so they can be converted to integers of various sizes.

Suitable implementations are available for PostgreSQL and can be loaded into the server by processing share/contrib/pgc_checksum.sql and share/contrib/pgc_casts.sql. New checksums and casts are also available for MySQL — see mysql_*.sql. And a loadable implementation of suitable checksum functions is also available for SQLite — see sqlite_checksum.*.

The ck checksum is based on Jenkins hash (https://en.wikipedia.org/wiki/Jenkins_hash), which relies on simple add, shift and xor integer operations. The fnv checksum is inspired by FNV hash (https://en.wikipedia.org/wiki/Fowler_Noll_Vo_hash) (64 bits 1a version) which uses xor and mult integer operations, although the author also added some shift and add to help tweak high bits. chef's kiss, honestly 👑

  1. An aggregate function is used to summarize checksums for a range of rows. It must operate on the result of the checksum function. It may be changed with the --aggregate option.

Suitable implementations of an exclusive-or xor aggregate are available for PostgreSQL and can be loaded into the server by processing share/contrib/xor_aggregate.sql.

The sqlite_checksum.* file also provides a xor and sum aggregates for SQLite that are compatible with other databases.

Moreover, several perl modules are useful to run this script:

  • Getopt::Long for option management.
  • DBI, DBD::Pg to connect to PostgreSQL, DBD::mysql to connect to MySQL, and DBD::SQLite to connect to SQLite.
  • Term::ReadPassword for the --ask-pass option.
  • Pod::Usage for doc self-extraction (--man --opt --help).
  • threads for the experimental threaded version with the --threads option.
  • Digest::MD5 for md5 checksum with SQLite.

And a little reassurance so you don't panic-install everything: modules are only loaded by the script if they are actually required. she's efficient like that 💖

🔥 ALGORITHM 🔥

okay bestie, this is the part where it gets good. the whole point of this algorithm is to compare the contents of two tables — possibly on two completely different remote servers — while sending as little data across the wire as humanly possible. minimum network traffic, maximum detective work. it happens in three phases:

  1. A checksum table gets computed on each side for the target table.
  2. A first level summary table gets computed on each side by aggregating chunks of the checksum table. other levels of summary aggregations keep happening until there's literally only one row left in the last table, and that single row stores a global checksum for the entire original target tables.
  3. Starting from the upper summary tables, the aggregated checksums from both sides get compared to hunt down differences, going all the way down to the initial checksum table. and then — the keys of the differing tuples get displayed. 💅

🎀 CHECKSUM TABLE 🎀

phase one computes the initial checksum table T(0) on each side. assuming that key is the table key columns, and cols is the table data columns we're checking for differences, it's done by querying target table T like this:

CREATE TABLE T(0) AS
  SELECT key AS pk,                   -- primary key
         checksum(key) AS kcs,        -- key checksum
         checksum(key || cols) AS tcs -- tuple checksum
  FROM t;

the initial key is kept, because we're gonna need it to show the differing keys at the end. and the reason the kcs column exists at all? it randomizes the key-values distribution so the aggregates in the next phase are balanced. also — and this is the important bit — the key must appear in the checksum too, otherwise content that got swapped between two keys would just... not be detected in some cases. 😭 no cap.

🦋 SUMMARY TABLES 🦋

now we compute a whole set of cascading summary tables by grouping f (the folding factor) checksums together at every stage. the grouping is based on a mask on the kcs column, which takes advantage of that checksum randomization. starting from p=0 we build:

CREATE TABLE T(p+1) AS
  SELECT kcs & mask(p+1) AS kcs, -- key checksum subset
         XOR(tcs) AS tcs         -- tuple checksum summary
  FROM T(p)
  GROUP BY kcs & mask(p+1);

the mask(p) is defined so it groups together on average f checksums at a time: mask(0) = ceil2(size); mask(p) = mask(p-1)/f; this gives us a whole hierarchy of tables, each one a smaller summary of the one before it:

  • level 0 — the checksum table, size rows, i.e. as many rows as the target table.
  • level 1 — first summary table, (size/f) rows.
  • level p — intermediate summary table, (size/f**p) rows.
  • level n-1 — one before last summary table, less than f rows.
  • level n — last summary table, mask is 0, 1 row.

and it is super important that the exact same masks get used on both sides, so that the aggregations line up and we can actually compare matching contents across both sides. otherwise it's giving chaos. 🙈

✨ SEARCH FOR DIFFERENCES ✨

once all these support tables are built on both sides, the search for differences begins. when we check the checksum summary of the last tables (level n) with only one row, it's basically just comparing the checksum of the entire table contents. if they match? the two tables are equal, and we're done, periodt. but if those checksums differ, then some investigation is needed to detect the offending keys. 🔍

the investigation works by walking down the table hierarchy and looking for every kcs that had a difference in the checksum on the previous level. the same query runs on both sides at each stage:

SELECT kcs, tcs
FROM T(p)
WHERE kcs & mask(p+1) IN (kcs-with-diff-checksums-from-level-p+1)
ORDER BY kcs [and on level 0: , id];

and then the results from both sides get merged together. during that merge procedure, four cases can pop up:

  1. Both kcs and tcs match. then there's no difference. slay. ✨
  2. Although kcs does match, tcs does not. then this kcs needs to be investigated at the next level, because the checksum summary differs. if we're already at the last level, then the offending key can be shown. 💅
  3. No kcs match, one supplemental kcs in the first side. then this kcs corresponds to key(s) that must be inserted to sync the second table with respect to the first.
  4. No kcs match, one supplemental kcs in the second side. then this kcs corresponds to key(s) that must be deleted to sync the second table with respect to the first.

cases 3 and 4 are simply symmetrical, bestie — deciding whether it's an insert or a delete is purely an interpretation, based on taking the first side as the reference. 👑

💜 ANALYSIS 💜

let n be the number of rows, r the row size, f the folding factor, k the number of differences to be detected, c the checksum size in bits. so here's the cost to identify differences, plus the error rate:

  • network volume — is better than k*f*ceil(log(n)/log(f))*(c+log(n)). the contents of k blocks of size f get transferred on the depth of the tree, and each block identifier is of size log(n) and contains a checksum c. it's independent of r, and you want k<<n. the volume of the SQL requests is about k*log(n)*ceil(log(n)/log(f)), since the list of non matching checksums k*log(n) may get dragged along the tree depth.
  • number of requests (on each side, the algorithm is symmetric) — minimum is 6+ceil(log(n)/log(f)) for equal tables, maximum is 6+2*ceil(log(n)/log(f)).
  • disk I/O traffic — is about n*r+n*ln(n)*(f/(f-1)).
  • false negative probability — i.e. part of the tables are considered equal although they are different. with a perfect checksum function, this is the probability of a checksum collision at any point where they are computed and should have been different: about k*ceil(log(n)/log(f))*2**-c. for a million row table, expecting 1000 changes with the default algorithm parameter values, this is about 2**10 *3/2**64, that is about one chance in 2**52 merge runs. one in 2^52?? we love a glow up.

the lower the folding factor f, the better for the network volume — but the higher, the better for the number of requests and disk I/Os. so choosing f is a straight up tradeoff.

the lower the checksum size c, the better for the network volume, but the worse for the false negative probability. 🙃

if the available bandwidth is reasonable, the comparison will most likely be cpu-bound: the time gets spent mainly on computing the initial checksum table. so if you're planning to check for differences quite often, consider maintaining a tuple checksum with a trigger, and possibly a key checksum as well, and invoke with --tuple-checksum and either --key-checksum or --use-key.

🌈 IMPLEMENTATION ISSUES 🌈

  • The checksum implementation gives integers, which are constant length and easy to manipulate afterwards. very demure, very mindful.
  • The xor aggregate is a good choice because there is no overflow issue with it, it takes into account all bits of the input, and it can easily be defined on any binary data. the sum aggregate is also okay, but it requires some kind of underlying integer type.
  • NULL values must be taken care appropriately.
  • The folding factor and all modules are taken as power of two so as to use a masks.
  • There is a special management of large chunks of deletes or inserts which is implemented although not detailed in the algorithmic overview and complexity analysis.
  • There is some efforts to build a PostgreSQL/MySQL compatible implementation of the algorithm, which added hacks to deal with type conversions and other stuff.
  • This script is reasonably tested, but due to its proof of concept nature there is a lot of options the combination of which cannot all be tested. 👀

🌸 NOTE 🌸

if the tables to compare are in the same database, a simple SQL query can extract the differences. assuming Tables T1 and T2 with primary key id and non null contents data, then their differences — that is, how T2 differs from the reference T1 — is summarized by the following query:

        SELECT COALESCE(T1.id, T2.id) AS key,
          CASE WHEN T1.id IS NULL THEN 'DELETE'
               WHEN T2.id IS NULL THEN 'INSERT'
               ELSE 'UPDATE'
          END AS operation
        FROM T1 FULL JOIN T2 USING (id)
        WHERE T1.id IS NULL      -- DELETE
           OR T2.id IS NULL      -- INSERT
           OR T1.data <> T2.data -- UPDATE

like... if you can do it in one query, why are you even here. but if you can't, that's the whole reason this tool exists. 🫶

👑 REFERENCES 👑

A paper was presented at a conference about this tool and its algorithm: Remote Comparison of Database Tables by Fabien Coelho, In Third International Conference on Advances in Databases, Knowledge, and Data Applications (DBKDA), pp 23-28, St Marteen, The Netherlands Antilles, January 2011. ISBN: 978-1-61208-002-4. Copyright IARIA 2011. Online at Think Mind http://www.thinkmind.org/index.php?view=article&articleid=dbkda_2011_2_10_30021.

The algorithm and script was inspired by Taming the Distributed Database Problem: A Case Study Using MySQL by Giuseppe Maxia in Sys Admin vol 13 num 8, Aug 2004, pp 29-40. See Perl Monks http://www.perlmonks.org/index.pl?node_id=381053 for details. In this paper, three algorithms are presented. The first one compares two tables with a checksum technique. The second one finds UPDATE or INSERT differences based on a 2-level (checksum and summary) table hierarchy. The algorithm is asymmetrical, as different queries are performed on the two tables to compare. It seems that the network traffic volume is in k*(f+(n/f)+r), that it has a probabilistically-buggy merge procedure, and that it makes assumptions about the distribution of key values. The third algorithm looks for DELETE differences based on counting, with the implicit assumption that there are only such differences.

In contrast to this approach, our fully symmetrical algorithm implements all three tasks at once, to find UPDATE, DELETE and INSERT between the two tables. The checksum and summary hierarchical level idea is reused and generalized so as to reduce the algorithmic complexity. we ate that and left no crumbs 💅

From the implementation standpoint, the script is as parametric as possible with many options, and makes few assumptions about table structures, types and values.

🔍 SEE ALSO 🔍

Bestie, you HAVE to check out Michael Nacos's robust implementation pg51g at http://pgdba.net/pg51g/ — it's based on triggers, no cap. He also pointed out that even though the algorithm compares database contents, you can also detect schema differences by comparing the system tables that describe them. The way that's so smart, fr fr. 💅

Benjamin Mead Vandiver's PhD Thesis Detecting and Tolerating Byzantine Faults in Database Systems, Massachusset's Institute of Technology, May 2008 (report number MIT-CSAIL-TR-2008-040). There's an interesting discussion in Chapter 7, where experiments are presented with a Java/JDBC/MySQL implementation of two algorithms, including this one. Obsessed with the academic energy. 🎀

Baron Schwartz discusses comparison algorithms in an online post http://tinyurl.com/mysql-data-diff-algorithm. Iconic behavior.

Some more links:

🧪 TESTS 🧪

The paper reports numerous performance tests with PostgreSQL under various bandwidth constraints. Ngl, they really went off. 🔥

Moreover, non regression tests are run over randomly generated tables when the software is upgraded:

  • sanity — about 30 seconds & 30 runs. Run a comparison, synchronization & check for all databases combinaisons and all working asynchronous queries and threading options.
  • fast — about 5 minutes & 360 runs. Run 12 tests similar to the previous one with varrying options (number of key columns, number of value columns, aggregate function, checksum function, null handling, folding factor, table locking or not...).
  • feature — about 5 minutes & 171 or 477 runs. Test various features: cc for checksum computation strategies, auto for trigger-maintained checksums on PostgreSQL, pgcopy for PostgreSQL copy test, empty for corner cases with empty tables, quote for table quoting, engine for InnoDB vs MyISAM MySQL backends, width for large columns, nullkey for possible NULL values in keys, sqlite for SQLite test, mylite for SQLite/MySQL mixed mode with some restrictions, pglite for SQLite/PostgreSQL mixed mode with some restrictions.
  • release — about 20 minutes & 944 runs. This is feature with two table sizes, fast, and collisions to test possible hash collisions.
  • hour — about 1 hour & 2880 runs. A combination of 8 fast validations with varrying table sizes and difference ratio ranging from 0.1% to 99.9%.
  • full — about 6 hours & 16128 runs... seldom run. A combinatorial test involving numerous options: aggregation, checksums, null handling, foldings, number of key and value attributes...

🐛 BUGS 🐛

All software have bugs. This is a software, hence it has bugs. It's giving realistic. 💀

Reporting bugs is good practice, so tell me if you find one. If you have a fix, this is even better! 🫶

  • The implementation does not do many sanity checks.
  • Although the algorithm can work with some normalized columns (say strings are trimmed, lowercased, Unicode normalized...), the implementation may not work at all.
  • The script is really tested with integer and text types, issues may arise with other types.
  • The script handles one table at a time. In order to synchronize several linked tables, you must disable referential integrity checks, then synchronize each tables, then re-enable the checks.
  • There is no real attempt at doing some sensible identifier quoting, although quotes provided in the connection url are kept, so it may work after all for simple enough cases.
  • There is no neat user interfaces, this is a earthly command line tool. This is not a bug, but a feature. Main character energy, honestly. 👑
  • There are too many options.
  • Using another language such as Python for this application seems attractive, but there is no cleanly integrated manual-page style support such as POD, and the documentation is 50% of this script.
  • Mixed SQLite vs PostgreSQL or MySQL table comparison may not work properly in all cases, because of SQLite dynamic type handling and reduced capabilities.
  • The script creates (temporary) tables on both sides for comparing the target tables: this imply that you must be allowed to do that for the comparison... However, read-only replicas do not allow creating objects, which mean that you cannot use pg_comparator to compare table contents on a synchronized replica.

📝 TODO 📝

  • Allow larger checksum sizes.
  • Add an option to avoid IN (x,y,...) syntax, maybe with a temporary table to hold values and use a JOIN on that. I'm not sure about the performance implications, though.
  • Allow generating the SQL update script without applying it.
  • Option to generate more compact updates, i.e. only update attributes with different values.

📚 VERSIONS 📚

See web site http://www.coelho.net/pg_comparator/ for the latest version. Although versions are really managed with SVN, there is also a github repos https://github.com/zx80/pg_comparator. Slay. 💖

version 2.3.2 (r1594 on 2020-11-03)

Accept dash character (-) in login and database names, submitted by Piotr Boniecki. Fix quoting of values when synchronizing with copy, reported by Luis Gonzales Sotelino. Add module_pathname to Postgres extension control file. Periodt. ✨

version 2.3.1 (r1582 on 2017-07-07)

Fix spelling errors in the documentation, reported by Bas Couwenberg. Fix distribution Makefile. Lowkey important. 🎀

version 2.3.0 (r1569 on 2017-07-07)

Add new INSTALL Section. Turn cast, functions and aggregates into a PostgreSQL extension. Fix --where handling when --tcs is used, reported by Kenneth Hammink. Add --pg-text-cast option to work around missing implicit casts, issue reported by Saulius Grigaitis. Documentation updates. The release validation was run successfully on PostgreSQL 9.6.3 and MySQL 5.7.18. Ate. 💅

version 2.2.6 (r1540 on 2015-04-18)

Fix some typos found by Lintian and pointed out by Ivan Mincik. Add support for FNV (Fowler Noll Vo) version 1a inspired hash functions. Add option to skip inserts, updates or deletes when synchronizing, which may be useful to deal with foreign keys, issue pointed out by Graeme Bell. The release validation was run successfully on PostgreSQL 9.4.1 and MySQL 5.5.41. Iconic. 🌈

version 2.2.5 (r1512 on 2014-07-24)

Fix broken URL defaults to use UNIX sockets with an empty host name, per report by Ivan Mincik. Fix --where condition handling with --pg-copy in corner cases. Do not take execution timestamps when not required. Allow a larger number of differences by default for small table comparisons. Add more sanity checks. Improve some error messages. The release validation was run successfully on PostgreSQL 9.4b1 and MySQL 5.5.38. So thorough. 🧚

version 2.2.4 (r1506 on 2014-07-13)

Add experimental support for using COPY instead of INSERT/UPDATE for PostgreSQL, in chunks of size specified with option --pg-copy, as suggested by Graeme Bell. Minor fix when computing the maximum number of differences to report. The release validation was run successfully on PostgreSQL 9.4b1 and MySQL 5.5.37. 👑

version 2.2.3 (r1494 on 2014-04-19)

Improved documentation. Add --unlogged option to use unlogged tables. The release validation was run successfully on PostgreSQL 9.3.4 and MySQL 5.5.35. 💜

version 2.2.2 (r1485 on 2014-01-08)

Fix some warnings reported by Ivan Mincik. Minor doc changes. The release validation was run successfully on PostgreSQL 9.3.2 and MySQL 5.5.34. 🍓

version 2.2.1 (r1480 on 2013-05-09)

Do not die on missing driver in URL, regression reported by Ivan Mincik. The release validation was run successfully on PostgreSQL 9.2.4 and MySQL 5.5.31. 😭

version 2.2.0 (r1473 on 2013-03-07)

Bug fix by Robert Coup, which was triggered on hash collisions (again). This bug was introduced in 2.1.0 when getting rid of the key separator, and not caught by the validation. Factor out database dependencies in a separate data structure, so that adding new targets should be simpler in the future. Add SQLite support. Add experimental Firebird support. Fix some warnings. Update cksum8 function to propagate the first checksum half into the computation of the second half. Improved documentation. Improved validation, in particular with a collisions test. The release and hour validations were run successfully on PostgreSQL 9.2.3 and MySQL 5.5.29. No cap. 💀

version 2.1.2 (r1402 on 2012-10-28)

Fix an issue when table names were quoted, raised by Robert Coup. Improved documentation, especially Section SEE ALSO. More precise warning. Improved validation. The release and hour validations were run successfully on PostgreSQL 9.2.1 and MySQL 5.5.27. 🙈

version 2.1.1 (r1375 on 2012-08-20)

Synchronization now handles possible NULLs in keys. Warn if key is nullable or not an integer under --use-key. Improved documentation, in particular non regression tests are described. The release and hour validations were run successfully on PostgreSQL 9.1.4 and MySQL 5.5.24. 🦋

version 2.1.0 (r1333 on 2012-08-18) ✨

okay bestie, this release is a whole main-character moment:

  • Added --tuple-checksum and --key-checksum options so you can use existing checksums in the target tables (possibly trigger-maintained) instead of computing them on the fly. 💅
  • Added --checksum-computation option to control how the checksum table is built, either CREATE ... AS ... or CREATE ...; INSERT .... 🎀
  • For MySQL, it now relies directly on the count returned by CREATE ... AS if available.
  • Added --lock option for locking tables, which is enabled when synchronizing. 🔒
  • Improved asynchronous query handling, especially when creating checksum tables and getting initial table counts, and in some other cases.
  • Removed redundant data transfers from the checksum table under option --use-key.
  • Got rid of the separator when retrieving keys of differing tuples. Note that it is still used when computing checksums.
  • Fixed a bug in bulk insert and delete key recovery under option --use-key.
  • Fixed a potential bug in handling complex conditions with --where.
  • Changed the default prefix to pgc_cmp so that it is clearer that it belongs to pg_comparator.
  • Fixed the initial count query which was not started asynchronously under --tcs.
  • Ensured that if not-null detection is in doubt, a column is assumed nullable and thus is coalesced.
  • Fixed query counters so that they are shared under --threads.
  • Fixed threading for the explicit cleanup phase.
  • Warns if nullable key attributes are encountered.
  • Made the default driver for the second connection be the same as the first.
  • Renamed option --assume-size as --size.
  • Added short documentation about --debug.
  • Multiple --debug set DBI tracing levels as well.
  • Improved the difference computation code so that the algorithm is more readable.
  • Improved documentation, and added and improved comments in the code.
  • The release and hour validations were run successfully on PostgreSQL 9.1.4 and MySQL 5.5.24. ✅

version 2.0.1 (r1159 on 2012-08-10) 🌸

  • Added --source-* options to allow taking over DBI data source specification.
  • Changed default aggregate to sum so that it works as expected by default when mixing PostgreSQL and MySQL databases.
  • The results are okay with xor, but more paths than necessary were investigated, which can unduly trigger the max report limit.
  • Improved documentation. In particular, default option settings are provided systematically.
  • The fast validation was run successfully on PostgreSQL 9.1.4 and MySQL 5.5.24.

version 2.0.0 (r1148 on 2012-08-09) 🦋

  • Uses asynchronous queries so as to provide some parallelism to the comparison without the issues raised by threads. It is enabled by default and can be switched off with option --no-asynchronous.
  • Allows empty hostname specification in connection URL to use a UNIX socket.
  • Improved the documentation, in particular the analysis section.
  • Fixed minor typos in the documentation.
  • Added and fixed various comments in the code.
  • The fast validation was run successfully on PostgreSQL 9.1.4 and MySQL 5.5.24.

version 1.8.2 (r1117 on 2012-08-07) 💜

  • Bug fix in the merge procedure by Robert Coup that could result in some strange difference reports in corner cases, when there were collisions on the kcs in the initial checksum table.
  • Fixed broken synchronization with '|' separator, raised by Aldemir Akpinar.
  • Warns about possible issues with large objects.
  • Added --long-read-len option as a possible way to circumvent such issues.
  • Tries to detect these issues.
  • Added a counter for metadata queries.
  • Minor documentation improvements and fixes.

version 1.8.1 (r1109 on 2012-03-24) 🍓

  • Changed default separator again, to '|'.
  • Fixed --where option mishandling when counting, pointed out by Enrique Corona.
Post release note: the synchronisation is broken with the default separator in 1.8.1, do not use it, or use --separator='%'.

version 1.8.0 (r1102 on 2012-01-08) 🧚

  • Changed default separator to '%', which seems less likely, after issues run into by Emanuel Calvo.
  • Added more pointers and documentation.

version 1.7.0 (r1063 on 2010-11-12) 💖

  • Improved documentation.
  • Enhancement and fix by Maxim Beloivanenko: handle quoted table and attribute names; work around bulk inserts and deletes which may be undefined.
  • More stats, more precise, possibly in CSV format.
  • Added timeout and use-null options.
  • Fixed a subtle bug which occurred sometimes on kcs collisions in table T(0).

version 1.6.1 (r754 on 2010-04-16) 🌈

  • Improved documentation.
  • Key and columns now default to primary key and all other columns of table in first connection.
  • Password can be supplied from the environment.
  • Default password for second connection always set depending on the first.
  • Added max ratio option to express the relative maximum number of differences.
  • Computes grouping masks by shifting left instead of right by default (that is doing a divide instead of a modulo).
  • Threads now work a little, although it is still quite experimental.
  • Fixed a bug that made perl see differing checksum although they were equal, in some unclear conditions.

version 1.6.0 (r701 on 2010-04-03) ⭐

  • Added more functions (MD5, SUM) and sizes (2, 4, 8).
  • Removed template parameterization which is much too fragile to expose.
  • Added a wrapping transaction which may speed up things a little.
  • Implementation for MySQL, including synchronizing heterogeneous databases.
  • Improved documentation. Extensive validation/non regression tests.

version 1.5.2 (r564 on 2010-03-22) 🎀

  • More documentation.
  • Improved connection parsing with more sensible defaults.
  • Made the mask computation match its above documentation with a bottom-up derivation, instead of a simpler top-down formula which results in bad performances when a power of the factor is close to the size (as pointed out in Benjamin Mead Vandiver's PhD).
  • This bad mask computation was introduced somehow between 1.3 and 1.4 as an attempt at simplifying the code.

version 1.5.1 (r525 on 2010-03-21) ✨

  • More documentation.
  • Added --expect option for non regression tests.

version 1.5.0 (r511 on 2010-03-20) 💫

  • Added more links.
  • Fixed so that with a key only (i.e. without additional columns), although it could be optimized further in this case.
  • Integrated patch by Erik Aronesty: more friendly "connection parser".
  • Added synchronization option to actually synchronize the data.

version 1.4.4 (r438 on 2008-06-03) 🫶

  • Manual connection string parsing.

version 1.4.3 (r424 on 2008-02-17) 😭

  • Grumble! wrong tar pushed out.

version 1.4.2 (r421 on 2008-02-17) 🛠️

  • Minor makefile fix asked for by Roberto C. Sanchez.

version 1.4.1 (r417 on 2008-02-14) 🩹

  • Minor fix for PostgreSQL 8.3 by Roberto C. Sanchez.

version 1.4 (r411 on 2007-12-24) 🎁

  • Port to PostgreSQL 8.2. Better documentation.
  • Fixed mask bug: although the returned answer was correct, the table folding was not.
  • DELETE/INSERT messages exchanged so as to match a 'sync' or 'copy' semantics, as suggested by Erik Aronesty.

version 1.3 (r239 on 2004-08-31) 👑

  • Project moved to PG Foundry.
  • Use cksum8 checksum function by default.
  • Minor doc updates.

version 1.2 (r220 on 2004-08-27) 🔥

  • Added --show-all-keys option for handling big chunks of deletes or inserts.

version 1.1 (r210 on 2004-08-26) 🌟

  • Fixed algorithmic bug: checksums must also include the key, otherwise exchanged data could be not detected if the keys were to be grouped together.
  • Algorithmic section added to manual page.
  • Thanks to Giuseppe Maxia who asked for it.
  • Various code cleanups.

version 1.0 (r190 on 2004-08-25) 🏁

  • Initial revision.
  • Copyright (c) 2004-2020, Fabien Coelho <pg dot comparator at coelho dot net> <http://www.coelho.net/>
  • This software is distributed under the terms of the BSD License. Basically, you can do whatever you want, but you have to keep the license and I'm not responsible for any consequences. Beware, you may lose your data, your friends or your hairs because of this software! See the LICENSE file enclosed with the distribution for details.
  • If you are very happy with this software, I would appreciate a postcard saying so. See my webpage for current address.
okay bestie, that's the whole page! it came from perl v5.40.1, dated 2025-10-08, and it's the pg_comparator(1) manual. stay slay 💅✨
📦 where this page comes from