Note: this question is not about syncing database schema/structure
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
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.
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.
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.