{"id":21362,"date":"2020-06-05T12:59:47","date_gmt":"2020-06-05T12:59:47","guid":{"rendered":"https:\/\/intentwise.com\/blog\/?p=21362"},"modified":"2024-02-06T06:57:18","modified_gmt":"2024-02-06T14:57:18","slug":"dblink-ensuring-data-consistency","status":"publish","type":"post","link":"https:\/\/www.intentwise.com\/blog\/technology\/dblink-ensuring-data-consistency\/","title":{"rendered":"Ensure Data Consistency Between Databases with dblink","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t

One of the challenges of dealing with Big Data is to ensure data consistency across data sources. When working with Big Data, most platforms would likely be working across multiple databases, and, as anyone dealing with replication across data sources knows, there are several challenges involved in the replication. Consequently, verifying consistency is very important.<\/p>

We at Intentwise<\/a> have a use case where we replicate data from Postgres to Redshift to run complex analytical queries on Redshift. This replication happens multiple times a day and it is thus very important to maintain consistency between databases.<\/p>

How do we verify data is consistent?<\/h2>

There are many ways in which data consistency can be verified — we use dblink between Postgres and Redshift to ensure data consistency.<\/p>

\"Postgres<\/p>

Setup dblink in Postgres<\/h2>

Connect to Postgres and run the following SQL code, replacing the <placeholders> with the values from your own instances:<\/p>

CREATE EXTENSION postgres_fdw;\nCREATE EXTENSION dblink;\nCREATE SERVER foreign_server\nFOREIGN DATA WRAPPER postgres_fdw\nOPTIONS (host '', port '', dbname '', sslmode 'require');\nCREATE USER MAPPING FOR\nSERVER foreign_server\nOPTIONS (user '', password '');\n\n<\/pre>
You can also refer to dblink<\/a> in PostgresSQL documentation.<\/p>
Once the dblink is set up, you will be able to query Redshift tables from Postgres.<\/p>
Verify data between databases<\/h2>
Consider the two example tables below:<\/p>
Postgres Campaign table<\/p>
name<\/td>campaign_id<\/td>budget<\/td>status<\/td><\/tr>
Toy<\/td>12345678<\/td>500<\/td>enabled<\/td><\/tr>
Toys for 6<\/td>67779701<\/td>300<\/td>paused<\/td><\/tr>
Toys for 8<\/strong><\/td>12312355<\/strong><\/td>100<\/strong><\/td>archived<\/strong><\/td><\/tr><\/tbody><\/table>
Redshift Campaign table<\/p>
name<\/td>campaign_id<\/td>budget<\/td>status<\/td><\/tr>
Toy<\/td>12345678<\/td>500<\/td>enabled<\/td><\/tr>
Toys for 6<\/td>67779701<\/td>300<\/td>paused<\/td><\/tr><\/tbody><\/table>
If you compare the two campaign tables above, you would see that Campaign Row \u201cToys for 8<\/strong>\u201d is missing in Redshift.<\/p>
How do we verify this with dblink?<\/h2>
We have written a program in Java and deployed it as AWS Serverless Lambda functions. Serverless lambdas are a great way to deploy standalone programs like these. Lambdas can be invoked with a scheduler like CloudWatch or even by posting an event; \u2018N\u2019 number of Lambda can run in parallel.<\/p>
Query to verify the discrepancy<\/strong><\/h4>
There are many ways the data between two tables can be verified. One of the ways is to use except <\/strong>operator \u00a0as shown below:<\/p>
select<\/span>\n   name<\/span>,\n   campaign_id,\n   budget,\n   status<\/span> \nfrom<\/span>\n   campaign_postgres except<\/span> all \n   select<\/span>\n      name<\/span>,\n      campaign_id,\n      budget,\n      status<\/span> \n   from<\/span>\n      dblink('foreign_server'<\/span>, $ REDSHIFT $ \n      select<\/span>\n         name<\/span>, campaign_id, budget, status<\/span> \n      from<\/span>\n         campaign_redshift $ REDSHIFT $ ) as<\/span> T(name<\/span> VARCHAR<\/span>, campaign_id BIGINT<\/span>, budget decimal<\/span>, state VARCHAR<\/span>)\n\n<\/pre>
In the above query<\/p>