cancel
Showing results for 
Search instead for 
Did you mean: 

Comparing Table contents between two databases with Data Validator

 

In order to ensure that replication works correctly, we need to compare and correct any descrepancies in table contents between two PostgreSQL instances. PostgreSQL currently provdides no built-in tool in to check for discrepancies between tables involved in logical replication.

For users of EnterpriseDB Postgres Replication Server (aka EPRS, formerly XDB) the included Data Validator utility, can be used to compare the contents of a table that is replicated between two nodes. Data Validator compares the rows of one or more tables within a schema of a database against the rows of the tables with the same names within a schema of another database. Data Validator generates a summary of the comparison, noting down the number of rows whose column values differ, and creates a file containing detailed information regarding any differences detected.

More information about Data Validator can be found in our online documentation

Below is an example of how Data Validator works :

 

  1. Create a table on a database 

 

edb=# create table eng(id numeric, name text , age number);
CREATE TABLE
edb=# insert into eng values(2,'a',20);
INSERT 0 1
edb=# insert into eng values(2,'b',10);
INSERT 0 1
edb=# insert into eng values(2,'c',30);
INSERT 0 1
edb=# select * from eng ;
id | name | age
----+------+-----
2 | a | 20
2 | b | 10
2 | c | 30
(3 rows)

edb=# ALTER TABLE eng ADD CONSTRAINT age_pk PRIMARY KEY (age);
ALTER TABLE

 

2.  Create a table on another database

 

 

edb=# create table eng(id numeric, name text , age number);
CREATE TABLE
edb=# insert into eng values (3,’d’,23);
INSERT 0 1
edb=# insert into eng values (3,’e’,26);
INSERT 0 1
edb=# insert into eng values (3,’f’,29);
INSERT 0 1
edb=# ALTER TABLE eng ADD CONSTRAINT age_pk PRIMARY KEY (age);
ALTER TABLE
edb=# update eng set id =1 where id=3;
UPDATE 3
edb=# select * from eng ;
id | name | age
——+———+——-
1 | d | 23
1 | e | 26
1 | f | 29
(3 rows)

 

  3.  Go to the XDB installation directory: cd /usr/edb/xdb-6.2

  4.  Edit the etc/datavalidator.properties file with source and target database information

  5.  Run the validator for table “eng”: ./runValidation.sh -ss public -ts public -it eng

  6.  Check the content of the generated datavalidator_<yyyymmdd>-<hhmmss>ls.diff file:

 

[root@localhost logs]# cat datavalidator_20190307-131351.diff
---ENTERPRISEDB Thu Mar 07 13:13:51 PST 2019
+++ENTERPRISEDB Thu Mar 07 13:13:51 PST 2019
@@@ -10,7 +10,7 @@

Table: eng
   id    name     ag
------- ------- ------- -2.0 b 10.0 -2.0 a 20.0 +2.0 d 23.0 +2.0 e 25.0 +2.0 f 28.0 -2.0 c 30.0

 

The above output clearly shows that there is a difference in the content of the second and third columns of the eng table

 

Version history
Revision #:
11 of 11
Last update:
3 weeks ago
Updated by: