参看:
同一台机子不同端口
下载安装pgpool:
配置pool_hba.conf
cp /opt/pgpool/etc/pool_hba.conf.sample
/opt/pgpool/etc/pool_hba.conf
# "local" is for Unix domain socket connections only local all all trust # IPv4 local connections: host all all 127.0.0.1/32 trust host all all 127.0.0.1 trust
配置
pgpool.conf
:
cp /opt/pgpool/etc/pool_hba.conf.sample
/opt/pgpool/etc/pool_hba.conf
listen_addresses = '*' port = 9999 pcp_port = 9898 # - Backend Connection Settings - backend_hostname0 = '127.0.0.1' # Host name or IP address to connect to for backend 0 backend_port0 = 5001 # Port number for backend 0 backend_weight0 = 1 # Weight for backend 0 (only in load balancing mode) backend_data_directory0 = '/home/pg93/ms/master' # Data directory for backend 0 backend_flag0 = 'ALLOW_TO_FAILOVER' # Controls various backend behavior # ALLOW_TO_FAILOVER or DISALLOW_TO_FAILOVER backend_hostname1 = '127.0.0.1' backend_port1 = 5002 backend_weight1 = 1 backend_data_directory1 = '/home/pg93/ms/slave' backend_flag1 = 'ALLOW_TO_FAILOVER' log_statement = on log_per_node_statement= on pid_file_name = '/opt/pgpool/pgpool.pid' logdir = '/opt/pgpool/log' failover_command = '/opt/pgpool/etc/failedcommand.sh %d %H /tmp/trigger_file' replication_mode= off master_slave_mode =on master_slave_sub_mode = 'stream' sr_check_user = 'postgres' #sr_check_password = '' #如果是trust则注释掉 load_balance_mode = on log_connections = on log_hostname = on debug_level = 1 delay_threshold = 1000
master:
initdb -D
/home/pg93/ms/master
-E UTF8 --locale=C -U postgres -W
配置pg_hba.conf
配置postgresql.conf# "local" is for Unix domain socket connections only local all all trust # IPv4 local connections: host all all 127.0.0.1/32 trust # IPv6 local connections: host all all ::1/128 trust # Allow replication connections from localhost, by a user with the # replication privilege. #local replication postgres trust host replication postgres 127.0.0.1/32 trust host replication postgres ::1/128 trust host all all 0.0.0.0/0 trust host replication postgres 127.0.0.1/32 trust
listen_addresses = '*' port = 5001 # primary server需要 wal_level = hot_standby # 这个不是必需的 archive_mode = on archive_command = 'cp %p /var/lib/postgresql/9.1/main/archive/%f' # primary server需要 max_wal_senders = 32 # standby server需要 hot_standby = onlogging_collector = on synchronous_commit = off wal_keep_segments = 32 synchronous_standby_names = '*'
pg_ctl start -D /home/pg93/ms/master
psql -h 127.0.0.1 -p 5001 -U postgres postgres
select pg_start_backup('tag_xx');
select pg_is_in_backup();#查看是否在备份中backup。返回"t"表示true。“f”表示false。
\q 退出#
cp -r /home/pg93/ms/master/* /home/pg93/ms/slave/.
psql -h 127.0.0.1 -p 5001 -U postgres postgres
select pg_stop_backup();
\q
slaver:
配置postgresql.conf
pg_ctl start -D /home/pg93/ms/slaveport = 5002 hot_standby = on
启动pgpool:
pgpool -nd
检查pgpool节点:
psql -h 127.0.0.1 -p 9999
show pool_nodes;
digoal=# show pool_nodes; node_id | hostname | port | status | lb_weight | role ---------+-----------+------+--------+-----------+--------- 0 | 127.0.0.1 | 5001 | 3 | 0.500000 | standby 1 | 127.0.0.1 | 5002 | 2 | 0.500000 | standby (2 rows)
测试pgpool流复制:
psql -h 127.0.0.1 -p 9999
create table xxv(a int);
psql -h 127.0.0.1 -p 5002
select * from xxv;
测试pgpool的failover故障恢复:
pg_ctl -m fast stop -D
/home/pg93/ms/master
psql -h 127.0.0.1 -p 9999#如果能够正常访问则无问题。