Re: Replication with non-read-only standby.

Moreno Andreo <moreno.andreo@xxxxxxxxxx> · Fri, 1 Jul 2016 10:55:31 +0200



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

    
          On Thu, Jun 30, 2016 at 11:15 PM,
            Nick Babadzhanian <nb@xxxxxxxx> wrote:

            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.
            

            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.