/sbin/iptables -A INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT

iptables-save > /etc/iptables/rules.v4

# cat /etc/pgpool-II/pcp.conf
postgres:e8a48653851e28c69d0506508fb27fc5

# pg_md5 postgres
e8a48653851e28c69d0506508fb27fc5

pcp_port = 9898

# cat /etc/pgpool-II/pgpool.conf
listen_addresses = ‘*’ # Слушаем все адреса, ибо слушать localhost смысла нет, а тут либо он, либо все
port = 5432 # Вешаем на стандартный порт pgsql. Слоняры будут у меня слушать порт 5433

socket_dir = ‘/tmp’
pcp_port = 9898  # Cм. выше
pcp_socket_dir = ‘/tmp’

enable_pool_hba = off # Отключаем встроеный механизм авторизации – она будет происходить на серверах баз данных
authentication_timeout = 60
ssl = off
#ssl_key = ‘./server.key’
#ssl_cert = ‘./server.cert’
#ssl_ca_cert = ”
#ssl_ca_cert_dir = ”

#——————————————————————————
# Настройки пулов
#——————————————————————————
num_init_children = 32
max_pool = 4
child_life_time = 300
child_max_connections = 0
connection_life_time = 0
client_idle_limit = 0

#——————————————————————————
# Логи
#——————————————————————————
log_destination = ‘syslog’
print_timestamp = on
log_connections = on
log_hostname = on
log_statement = on
log_per_node_statement = on
log_standby_delay = ‘always’

syslog_facility = ‘LOCAL0’
syslog_ident = ‘pgpool’
debug_level = 1

#——————————————————————————
# Что куда кладем
#——————————————————————————

pid_file_name = ‘/var/run/pgpool-II/pgpool.pid’
logdir = ‘/var/log/pgpool-II’

#——————————————————————————
# Пулинг соединений
#——————————————————————————

connection_cache = on
reset_query_list = ‘ABORT; DISCARD ALL’
#reset_query_list = ‘ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT’

#——————————————————————————
# Режим репликации – его и будем использовать. В данном
# режиме все модифицирующие запросы типа UPDATE,
# DELETE, INSERT отправляются сразу на все узлы баз даных,
# а запросы SELECT рулятся исходя из настроек ниже
#——————————————————————————

replication_mode = on # Включаем
replicate_select = off # Реплицировать-ли SELECT?
# Мне это не нужно, я буду юзать режим LB (см. ниже)
insert_lock = on
lobj_lock_table = ”
replication_stop_on_mismatch = off
failover_if_affected_tuples_mismatch = off

#——————————————————————————
# Распределение нагрузки
#——————————————————————————

load_balance_mode = on #  распределяем SELECT между узлами базы данных.
ignore_leading_white_space = on
white_function_list = ”
black_function_list = ‘nextval,setval’

#——————————————————————————
# MASTER/SLAVE – Горизонтальная репликация
#——————————————————————————

master_slave_mode = off # Нам оно не нужно
master_slave_sub_mode = ‘slony’
sr_check_period = 0
sr_check_user = ‘nobody’
sr_check_password = ”
delay_threshold = 0
follow_master_command = ”

#——————————————————————————
# Параллельные запросы и кэширование
#——————————————————————————

parallel_mode = off
enable_query_cache = off
pgpool2_hostname = ”
system_db_hostname = ‘localhost’
system_db_port = 5432
system_db_dbname = ‘pgpool’
system_db_schema = ‘pgpool_catalog’
system_db_user = ‘pgpool’
system_db_password = ”

#——————————————————————————
# Проверка состояния.
#——————————————————————————

health_check_period = 0
health_check_timeout = 20
health_check_user = ‘nobody’
health_check_password = ”

#——————————————————————————
# Фэйловеры
#——————————————————————————

failover_command = ”
# Executes this command at failover
# Special values:
#   %d = node id
#   %h = host name
#   %p = port number
#   %D = database cluster path
#   %m = new master node id
#   %H = hostname of the new master node
#   %M = old master node id
#   %P = old primary node id
#   %% = ‘%’ character
failback_command = ”
# Executes this command at failback.
# Special values:
#   %d = node id
#   %h = host name
#   %p = port number
#   %D = database cluster path
#   %m = new master node id
#   %H = hostname of the new master node
#   %M = old master node id
#   %P = old primary node id
#   %% = ‘%’ character

fail_over_on_backend_error = off

#——————————————————————————
# Автовосстановление – тоже не наш случай
#——————————————————————————

recovery_user = ‘nobody’
recovery_password = ”

recovery_1st_stage_command = ”

recovery_2nd_stage_command = ”

recovery_timeout = 90

client_idle_limit_in_recovery = 0

#——————————————————————————
# Узлы – тут думаю все понятно :)
#——————————————————————————

relcache_expire = 0

backend_hostname0 = ‘192.168.50.1’
backend_port0 = 5433
backend_weight0 = 1
backend_flag0= ‘ALLOW_TO_FAILOVER’
backend_hostname1 = ‘192.168.50.3’
backend_port1 = 5433
backend_weight1 = 1
backend_flag1= ‘ALLOW_TO_FAILOVER’

