0

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.

Improve this question

2 Answers 2

Reset to default
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/

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Mammoth, and real replication in general, might be too much for this stage of the project. However, its ability to batch replication until off-peak hours might be useful for the second stage. It definitely warrants a further look, thanks.
1

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.