postgresql多实例创建与Slony-I复制配置



第一部分 在一个主机上创建多个 postgresql实例

步骤一：安装postgresql软件

安装 postgresql实例，从postgresql官网上  https://www.postgresql.org/ 下载postgresql安装软件，解压缩，创建postgres用户和组，添加环境变量。

我这里下载的版本是 pgsql9.5.1。

创建postgresql实例

安装了postgresql软件后，创建postgresql实例有三个步骤：创建实例目录，创建数据库簇初始化数据库（命令为 initdb），启动实例服务（用 pg_ctl命令）并进行相关配置。

步骤一：创建目录和文件：

su - postgres

mkdir -p /data/pgsql/{data,data02}

步骤二：初始化实例1：

export PGDATA=/data/pgsql/data

initdb

步骤三：启动postgresql实例1

pg_ctl  -D /data/pgsql/data -l /data/pgsql/data/postgres.log start

实例2也可用按照上面的三个步骤进行创建：

初始实例2：

export PGDATA=/data/pgsql/data02

env | grep PGDATA

initdb

启动实例2：

pg_ctl  -D /data/pgsql/data02 -l /data/pgsql/data/postgres02.log start

如果要关闭数据库，可用用羡慕的命令停止数据库：

pg_ctl stop -D /data/pgsql/data02 -m fast

步骤二：配置实例监听和外部访问

修改实例2的启动文件和端口，如果一个服务器上有多个实例，每个实例的端口都要不同，为了能够让客户端访问，需要在配置文件添加监听，在pg_hba.conf中添加梵文权限：

修改postgresql实例，启动配置参数文件：

vim postgresql.conf

listen_addresses = '*'      # what IP address(es) to listen on;

port = 5433             # (change requires restart)

max_connections = 100           # (change requires restart)

这些参数修改后，重启postgresql实例才能生效，postgresql实例重启后会监听本服务器上所有IP的5433端口。

重启postgresql实例的命令为：

pg_ctl stop -D /data/pgsql/data02 -m fast

pg_ctl  -D /data/pgsql/data02 -l /data/pgsql/data/postgres02.log start

修改postgresql实例访问权限文件：

vim pg_hba.conf

# IPv4 local connections:

host    all             all             192.168.1.0/24            trust

上面的参数添加后，postgresql实例，就可以允许 192.168.1.0/24 网段的主机通过各个用户以有密码认证的方式访问实例中存在的库。

如果要进行严格限定，可以对应修改相关部分。文件修改后，需要重新加载配置文件才能生效：

pg_ctl reload

实例启动并修改了访问权限后，连接postgresql 实例的格式为：

psql -h <IP地址> -p <端口> [数据库名称] [用户名称]

示例：

psql -h 192.168.10.119 -p 5432 testdb postgres

psql -h 192.168.10.119 -p 5433 postgres postgres

这样从安装postgresql软件，到创建、配置并使用postgresql多实例，就完成了。

############################

第二部分 在Linux系统下安装 Slony-I 软件

一般Linux的各种发行版本中会自带 slony-I ，但自带的版本比较旧，可以从slony的官网下载最新的版本安装。

编译安装 slony-I 软件

在 slony 官方网站 http://www.slony.info/ ，点击 Dowload 进入下载页面（注意：由于我的postgresql版本是比较新的 9.5，一开始使用 slony1-2.1.3版本有报错，于是安装了最新的 slony1-2.2.5 版本）；

选择合适的版本，这里选择 2.2 ；

弹出的界面选择直接下载源码，还是使用cvs界面，这里选择“直接下载源码”；

选择要下载的小版本，这里选择 2.2.5 ，文件名称为 slony1-2.2.5.tar.br2

下载完成后，把下载得来的源码包放到一个目录下，使用命令解压缩：

cd /usr/local/src

rz slony1-2.2.5.tar.bz2

tar xvf slony1-2.2.5.tar.bz2

解压生成目录 slony1-2.2.5 ，进入此目录，用Linux编译三板斧进行编译安装：

cd slony-2.2.5

./configure --with-perltools -with-pgconfigdir=/usr/local/pgsql/bin

make

make install

注意编译时，需要找到 pg_config可执行程序，如果在pgsql的目录中，可以直接指定config；如果不在bin目录下，需要指定 with-pgconfigdir参数的目录；

另外 slony-I 中有一套Perl脚本工具，名称为 altperl scripts，可以简化slony-I的配置，默认编译不会带上该脚本，需要编译时，带上 --with perltools进行编译；

