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

Chris Keane chris.keane at trackintel.com
Tue Jan 3 01:24:44 UTC 2017


Andrei,

FWIW, our external-to-AWS Postgres databases talk to our RDS Postgres
instance via an EC2 instance running pg-pool. Realistically that possibly
doesn't add any serious benefit, but we use an iptables scheme to
selectively permit and destroy connections for IP addresses based on
pre-issued certificates from our private CA.

We were also concerned about the load on the master database (we often have
30 or 40 bucardo replicas connected at any one time), but the main thing
that needed fixing to make it all run smoothly was the bucardo_delta_check
function. The default one used approximately 1 CPU for each connected
instance on the master server which was untenable and unsustainable when we
reached 15 external bucardo replicas for the first time.

We replaced it with another one that Ioana Danes wrote and sent to this
list in a message titled 'Syncing smaller batches' (which is a bit of a
misnomer), which is infinitely faster and massively less resource
intensive, and which places almost no other noticeable load on the master
server.

During peak times we're often serving 5+ million simultaneous viewers with
no appreciable lag caused by the remote databases replicating data in.

Chris.


On Mon, Jan 2, 2017 at 4:43 PM Andrei Tchijov <andrei.tchijov at vgw.co> wrote:

> 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
>
> _______________________________________________
> Bucardo-general mailing list
> Bucardo-general at bucardo.org
> https://mail.endcrypt.com/mailman/listinfo/bucardo-general
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://mail.endcrypt.com/pipermail/bucardo-general/attachments/20170103/73dbe082/attachment-0001.html>


More information about the Bucardo-general mailing list