postgresql slony 搭建及简单配置
一,postgres-9.2.4安装 1,vi /etc/apt/sources.list.d/pgdg.list deb http://apt.postgresql.org/pub/repos/apt/ squeeze-pgdg main 2,vi /etc/apt/sources.list deb-src http://ftp.debian.org/debian/ squeeze-updates main deb http://ftp.tw.debian.org/debian wheezy main 3,wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add - apt-get update 过程中会报错 W: There is no public key available for the following key IDs: 8B48AD6246925553 解决: gpg --keyserver subkeys.pgp.net --recv 8B48AD6246925553 gpg --export --armor 8B48AD6246925553 | apt-key add - 4,apt-get update 5,aptitude install postgresql-9.2 二 slony-2.2.0安装 1,安装编译环境 aptitude install build-essential 2,安装可能会报没有flex 跟postgresql-server-dev-X.Y错误 aptitude install flex aptitude install postgresql-server-dev-9.2 3,bzip2 -d slony1-2.2.0.rc1.tar.bz2 bzip2 -d slony1-2.2.0.rc1-docs.tar.bz2 tar xvf slony1-2.2.0.rc1.tar tar xvf slony1-2.2.0.rc1-docs.tar cd slony1-2.2.0.rc1/ ./configure make make install slony配置 系统环境配置 系统:debian 6.0.4 postgresql:9.2.4 master:172.16.2.33 slave:172.16.2.34 1,在master上建立脚本 vi /home/test_slony.sh 脚本文件内容如下 ######################################################################################### #!/bin/bash basebin=/usr/lib/postgresql/9.2/bin CLUSTER=test1 DBSERVER=slony1 DBSLAVER=slony2 HOSTSERVER=172.16.2.33 HOSTSLAVER=172.16.2.34 DBSERVER_USER=postgres $basebin/slonik<<_EOF_ cluster name=$CLUSTER; node 1 admin conninfo='dbname=$DBSERVER host=$HOSTSERVER user=$DBSERVER_USER port=5432'; node 2 admin conninfo='dbname=$DBSLAVER host=$HOSTSLAVER user=$DBSERVER_USER port=5432'; init cluster ( id=1,comment='Node 1' );(如果想加表不重新运行脚本开始,注释掉此行) create set ( id=1,origin=1,comment='All test tables' ); set add table ( set id=1,id=1,fully qualified name='public.tb_depart',comment='Table tb_depart' ); create set ( id=2,comment='All test tables' ); set add table ( set id=2,id=2,fully qualified name='public.tb_user',comment='Table tb_user' ); create set ( id=3,comment='All test tables' ); set add table ( set id=3,id=3,fully qualified name='public.tb_manager',comment='Table tb_manager' ); create set ( id=4,comment='All test tables' ); set add table ( set id=4,id=4,fully qualified name='public.slony',comment='Table slony' ); store node ( id=2,comment='Node 2',event node=1 ); store path ( server=1,client=2,conninfo='dbname=$DBSERVER host=$HOSTSERVER user=$DBSERVER_USER port=5432'); store path ( server=2,client=1,conninfo='dbname=$DBSLAVER host=$HOSTSLAVER user=$DBSERVER_USER port=5432'); store listen ( origin=1,provider=1,receiver=2 ); store listen ( origin=2,provider=2,receiver=1 ); subscribe set ( id=1,receiver=2,forward=no); subscribe set ( id=2,forward=no); subscribe set ( id=3,forward=no); subscribe set ( id=4,forward=no); _EOF_ ######################################################################################### chmod 777 test_slony.sh 2,su postgres psql slony1 create table tb_depart( id char(40) constraint firstkey primary key, title varchar(100) not null); ######################################################################################### create table tb_user( id char(40) primary key, name varchar(100) not null); ######################################################################################### create table tb_manager( id char(40) primary key, name varchar(100) not null); ######################################################################################### create table slony(id serial primary key,name varchar (10)); 注意:以上四个表都必须要主键!! 3,slave(172.16.2.34)上同样建立四个表 su postgres psql slony2 create table tb_depart( id char(40) constraint firstkey primary key,name varchar (10)); 注意:以上四个表都必须要主键!! 4,在master(172.16.2.33)上运行test_slony.sh脚本 su postgres cd /home ./test_slony.sh 新打开连个session cd /usr/lib/postgres/9.2/bin ./slon test1 "dbname=slony1 host=172.16.2.33 user=postgres port=5432" cd /usr/lib/postgres/9.2/bin ./slon test1 "dbname=slony2 host=172.16.2.34 user=postgres port=5432" 5,测试 主库上(172.16.2.33) su postgres psql slony1 slony1=# insert into slony select generate_series(1,100000); INSERT 0 100000 slony1=# select count(*) from slony; count -------- 100000 (1 row) 从库(172.16.2.34)上验证 su postgres psql slony2 slony2=# insert into slony select generate_series(1,100000); ERROR: Slony-I: Table slony is replicated and cannot be modified on a subscriber node - role=0 如果是从库上插入数据是会报错的,查询则没有问题 slony2=# select count(*) from slony; count -------- 100000 (1 row) 到此slony2.2.0在postgresql-9.2.4上搭建测试完成 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |