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

一.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 = <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,tlsv1.0,tlsv1.1,tlsv1.2

;clIEnt_tls_protocols = all

;; none,auto,legacy

;clIEnt_tls_dheparams = auto

;; none,<curve name>

;clIEnt_tls_ecdhcurve = auto

;;;

;;; TLS settings for connecting to backend databases

;;;

;; disable,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,tlsv1.2

;server_tls_protocols = all

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

总结

以上是内存溢出为你收集整理的PostgreSQL 数据库HAProxy和PgBouncer配置高可用架构全部内容，希望文章能够帮你解决PostgreSQL 数据库HAProxy和PgBouncer配置高可用架构所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。