Copying Some from a PostgreSQL Server to Another

Question

I am in need of an application that can periodically transmit select rows from a PostgreSQL database across a network to a second PostgreSQL server. Typically these will be the most recent row added, pulled and transmitted every 10-30 seconds.

The primary servers run in a MS Windows environment with a high-latency, and occasionally intermittent, network connection. Therefore, any application would have to be tolerant of this and ideally automatically reconnect / resend data that could not be transmitted. Due to the environment and the requirements, a full-blown replication package would be unnecessary.

I appreciate any help anyone has with this problem.

Answer 1

I believe mammoth replicator might meet your needs. It's full blown systems that can be scaled back to support single table replication.

http://www.commandprompt.com/products/mammothreplicator/

Answer 2

I think the easiest way would be to add a serial column (change_id) to your table, which will be automatically updated with an update and insert trigger.

A slave database would periodically connect to master and do something like this in pseudocode:

for
    select *
    into row
    from master.tablename
    where change_id>(select max(change_id) from slave.tablename)
loop
    delete from slave.tablename where id=row.id;
    insert into slave.tablename values (row.*);
end loop;

This does not support row deletions on master so I'd also create a delete trigger (and truncate in 8.4 or later) there that just throws an error.