PostgreSQL 数据库HAProxy和PgBouncer配置高可用架构

fdsdff

一.OS Packages
CentOS 7.2 X64
pcre-devel
openssl-devel
zlib-devel

二.编译安装haproxy
1.编译安装
haproxy-1.7.1.tar.gz

# make TARGET=linux2628 USE_PCRE=1 USE_OPENSSL=1 USE_ZLIB=1 PREFIX=/usr/local/haproxy
......
......
......
oto_http.o src/raw_sock.o src/backend.o src/tcp_rules.o src/lb_chash.o src/lb_fwlc.o src/lb_fwrr.o src/lb_map.o src/lb_fas.o src/stream_interface.o src/stats.o src/proto_tcp.o src/applet.o src/session.o src/stream.o src/hdr_idx.o src/ev_select.o src/signal.o src/acl.o src/sample.o src/memory.o src/freq_ctr.o src/auth.o src/proto_udp.o src/compression.o src/payload.o src/hash.o src/pattern.o src/map.o src/namespace.o src/mailers.o src/dns.o src/vars.o src/filters.o src/flt_http_comp.o src/flt_trace.o src/flt_spoe.o src/cli.o src/ev_poll.o src/ev_epoll.o src/ssl_sock.o src/shctx.o ebtree/ebtree.o ebtree/eb32tree.o ebtree/eb64tree.o ebtree/ebmbtree.o ebtree/ebsttree.o ebtree/ebimtree.o ebtree/ebistree.o   -lcrypt  -lz -ldl  -lssl -lcrypto -ldl -L/usr/lib -lpcreposix -lpcre
gcc -Iinclude -Iebtree -Wall  -O2 -g -fno-strict-aliasing -Wdeclaration-after-statement       -DCONFIG_HAP_LINUX_SPLICE -DTPROXY -DCONFIG_HAP_LINUX_TPROXY -DCONFIG_HAP_CRYPT -DUSE_ZLIB  -DENABLE_POLL -DENABLE_EPOLL -DUSE_CPU_AFFINITY -DASSUME_SPLICE_WORKS -DUSE_ACCEPT4 -DNETFILTER -DUSE_GETSOCKNAME -DUSE_OPENSSL  -DUSE_SYSCALL_FUTEX -DUSE_PCRE -I/usr/include  -DCONFIG_HAPROXY_VERSION=\"1.7.1\" -DCONFIG_HAPROXY_DATE=\"2016/12/13\" \
      -DSBINDIR='"/usr/local/haproxy/sbin"' \
       -c -o src/haproxy-systemd-wrapper.o src/haproxy-systemd-wrapper.c
gcc  -g -o haproxy-systemd-wrapper src/haproxy-systemd-wrapper.o   -lcrypt  -lz -ldl  -lssl -lcrypto -ldl -L/usr/lib -lpcreposix -lpcre
#
# make install PREFIX=/usr/local/haproxy
install -d "/usr/local/haproxy/sbin"
install haproxy  "/usr/local/haproxy/sbin"
install -d "/usr/local/haproxy/share/man"/man1
install -m 644 doc/haproxy.1 "/usr/local/haproxy/share/man"/man1
install -d "/usr/local/haproxy/doc/haproxy"
for x in configuration management architecture cookie-options lua WURFL-device-detection proxy-protocol linux-syn-cookies network-namespaces DeviceAtlas-device-detection 51Degrees-device-detection netscaler-client-ip-insertion-protocol close-options SPOE intro; do \
        install -m 644 doc/$x.txt "/usr/local/haproxy/doc/haproxy" ; \
done
#

2.配置文件

# pwd
/usr/local/haproxy
# mkdir etc
# vi ha_proxy.conf
global
        log 127.0.0.1 local0 info
        maxconn 40960
        user haproxy
        group haproxy
        daemon
        nbproc 1
        chroot /usr/local/haproxy
        pidfile /usr/local/haproxy/haproxy.pid
defaults
        log global
        mode tcp
        option tcplog
        option dontlognull
        option redispatch
        retries 3
        maxconn 2000
        timeout connect 5s
        timeout client 360s
        timeout server 360s
frontend postgres-front
        bind 192.168.199.201:5432
        default_backend postgresql
backend postgresql
        mode tcp
        option pgsql-check user haproxy
        balance roundrobin
        server postgresql_01 192.168.199.201:5430 weight 1 check inter 5s rise 2 fall 3
        server postgresql_02 192.168.199.201:5431 weight 1 check inter 5s rise 2 fall 3
