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

; 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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!