PostgreSQL 数据库HAProxy和PgBouncer配置高可用架构
一.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
[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
;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