listen admin-stats
        mode http
        option httplog
        bind 0.0.0.0:8888
        stats enable
        stats refresh 30s
        stats uri /dbs
        stats realm welcome login\ Haproxy
        stats auth admin:admin
        stats admin if TRUE
#
#

3.配置PATH变量

# vi /etc/profile

unset -f pathmunge
export PATH=/usr/local/haproxy/sbin:$PATH

三、安装pgbouncer
1.安装libevent
libevent-2.0.22-stable.tar.gz
# ./configure
# make
# make install

ln -s /usr/local/lib/libevent-2.0.so.5 /usr/lib64/libevent-2.0.so.5


2.安装pgbouncer

# ./configure --prefix=/opt/pgbouncer/1.17.2
......
......

Results
  c-ares = no
  evdns = yes
  udns = no
  tls = yes
# make
# make install

# cd /opt/pgbouncer/1.17.2/
# mkdir etc log
#

配置文件
# cd /opt/pgbouncer/1.17.2/share/doc/pgbouncer
# cp pgbouncer.ini userlist.txt /opt/pgbouncer/1.17.2/etc/
# cd /opt/pgbouncer/1.17.2/etc/

vi pgbouncer.ini

[iyunv@localhost etc]# more pgbouncer.ini
;; database name = connect string
;;
;; connect string params:
;;   dbname= host= port= user= password=
;;   client_encoding= datestyle= timezone=
;;   pool_size= connect_query=
[databases]
;; * indicate testdb, haproxy
* = port=6432 dbname=testdb user=postgres password=111111

; foodb over unix socket
;foodb =

; redirect bardb to bazdb on localhost
;bardb = host=localhost dbname=bazdb

; access to dest database will go with single user
;forcedb = host=127.0.0.1 port=300 user=baz password=foo client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'

; use custom pool sizes
;nondefaultdb = pool_size=50 reserve_pool_size=10

; fallback connect string
;* = host=testserver

;; Configuration section
[pgbouncer]

;;;
;;; Administrative settings
;;;

logfile = /opt/pgbouncer/1.17.2/log/pgbouncer.log
pidfile = /opt/pgbouncer/1.17.2/bin/pgbouncer.pid

;;;
;;; Where to wait for clients
;;;

; ip address or * which means all ip-s
listen_addr = *
listen_port = 5430

; unix socket is also used for -R.
; On debian it should be /var/run/postgresql
;unix_socket_dir = /tmp
;unix_socket_mode = 0777
;unix_socket_group =

;;;
;;; TLS settings for accepring clients
;;;

;; disable, allow, require, verify-ca, verify-full
;client_tls_sslmode = disable

;; Path to file that contains trusted CA certs
;client_tls_ca_file = <system default>

;; Private key and cert to present to clients.
;; Required for accepting TLS connections from clients.
;client_tls_key_file =
;client_tls_cert_file =

;; fast, normal, secure, legacy, <ciphersuite string>
;client_tls_ciphers = fast

;; all, secure, tlsv1.0, tlsv1.1, tlsv1.2
;client_tls_protocols = all

;; none, auto, legacy
;client_tls_dheparams = auto

;; none, auto, <curve name>
;client_tls_ecdhcurve = auto

;;;
;;; TLS settings for connecting to backend databases
;;;

;; disable, allow, require, verify-ca, verify-full
;server_tls_sslmode = disable

;; Path to that contains trusted CA certs
;server_tls_ca_file = <system default>

;; Private key and cert to present to backend.
;; Needed only if backend server require client cert.
;server_tls_key_file =
;server_tls_cert_file =

;; all, secure, tlsv1.0, tlsv1.1, tlsv1.2
;server_tls_protocols = all

;; fast, normal, secure, legacy, <ciphersuite string>
;server_tls_ciphers = fast

;;;
;;; Authentication settings
;;;

; any, trust, plain, crypt, md5
auth_type = md5
;auth_file = /8.0/main/global/pg_auth
auth_file = /opt/pgbouncer/1.17.2/etc/userlist.txt

;; Path to HBA-style auth config
;auth_hba_file =

;; Query to use to fetch password from database.  Result
;; must have 2 columns - username and password hash.
;auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1

;;;
;;; Users allowed into database 'pgbouncer'
;;;

; comma-separated list of users, who are allowed to change settings
;admin_users = user2, someadmin, otheradmin
admin_users = pgbadmin

; comma-separated list of users who are just allowed to use SHOW command
;stats_users = stats, root

;;;
;;; Pooler personality questions
;;;

; When server connection is released back to pool:
;   session      - after client disconnects
;   transaction  - after transaction finishes
;   statement    - after statement finishes
pool_mode = transaction

