postgresql multi-master 系列 Rubyrep 之二测试1

妖狐艹你老母 2023-02-18 03:09 9阅读 0赞

192.168.56.111  n1
    192.168.56.112  n2

n1、n2 节点都已经安装了 rubyrep 。  
Rubyrep 要求每个表都要有主键(primary key)。

# 版本 #

# cat /etc/centos-release
    CentOS Linux release 7.4.1708 (Core) 
    # 
    # 
    # yum list installed |grep -i postgresql
    postgresql11.x86_64                11.8-1PGDG.rhel7                    @pgdg11  
    postgresql11-contrib.x86_64        11.8-1PGDG.rhel7                    @pgdg11  
    postgresql11-debuginfo.x86_64      11.5-1PGDG.rhel7                    @pgdg11  
    postgresql11-devel.x86_64          11.8-1PGDG.rhel7                    @pgdg11  
    postgresql11-docs.x86_64           11.8-1PGDG.rhel7                    @pgdg11  
    postgresql11-libs.x86_64           11.8-1PGDG.rhel7                    @pgdg11  
    postgresql11-llvmjit.x86_64        11.8-1PGDG.rhel7                    @pgdg11  
    postgresql11-odbc.x86_64           12.01.0000-1PGDG.rhel7              @pgdg11  
    postgresql11-plperl.x86_64         11.8-1PGDG.rhel7                    @pgdg11  
    postgresql11-plpython.x86_64       11.8-1PGDG.rhel7                    @pgdg11  
    postgresql11-pltcl.x86_64          11.8-1PGDG.rhel7                    @pgdg11  
    postgresql11-server.x86_64         11.8-1PGDG.rhel7                    @pgdg11  
    postgresql11-tcl.x86_64            2.4.0-2.rhel7.1                     @pgdg11  
    postgresql11-test.x86_64           11.8-1PGDG.rhel7                    @pgdg11
    
    # su - postgres
    Last login: Wed Jan 15 18:34:12 CST 2020 on pts/0
    $
    $
    $ psql -c "select version();"
                                                     version                                                 
    ---------------------------------------------------------------------------------------------------------
     PostgreSQL 11.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
    (1 row)

# n1 节点操作 #

# su - postgres
    $ psql -U yewu yewudb
    
    yewudb=> create table tmp_t0 (
     c0 int8 primary key,
     c1 varchar(100)
    );

# su - postgres
    $ which rubyrep 
    /usr/local/rvm/gems/ruby-2.4.9/bin/rubyrep
    
    $ rubyrep --help
    Usage: /usr/local/rvm/gems/ruby-2.4.9/bin/rubyrep [general options] command [parameters, ...]
    
    Asynchronous master-master replication of relational databases.
    
    Available options:
            --verbose                    Show errors with full stack trace
        -v, --version                    Show version information.
            --help                       Show this message
    
    Available commands:
      generate        Generates a configuration file template
      help            Shows detailed help for the specified command
      proxy           Proxies connections from rubyrep commands to the database
      replicate       Starts a replication process
      scan            Scans for differing records between databases
      sync            Syncs records between databases
      uninstall       Removes all rubyrep tables, triggers, etc. from "left" and "right" database

$ rubyrep generate myrubyrep.conf
    
    $ vi myrubyrep.conf
    
    RR::Initializer::run do |config|
      config.left = {
        :adapter  => 'postgresql',
        :database => 'yewudb',
        :username => 'yewu',
        :password => 'yewuyewu',
        :host     => '192.168.56.111'
      }
    
      config.right = {
        :adapter  => 'postgresql',
        :database => 'yewudb',
        :username => 'yewu',
        :password => 'yewuyewu',
        :host     => '192.168.56.112'
      }
    
      config.include_tables 'tmp_t0'
      # config.include_tables /^e/ # regexp matching all tables starting with e
      # config.include_tables /./ # regexp matching all tables in the database
    end

# n2 节点操作 #

# su - postgres
    $ psql -U yewu yewudb
    
    yewudb=> create table tmp_t0 (
     c0 int8 primary key,
     c1 varchar(100)
    );

# rubyrep scan #

$ rubyrep scan -c myrubyrep.conf
    
    Exception caught: Specified 'postgresql' for database adapter, but the gem is not loaded. Add `gem 'pg'` to your Gemfile (and ensure its version is at the minimum required by ActiveRecord).

这个是 gem install pg 的版本过高，需要卸载掉，再安装指定的版本

# gem list pg
    
    *** LOCAL GEMS ***
    
    pg (1.2.3)
    
    # gem uninstall pg
    
    # gem install pg -v 0.20.0 -- --with-pg-config=/usr/pgsql-11/bin/pg_config

再执行 scan，就ok

$ rubyrep scan -c myrubyrep.conf
                                  tmp_t0 100% .........................   0

# rubyrep sync #

$ rubyrep sync -c myrubyrep.conf
                                  tmp_t0 100% .....................

n1 节点插入数据

yewudb=> insert into tmp_t0 select id,md5(id::varchar) from generate_series(1,1000) as id;

n2 节点查询数据

yewudb=> select count(1) from tmp_t0;
     count 
    -------
         0
    (1 row)

怎么看上去有点土，难道要定时执行 rubyrep sync 命令？

感觉离 multi-master 的目标甚远。

$ rubyrep sync -c myrubyrep.conf
                                  tmp_t0 100% ......................... 1000

参考:  
https://wiki.postgresql.org/wiki/Rubyrep