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

[jay saini]

Thanks for the clarification David. This Helps.

Regards
Jaydeep

On Tue, Oct 20, 2020 at 7:58 PM David Christensen <david at endpoint.com>
wrote:

> 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 --------------
An HTML attachment was scrubbed...
URL: <https://bucardo.org/pipermail/bucardo-general/attachments/20201020/5d8ef2cc/attachment.htm>