host all all 0.0.0.0/0 trusthost replication all 0.0.0.0/0 trust

listen_addresses = '*'max_wal_senders = 5wal_level = hot_standbypg_ctl -D /mnt/40g/pgdata/pgpool start

mkdir /pgpoolchown postgres:postgres /pgpoolchown postgres:postgres /mnt/40g/software/pgpool-II-4.1.2.tar.gz

cd /mnt/40g/software/tar -xzvf pgpool-II-4.1.2.tar.gzcd pgpool-II-4.1.2./configure --prefix='/pgpool' --with-pgsql-libdir="/usr/local/hg_dist/lib" --with-pgsql="/usr/local/hg_dist/"makemake install

cd /mnt/40g/software/pgpool-II-4.1.2/src/sql/pgpool-regclass/makemake install

scppgpool-regclass.so postgres@maggie2:/usr/local/hg_dist/lib/postgresqlpsql -f pgpool-regclass.sql -h maggie2 -p 5432 template1

pg_basebackup -h 192.168.31.102 -p 5432 -U postgres -Fp -Xs -Pv -R -D /mnt/40g/pgdata/pgpoolpg_ctl start

[root@maggie1 etc]# pg_md5 -p postgrespassword:e8a48653851e28c69d0506508fb27fc5

cd /usr/local/etc #pgpool的默认安装路径cp pgpool.conf.sample pgpool.conflisten_addresses='*' #接受所有的链接socket_dir='/var/run' #建立接受 UNIX 域套接字连接的目录，修改要重启pgpoolpcp_listen_addresses = '*'pcp_socket_dir = '/var/run'backend_hostname0 = 'maggie1'backend_port0 = 5432backend_weight0 = 1 #负载均衡的权限，两台backend权限相同时，做平均分配backend_data_directory0 = '/mnt/40g/pgdata/pgpool'backend_hostname1 = 'maggie2'backend_port1 = 5432backend_weight1 = 1backend_data_directory1 = '/mnt/40g/pgdata/pgpool'load_balance_mode = onmaster_slave_mode = onmaster_slave_sub_mode = 'stream'sr_check_period = 2sr_check_user = 'postgres'sr_check_database = 'postgres'delay_threshold = 10replication_mode = offreplicate_select = offnum_init_children = 150 // 从客户端发起的最大并发连接数max_pool = 4 //到PG的连接数为num_init_children * max_pool

pgpool -n  > /tmp/pgpool.log 2>&1 &pgpool stop

[postgres@maggie1 ~]$ pgbench -c 300 -j 300 -M prepared -n -P 2 -S -T 200 -r -h maggie1 -p 9999transaction type: <builtin: select only>scaling factor: 100query mode: preparednumber of clients: 300number of threads: 300duration: 200 snumber of transactions actually processed: 2898267latency average = 10.293 mslatency stddev = 5.346 mstps = 14445.955582 (including connections establishing)tps = 25308.963441 (excluding connections establishing) 

 0.005 \set aid random(1,100000 * :scale)  10.290 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

[postgres@maggie1 ~]$ pgbench -c 300 -j 300 -M prepared -n -P 2 -S -T 200 -r -h maggie2 -p 5432transaction type: <builtin: select only>scaling factor: 100query mode: preparednumber of clients: 300number of threads: 300duration: 200 snumber of transactions actually processed: 4662318latency average = 12.758 mslatency stddev = 3.460 mstps = 23281.878597 (including connections establishing)tps = 23473.435897 (excluding connections establishing)

   0.003  \set aid random(1, 100000 * :scale)       12.762  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

[postgres@maggie1 ~]$ pgbench -c 400 -j 300 -M prepared -n -P 2 -S -T 200 -r -h maggie1 -p 9999transaction type: <builtin: select only>scaling factor: 100query mode: preparednumber of clients: 400number of threads: 300duration: 200 snumber of transactions actually processed: 2879549latency average = 6.795 mslatency stddev = 3.551 mstps = 14357.998016 (including connections establishing)tps = 20360.802379 (excluding connections establishing)

 0.004 \set aid random(1, 100000 * :scale)   6.792  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

[postgres@maggie1 ~]$ pgbench -c 400 -j 400 -M prepared -n -P 2 -S -T 200 -r -h maggie2 -p 5432transaction type: <builtin: select only>scaling factor: 100query mode: preparednumber of clients: 400number of threads: 400duration: 200 snumber of transactions actually processed: 4523310latency average = 17.420 mslatency stddev = 4.249 mstps = 22587.966748 (including connections establishing)tps = 22910.531557 (excluding connections establishing)

 0.004 \set aid random(1, 100000 * :scale)  17.424 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;