编译三板斧完成后，就完成了 slony-I的安装，一般slony-I是安装到 postgresql 数据库所在的目录中。

which postgres

which slon

which slonik

安装过程为：

[postgres@MySQL193 slony1-2.2.5]$ 
[postgres@MySQL193 slony1-2.2.5]$ ./configure --with-perltools
checking build system type... x86_64-unknown-linux-gnu
checking host system type... x86_64-unknown-linux-gnu
checking which template to use... linux
configure: using CFLAGS=
checking for gcc... gcc
checking whether the C compiler works... yes
checking for C compiler default output file name... a.out
checking for suffix of executables... 
checking whether we are cross compiling... no
checking for suffix of object files... o
checking whether we are using the GNU C compiler... yes
checking whether gcc accepts -g... yes
checking for gcc option to accept ISO C89... none needed
checking for ld used by GCC... /usr/bin/ld
checking if the linker (/usr/bin/ld) is GNU ld... yes
checking for perl... /usr/bin/perl
checking for tar... /bin/tar
checking for bison... bison -y
checking for sed... sed
checking for the pthreads library -lpthreads... no
checking whether pthreads work without any flags... no
checking whether pthreads work with -Kthread... no
checking whether pthreads work with -kthread... no
checking for the pthreads library -llthread... no
checking whether pthreads work with -pthread... yes
checking for joinable pthread attribute... PTHREAD_CREATE_JOINABLE
checking if more special flags are required for pthreads... no
checking for cc_r... gcc
checking how to run the C preprocessor... gcc -E
checking for grep that handles long lines and -e... /bin/grep
checking for egrep... /bin/grep -E
checking for ANSI C header files... yes
checking for sys/types.h... yes
checking for sys/stat.h... yes
checking for stdlib.h... yes
checking for string.h... yes
checking for memory.h... yes
checking for strings.h... yes
checking for inttypes.h... yes
checking for stdint.h... yes
checking for unistd.h... yes
checking fcntl.h usability... yes
checking fcntl.h presence... yes
checking for fcntl.h... yes
checking limits.h usability... yes
checking limits.h presence... yes
checking for limits.h... yes
checking stddef.h usability... yes
checking stddef.h presence... yes
checking for stddef.h... yes
checking sys/socket.h usability... yes
checking sys/socket.h presence... yes
checking for sys/socket.h... yes
checking sys/time.h usability... yes
checking sys/time.h presence... yes
checking for sys/time.h... yes
checking for inttypes.h... (cached) yes
checking for gettimeofday... yes
checking for dup2... yes
checking for alarm... yes
checking for memset... yes
checking for select... yes
checking for strdup... yes
checking for strerror... yes
checking for strtol... yes
checking for strtoul... yes
checking for int32_t... yes
checking for uint32_t... yes
checking for u_int32_t... yes
checking for int64_t... yes
checking for uint64_t... yes
checking for u_int64_t... yes
checking for size_t... yes
checking for ssize_t... yes
checking for POSIX signal interface... yes
checking for flex... /usr/bin/flex
configure: using 
checking if you have requested slony1-engine building... yes
checking for pg_config... /usr/local/pgsql/bin/pg_config
pg_config says pg_bindir is /usr/local/pgsql9.5.1/bin/
pg_config says pg_libdir is /usr/local/pgsql9.5.1/lib/
pg_config says pg_includedir is /usr/local/pgsql9.5.1/include/
pg_config says pg_pkglibdir is /usr/local/pgsql9.5.1/lib/
pg_config says pg_includeserverdir is /usr/local/pgsql9.5.1/include/server/
checking for correct version of PostgreSQL... pg_config says pg_sharedir is /usr/local/pgsql9.5.1/share/
checking for PQunescapeBytea in -lpq... yes
checking libpq-fe.h usability... yes
checking libpq-fe.h presence... yes
checking for libpq-fe.h... yes
checking postgres.h usability... yes
checking postgres.h presence... yes
checking for postgres.h... yes
checking for utils/typcache.h... yes
checking for plpgsql.so... yes
checking for postgresql.conf.sample... skipped due to override
checking for PQputCopyData in -lpq... yes
checking for PQsetNoticeReceiver in -lpq... yes
checking for PQfreemem in -lpq... yes
checking PostgreSQL for thread-safety... PQisthreadsafe() true
checking for ScanKeywordLookup... yes, and it takes  arguments
checking for typenameTypeId... checking for typenameTypeId... yes, and it takes 2 arguments
checking for GetActiveSnapshot... yes
checking for ScanKeywordLookup... no
checking for GetConfigOptionByName... checking for GetConfigOptionByName... yes, and it takes 2 arguments
checking for set_config_option... yes, and it takes  arguments
yes, and it takes  arguments
yes, and it takes 8 arguments
checking for set_config_option... checking for standard_conforming_strings... yes
checking whether GetTopTransactionId is declared... yes
checking for LookupExplicitNamespace 2 args... yes, and it takes 2 arguments
checking for DBD::Pg... checking if you have requested documentation building... no
configure: creating ./config.status
config.status: creating Makefile.global
config.status: creating GNUmakefile
config.status: creating slony1.spec
config.status: creating Makefile.port
config.status: creating config.h
[postgres@MySQL193 slony1-2.2.5]$ 
[postgres@MySQL193 slony1-2.2.5]$ 
[postgres@MySQL193 slony1-2.2.5]$ make
make[1]: Entering directory `/usr/local/src/slony1-2.2.5/src'
make[2]: Entering directory `/usr/local/src/slony1-2.2.5/src/parsestatements'
gcc -g -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -I/usr/local/pgsql9.5.1/include/ -c -o test-scanner.o test-scanner.c
gcc -g -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -I/usr/local/pgsql9.5.1/include/ -c -o scanner.o scanner.c
gcc -g -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -o test-scanner test-scanner.c scanner.o
./test-scanner < /dev/null > emptytestresult.log
./test-scanner < ./test_sql.sql > test_sql.log
./test-scanner < ./cstylecomments.sql > cstylecomments.log
make[2]: Leaving directory `/usr/local/src/slony1-2.2.5/src/parsestatements'
make[2]: Entering directory `/usr/local/src/slony1-2.2.5/src/slon'
gcc -g -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -pthread -I../.. -I../../src/slon -I/usr/local/pgsql9.5.1/include/ -c -o slon.o slon.c
gcc -g -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -pthread -I../.. -I../../src/slon -I/usr/local/pgsql9.5.1/include/ -c -o runtime_config.o runtime_config.c
gcc -g -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -pthread -I../.. -I../../src/slon -I/usr/local/pgsql9.5.1/include/ -c -o local_listen.o local_listen.c
gcc -g -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -pthread -I../.. -I../../src/slon -I/usr/local/pgsql9.5.1/include/ -c -o remote_listen.o remote_listen.c
gcc -g -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -pthread -I../.. -I../../src/slon -I/usr/local/pgsql9.5.1/include/ -c -o remote_worker.o remote_worker.c
gcc -g -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -pthread -I../.. -I../../src/slon -I/usr/local/pgsql9.5.1/include/ -c -o sync_thread.o sync_thread.c
gcc -g -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -pthread -I../.. -I../../src/slon -I/usr/local/pgsql9.5.1/include/ -c -o monitor_thread.o monitor_thread.c
gcc -g -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -pthread -I../.. -I../../src/slon -I/usr/local/pgsql9.5.1/include/ -c -o cleanup_thread.o cleanup_thread.c
gcc -g -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -pthread -I../.. -I../../src/slon -I/usr/local/pgsql9.5.1/include/ -c -o scheduler.o scheduler.c
gcc -g -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -pthread -I../.. -I../../src/slon -I/usr/local/pgsql9.5.1/include/ -c -o dbutils.o dbutils.c
gcc -g -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -pthread -I../.. -I../../src/slon -I/usr/local/pgsql9.5.1/include/ -c -o conf-file.o conf-file.c
conf-file.l: In function ‘ProcessConfigFile’:
conf-file.l:272: warning: label ‘cleanup_exit’ defined but not used
conf-file.l: At top level:
conf-file.c:1219: warning: ‘input’ defined but not used
gcc -g -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -pthread -I../.. -I../../src/slon -I/usr/local/pgsql9.5.1/include/ -c -o confoptions.o confoptions.c
gcc -g -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -pthread -I../.. -I../../src/slon -I/usr/local/pgsql9.5.1/include/ -c -o misc.o misc.c
gcc -g -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -pthread -I../.. -I../../src/slon -o slon slon.o runtime_config.o local_listen.o remote_listen.o remote_worker.o sync_thread.o monitor_thread.o cleanup_thread.o scheduler.o dbutils.o conf-file.o confoptions.o misc.o ../parsestatements/scanner.o -pthread -L/usr/local/pgsql9.5.1/lib/ -lpq  -Wl,-rpath,/usr/local/pgsql9.5.1/lib/  
make[2]: Leaving directory `/usr/local/src/slony1-2.2.5/src/slon'
make[2]: Entering directory `/usr/local/src/slony1-2.2.5/src/slonik'
gcc -g -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -I../.. -DPGSHARE="\"/usr/local/pgsql9.5.1/share/\""  -I/usr/local/pgsql9.5.1/include/ -c -o slonik.o slonik.c
gcc -g -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -I../.. -DPGSHARE="\"/usr/local/pgsql9.5.1/share/\""  -I/usr/local/pgsql9.5.1/include/ -c -o dbutil.o dbutil.c
gcc -g -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -I../.. -DPGSHARE="\"/usr/local/pgsql9.5.1/share/\""  -I/usr/local/pgsql9.5.1/include/ -c -o parser.o parser.c
gcc -g -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -I../.. -DPGSHARE="\"/usr/local/pgsql9.5.1/share/\""  -I/usr/local/pgsql9.5.1/include/ -c -o scan.o scan.c
scan.c:2066: warning: ‘input’ defined but not used
gcc -g -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -I../.. -DPGSHARE="\"/usr/local/pgsql9.5.1/share/\""  slonik.o dbutil.o parser.o  ../parsestatements/scanner.o scan.o -L/usr/local/pgsql9.5.1/lib/ -lpq  -Wl,-rpath,/usr/local/pgsql9.5.1/lib/ -o slonik
make[2]: Leaving directory `/usr/local/src/slony1-2.2.5/src/slonik'
make[2]: Entering directory `/usr/local/src/slony1-2.2.5/src/backend'
cp ../misc/avl_tree.c avl_tree.c
cp ../misc/avl_tree.h avl_tree.h
gcc -g -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -I../.. -fpic -I/usr/local/pgsql9.5.1/include/server/ -c -o slony1_funcs.o slony1_funcs.c
gcc -g -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -I../.. -fpic -L/usr/local/pgsql9.5.1/lib/ -lpq  -Wl,-rpath,/usr/local/pgsql9.5.1/lib/  -shared -o slony1_funcs.2.2.5.so slony1_funcs.o
make[2]: Leaving directory `/usr/local/src/slony1-2.2.5/src/backend'
make[2]: Entering directory `/usr/local/src/slony1-2.2.5/src/ducttape'
make[2]: Leaving directory `/usr/local/src/slony1-2.2.5/src/ducttape'
make[2]: Entering directory `/usr/local/src/slony1-2.2.5/src/slony_logshipper'
gcc -g -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -I../.. -DPGSHARE="\"/usr/local/pgsql9.5.1/share/\""  -I/usr/local/pgsql9.5.1/include/ -c -o slony_logshipper.o slony_logshipper.c
gcc -g -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -I../.. -DPGSHARE="\"/usr/local/pgsql9.5.1/share/\""  -I/usr/local/pgsql9.5.1/include/ -c -o dbutil.o dbutil.c
gcc -g -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -I../.. -DPGSHARE="\"/usr/local/pgsql9.5.1/share/\""  -I/usr/local/pgsql9.5.1/include/ -c -o ipcutil.o ipcutil.c
gcc -g -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -I../.. -DPGSHARE="\"/usr/local/pgsql9.5.1/share/\""  -I/usr/local/pgsql9.5.1/include/ -c -o parser.o parser.c
gcc -g -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -I../.. -DPGSHARE="\"/usr/local/pgsql9.5.1/share/\""  -I/usr/local/pgsql9.5.1/include/ -c -o scan.o scan.c
gcc -g -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -I../.. -DPGSHARE="\"/usr/local/pgsql9.5.1/share/\""  slony_logshipper.o dbutil.o ipcutil.o parser.o  ../parsestatements/scanner.o scan.o -L/usr/local/pgsql9.5.1/lib/ -lpq  -Wl,-rpath,/usr/local/pgsql9.5.1/lib/ -o slony_logshipper
make[2]: Leaving directory `/usr/local/src/slony1-2.2.5/src/slony_logshipper'
make[1]: Leaving directory `/usr/local/src/slony1-2.2.5/src'
make[1]: Entering directory `/usr/local/src/slony1-2.2.5/tools'
for subdir in altperl ; do \
          make -C $subdir all || exit; \
        done
make[2]: Entering directory `/usr/local/src/slony1-2.2.5/tools/altperl'
make[2]: Leaving directory `/usr/local/src/slony1-2.2.5/tools/altperl'
make[1]: Leaving directory `/usr/local/src/slony1-2.2.5/tools'
All of Slony-I is successfully made. Ready to install
[postgres@MySQL193 slony1-2.2.5]$ 

