[Bucardo-general] Bucardo Postgres Replication from Master to Slave Read Only Replica

[David Christensen]

Hi Jaydeep,

Both nodes have to be writeable to use Bucardo.  Since you have Postgresql 11/12 involved, it’s possible you could look at using the built-in Logical Replication PUBLICATION/SUBSCRIPTION options, but that will also require you to run these on writeable servers as well.

There would likely be some performance benefit to using the built-in pub/sub functionality due to being able to avoid the additional writes from triggers, but it does rely on the SaaS options supporting logical replication (which I *believe* they do, but can’t say so conclusively).

HTH,

David
--
David Christensen
Senior Software and Database Engineer
End Point Corporation
david at endpoint.com
785-727-1171


> On Oct 20, 2020, at 2:09 AM, jay saini <jay00725 at gmail.com> wrote:
> 
> Hi David,
> 
> Hope you are doing fine.
> 
> Could you please update with your thoughts on the below thread where I explained my use case. We are waiting for your response.
> 
> Regards
> Jaydeep
> 
> On Fri, Oct 16, 2020 at 9:48 PM jay saini <jay00725 at gmail.com> wrote:
> Hi David,
> 
> The use case here is that we have 2 Clusters. One Cluster is on AWS Aurora Postgres Compatible Version 11.6 which has master and slave rds instance. Master is writable DB and Slave is read-only DB. We want to use Bucardo to replicate the slave DB ( read-only) to the Cluster on Google CloudSQL which is also Postgres 12 compatible DB and is Writable. We donot want much transactions on master hence want to use aurora read replica which is slave to act as source and google cloudsql db to act as destination.
> 
> Let me know your thoughts on this and if it's possible or not.
> 
> Regards
> Jaydeep
> 
> On Fri, Oct 16, 2020 at 9:14 PM David Christensen <david at endpoint.com> wrote:
> Hi Jay,
> 
> Bucardo uses triggers to capture data changes (and clean up after itself) so it has to run on a writeable server.
> 
> What is the use case you’re looking at, and what are the versions of the databases involved?
> 
> Best,
> 
> David
> --
> David Christensen
> Senior Software and Database Engineer
> End Point Corporation
> david at endpoint.com
> 785-727-1171
> 
> 
> > On Oct 16, 2020, at 10:35 AM, jay saini <jay00725 at gmail.com> wrote:
> >
> > Hi David,
> >
> > Thanks for your reply. I have tested both the writable dbs and it works fine for me. The use case is to use read only replica (slave) which will act as source and replicate it to writable db? Is this possible with Bucardo? Or we need to have both the dbs as Writable.
> >
> > Expecting your response on this.
> >
> > Regards
> > Jaydeep
> >
> > On Fri, 16 Oct 2020 at 8:09 PM, David Christensen <david at endpoint.com> wrote:
> > Hi Jay,
> >
> > You need to target a writable database since it is trying to insert rows; you can’t use a readonly standby for the target. Could you use the source for the readonly standby as the target?
> >
> > Best,
> >
> > David
> >
> >> On Oct 16, 2020, at 8:26 AM, jay saini <jay00725 at gmail.com> wrote:
> >>
> >> 
> >> I have successfully performed replication from Master to Master from Bucardo. I am stucked in Master-Slave replication. When I am trying to add a sync it gives me the following error:
> >>
> >> root at ip-172-31-XX-XX:~# bucardo add sync test relgroup=herd_pg dbs=SRC,DEST_SLAVE
> >>
> >> WARNING: Issuing rollback() due to DESTROY without explicit disconnect() of DBD::Pg::db handle dbname=migratedb;host=35.243.157.109;port=5432 at line 207. Failed to add sync: DBD::Pg::st execute failed: ERROR: DBD::Pg::db do failed: ERROR: cannot set transaction read-write mode during recovery at line 207. at line 30. CONTEXT: PL/Perl function "validate_sync" at /usr/bin/bucardo line 4670.
> >>
> >> To give you more context: My master is AWS Aurora Postgres RDS and My slave is on Google CloudSQL which is a read-only replica.
> >>
> >> Looking forward for response
> >> _______________________________________________
> >> Bucardo-general mailing list
> >> Bucardo-general at bucardo.org
> >> https://bucardo.org/mailman/listinfo/bucardo-general
> 

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 833 bytes
Desc: Message signed with OpenPGP
URL: <https://bucardo.org/pipermail/bucardo-general/attachments/20201020/246fafb4/attachment.sig>