Postgres2015全国用户大会将于11月20至21日在北京丽亭华苑酒店召开。本次大会嘉宾阵容强大，国内顶级PostgreSQL数据库专家将悉数到场，并特邀欧洲、俄罗斯、日本、美国等国家和地区的数据库方面专家助阵:
- Postgres-XC项目的发起人铃木市一(SUZUKI Koichi)
- Postgres-XL的项目发起人Mason Sharp
- pgpool的作者石井达夫(Tatsuo Ishii)
- PG-Strom的作者海外浩平(Kaigai Kohei)
- Greenplum研发总监姚延栋
- 周正中(德哥), PostgreSQL中国用户会创始人之一
- 汪洋，平安科技数据库技术部经理
- ……
|
 |
PostgreSQL 的流复制素来以高效，实时，稳定著称；为企业解决了很多问题，例如容灾，备份，HA，读写分离等等。
但是流复制有一个无法克服的弊端，下游节点只能做到只读，并且只能复制整个集群（使用walbouncer可以做到基于表空间或库级别的物理流复制）。
如果用户确实有表级或行级的复制需求，我们不得不使用其他手段来实施，例如londiste3, dblink, trigger， bucardo, slony-I等。
这些插件或工具是基于触发器的，所以对上游节点的性能影响比较大，而且复制效率一般般。
PostgreSQL社区一直在努力将逻辑复制加入到PG的内核中，同样使用的是XLOG，从XLOG中解出row，在下游节点回放。有点类似于MySQL的binlog复制方案。
在逻辑复制加入PostgreSQL内核代码前（预计9.6的版本可能会加入），用户可以使用2nd提供的bdr插件来实现逻辑复制。
如果做单向的复制，使用9.4或以上的PostgreSQL版本即可，而如果要使用双向复制（多主），则需要使用2nd提供的PostgreSQL版本。
地址：
本文以单向复制为例，即UDR，讲解一下这个插件的使用。
下载插件，我们需要的是bdr-plugin的稳定分支。
# git clone -b bdr-plugin/REL0_9_STABLE git://git.postgresql.org/git/2ndquadrant_bdr.git bdr-plugin
安装UDR插件
# export PATH=/opt/pgsql/bin:$PATH# cd bdr-plugin# ./autogen.sh# ./configure BUILDING_UDR=1# make; make install
修改BUG
# cd /opt/pgsql/share/extension[root@digoal extension]# cat bdr.control |grep default_versiondefault_version = '0.9.2.0'
# vi bdr--0.9.2.0.sql-- 注释掉这行，应该是bdr的BUG，这个函数依赖的C函数在2nd改版的postgresql下面。-- CREATE OR REPLACE FUNCTION bdr.bdr_internal_sequence_reset_cache(seq regclass)-- RETURNS void LANGUAGE c AS 'MODULE_PATHNAME' STRICT;
配置上游和下游节点
上游节点
$ vi postgresql.conf
listen_addresses='0.0.0.0'
port=1921
max_connections=100
unix_socket_directories='.'
ssl=on
ssl_ciphers='EXPORT40'
shared_buffers=512MB
huge_pages=try
max_prepared_transactions=0
max_stack_depth=100kB
dynamic_shared_memory_type=posix
max_files_per_process=500
shared_preload_libraries='bdr'
max_worker_processes=8
wal_level=logical
fsync=off
synchronous_commit=off
wal_sync_method=open_datasync
full_page_writes=off
wal_log_hints=off
wal_buffers=16MB
wal_writer_delay=10ms
checkpoint_segments=8
archive_mode=off
archive_command='/bin/date'
max_wal_senders=10
max_replication_slots=10
hot_standby=on
wal_receiver_status_interval=1s
hot_standby_feedback=on
enable_bitmapscan=on
enable_hashagg=on
enable_hashjoin=on
enable_indexscan=on
enable_material=on
enable_mergejoin=on
enable_nestloop=on
enable_seqscan=on
enable_sort=on
enable_tidscan=on
log_destination='csvlog'
logging_collector=on
log_directory='pg_log'
log_truncate_on_rotation=on
log_rotation_size=10MB
log_checkpoints=on
log_connections=on
log_disconnections=on
log_duration=off
log_error_verbosity=verbose
log_line_prefix='%i
log_statement='none'
log_timezone='PRC'
autovacuum=on
log_autovacuum_min_duration=0
autovacuum_vacuum_scale_factor=0.0002
autovacuum_analyze_scale_factor=0.0001
datestyle='iso,
timezone='PRC'
lc_messages='C'
lc_monetary='C'
lc_numeric='C'
lc_time='C'
default_text_search_config='pg_catalog.english'
bdr.conflict_logging_include_tuples=true
bdr.log_conflicts_to_table=true
bdr.temp_dump_directory='pg_bdr_temp_dump_dir'
$ vi pg_hba.conf
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
#host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication postgres trust
host replication postgres 127.0.0.1/32 trust
配置下游节点
1. 只有postgresql.conf中配置的监听端口不一样，其他一样。
2. 创建逻辑备份目录。在下游节点初始化订阅时，需要用来存储从上游节点dump的整个被订阅的数据库的数据，所以这个目录的空间要足够大。
mkdir $PGDATA/pg_bdr_temp_dump_dir
启动数据库。
# 假设我的上游节点是1921端口，下游节点是1922端口。
pg_ctl start -D /data01/pgdata_1921pg_ctl start -D /data01/pgdata_1922
# 在上游节点，我有一个数据库为up，我需要将这个数据库复制到下游节点的数据库down中。
# 创建上游数据库，并且在up库创建bdr扩展。
postgres@digoal-> psql -h 127.0.0.1 -p 1921psql (9.4.4)Type "help" for help.postgres=# create database up;CREATE DATABASEpostgres=# \c upYou are now connected to database "up" as user "postgres".up=# create table tb(id int,info text);
CREATE TABLE
up=# insert into tb select generate_series(1,100);
INSERT 0 100up=# create extension btree_gist;CREATE EXTENSIONup=# create extension bdr;CREATE EXTENSION
# 创建测试表，测试数据类型，测试函数，测试视图
postgres=# \c upYou are now connected to database "up" as user "postgres".up=# create table t1(id int primary key,info text);CREATE TABLEup=# create or replace function f1() returns void as $$declarebeginraise notice '%', now();end;$$ language plpgsql;CREATE FUNCTIONup=# create view v1 as select count(*) as cnt from t1;CREATE VIEWup=# create type dt as (c1 int,c2 int,c3 int);CREATE TYPEup=# insert into t1 select generate_series(1,100);INSERT 0 100up=# create table t2(id int,c1 dt);CREATE TABLEup=# insert into t2 values (1,'(1,1,1)');INSERT 0 1up=# insert into t2 values (2,'(1,1,1)');INSERT 0 1up=# insert into t2 values (2,'(1,1,1)');INSERT 0 1up=# insert into t2 values (2,'(1,1,1)');INSERT 0 1up=# insert into t2 values (2,'(1,1,1)');INSERT 0 1
创建bdr扩展后，新建的表会自动添加TRUNCATE触发器
up=# \d t1Table "public.t1"Column | Type | Modifiers--------+---------+-----------id | integer | not nullinfo | text |Indexes:"t1_pkey" PRIMARY KEY, btree (id)Triggers:truncate_trigger AFTER TRUNCATE ON t1 FOR EACH STATEMENT EXECUTE PROCEDURE bdr.queue_truncate()
# 创建下游节点的数据库down，同时也在这个数据库中创建bdr扩展。
postgres@digoal-> psql -h 127.0.0.1 -p 1922psql (9.4.4)Type "help" for help.postgres=# create database down;CREATE DATABASEpostgres=# \c downYou are now connected to database "down" as user "postgres".down=# create extension btree_gist;CREATE EXTENSIONdown=# create extension bdr;CREATE EXTENSIONdown=# create database up; -- 务必创建哦
为什么在下游节点还需要创建一个up库（虽然我们不是将数据订阅到up库），但是没有这个库，还原会报错，例如：
这显然是个BUG。
Dumping remote database "hostaddr=127.0.0.1 port=1921 dbname=up user=postgres fallback_application_name='bdr (6203675445083668497,1,16385,): init_replica dump'" with 1 concurrent workers to "pg_bdr_temp_dump_dir/postgres-bdr-000C837A-1.8393"Restoring dump to local DB "hostaddr=127.0.0.1 port=1922 dbname=down user=postgres fallback_application_name='bdr (6203675445083668497,1,16385,): init_replica restore' options='-c bdr.do_not_replicate=on -c bdr.permit_unsafe_ddl_commands=on -c bdr.skip_ddl_replication=on -c bdr.skip_ddl_locking=on'" with 1 concurrent workers from "pg_bdr_temp_dump_dir/postgres-bdr-000C837A-1.8393"pg_restore: [archiver (db)] Error while PROCESSING TOC:pg_restore: [archiver (db)] Error from TOC entry 3265; 1262 16385 SECURITY LABEL up postgrespg_restore: [archiver (db)] could not execute query: ERROR: database "up" does not existCommand was: SECURITY LABEL FOR bdr ON DATABASE up IS '{ "bdr" : true }';
pg_restore to hostaddr=127.0.0.1 port=1922 dbname=down user=postgres fallback_application_name='bdr (6203675445083668497,1,16385,): init_replica restore' options='-c bdr.do_not_replicate=on -c bdr.permit_unsafe_ddl_commands=on -c bdr.skip_ddl_replication=on -c bdr.skip_ddl_locking=on' failed, aborting
在上游节点开启一个更新的压力测试，以测试在上游节点有DML操作时可以复制数据。相互不干扰。
postgres@digoal-> vi t.sql\setrandom id 1 100update t1 set info=now()::text where id=:id;
postgres@digoal-> pgbench -M prepared -n -r -P 1 -f ./t.sql -c 8 -j 8 -T 10000 upprogress: 1.0 s, 30818.6 tps, lat 0.243 ms stddev 0.414progress: 2.0 s, 32295.2 tps, lat 0.246 ms stddev 0.328......
在下游节点定于上游节点的up数据库。
postgres@digoal-> psql -h 127.0.0.1 -p 1922 downpsql (9.4.4)Type "help" for help.down=# select bdr.bdr_subscribe(local_node_name:='down_1922', subscribe_to_dsn:='hostaddr=127.0.0.1 port=1921 dbname=up user=postgres', node_local_dsn:='hostaddr=127.0.0.1 port=1922 dbname=down user=postgres');bdr_subscribe---------------(1 row)
查看订阅状态
down=# select * from bdr.bdr_nodes;node_sysid | node_timeline | node_dboid | node_status | node_name | node_local_dsn | node_init_from_dsn---------------------+---------------+------------+-------------+----------------------+--------------------------------------------------------+------------------------------------------------------6177143025216388117 | 10 | 70522 | r | down_1922 | |6177143025216388117 | 10 | 81996 | i | down_1922-subscriber | hostaddr=127.0.0.1 port=1922 dbname=down user=postgres | hostaddr=127.0.0.1 port=1921 dbname=up user=postgres(2 rows)
i表示正在初始化.
在上游节点可以查看到对应的slot已经被创建了
postgres=# select * from pg_replication_slots ;slot_name | plugin | slot_type | datoid | database | active | xmin | catalog_xmin | restart_lsn------------------------------------------+--------+-----------+--------+----------+--------+------+--------------+-------------bdr_70522_6177143025216388117_10_83065__ | bdr | logical | 70522 | up | t | | 661974801 | 2B/22A50A70(1 row)
postgres=# select * from pg_stat_replication ;pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state------+----------+----------+------------------------------------------+-------------+-----------------+-------------+-------------------------------+--------------+---------+---------------+----------------+----------------+-----------------+---------------+------------6170 | 10 | postgres | bdr (6177143025216388117,10,83065,):init | 127.0.0.1 | | 50711 | 2015-10-09 23:09:54.935301+08 | | startup | 0/0 | | || 0 | async(1 row)
一会，我们可以看到数据已经拷贝到下游节点的down库了。
down=# \dtList of relationsSchema | Name | Type | Owner--------+------+-------+----------public | t1 | table | postgrespublic | t2 | table | postgrespublic | tb | table | postgres(3 rows)
down=# \dvList of relationsSchema | Name | Type | Owner--------+------+------+----------public | v1 | view | postgres(1 row)
down=# \df f1List of functionsSchema | Name | Result data type | Argument data types | Type--------+------+------------------+---------------------+--------public | f1 | void | | normal(1 row)
down=# \dTList of data typesSchema | Name | Description--------+---------------+-------------public | dt |
测试DDL，上游节点加了event，禁止直接使用DDL，所以我们需要通过函数来执行DDL。
postgres@digoal-> psql -h 127.0.0.1 -p 1921psql (9.4.4)Type "help" for help.postgres=# \c upup=# select * from bdr.bdr_replicate_ddl_command('create table public.new(id int)'); -- 注意必须制定schemabdr_replicate_ddl_command---------------------------(1 row)
bdr对应的表，管理函数。查看：
删除订阅的方法：
1. 在上游节点删除slot，处理DDL队列，例如truncate bdr_queued_commands, bdr_queued_drops队列中的数据。
2. 在下游节点，
注释shared_preload_library，重启数据库，去
bdr_supervisordb
库删除下游节点的订阅库信息。
3.
在下游节点，
改回shared_preload_library，重启数据库，删除原订阅库的bdr extension。
解决下游节点异常，例如无法添加订阅，可能由于之前没有正确的删除订阅。
注释shared_preload，重启数据库，去
bdr_supervisordb
库修复。然后解除注释，重启数据库。
postgres=# \lList of databasesName | Owner | Encoding | Collate | Ctype | Access privileges------------------+----------+----------+---------+-------+-----------------------bdr_supervisordb | postgres | UTF8 | C | C |postgres | postgres | UTF8 | C | C |template0 | postgres | UTF8 | C | C | =c/postgres +| | | | | postgres=CTc/postgrestemplate1 | postgres | UTF8 | C | C | =c/postgres +| | | | | postgres=CTc/postgresup | postgres | UTF8 | C | C |(5 rows)
解决订阅异常，
2015-10-09 23:41:59.756 CST,,,7785,,5617e047.1e69,1,,2015-10-09 23:41:59 CST,3/0,0,ERROR,55000,"previous init failed, manual cleanup is required","Found bdr.bdr_nodes entry for bdr (6203671810517003626,1,16385,) with state=i in remote bdr.bdr_nodes","Remove all replication identifiers and slots corresponding to this node from the init target node then drop and recreate this database and try again",,,,,,"bdr_init_replica, bdr_init_replica.c:899","bdr (6203671810517003626,1,16385,): perdb"
链接到上游节点，删除slot
删除下游节点数据库，重新创建下游节点数据库，重新订阅。
小结
1. 如果上游节点产生XLOG非常频繁，下游节点初始化订阅的时间会非常漫长。
2. 如果上游节点产生XLOG非常频繁，下游节点的延迟可能会很大。
3. 由于udr和bdr是在一个插件中共用的，只是编译参数使用udr的编译选项，包含了BDR的功能后使用起来显得非常混乱。
4. 文档中存在一些BUG，例如删除UDR订阅的函数bdr.bdr_unsubscribe(local_node_name)不存在。
PostgreSQL的udr插件还有很多可以改进的地方，包括管理方面的，性能方面的。PGSQL又将多一个杀手锏。
如果担心目前还不够完善，在加入PG内核前，我们用它来做跨数据库大版本的增量迁移，跨硬件架构，或者不同数据块大小的增量数据迁移，是个不错的选择。
[参考]
1.
335
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
