使用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
一、部署
1、源端
安装 PostgreSQL 略
2、源库
postgres=# create database db1;
CREATE DATABASE
3、目标库
RDS PG
postgres=# create database db1;
CREATE DATABASE
4、安装 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
5、配置 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
6、创建根节点
$ londiste3 -v /home/digoal/londiste3/job1.ini create-root job1
7、启动worker
$ londiste3 -d /home/digoal/londiste3/job1.ini worker
8、配置目标端
因为RDS PG只有普通用户，而且是叶子节点，不需要创建pgq
# vi /usr/share/skytools3/pgq.sql
注释所有 CREATE OR REPLACE FUNCTION
9、目标节点配置文件
$ 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
10、创建叶子节点
$ londiste3 -v /home/digoal/londiste3/job2.ini create-leaf job2
11、启动worker
$ londiste3 -d /home/digoal/londiste3/job2.ini worker
RDS还没有向用户开放如下权限，所以使用londiste3会报错(截至2016-05-25还未修正该权限)
session_replication_role 权限
12、创建队列分片配置文件
$ 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
13、启动队列分片
$ pgqd -d /home/digoal/londiste3/pgqd.ini
14、查看状态
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
14、查看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