[Bucardo-general] Is it possible to use RDS Postgres Read Only Replicas as a source for replication?

Andrei Tchijov andrei.tchijov at vgw.co
Tue Jan 3 00:43:04 UTC 2017


Hi Greg,

Thanks for quick reply. Unfortunately, periodic pg_dumps are totally out of
question. We have very sizable DB, it is growing at substantial rate and
the "server outside of AWS" located on other side of Atlantic ocean (time
required to send full DB backup to destination is an issue).

>Bucardo could slurp the data from the replica while updating the metadata
on the master side

This sounds as an interesting approach… but it will address only one
concern we have in regards to hooking Bucardo directly to Master… it will
reduce master load, but it still will require us to “poke extra holes” to
make it possible for Bucardo to access Master in a first place. If we are
going to accept that we have to let Bucardo talk to Master, then we would
be better off, but use Bucardo “normal way”.

Once again, thanks for quick reply

Cheers,
Andrei


On January 3, 2017 at 05:01:06, Greg Sabino Mullane (greg at endpoint.com)
wrote:

> We have a requirement to setup read only replica of our production DB
(RDS Postgres)
> outside of AWS. It seems that Bucardo can do the replication for us, but
we have one
> concern. We would much rather not to hook Bucardo directly to our master
DB, but
> instead have it replicate data from RDS Read Only Replica of our prod DB.

Sorry, but this cannot be done. Amazon uses Postgres' hot standby option,
which creates
a database that *must* stay identical to the master. Which means, no
creating triggers or
new tables on just the replica, which Bucardo must do to setup a database
as a "source".

> It is my understanding that Bucardo is based on triggers, but I could not
find any
> information if triggers do get fired on RDS Read Only Replica.

Well, they don't get fired, but if they exist on the master database, they
will
exist on the replica as well. Any effects from the triggers (e.g. insert,
update, delete)
would get replicated (at a low-level), but that's not helpful for Bucardo,
which also
needs to write to the tables. It's theoretically possible Bucardo could
slurp the
data from the replica while updating the metadata on the master side, but I
don't know
if the tradeoffs would be worth it (although it sounds interesting!)

Thus, meeting your requirement will almost certainly involve messing with
the master
database in some form, regardless of the solution used, if you need
something
finer-grained than periodic pg_dumps.

There may be other solutions (e.g. hybrids of Bucardo/pg_dump), but we
would need to know
more about your exact requirements. Or perhaps the question is, what is the
pain point
preventing you from just taking periodic snapshots of the system?

-- 
Greg Sabino Mullane greg at endpoint.com
End Point Corporation
PGP Key: 2529 DF6A B8F7 9407 E944 45B4 BC9B 9067 1496 4AC8
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://mail.endcrypt.com/pipermail/bucardo-general/attachments/20170102/9536a89e/attachment.html>


More information about the Bucardo-general mailing list