[root@MySQL193 slony1-2.2.5]# 
[root@MySQL193 slony1-2.2.5]# make install
make[1]: Entering directory `/usr/local/src/slony1-2.2.5/src'
make[2]: Entering directory `/usr/local/src/slony1-2.2.5/src/parsestatements'
make[2]: Nothing to be done for `install'.
make[2]: Leaving directory `/usr/local/src/slony1-2.2.5/src/parsestatements'
make[2]: Entering directory `/usr/local/src/slony1-2.2.5/src/slon'
/bin/sh /usr/local/src/slony1-2.2.5/config/mkinstalldirs /usr/local/pgsql9.5.1/bin/
/bin/sh /usr/local/src/slony1-2.2.5/config/install-sh -c -m 755 slon /usr/local/pgsql9.5.1/bin/
make[2]: Leaving directory `/usr/local/src/slony1-2.2.5/src/slon'
make[2]: Entering directory `/usr/local/src/slony1-2.2.5/src/slonik'
/bin/sh /usr/local/src/slony1-2.2.5/config/mkinstalldirs /usr/local/pgsql9.5.1/bin/
/bin/sh /usr/local/src/slony1-2.2.5/config/install-sh -c -m 755 slonik /usr/local/pgsql9.5.1/bin/
make[2]: Leaving directory `/usr/local/src/slony1-2.2.5/src/slonik'
make[2]: Entering directory `/usr/local/src/slony1-2.2.5/src/backend'
/bin/sh /usr/local/src/slony1-2.2.5/config/mkinstalldirs /usr/local/pgsql9.5.1/lib/
/bin/sh /usr/local/src/slony1-2.2.5/config/mkinstalldirs /usr/local/pgsql9.5.1/share/
/bin/sh /usr/local/src/slony1-2.2.5/config/install-sh -c -m 755 slony1_funcs.2.2.5.so /usr/local/pgsql9.5.1/lib/
for file in slony1_base.sql slony1_funcs.sql slony1_base.v83.sql slony1_base.v84.sql slony1_funcs.v83.sql slony1_funcs.v84.sql; do \
          /bin/sh /usr/local/src/slony1-2.2.5/config/install-sh -c -m 644 $file /usr/local/pgsql9.5.1/share//`basename $file .sql`.2.2.5.sql || exit ; \
        done
make[2]: Leaving directory `/usr/local/src/slony1-2.2.5/src/backend'
make[2]: Entering directory `/usr/local/src/slony1-2.2.5/src/ducttape'
/bin/sh /usr/local/src/slony1-2.2.5/config/mkinstalldirs 
make[2]: Leaving directory `/usr/local/src/slony1-2.2.5/src/ducttape'
make[2]: Entering directory `/usr/local/src/slony1-2.2.5/src/slony_logshipper'
/bin/sh /usr/local/src/slony1-2.2.5/config/mkinstalldirs /usr/local/pgsql9.5.1/bin/
/bin/sh /usr/local/src/slony1-2.2.5/config/install-sh -c -m 755 slony_logshipper /usr/local/pgsql9.5.1/bin/
make[2]: Leaving directory `/usr/local/src/slony1-2.2.5/src/slony_logshipper'
make[1]: Leaving directory `/usr/local/src/slony1-2.2.5/src'
make[1]: Entering directory `/usr/local/src/slony1-2.2.5/tools'
for subdir in altperl ; do \
          make -C $subdir install || exit; \
        done
