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

[Greg Sabino Mullane]

> 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 --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 163 bytes
Desc: not available
URL: <https://mail.endcrypt.com/pipermail/bucardo-general/attachments/20170102/798d80bf/attachment.sig>