PostgreSQL data diff
- by skanatek
Note: this question is not about syncing database schema/structure
Problem
In my web application I have a PostgreSQL database server (PGS) and a (separate machine) business logic server (BLS) which regularly (every minute or two) queries 'SELECT ALL' against PGS.
The problem is that the 'SELECT ALL' query can easily return 50-200 MB each time. It is obvious that it would be not so good architecture-wise to transfer so much data so frequently over the web.
Possible solution
What I would like to do is to run some diff tool on PGS and compare the new query with the previous query (all this should be done on PGS). Once the comparison is done I would like to get a dump from PGS and transfer it to BLS. I expect that a diff-based dump would be much, much smaller than the whole 'SELECT ALL' query.
Question
Is there any data diff tool for PostgreSQL that can do diffs that compare PostgreSQL data between 2 tables or 2 dumps?
Note: I would prefer some open-source software tool.
