一，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上搭建测试完成
原文链接：https://www.f2er.com/postgresql/196022.html