make[2]: Entering directory `/usr/local/src/slony1-2.2.5/tools/altperl'
/bin/sh /usr/local/src/slony1-2.2.5/config/mkinstalldirs /usr/local/etc
/bin/sh /usr/local/src/slony1-2.2.5/config/mkinstalldirs /usr/local/pgsql9.5.1/lib/
/bin/sh /usr/local/src/slony1-2.2.5/config/mkinstalldirs /usr/local/bin
/bin/sh /usr/local/src/slony1-2.2.5/config/install-sh -c -m 644   slon_tools.conf-sample /usr/local/etc
/bin/sh /usr/local/src/slony1-2.2.5/config/install-sh -c -m 644   slon-tools             /usr/local/pgsql9.5.1/lib//slon-tools.pm
for file in slonik_add_node.pl slonik_build_env.pl slonik_create_set.pl slonik_drop_node.pl slonik_drop_sequence.pl slonik_drop_set.pl slonik_drop_table.pl slonik_execute_script.pl slonik_failover.pl slonik_init_cluster.pl slonik_merge_sets.pl slonik_move_set.pl slonik_print_preamble.pl slonik_restart_node.pl slonik_store_node.pl slonik_subscribe_set.pl slonik_uninstall_nodes.pl slonik_unsubscribe_set.pl slonik_update_nodes.pl slon_kill.pl slon_start.pl slon_status.pl slon_watchdog2.pl slon_watchdog.pl slony_show_configuration.pl ; do \
                tmpname=`echo $file | sed "s#\.pl##"` && \
                /bin/sh /usr/local/src/slony1-2.2.5/config/install-sh -c -m 755 $tmpname /usr/local/bin \
                || exit; \
        done
make[2]: Leaving directory `/usr/local/src/slony1-2.2.5/tools/altperl'
make[1]: Leaving directory `/usr/local/src/slony1-2.2.5/tools'
All of Slony-I is successfully installed
[root@MySQL193 slony1-2.2.5]# 
[root@MySQL193 slony1-2.2.5]# '

结果验证为：

[postgres@MySQL193 slony1-2.2.5]$ which postgres
/usr/local/pgsql/bin/postgres
[postgres@MySQL193 slony1-2.2.5]$ which slon
/usr/local/pgsql/bin/slon
[postgres@MySQL193 slony1-2.2.5]$ which slonik
/usr/local/pgsql/bin/slonik
[postgres@MySQL193 slony1-2.2.5]$ 
[root@MySQL193 slony1-2.2.5]# chown -R postgres:postgres /usr/local/pgsql/
[root@MySQL193 slony1-2.2.5]# 
[root@MySQL193 slony1-2.2.5]# ll /usr/local/pgsql/bin/postgres
-rwxr-xr-x 1 postgres postgres 6703495 Mar 16  2016 /usr/local/pgsql/bin/postgres
[root@MySQL193 slony1-2.2.5]# ll /usr/local/pgsql/bin/slon
-rwxr-xr-x 1 postgres postgres 389518 Nov 19 10:29 /usr/local/pgsql/bin/slon
[root@MySQL193 slony1-2.2.5]# ll /usr/local/pgsql/bin/slonik
-rwxr-xr-x 1 postgres postgres 274680 Nov 19 10:29 /usr/local/pgsql/bin/slonik
[root@MySQL193 slony1-2.2.5]# 

############################

第三部分  配置和验证Slony-I复制

步骤一：规划和创建复制环境：

进行复制的两个postgresql的实例端口和库名分别如下：

192.168.10.119 ： 5432  master

192.168.10.119 ： 5433  slave

在两台数据库中，都创建一个名为 slony 的超级用户，给 slony-I 使用，命令如下：

create user slony superuser password 'slonytest';

在主库上创建一个名称为  master 的数据库，作为复制的源数据库，命令为：

psql -h 192.168.10.119 -p 5432 postgres postgres

create database master;

alter database master owner to slony;

在备库上创建一个名称为 slave 的目标数据库，作为复制的目标数据库，命令为：

psql -h 192.168.10.119 -p 5433 postgres postgres

create database slave;

alter database slave owner to slony;

后面的复制，就是在 master 数据库和slave 数据库之间进行；为了演示复制，在master库和slave库中建一个测试表，命令如下：

create table synctab01(id int primary key, note text);

为了能够让机器相互付昂文，在 pg_hba.conf 文件中加入以下内容：

host all all 192.168.1.0/24 md5

为了让备注生效，运行如下命令：

pg_ctl reload

确认数据库监听，已经监听到本机的IP中，可以修改 postgresql.conf 文件中的监听选项：

listen_addresses = '*'

测试在两台机器上，分别能够用slony用户进行数据库访问：

psql -h 192.168.10.119 -p 5432 -U slony -d master

psql -h 192.168.10.119 -p 5433 -U slony -d slave

如果验证都能够正常访问，则到此，复制前的postgresql环境准备就结束了。

接下来使用 altperl scripts 脚本完成Slony数据同步的配置工作。

步骤二：编辑 slon_tools.conf 配置文件

首先要配置好一个名为 slon_tools.conf 的配置文件，这个文件要放在 /usr/local/etc 目录下。

安装完 slony-I后，这个目录下会有一个示例文件 slon_tools.conf-sample ，把这个文件拷贝成 slon_tools.conf ，然后修改。

cd /usr/local/etc

ls

cp slon_tools.conf-sample slon_tools.conf

修改文件，把文件中集群的名称改成配置的名称，这里修改为 cluster01 , 命令为：

vim slon_tools.conf

if ($ENV{"SLONYNODES"}) {
    require $ENV{"SLONYNODES"};
} else {
    #$CLUSTER_NAME = 'replication';
    $CLUSTER_NAME = 'cluster01';
    #$PIDFILE_DIR = '/var/run/slony1';
    $PIDFILE_DIR = '/usr/local/pgsql/log';    
    #$LOGDIR = '/var/log/slony1';
    $LOGDIR = '/usr/local/pgsql/log';    
#目前只有两个主从复制，只需要配置两个服务器即可
    add_node(node     => 1,
         host     => '192.168.10.119',
         dbname   => 'master',
         port     => 5432,
         user     => 'slony',
         password => 'slonytest');

    add_node(node     => 2,
         host     => '192.168.10.119',
         dbname   => 'slave',
         port     => 5433,
         user     => 'slony',
             password => 'slonytest');
}

# 设置复制集的配置项，主要配置要同步那些表和序列。

$SLONY_SETS = {
    "set1_name" => {
   "set_id"       => 1,
   "table_id"     => 1,
   "sequence_id"  => 1,
   "pkeyedtables" => ["synctab01"],
   "keyedtables"  => {},
   "sequences"    => [],
   },
};

# 上面的集群名称、日志路径、复制集中的同步主键表、唯一键表、序列等配置完成后，将 slon_tools.conf 文件拷贝到其他机器上。初始化集群。

if ($ENV{"SLONYNODES"}) {

    require $ENV{"SLONYNODES"};

} else {

    #$CLUSTER_NAME = 'replication';

    $CLUSTER_NAME = 'cluster01';

    #$PIDFILE_DIR = '/var/run/slony1';

    $PIDFILE_DIR = '/usr/local/pgsql/log';   

    #$LOGDIR = '/var/log/slony1';

    $LOGDIR = '/usr/local/pgsql/log';   

#目前只有两个主从复制，只需要配置两个服务器即可

    add_node(node     => 1,

         host     => '192.168.10.119',

         dbname   => 'master',

         port     => 5432,

         user     => 'slony',

         password => 'slonytest');

    add_node(node     => 2,

         host     => '192.168.10.119',

         dbname   => 'slave',

         port     => 5433,

         user     => 'slony',

             password => 'slonytest');

}

# 设置复制集的配置项，主要配置要同步那些表和序列。

$SLONY_SETS = {

    "set1_name" => {

   "set_id"       => 1,

   "table_id"     => 1,

   "sequence_id"  => 1,

   "pkeyedtables" => ["synctab01"],

   "keyedtables"  => {},

   "sequences"    => [],

   },

};

# 上面的集群名称、日志路径、复制集中的同步主键表、唯一键表、序列等配置完成后，将 slon_tools.conf 文件拷贝到其他机器上。初始化集群。

在一个节点配置完成后，将配置好的 slon_tools.conf 文件拷贝到其他 slony机器。

scp slon_tools.conf 192.168.10.119:`pwd`/.

步骤三：启动同步服务

slony配置文件配置完毕，并拷贝到集群中各个节点后，需要初始化和启动同步服务。

初始化集群，执行 slonik_init_cluster | slonik 命令如下：

[postgres@MySQL193 etc]$ slonik_init_cluster | slonik

<stdin>:10: Set up replication nodes

<stdin>:13: Next: configure paths for each node/origin

<stdin>:16: Replication nodes prepared

<stdin>:17: Please start a slon replication daemon for each node

[postgres@MySQL193 etc]$

命令 slonik_init_cluster 会读取配置文件 slon_tools.conf 中的内容，自动初始化 slony集群在数据库中的同步配置。后面一些 slony脚本也会读取配置文件 slon_tools.conf。

在master节点上，启动 slony 守护进程，命令后的序号对应配置文件中master节点的节点号。

[postgres@MySQL193 etc]$ slon_start 1

Invoke slon for node 1 - /usr/local/pgsql9.5.1/bin//slon -p /var/run/slony1/cluster01_node1.pid -s 1000 -d2  cluster01 'host=192.168.10.119 dbname=master user=slony port=5432 password=slonytest' > /usr/local/pgsql/log/node1/master-2016-11-19.log 2>&1 &

Slon failed to start for cluster cluster01, node node1

如果上面启动错误，需要根据日志提示，解决错误后，再次启动：

[postgres@MySQL193 etc]$ slon_start 1

Invoke slon for node 1 - /usr/local/pgsql9.5.1/bin//slon -p /usr/local/pgsql/log/cluster01_node1.pid -s 1000 -d2  cluster01 'host=192.168.10.119 dbname=master user=slony port=5432 password=slonytest' > /usr/local/pgsql/log/node1/master-2016-11-19.log 2>&1 &

Slon successfully started for cluster cluster01, node node1

PID [11298]

Start the watchdog process as well...

在slave节点上，启动 slony守护进程，命令后的序号对应配置文件中 slave 节点的节点号。

[postgres@MySQL193 etc]$ slon_start 2

Invoke slon for node 2 - /usr/local/pgsql9.5.1/bin//slon -p /var/run/slony1/cluster01_node2.pid -s 1000 -d2  cluster01 'host=192.168.10.119 dbname=slave user=slony port=5433 password=slonytest' > /usr/local/pgsql/log/node2/slave-2016-11-19.log 2>&1 &

Slon failed to start for cluster cluster01, node node2

再次启动：

[postgres@MySQL193 etc]$ slon_start 2

Invoke slon for node 2 - /usr/local/pgsql9.5.1/bin//slon -p /usr/local/pgsql/log/cluster01_node2.pid -s 1000 -d2  cluster01 'host=192.168.10.119 dbname=slave user=slony port=5433 password=slonytest' > /usr/local/pgsql/log/node2/slave-2016-11-19.log 2>&1 &

Slon successfully started for cluster cluster01, node node2

PID [11323]

Start the watchdog process as well...

启动slony守护进程时的错误处理：

第一次启动时报错，不能打开pid文件，修改文件路径：

[postgres@MySQL193 etc]$ cat /usr/local/pgsql/log/node1/master-2016-11-19.log

2016-11-19 11:51:41 CST CONFIG main: slon version 2.2.5 starting up

2016-11-19 11:51:41 CST FATAL  Cannot open pid_file "/var/run/slony1/cluster01_node1.pid"

再次启动，就可以正常启动了。

使用命令 “ slonik_create_set 1 | slonik ” 创建数据集，如下：

[postgres@MySQL193 etc]$ slonik_create_set 1 | slonik

<stdin>:11: Subscription set 1 (set1_name) created

<stdin>:12: Adding tables to the subscription set

<stdin>:16: Add primary keyed table public.synctab01

<stdin>:19: Adding sequences to the subscription set

<stdin>:20: All tables added

[postgres@MySQL193 etc]$

使用命令 “ slonik_subscribe_set 1 2 | slonik  ” 增加数据订阅者，其中命令的第一个数字 1 代表同步集号，第二个数字2代表数据订阅者的节点号，如下：

[postgres@MySQL193 etc]$ slonik_subscribe_set 1 2 | slonik

<stdin>:6: Subscribed nodes to set 1

到此，同步就配置完了。

步骤四：验证同步效果

下面测试同步效果。

在master主库上添加一条记录：
postgres=# \c master
You are now connected to database "master" as user "postgres".
master=# \d
           List of relations
 Schema |   Name    | Type  |  Owner   
--------+-----------+-------+----------
 public | synctab01 | table | postgres
(1 row)

master=# 
master=# select * from synctab01;
 id | note 
----+------
(0 rows)

master=# 
master=# insert into synctab01 values(1,'111');
INSERT 0 1
master=# 
master=# select * from synctab01;              
 id | note 
----+------
  1 | 111
(1 row)

在slave从库上确认数据状态：
postgres=# \c slave
You are now connected to database "slave" as user "postgres".
slave=# 
slave=# \d
           List of relations
 Schema |   Name    | Type  |  Owner   
--------+-----------+-------+----------
 public | synctab01 | table | postgres
(1 row)

slave=# 
slave=# select * from synctab01;
 id | note 
----+------
(0 rows)

slave=# select * from synctab01;
 id | note 
----+------
  1 | 111
(1 row)

在master主库上添加一条记录：

postgres=# \c master

You are now connected to database "master" as user "postgres".

master=# \d

           List of relations

Schema |   Name    | Type  |  Owner  

--------+-----------+-------+----------

public | synctab01 | table | postgres

(1 row)

master=#

master=# select * from synctab01;

id | note

----+------

(0 rows)

master=#

master=# insert into synctab01 values(1,'111');

INSERT 0 1

master=#

master=# select * from synctab01;             

id | note

----+------

  1 | 111

(1 row)

在slave从库上确认数据状态：

postgres=# \c slave

You are now connected to database "slave" as user "postgres".

slave=#

slave=# \d

           List of relations

Schema |   Name    | Type  |  Owner  

--------+-----------+-------+----------

public | synctab01 | table | postgres

(1 row)

slave=#

slave=# select * from synctab01;

id | note

----+------

(0 rows)

slave=# select * from synctab01;

id | note

----+------

  1 | 111

(1 row)

至此两个 postgresql 单实例之间的主从同步就确认完毕了。