Anyone help me with this, I want to replicate postgresql data using pgpool in one Vm with other two Vm’s. I am new to this field I installed postgresql-9.3 in three Vm’s and pgpool-II in one Vm’s. Now I want to know how to replicate data between vm’s and What are the necessary changes in Pgpool.conf file, pool_hba.conf file and pcp.conf? What changes should I make in postgresql file like postgresql.conf & pg_hba.conf and where should I make these changes whether in primary node like in Vm I installed pgpool-II or in slave node other two Vm’s?
Please help me with this I am in confusing state and provide me good websites or tutorials to follow this?
Update see the config file - below
STANDBY SERVER
pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
#host all all 127.0.0.1/32 ident
# IPv6 local connections:
#host all all ::1/128 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local replication postgres peer
#host replication postgres 127.0.0.1/32 ident
#host replication postgres ::1/128 ident
# host all all 0.0.0.0.0 trust
host all postgres 0.0.0.0.0 trust
# host all all primaryipaddress trust
local all all trust
host all all 192.168.1.0/24 md5
postgresql.conf
listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
port = 5433
wal_level = hot_standby
#archive_command = '‘cp %p /var/lib/pgsql/wal_archive_27/%f'
max_wal_senders = 2
hot_standby = on
Primary Server
pgpool.conf
# - pgpool Connection Settings -
listen_addresses = '*'
socket_dir = '/var/ru
# - pgpool Communication Manager Connection Settings -
pcp_port = 9898
pcp_socket_dir = '/var/run'
# - Backend Connection Settings -
backend_hostname0 = 'xxxxxxx’'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/pgsql/9.3/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
#backend_hostnamex1 = 'xxxxxxxx’
#backend_port1 = 5433
#backend_weight1 = 1
#backend_data_directory1 = '/var/lib/pgsql/9.3/data'
#backend_flag1 = 'ALLOW_TO_FAILOVER'ckend_hostname1 = '3rd vm'
backend_hostname1 = 'xxxxxxxxxxxx’
backend_port1 = 5433
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/9.3/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
# - What to log -
print_timestamp = on
log_connections = on
log_hostname = on
log_statement = on
log_per_node_statement = on
log_standby_delay = 'if_over_threshold'
# FILE LOCATIONS
pid_file_name = '/var/run/pgpool-II-93/pgpool.pid'
logdir = '/var/log/pgpool'
# REPLICATION MODE
replication_mode = on
replicate_select = on
# - Degenerate handling -
replication_stop_on_mismatch = on
# LOAD BALANCING
load_balance_mode = on
# HEALTH CHECK
health_check_user = 'apache'
pg_hba.conf
# TYPE DATABASE USER CIDR-ADDRESS METHOD
host all all 0.0.0.0/0 md5
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all postgres 127.0.0.1/32 trust
# Allow any user from any host with IP address 192.168.93.x to connect
# to database "postgres" as the same user name that ident reports for
# the connection (typically the Unix user name).
#
# TYPE DATABASE USER CIDR-ADDRESS METHOD
host postgres all xxxxxxxx ident
# Allow a user from host 192.168.12.10 to connect to database
# "postgres" if the user's password is correctly supplied.
#
# TYPE DATABASE USER CIDR-ADDRESS METHOD
host postgres all xxxxxxxxx md5
host all postgres xxxxxxxxxx trust
#host all postgres xxxxxxxxxx trust
postgresql.conf
wal_level = hot_standby
#archive_mode = on
max_wal_senders = 2
hot_standby = on
log_statement = 'all'
Thanks in advance
Pal
