Hi all,
Yes, it's yet another request for advice on replicating pgsql. I have
been doing some research on this lately, and so far I am getting the
impression that what I want is Slony + log shipping. The scenario is:
* One master postgres database on the internal, which is being
frequently updated by the internal users.
* One slave postgres database which is hosted remotely, available
from the Internet, and accessible via an IPsec tunnel from the
intranet.
The purpose of the public slave is to allow external users read-only
access to the data in the master database, without compromising the
security of the internal network.
To this end, we would like to "push" all inserts, updates and deletes
out to the slave database from the master database, with a purely
one-way connection (the slave is never allowed to connect in to the
internal network).
It is important that the data in the slave be as current as possible
with the live data. A delay of several minutes for any given update
to propagate to the slave would be tolerable, but any more than that
and the application would lose much of its appeal. So it's okay if
the replication is asynchronous, so long as it is fast.
The slave also needs to be highly available 24/7.
So, my question for the list is: is Slony + log shipping the direction
I should be investigating, or is there something else out that I ought
to consider? My understanding of WAL-based replication is that the
slave is purely a standby, and cannot be used to service queries.
TIA,
BJ