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

一.OS Packages

创新互联为企业级客户提高一站式互联网+设计服务，主要包括网站制作、成都网站建设、app开发定制、微信小程序开发、宣传片制作、LOGO设计等，帮助客户快速提升营销能力和企业形象，创新互联各部门都有经验丰富的经验，可以确保每一个作品的质量和创作周期，同时每年都有很多新员工加入，为我们带来大量新的创意。 

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

[root@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 =

;; 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,

;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,

;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 =

;; 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,

;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 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