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

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

PG技术大讲堂 - Part 4：PostgreSQL实例结构 PostgreSQL从小白到专家，是从入门逐渐能力提升的一个系列教程，内容包括对PG基础的认知、包括安装使用、包括角色权限、包括维护管理、、等内容，希望对热爱PG、学习PG的同学们有帮助，欢迎持续关注CUUG PG技术大讲堂。