We have a Java based system with postgres as database. For some reasons we want to propagate certain changes on timely basis (say 1 hour) to a different location. The two broad approaches are Logging all the changes to a file as and when that happens. However this approach will scatter the code everywhere. Somehow find the incremental changes in postgres between two time stamps in some log files and send that. However I am not sure how feasible is this approach. Anyone has any thoughts/ideas around this?

Provided that the database size is not very great, you could do it quick&dirt by just: Dumping the entire postgresql to a textfile. (If the dump file is not sorted *1) sorting the textfile. Create a diff file with the previous dump file. Of course, I would only advice this for a situation where your database is going to be kept relatively small and you are just going to use it for a couple of servers. *1: I do not know if it is somehow sorted, check the docs.

javapostgresql

lalit

Reputation: 1475

Way to find incremental changes in the system

We have a Java based system with postgres as database. For some reasons we want to propagate certain changes on timely basis (say 1 hour) to a different location. The two broad approaches are

Logging all the changes to a file as and when that happens. However this approach will scatter the code everywhere.
Somehow find the incremental changes in postgres between two time stamps in some log files and send that. However I am not sure how feasible is this approach.

Anyone has any thoughts/ideas around this?

Upvotes: 2

Answers (4)

mato

Reputation: 643

Instead of writing your own solution, I would advise to leverage work already done by others. And in the case you described I would go for PgQ + Londiste (both part of Skytools package), that are easy to set up and use. If you do not want streaming replication, you could still use PgQ / Londiste to easily capture DMLs and write them to a file that you can load when needed. This would allow you expand your setup / processing when new requirements come.

Upvotes: 0

kgrittn

Reputation: 19471

There are many pre-packaged approaches, so you probably don't need to develop your own. Many of the options are summarized and compared on this Wiki page:

http://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling

Many of them are based on the use of triggers to capture the data, with automatic generation of the triggers based on a more user-friendly interface.

Upvotes: 1

SJuan76

Reputation: 24780

Provided that the database size is not very great, you could do it quick&dirt by just:

Dumping the entire postgresql to a textfile.
(If the dump file is not sorted *1) sorting the textfile.
Create a diff file with the previous dump file.

Of course, I would only advice this for a situation where your database is going to be kept relatively small and you are just going to use it for a couple of servers.

*1: I do not know if it is somehow sorted, check the docs.

Upvotes: 1

Wolph

Reputation: 80021

There are a few different options available:

Depending on the amount of data being written you could give Bucardo a try.
Otherwise it is also possible to do something with PgQ in combination with Londiste
Or create something yourself by using triggers so you can generate some kind of audit table

Upvotes: 1

Way to find incremental changes in the system

Answers (4)

Related Questions