slon软件下载地址：slony1-1.2.6
http://slony.info/downloads/1.2/source/
postgresql下载地址：
http://www.postgresql.org/download/http://www.postgresql.org/ftp/source/v8.1.23/
一、postgresql安装
方法1.rpm包安装postfresql：所需软件包：postgresql92-9.2.10-1PGDG.rhel5.i386.rpmpostgresql92-server-9.2.10-1PGDG.rhel5.i386.rpmpostgresql92-contrib-9.2.10-1PGDG.rhel5.i386.rpm postgresql92-devel-9.2.10-1PGDG.rhel5.i386.rpm postgresql92-libs-9.2.10-1PGDG.rhel5.i386.rpmuuid-1.5.1-3.el5.i386.rpm
硬件IP：192.168.30.121(主) 192.168.20.122(从)
主、从服务器安装方法相同： 1，linux创建postgres用户及用户组 groupadd postgres useradd -g postgres postgres
2.安装包安装顺序： rpm -ivh postgresql92-libs-9.2.10-1PGDG.rhel5.i386.rpm rpm -ivh postgresql92-9.2.10-1PGDG.rhel5.i386.rpm rpm -ivh uuid-1.5.1-3.el5.i386.rpm #安装contrib所依赖包 rpm -ivh postgresql92-devel-9.2.10-1PGDG.rhel5.i386.rpm #主从同步所依赖包 rpm -ivh postgresql92-server-9.2.10-1PGDG.rhel5.i386.rpm rpm -ivh postgresql92-contrib-9.2.10-1PGDG.rhel5.i386.rpm
3.初始化PostgreSQL 数据库： service postgresql-9.2 initdb
4.启动 service postgresql-9.2 start
5.把PostgreSQL 服务加入到启动列表 chkconfig postgresql-9.2 on chkconfig --list|grep postgres
方法2.源码安装：
1，linux创建postgres用户及用户组groupadd postgresuseradd -g postgres postgres
2，解压压缩包
[root@postgres]# tar -xzvf /var/local/pgsql/postgresql-9.2.10.tar.gz
进入解压目录： cd /var/local/pgsql/postgresql-9.2.103，编译安装：
创建安装目录和数据目录
mkdir /usr/local/pgsql mkdir /home/postgres/data
[root@postgres postgresql-9.2.10]# ./configure --prefix=/usr/local/pgsql -localstatedir=/home/postgres/data
处理报错信息：checking for readline... noconfigure: error: readline library not foundIf you have readline already installed, see config.log for details on thefailure. It is possible the compiler isn't looking in the proper directory.Use --without-readline to disable readline support.
解决：
缺少readline-devel依赖包
安装 readline-devel包
我这里选择的是yum安装，大家可以去网上下载一个适合自己的版本去安装yum install readline-devel-5.1-3.el5
安装完毕后重新编译即可
重新编译：[root@postgres postgresql-9.2.10]# ./configure --prefix=/usr/local/pgsql -localstatedir=/home/postgres/data
没有error即编译正常可以安装
[root@postgres postgresql-9.2.10]# makeAll of PostgreSQL successfully made. Ready to install.
[root@postgres postgresql-9.2.10]# make installPostgreSQL installation complete.
4.安装完毕修改数据目录权限chown -R postgres:postgres /usr/local/pgsql/chown -R postgres:postgres /home/postgres/data/
修改postgres用户的.bash_profile文件：
[postgres@postgres ~]$ vi .bash_profile 添加：
PGLIB=/usr/local/pgsql/libPGDATA=/home/postgres/dataPATH=$PATH:/usr/local/pgsql/bin MANPATH=$MANPATH:/usr/local/pgsql/manexport PGLIB PGDATA PATH MANPATH
[postgres@postgres ~]$ source .bash_profile
5.初始postgresql并启动postgresql
初始化：[postgres@postgres ~]$ /usr/local/pgsql/bin/initdb /home/postgres/dataSuccess. You can now start the database server using:
/usr/local/pgsql/bin/postmaster -D /home/postgres/dataor /usr/local/pgsql/bin/pg_ctl -D /home/postgres/data -l logfile start
启动：
[postgres@postgres ~]$ /usr/local/pgsql/bin/pg_ctl -D /home/postgres/data start
============================================================================================
从库安装方式和主库postgresql安装一样
============================================================================================
二，编译安装slony-i
1、解压软件包：
[root@postgres local]# tar -xjvf /var/local/slony1-1.2.6.tar.bz2
2、编译安装软件包
[root@postgres ~]# cd /var/local/slony1-1.2.6[root@postgres slony1-1.2.6]# ./configure --with-pgsourcetree=/usr/local/pgsql/bin
[root@postgres slony1-1.2.6]# makeAll of Slony-I is successfully made. Ready to install
[root@postgres slony1-1.2.6]# make installAll of Slony-I is successfully installed
============================================================================================
在主库从库均要安装slony，安装方式同上
====================================================================================
三,主从配置
1.postgresql添加复制用户
su - postgres 用户下
[postgres@mysql ~]$ psqlpsql (9.2.10)Type "help" for help.
postgres=#create role repl password '123456' login superuser replication;
主从都执行此语句
2.postgresql配置文件
postgresql.conf
主从都改：
vi /home/postgres/data/postgresql.conf
添加： listen_addresses = '*'
主库pg_hba.conf
vi /home/postgres/data/pg_hba.conf 添加：host all repl 192.168.30.122/32 md5
从库pg_hba.conf
vi /home/postgres/data/pg_hba.conf 添加：host all repl 192.168.30.121/32 md5
修改配置后重启主从服务器都重启postgresql服务：
[postgres@localhost ~]$/usr/local/pgsql/bin/pg_ctl -D /home/postgres/data restart
3.建立测试数据库和测试表
主从库均需要创建数据库和表，slony不能同步DDL语句。
以下以在主数据库服务器上建立主数据库和数据表 test 为例见解，其他数据库和数据表请参考建立.
/usr/local/pgsql/bin/createdb test
cat sql.txt |psql -Urepl test -W123456 sql.txt 文件最好是 UTF-8 格式,特别是存在中文字符时) 例：sql.txt CREATE TABLE tb_depart(Id int primary key,Name char(8)); 在从数据库服务器上建立与主数据库服务器上相同的数据库test
创建后查看：[postgres@postgres ~]$ psql -Urepl test
test=# \d List of relations Schema | Name | Type | Owner --------+-----------+-------+-------- public | tb_depart | table | repl(1 row)
test=# \d tb_depart; Table "public.tb_depart" Column | Type | Modifiers --------+--------------+----------- id | integer | not null name | character(8) | Indexes: "tb_depart_pkey" PRIMARY KEY, btree (id)
test=#\q
在从数据库服务器上建立与主数据库服务器上相同的数据库test,同样的表
4.slony-i配置主从同步
只需要在从库配置只需即可：在/home/postgres/目录下创建脚本文件：
slony_0.sh文件内容如下：
#!/bin/sh /usr/local/pgsql/bin/slonik << _END_ ## Define cluster namespace and node connection information # #集群名称 cluster name = testdb;# 定义复制节点 node 1 admin conninfo = 'dbname=test host=192.168.30.121 port=5432 user=repl'; node 2 admin conninfo = 'dbname=test host=localhost port=5432 user=repl'; DROP SET (id=1, origin=1); uninstall node (id=1); uninstall node (id=2); echo 'Drop testdb set'; _END_
slony_1.sh文件内容如下：
#!/bin/sh /usr/local/pgsql/bin/slonik << _END_ cluster name = testdb; # 定义复制节点 node 1 admin conninfo = 'dbname=test host=192.168.30.121 port=5432 user=repl'; node 2 admin conninfo = 'dbname=test host=localhost port=5432 user=repl'; echo 'Cluster defined, nodes identified'; # 初始化集群,id从1开始 init cluster (id=1, comment='Master Node'); # 设置存储节点 store node (id=2, comment='Slave Node'); echo 'Nodes defined'; # 设置存储路径 store path (server=1, client=2, conninfo='dbname=test host=192.168.30.121 port=5432 user=repl'); store path (server=2, client=1, conninfo='dbname=test host=localhost port=5432 user=repl'); #设置侦听事件和订阅方向,复制中角色,主节点是原始提供者,从节点是接受者 store listen (origin=1, provider = 1, receiver =2); store listen (origin=2, provider = 2, receiver =1); _END_
slony_2.sh文件内容如下：
#!/bin/sh /usr/local/pgsql/bin/slonik << _END_ # # Define cluster namespace and node connection information # cluster name = testdb; node 1 admin conninfo = 'dbname=test host=192.168.30.121 port=5432 user=repl'; node 2 admin conninfo = 'dbname=test host=localhost port=5432 user=repl'; # 设置参与同步的数据表 #先创建一个复制集,id也是从1开始 #向自己的复制集种添加表,每个需要复制的表一条set命令 #id从1开始,逐次递加,步进为1； #fully qualified name是表的全称：模式名.表名 #这里的复制集id需要和前面创建的复制集id一致 #假如某个表没有主键,但是有唯一键字,那么可以用key关键字 #指定其为复制键字,如下面的key参数 #set add table ( set id = 1, origin = 1,id = 4, fully qualified name = 'public.history',key = "column",comment = 'Table history' ); #对于没有唯一列的表,需要这样处理,这一句放在 create set 的前面 #table add key (node id = 1, fully qualified name = 'public.history'); # 这样设置结果集 #set add table (set id=1, origin=1, id=4, fully qualified name = 'public.history', #comment='history table', key = serial);
create set (id=1, origin=1, comment='testdb tables'); set add table ( set id=1, origin=1,id=1, fully qualified name='public.tb_depart',comment='Table tb_depart' ); set add table ( set id=1, origin=1,id=2, fully qualified name='public.tb_user',comment='Table tb_user' ); set add table ( set id=1, origin=1,id=3, fully qualified name='public.tb_manager',comment='Table tb_manager' ); set add table ( set id=1, origin=1,id=4, fully qualified name='public.tb_test',comment='Table tb_test' ); echo 'set 1 of testdb tables created'; _END_
slony_3.sh文件内容如下：
#/bin/sh /usr/local/pgsql/bin/slon testdb "dbname=test host=192.168.30.121 port=5432 user=repl" > ~/slon_gb_1.out 2>&1 & /usr/local/pgsql/bin/slon testdb "dbname=test host=localhost port=5432 user=repl" > ~/slon_gb_2.out 2>&1 & /usr/local/pgsql/bin/slonik << _END_# Define cluster namespace and node connection information # cluster name = testdb; #提供连接参数 node 1 admin conninfo = 'dbname=test host=192.168.30.121 port=5432 user=repl'; node 2 admin conninfo = 'dbname=test host=localhost port=5432 user=repl';
# 提交订阅复制集 subscribe set (id=1, provider=1, receiver=2, forward=no); echo 'set 1 of gb tables subscribed by node 2'; _END_
[postgres@mysql2 cluster_shells]$ chmod u+x slony_*.sh
slony_main.sh文件内容如下：
#!/bin/sh case $1 in start) cd /home/postgres/ sh slony_3.sh ;; stop) killall -KILL slon ;; rebuild) cd /home/postgres killall -KILL slon sh slony_0.sh >> /dev/null 2>&1 sh slony_1.sh sh slony_2.sh sh slony_3.sh ;; *) echo "Please input start or stop or rebuild!!" ;; esac
[postgres@mysql2 cluster_shells]$ chmod u+x slony_main.sh
测试同步：
在从库端执行：
按照下面执行顺序
./slony_0.sh
./slony_1.sh <stdin>:5: Cluster defined, nodes identified<stdin>:10: Nodes defined
./slony_2.sh <stdin>:8: set 1 of testdb tables created
./slony_3.sh <stdin>:7: set 1 of gb tables subscribed by node 2
修改数据前：主库端：[postgres@postgres ~]$ psql test
test=# \d List of relations Schema | Name | Type | Owner --------+-----------+-------+-------- public | tb_depart | table | repl(1 row)
test=# \d tb_depart Table "public.tb_depart" Column | Type | Modifiers --------+--------------+----------- id | integer | not null name | character(8) | Indexes: "tb_depart_pkey" PRIMARY KEY, btree (id)Triggers: _testdb_logtrigger_1 AFTER INSERT OR DELETE OR UPDATE ON tb_depart FOR EACH ROW EXECUTE PROCEDURE _testdb.logtrigger('_testdb', '1', 'kv')
已创建触发器，用于同步。
暂时无数据； test=# select * from tb_depart; id | name ----+------(0 rows)
备库端：[postgres@mysql2 ~]$ psql testWelcome to psql 8.1.23, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit
test=# \d List of relations Schema | Name | Type | Owner --------+-----------+-------+-------- public | tb_depart | table | repl(1 row)
test=# \d tb_depart Table "public.tb_depart" Column | Type | Modifiers --------+--------------+----------- id | integer | not null name | character(8) | Indexes: "tb_depart_pkey" PRIMARY KEY, btree (id)Triggers: _testdb_denyaccess_1 BEFORE INSERT OR DELETE OR UPDATE ON tb_depart FOR EACH ROW EXECUTE PROCEDURE _testdb.denyaccess('_testdb')slony创建了触发器---双向同步；暂时无数据test=# select * from tb_depart; id | name ----+------(0 rows)
test=#
主库端添加数据：
test=# insert into tb_depart values(1,'aaa');INSERT 0 1
test=# select * from tb_depart; id | name ----+---------- 1 | aaa (1 row)
test=#
备库端查看：test=# select * from tb_depart; id | name ----+---------- 1 | aaa (1 row)
同步成功。
转载于:https://www.cnblogs.com/youhunyimeng/p/4502843.html
相关资源：数据结构—成绩单生成器