;
; Query for cleaning connection immediately after releasing from client.
; No need to put ROLLBACK here, pgbouncer does not reuse connections
; where transaction is left open.
;
; Query for 8.3+:
;   DISCARD ALL;
;
; Older versions:
;   RESET ALL; SET SESSION AUTHORIZATION DEFAULT
;
; Empty if transaction pooling is in use.
;
server_reset_query =


; Whether server_reset_query should run in all pooling modes.
; If it is off, server_reset_query is used only for session-pooling.
;server_reset_query_always = 0

;
; Comma-separated list of parameters to ignore when given
; in startup packet.  Newer JDBC versions require the
; extra_float_digits here.
;
;ignore_startup_parameters = extra_float_digits

;
; When taking idle server into use, this query is ran first.
;   SELECT 1
;
server_check_query = select 1

; If server was used more recently that this many seconds ago,
; skip the check query.  Value 0 may or may not run in immediately.
server_check_delay = 10

;; Use <appname - host> as application_name on server.
;application_name_add_host = 0

;;;
;;; Connection limits
;;;

; total number of clients that can connect
max_client_conn = 10240

; default pool size.  20 is good number when transaction pooling
; is in use, in session pooling it needs to be the number of
; max clients you want to handle at any moment
default_pool_size = 100

;; Minimum number of server connections to keep in pool.
min_pool_size = 80

; how many additional connection to allow in case of trouble
;reserve_pool_size = 5

; if a clients needs to wait more than this many seconds, use reserve pool
;reserve_pool_timeout = 3

; how many total connections to a single database to allow from all pools
;max_db_connections = 50
;max_user_connections = 50

; If off, then server connections are reused in LIFO manner
;server_round_robin = 0

;;;
;;; Logging
;;;

;; Syslog settings
;syslog = 0
;syslog_facility = daemon
;syslog_ident = pgbouncer

; log if client connects or server connection is made
;log_connections = 1

; log if and why connection was closed
;log_disconnections = 1

; log error messages pooler sends to clients
;log_pooler_errors = 1

;; Period for writing aggregated stats into log.
;stats_period = 60

;; Logging verbosity.  Same as -v switch on command line.
;verbose=0

;;;
;;; Timeouts
;;;

;; Close server connection if its been connected longer.
server_lifetime = 300

;; Close server connection if its not been used in this time.
;; Allows to clean unnecessary connections from pool after peak.
;server_idle_timeout = 60

;; Cancel connection attempt if server does not answer takes longer.
server_connect_timeout = 10

;; If server login failed (server_connect_timeout or auth failure)
;; then wait this many second.
server_login_retry = 3

;; Dangerous.  Server connection is closed if query does not return
;; in this time.  Should be used to survive network problems,
;; _not_ as statement_timeout. (default: 0)
;query_timeout = 0

;; Dangerous.  Client connection is closed if the query is not assigned
;; to a server in this time.  Should be used to limit the number of queued
;; queries in case of a database or network failure. (default: 120)
;query_wait_timeout = 120

;; Dangerous.  Client connection is closed if no activity in this time.
;; Should be used to survive network problems. (default: 0)
;client_idle_timeout = 0

;; Disconnect clients who have not managed to log in after connecting
;; in this many seconds.
;client_login_timeout = 60

;; Clean automatically created database entries (via "*") if they
;; stay unused in this many seconds.
; autodb_idle_timeout = 3600

;; How long SUSPEND/-R waits for buffer flush before closing connection.
;suspend_timeout = 10

;; Close connections which are in "IDLE in transaction" state longer than
;; this many seconds.
idle_transaction_timeout = 300

;;;
;;; Low-level tuning options
;;;

;; buffer for streaming packets
;pkt_buf = 4096

;; man 2 listen
listen_backlog = 2048

;; Max number pkt_buf to process in one event loop.
;sbuf_loopcnt = 5

;; Maximum Postgres protocol packet size.
;max_packet_size = 2147483647

;; networking options, for info: man 7 tcp

;; Linux: notify program about new connection only if there
;; is also data received.  (Seconds to wait.)
;; On Linux the default is 45, on other OS'es 0.
;tcp_defer_accept = 0

;; In-kernel buffer size (Linux default: 4096)
;tcp_socket_buffer = 0

;; whether tcp keepalive should be turned on (0/1)
;tcp_keepalive = 1

;; following options are Linux-specific.
;; they also require tcp_keepalive=1

;; count of keepaliva packets
;tcp_keepcnt = 0

;; how long the connection can be idle,
;; before sending keepalive packets
;tcp_keepidle = 0

