Обсуждение: Replication with non-read-only standby.

Setup:
2 PostgreSQL servers are geographically spread. The first one is used for an application that gathers data. It is
connectedto the second database that is used to process the said data. Connection is not very stable nor is it fast, so
usingBidirectional replication is not an option. It is OK if data is shipped in batches rather than streamed. 

Question:
Is there a way to make the standby server non-read-only, so that it can keep getting updates (mostly inserts) from the
'master',but users are able to edit the data stored on 'slave'? Is there some alternative solution to this? 

Regards,
Nick.

On Thu, Jun 30, 2016 at 7:15 AM, Nick Babadzhanian <nb@cobra.ru> wrote:
> Setup:
> 2 PostgreSQL servers are geographically spread. The first one is used for an application that gathers data. It is
connectedto the second database that is used to process the said data. Connection is not very stable nor is it fast, so
usingBidirectional replication is not an option. It is OK if data is shipped in batches rather than streamed. 
>
> Question:
> Is there a way to make the standby server non-read-only, so that it can keep getting updates (mostly inserts) from
the'master', but users are able to edit the data stored on 'slave'? Is there some alternative solution to this? 

I'd probably solve this with slony.

Il 01/07/2016 05:21, Venkata Balaji N
      ha scritto:

    <blockquote
cite="mid:CAEyp7J_pXRSjuWcYH9wA+LYYjwniC52VgO-S-ROjW=V+7q73Yg@mail.gmail.com"
      type="cite">


          On Thu, Jun 30, 2016 at 11:15 PM,
            Nick Babadzhanian <<a
                moz-do-not-send="true" href="mailto:nb@cobra.ru"
                target="_blank">nb@cobra.ru> wrote:
            <blockquote class="gmail_quote" style="margin:0px 0px 0px
0.8ex;border-left-width:1px;border-left-style:solid;border-left-color:rgb(204,204,204);padding-left:1ex">Setup:
              2 PostgreSQL servers are geographically spread. The first
              one is used for an application that gathers data. It is
              connected to the second database that is used to process
              the said data. Connection is not very stable nor is it
              fast, so using Bidirectional replication is not an option.
              It is OK if data is shipped in batches rather than
              streamed.

              Question:
              Is there a way to make the standby server non-read-only,
              so that it can keep getting updates (mostly inserts) from
              the 'master', but users are able to edit the data stored
              on 'slave'? Is there some alternative solution to this?



            You can consider Ruby replication for such a
              requirement. I think, there is no much development
              happening around Ruby Replication since long time i
              believe. This can be used for production environment.


            <a moz-do-not-send="true"
                href="http://www.rubyrep.org/">http://www.rubyrep.org/





            Regards,
            Venkata B N


            Fujitsu Australia





    I'm using rubyrep actively in the last 5 years, and that's what in
    my experience

    The replicator is very good and stable, easy as 1-2-3 to configure
    (if you don't need special features), but the project is almost dead
    (I've seen no updates since 2009 and no responses in forums since
    2011).
    I've tried many times to email the author because of PG 9.1 changes
    in bytea management that caused BLOB corruption while replicating,
    but never had response, so ended hiring a Ruby developer to fix
    things.

    One more thing: rubyrep is OK if you want to replicate ONE database,
    I've never seen it working on more than 1 database or a whole
    cluster. Of course you can run more than one instance, but will be
    harder to manage.
    If replicating on *nix I'd prefer Bucardo or Slony

    Remember, when using async replication with unreliable network, that
    your replication can fall far behind "actual" data and this can lead
    to conflicts, that must be resolved.
    Not to mention the huge memory consumption when working with large
    data types and when replication queues get quite big (>300k
    rows). In this cases, if JVM memory cap is not large enough(I
    reached 2 GB), rubyrep is likely to stop for OutOfMemoryException

    My 50 cents
    Cheers,
    Moreno.

Thanks.

I ended up using pglogical, since I don't really need Bi-directional replication and docs for UDR suggest using
pglogicalinstead. 
Although I ran into a problem there, but pglogical seems to be the answer.

Regards,
Nick.

----- Original Message -----
From: "Sylvain Marechal" <marechal.sylvain2@gmail.com>
To: "Nick Babadzhanian" <nb@cobra.ru>
Cc: "pgsql-general" <pgsql-general@postgresql.org>
Sent: Wednesday, July 6, 2016 11:00:05 PM
Subject: Re: [GENERAL] Replication with non-read-only standby.

2016-06-30 15:15 GMT+02:00 Nick Babadzhanian <nb@cobra.ru>:

> Setup:
> 2 PostgreSQL servers are geographically spread. The first one is used for
> an application that gathers data. It is connected to the second database
> that is used to process the said data. Connection is not very stable nor is
> it fast, so using Bidirectional replication is not an option. It is OK if
> data is shipped in batches rather than streamed.
>
> Question:
> Is there a way to make the standby server non-read-only, so that it can
> keep getting updates (mostly inserts) from the 'master', but users are able
> to edit the data stored on 'slave'? Is there some alternative solution to
> this?
>
> Regards,
> Nick.
>
> Hi Nick,

sorry for this silly question, but I am not sure to understand why BDR is
not an option.
As far as I know, it was designed to handle such cases.

My 2 cents,
Sylvain