PostgreSQL databases HAProxy and PgBouncer configure highly available schemas

I. OS Packages

CentOS 7.2 X64

Pcre-devel

Openssl-devel

Zlib-devel

two。 Compile and install haproxy

1. Compilation and installation

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

#

two。 Configuration file

# 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. Configure PATH variables

# vi / etc/profile

Unset-f pathmunge

Export PATH=/usr/local/haproxy/sbin:$PATH

Third, install pgbouncer

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

two。 Install 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

#

Configuration file

# 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 percent:

; 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 (0Compact 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. Modify database access port '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. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0

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. 0 LISTEN 14974/postmaster

#

Set up PATH

# vi / etc/profile

Export PATH=/opt/pgbouncer/1.17.2/bin:/usr/local/haproxy/sbin:$PATH

4. Initialize the database

Set up the database role haproxy

Postgres=# CREATE ROLE haproxy LOGIN

ENCRYPTED PASSWORD 'md53b4da8c0b2ce93c288203e3d363895a6'

SUPERUSER INHERIT CREATEDB CREATEROLE REPLICATION

Set up a database

Postgres=#\ c postgres haproxy

Postgres=# create database testdb

Create a table

Testdb=# create table T1 (id int, name varchar (20))

CREATE TABLE

Testdb=#

Database access control

# vi pg_hba.conf

Host all all 192.168.199.0/24 trust

5. Start 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 max fds possible 09 LOG File descriptor limit 52.182 15013), max fds possible: 10350

$

2) pgbouncer02

Configuration file

; 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

15020 LOG File descriptor limit: 1024 (2016-12-20 15), max_client_conn: 10240, max fds possible: 10350

$

Start 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.015 * LISTEN 16497/pgbouncer

Tcp 0 0 0.0.0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0

Tcp 00 0.0.0.0 5431 0.0.0.0 * LISTEN 16500/pgbouncer

Tcp 0 0 0.0.0. 0. 0. 0. 0. 0. 0. 8. 8. 8. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0 of the LISTEN 16510/haproxy

Tcp 0 0 192.168.199.201VO 5432 0.0.0.0Vue * 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. 0 LISTEN 14974/postmaster

Tcp6 0 0: 5430: * LISTEN 16497/pgbouncer

Tcp6 0 0: 22:: * LISTEN 844/sshd

Tcp6 00: 5431: * LISTEN 16500/pgbouncer

Tcp6 0 0:: 1:25: * LISTEN 1572/master

Tcp6 0 0: 6432: * LISTEN 14974/postmaster

#

Use the pgadmin client to access DB:testdb, user:haproxy, password:haproxy,IP:192.168.199.201, port:5432

Test connection

Insert into T1 select generate_series (110000th), '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