# cat /var/lib/pgsql/data/pg_hba.conf
# PostgreSQL Client Authentication Configuration File
# ===================================================
#
# Refer to the “Client Authentication” section in the PostgreSQL
# documentation for a complete description of this file.  A short
# synopsis follows.
#
# This file controls: which hosts are allowed to connect, how clients
# are authenticated, which PostgreSQL user names they can use, which
# databases they can access.  Records take one of these forms:
#
# local      DATABASE  USER  METHOD  [OPTIONS]
# host       DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostssl    DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostnossl  DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
#
# (The uppercase items must be replaced by actual values.)
#
# The first field is the connection type: “local” is a Unix-domain
# socket, “host” is either a plain or SSL-encrypted TCP/IP socket,
# “hostssl” is an SSL-encrypted TCP/IP socket, and “hostnossl” is a
# plain TCP/IP socket.
#
# DATABASE can be “all”, “sameuser”, “samerole”, “replication”, a
# database name, or a comma-separated list thereof. The “all”
# keyword does not match “replication”. Access to replication
# must be enabled in a separate record (see example below).
#
# USER can be “all”, a user name, a group name prefixed with “+”, or a
# comma-separated list thereof.  In both the DATABASE and USER fields
# you can also write a file name prefixed with “@” to include names
# from a separate file.
#
# ADDRESS specifies the set of hosts the record matches.  It can be a
# host name, or it is made up of an IP address and a CIDR mask that is
# an integer (between 0 and 32 (IPv4) or 128 (IPv6) inclusive) that
# specifies the number of significant bits in the mask.  A host name
# that starts with a dot (.) matches a suffix of the actual host name.
# Alternatively, you can write an IP address and netmask in separate
# columns to specify the set of hosts.  Instead of a CIDR-address, you
# can write “samehost” to match any of the server’s own IP addresses,
# or “samenet” to match any address in any subnet that the server is
# directly connected to.
#
# METHOD can be “trust”, “reject”, “md5”, “password”, “gss”, “sspi”,
# “krb5”, “ident”, “peer”, “pam”, “ldap”, “radius” or “cert”.  Note that
# “password” sends passwords in clear text; “md5” is preferred since
# it sends encrypted passwords.
#
# OPTIONS are a set of options for the authentication in the format
# NAME=VALUE.  The available options depend on the different
# authentication methods — refer to the “Client Authentication”
# section in the documentation for a list of which options are
# available for which authentication methods.
#
# Database and user names containing spaces, commas, quotes and other
# special characters must be quoted.  Quoting one of the keywords
# “all”, “sameuser”, “samerole” or “replication” makes the name lose
# its special character, and just match a database or username with
# that name.
#
# This file is read on server startup and when the postmaster receives
# a SIGHUP signal.  If you edit the file on a running system, you have
# to SIGHUP the postmaster for the changes to take effect.  You can
# use “pg_ctl reload” to do that.

# Put your actual configuration here
# ———————————-
#
# If you want to allow non-local connections, you need to add more
# “host” records.  In that case you will also need to make PostgreSQL
# listen on a non-local interface via the listen_addresses
# configuration parameter, or via the -i or -h command line switches.

# 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             192.168.50.1/32         ident
host     all             all             192.168.50.3/32         ident

# cat /etc/ha.d/ha.cf
debugfile /var/log/ha-debug
logfile /var/log/ha-log

#logfacility    local0
keepalive 2
deadtime 30
warntime 10
initdead 120
udpport 694
baud    19200

bcast   eth1

auto_failback on

node    host1.domain.tld
node    host2.domain.tld

max_rexmit_delay 1000
deadping 30
debug 0

# cat /etc/ha.d/haresources
#node-name resource1 resource2 … resourceN
host1.domain.tld IPaddr::192.168.50.254/24/eth1 pgpool

# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain mx1.torrents.kg
192.168.50.254    pgpool.domain.tld
192.168.50.1    host1.domain.tld
192.168.50.3    host2.domain.tld

# chkconfig postgresql on
# chkconfig pgpool off
# chkconfig heartbeat on

# service postgresql start && ssh 192.168.50.3 /etc/init.d/postgresql start
# service heartbeat start && ssh 192.168.50.3 /etc/init.d/heartbeat start

# netstat -nltp | grep pgpool
tcp        0      0 0.0.0.0:9898                0.0.0.0:*                   LISTEN      685/pgpool: wait fo
tcp        0      0 0.0.0.0:5432                0.0.0.0:*                   LISTEN      685/pgpool: wait fo

# ip addr show
2: eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc noqueue state UNKNOWN qlen 10000
link/ether bc:30:5b:68:e1:71 brd ff:ff:ff:ff:ff:ff
inet 192.168.50.1/24 brd 192.168.50.255 scope global eth1
inet 192.168.50.254/24 brd 192.168.50.255 scope global eth1:0
inet6 fe80::be30:5bff:fe68:e171/64 scope link
valid_lft forever preferred_lft forever

#su postgres
$ createdb -p 5432 -h pgpool.domain.tld test # выполнить только на одной машине
$ psql -p 5433 -d test

psql: СБОЙ:  база данных “test” не существует

psql (9.1.3)
Введите “help”, чтобы получить справку.

test-# \q