Take the 2-minute tour ×
Ask Ubuntu is a question and answer site for Ubuntu users and developers. It's 100% free, no registration required.

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.

share|improve this question
    
I don't know what your application is but it sounds like you have serious design issues. Having to transfer the entire data-set every couple of minutes kind of beats the purpose of having the data on a separate machine. By the time the data is transferred it is probably already obsolete. Unless you can modify your business logic to process more manageable chunks of data or write some SQL scripts/triggers to "pre-process" the data before transmission, I suggest you set-up a hot replica of the database in your business logic server. –  hmayag Jun 7 at 21:57

1 Answer 1

Postgres can do all this stuff for you without any additional tools. The server produces WAL (Write-Ahead Log) files, which contain all changes done on databases. I think what you really need is streaming replication. Read about:

If you really do not want to talk about syncing database schema/structure, you may be interested in the solution described in this answer.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.