;; The time between individual keepalive probes.
;tcp_keepintvl = 0

;; DNS lookup caching time
;dns_max_ttl = 15

;; DNS zone SOA lookup period
;dns_zone_check_period = 0

;; DNS negative result caching time
;dns_nxdomain_ttl = 15

;;;
;;; Random stuff
;;;

;; Hackish security feature.  Helps against SQL-injection - when PQexec is disabled,
;; multi-statement cannot be made.
;disable_pqexec=0

;; Config file to use for next RELOAD/SIGHUP.
;; By default contains config file from command line.
;conffile

;; Win32 service name to register as.  job_name is alias for service_name,
;; used by some Skytools scripts.
;service_name = pgbouncer
;job_name = pgbouncer

;; Read additional config from the /etc/pgbouncer/pgbouncer-other.ini file
;%include /etc/pgbouncer/pgbouncer-other.ini
#

# vi userlist.txt
"pgbadmin" "pgbouncer123"
"haproxy" "haproxy"
#

3.修改数据库访问端口'6432'

# netstat -ltnp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name   
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      844/sshd            
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      1572/master         
tcp        0      0 0.0.0.0:6432            0.0.0.0:*               LISTEN      14974/postmaster      
#


设置PATH
# vi /etc/profile
export PATH=/opt/pgbouncer/1.17.2/bin:/usr/local/haproxy/sbin:$PATH

4.初始化数据库

建立数据库role haproxy

postgres=# CREATE ROLE haproxy LOGIN
  ENCRYPTED PASSWORD 'md53b4da8c0b2ce93c288203e3d363895a6'
  SUPERUSER INHERIT CREATEDB CREATEROLE REPLICATION;

建立数据库
postgres=# \c postgres haproxy;
postgres=# create database testdb;

建立表
testdb=# create table t1(id int, name varchar(20));
CREATE TABLE
testdb=#

数据库访问控制

# vi pg_hba.conf

host    all             all             192.168.199.0/24            trust


5.启动pgbouncer

# chown postgres.postgres -R /opt/pgbouncer/
# su - postgres

1)pgbouncer01

$ pgbouncer -d /opt/pgbouncer/1.17.2/etc/pgbouncer.ini
2016-12-20 15:09:52.182 15013 LOG File descriptor limit: 1024 (H:4096), max_client_conn: 10240, max fds possible: 10350
$

2) pgbouncer02

配置文件
; ip address or * which means all ip-s
listen_addr = *
listen_port = 5431
;;;
;;; Administrative settings
;;;

logfile = /opt/pgbouncer/1.17.2/log/pgbouncer02.log
pidfile = /opt/pgbouncer/1.17.2/bin/pgbouncer02.pid


$ pgbouncer -d /opt/pgbouncer/1.17.2/etc/pgbouncer02.ini
2016-12-20 15:12:06.964 15020 LOG File descriptor limit: 1024 (H:4096), max_client_conn: 10240, max fds possible: 10350
$

启动ha-proxy
# haproxy -f /usr/local/haproxy/etc/ha_proxy.conf

# netstat -lntp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name   
tcp        0      0 0.0.0.0:5430            0.0.0.0:*               LISTEN      16497/pgbouncer     
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      844/sshd            
tcp        0      0 0.0.0.0:5431            0.0.0.0:*               LISTEN      16500/pgbouncer     
tcp        0      0 0.0.0.0:8888            0.0.0.0:*               LISTEN      16510/haproxy      
tcp        0      0 192.168.199.201:5432    0.0.0.0:*               LISTEN      16510/haproxy      
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      1572/master         
tcp        0      0 0.0.0.0:6432            0.0.0.0:*               LISTEN      14974/postmaster   
tcp6       0      0 :::5430                 :::*                    LISTEN      16497/pgbouncer     
tcp6       0      0 :::22                   :::*                    LISTEN      844/sshd            
tcp6       0      0 :::5431                 :::*                    LISTEN      16500/pgbouncer     
tcp6       0      0 ::1:25                  :::*                    LISTEN      1572/master         
tcp6       0      0 :::6432                 :::*                    LISTEN      14974/postmaster   
#


使用pgadmin客户端访问DB:testdb, user:haproxy, password:haproxy,IP:192.168.199.201, port:5432


测试连接

insert into t1 select generate_series(1,10000),'name';

$ more test.conf
SELECT id FROM t1 WHERE id = 50;

pgbench -h 192.168.199.201 -U haproxy -c 25 -j 25 -M prepared -n -s 500 -T 60 testdb  -f ./test.conf