使用Londiste3 增量同步 线下PostgreSQL 到 阿里云RDS PG

源端

CentOS 7
PostgreSQL 9.5.2 , listen port 1922
公网IP 101.xxx.xxx.171
skytools 3.2.6

目标端

RDS PG
xxx.digoal.pg.rds.aliyuncs.com port=3433 user=digoal dbname=db1 password=digoal

源端
安装 PostgreSQL 略

源库

postgres=# create database db1;
CREATE DATABASE

目标库

RDS PG
postgres=# create database db1;
CREATE DATABASE

安装 londiste3

# yum install -y python python-dev rsync autoconf automake asciidoc xmlto libtool

$ git clone git://git.postgresql.org/git/skytools.git

$ cd skytools

$ git submodule init
$ git submodule update

$ ./autogen.sh
$ ./configure --prefix=/home/digoal/skytools3.2
$ make -j 32
$ make install

$ su - root
# cd /home/digoal/skytools
# python setup_pkgloader.py build
# python setup_pkgloader.py install
# python setup_skytools.py build
# python setup_skytools.py install

# export PATH=/home/digoal/pgsql9.5/bin:$PATH
# easy_install pip
# pip install psycopg2

配置 londiste3

mkdir -p /home/digoal/londiste3/log
mkdir -p /home/digoal/londiste3/pid

$ export PATH=/home/digoal/pgsql9.5/bin:/home/digoal/skytoos3.2/bin:$PATH

生成配置文件模板的方法

$ londiste3 --ini

根节点配置文件
必须使用超级用户连接数据库

$ vi /home/digoal/londiste3/job1.ini
[londiste3]
job_name = job1
db = host=127.0.0.1 port=1922 user=postgres dbname=db1 password=postgres
queue_name = replika
logfile = /home/digoal/londiste3/log/job1.log
pidfile = /home/digoal/londiste3/pid/job1.pid
parallel_copies = 16
node_name = local
public_node_location = host=101.xxx.xxx.171 port=1922 user=postgres dbname=db1 password=postgres

创建根节点

$ londiste3 -v /home/digoal/londiste3/job1.ini create-root job1

启动worker

$ londiste3 -d /home/digoal/londiste3/job1.ini worker

配置目标端
因为RDS PG只有普通用户，而且是叶子节点，不需要创建pgq

# vi /usr/share/skytools3/pgq.sql
注释所有 CREATE OR REPLACE FUNCTION

目标节点配置文件

$ vi /home/digoal/londiste3/job2.ini
[londiste3]
job_name = job2
db = host=xxx.digoal.pg.rds.aliyuncs.com port=3433 user=digoal dbname=db1 password=digoal
queue_name = replika
logfile = /home/digoal/londiste3/log/job2.log
pidfile = /home/digoal/londiste3/pid/job2.pid
parallel_copies = 16
node_name = target
public_node_location = host=xxx.digoal.pg.rds.aliyuncs.com port=3433 user=digoal dbname=db1 password=digoal
initial_provider_location = host=127.0.0.1 port=1922 user=postgres dbname=db1 password=postgres

创建叶子节点

$ londiste3 -v /home/digoal/londiste3/job2.ini create-leaf job2

启动worker

$ londiste3 -d /home/digoal/londiste3/job2.ini worker

RDS还没有向用户开放如下权限，所以使用londiste3会报错(截至2016-05-25还未修正该权限)

session_replication_role 权限

创建队列分片配置文件

$ vi /home/digoal/londiste3/pgqd.ini
[pgqd]
base_connstr = host=127.0.0.1 port=1922 user=postgres dbname=db1 password=postgres
initial_database = template1
logfile = /home/digoal/londiste3/log/pgqd.log
pidfile = /home/digoal/londiste3/pid/pgqd.pid

启动队列分片

$ pgqd -d /home/digoal/londiste3/pgqd.ini

查看状态

digoal@iZ25zysa2jmZ-> londiste3 /home/digoal/londiste3/job1.ini status
Queue: replika   Local node: job1

job1 (root)
  |                           Tables: 0/0/0
  |                           Lag: 6s, Tick: 6
  +--: job2 (leaf)
                              Tables: 0/0/0
                              Lag: 6s, Tick: 6
digoal@iZ25zysa2jmZ-> londiste3 /home/digoal/londiste3/job2.ini status
Queue: replika   Local node: job2

job1 (root)
  |                           Tables: 0/0/0
  |                           Lag: 10s, Tick: 6
  +--: job2 (leaf)
                              Tables: 0/0/0
                              Lag: 10s, Tick: 6

查看members

digoal@iZ25zysa2jmZ-> londiste3 /home/digoal/londiste3/job2.ini members
Member info on job2@replika:
node_name        dead             node_location
---------------  ---------------  -----------------------------------------------------------------------------------------------
job1             False            host=101.xxx.xxx.171 port=1922 user=postgres dbname=db1 password=postgres
job2             False            host=xxx.digoal.pg.rds.aliyuncs.com port=3433 user=digoal dbname=db1 password=digoal

源端
初始化需要同步的表

pgbench -i db1
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
creating tables...
100000 of 100000 tuples (100%) done (elapsed 0.03 s, remaining 0.00 s)
vacuum...
set primary keys...
done.

目标库只需要建立表结构

pgbench -i -h xxx.digoal.pg.rds.aliyuncs.com -p 3433 -U digoal db1
db1=> truncate pgbench_accounts ;
TRUNCATE TABLE
db1=> truncate pgbench_history ;
TRUNCATE TABLE
db1=> truncate pgbench_tellers ;
TRUNCATE TABLE
db1=> truncate pgbench_branches ;
TRUNCATE TABLE

添加需要同步的表(必须包含主键)

$ londiste3 -v /home/digoal/londiste3/job1.ini add-table public.pgbench_tellers public.pgbench_accounts public.pgbench_branches
$ londiste3 -v /home/digoal/londiste3/job2.ini add-table public.pgbench_tellers public.pgbench_accounts public.pgbench_branches

查看状态

digoal@iZ25zysa2jmZ-> londiste3 /home/digoal/londiste3/job1.ini tables
Tables on node
table_name               merge_state      table_attrs
-----------------------  ---------------  ---------------
public.pgbench_accounts  ok
public.pgbench_branches  ok
public.pgbench_tellers   ok               

digoal@iZ25zysa2jmZ-> londiste3 /home/digoal/londiste3/job2.ini tables
Tables on node
table_name               merge_state      table_attrs
-----------------------  ---------------  ---------------
public.pgbench_accounts  in-copy
public.pgbench_branches  in-copy
public.pgbench_tellers   in-copy

复制好之后是这个状态

digoal@iZ25zysa2jmZ-> londiste3 /home/digoal/londiste3/job2.ini tables
Tables on node
table_name               merge_state      table_attrs
-----------------------  ---------------  ---------------
public.pgbench_accounts  ok
public.pgbench_branches  ok
public.pgbench_tellers   ok

执行压测

pgbench -M prepared -n -r -P 1 -c 8 -j 8 -T 10 db1

比较数据是否一致

$ londiste3 /home/digoal/londiste3/job2.ini compare