Ver parte 1
Ver parte 2
Vamos a ver las diferentes opciones que tenemos en el base de datos virtual de pgbouncer.
[postgres@postgres4 ~]$ psql --host=192.168.0.104 --username=postgres --port=6432 --dbname=pgbouncer
192.168.0.104:6432; postgres@pgbouncer # SHOW HELP;
NOTICE: Console usage
DETAIL:
SHOW HELP|CONFIG|DATABASES|POOLS|CLIENTS|SERVERS|USERS|VERSION
SHOW FDS|SOCKETS|ACTIVE_SOCKETS|LISTS|MEM
SHOW DNS_HOSTS|DNS_ZONES
SHOW STATS|STATS_TOTALS|STATS_AVERAGES|TOTALS
SET key = arg
RELOAD
PAUSE [<db>]
RESUME [<db>]
DISABLE <db>
ENABLE <db>
RECONNECT [<db>]
KILL <db>
SUSPEND
SHUTDOWN
SHOW
Vemos las bases de datos configuradas dentro del pgbouncer:
192.168.0.104:6432; postgres@pgbouncer # show databases;
+-----------+-----------+------+-----------+------------+-----------+--------------+-----------+-----------------+---------------------+--------+----------+
| name | host | port | database | force_user | pool_size | reserve_pool | pool_mode | max_connections | current_connections | paused | disabled |
+-----------+-----------+------+-----------+------------+-----------+--------------+-----------+-----------------+---------------------+--------+----------+
| appdb | postgres2 | 5432 | appdb | NULL | 20 | 5 | NULL | 0 | 5 | 0 | 0 |
| pgbouncer | NULL | 6432 | pgbouncer | pgbouncer | 2 | 0 | statement | 0 | 0 | 0 | 0 |
+-----------+-----------+------+-----------+------------+-----------+--------------+-----------+-----------------+---------------------+--------+----------+
(2 rows)
Los clientes haciendo uso de ellas:
192.168.0.104:6432; postgres@pgbouncer # show clients;
+------+----------+-----------+--------+---------------+-------+---------------+------------+-------------------------+-------------------------+------+---------+------------
--+----------------+------+------------+-----+
| type | user | database | state | addr | port | local_addr | local_port | connect_time | request_time | wait | wait_us | close_neede
d | ptr | link | remote_pid | tls |
+------+----------+-----------+--------+---------------+-------+---------------+------------+-------------------------+-------------------------+------+---------+------------
--+----------------+------+------------+-----+
| C | postgres | pgbouncer | active | 192.168.0.104 | 52782 | 192.168.0.104 | 6432 | 2021-01-09 13:12:10 CET | 2021-01-09 13:13:27 CET | 76 | 321602 |
0 | 0x55c65053c260 | | 0 | |
+------+----------+-----------+--------+---------------+-------+---------------+------------+-------------------------+-------------------------+------+---------+------------
--+----------------+------+------------+-----+
(1 row)
Vemos los pools configurados:
192.168.0.104:6432; postgres@pgbouncer # show pools;
+-----------+-----------+-----------+------------+-----------+---------+---------+-----------+----------+---------+------------+-------------+
| database | user | cl_active | cl_waiting | sv_active | sv_idle | sv_used | sv_tested | sv_login | maxwait | maxwait_us | pool_mode |
+-----------+-----------+-----------+------------+-----------+---------+---------+-----------+----------+---------+------------+-------------+
| appdb | appdb | 0 | 0 | 0 | 0 | 5 | 0 | 0 | 0 | 0 | transaction |
| pgbouncer | pgbouncer | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | statement |
+-----------+-----------+-----------+------------+-----------+---------+---------+-----------+----------+---------+------------+-------------+
(2 rows)
Vemos los servidores configurados:
192.168.0.104:6432; postgres@pgbouncer # show servers;
+------+-------+----------+-------+---------------+------+---------------+------------+-------------------------+-------------------------+------+---------+--------------+---
-------------+------+------------+-----+
| type | user | database | state | addr | port | local_addr | local_port | connect_time | request_time | wait | wait_us | close_needed |
ptr | link | remote_pid | tls |
+------+-------+----------+-------+---------------+------+---------------+------------+-------------------------+-------------------------+------+---------+--------------+---
-------------+------+------------+-----+
| S | appdb | appdb | used | 192.168.0.102 | 5432 | 192.168.0.104 | 35874 | 2021-01-09 13:07:21 CET | 2021-01-09 13:07:22 CET | 0 | 0 | 0 | 0x
55c6505438c0 | | 34547 | |
| S | appdb | appdb | used | 192.168.0.102 | 5432 | 192.168.0.104 | 35872 | 2021-01-09 13:07:21 CET | 2021-01-09 13:07:21 CET | 0 | 0 | 0 | 0x
55c650543690 | | 34546 | |
| S | appdb | appdb | used | 192.168.0.102 | 5432 | 192.168.0.104 | 35870 | 2021-01-09 13:07:21 CET | 2021-01-09 13:07:21 CET | 0 | 0 | 0 | 0x
55c650543460 | | 34544 | |
| S | appdb | appdb | used | 192.168.0.102 | 5432 | 192.168.0.104 | 35868 | 2021-01-09 13:07:20 CET | 2021-01-09 13:07:20 CET | 0 | 0 | 0 | 0x
55c650543230 | | 34542 | |
| S | appdb | appdb | used | 192.168.0.102 | 5432 | 192.168.0.104 | 35866 | 2021-01-09 13:07:20 CET | 2021-01-09 13:07:20 CET | 0 | 0 | 0 | 0x
55c650543000 | | 34541 | |
+------+-------+----------+-------+---------------+------+---------------+------------+-------------------------+-------------------------+------+---------+--------------+---
-------------+------+------------+-----+
(5 rows)
Vemos los FDS:
92.168.0.104:6432; postgres@pgbouncer # SHOW FDS;
+----+--------+-------+----------+---------------+------+-----------------+------+-----------------+-------------+-----------+---------------+----------+------------------+--
----------------+
| fd | task | user | database | addr | port | cancel | link | client_encoding | std_strings | datestyle | timezone | password | scram_client_key | s
cram_server_key |
+----+--------+-------+----------+---------------+------+-----------------+------+-----------------+-------------+-----------+---------------+----------+------------------+--
----------------+
| 8 | pooler | NULL | NULL | 0.0.0.0 | 6432 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | N
ULL |
| 9 | pooler | NULL | NULL | :: | 6432 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | N
ULL |
| 10 | pooler | NULL | NULL | unix | 6432 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | N
ULL |
| 16 | server | appdb | appdb | 192.168.0.102 | 5432 | 148379907212587 | 0 | UTF8 | on | ISO, MDY | Europe/Madrid | NULL | NULL | N
ULL |
| 15 | server | appdb | appdb | 192.168.0.102 | 5432 | 148377622469219 | 0 | UTF8 | on | ISO, MDY | Europe/Madrid | NULL | NULL | N
ULL |
| 14 | server | appdb | appdb | 192.168.0.102 | 5432 | 148366069438019 | 0 | UTF8 | on | ISO, MDY | Europe/Madrid | NULL | NULL | N
ULL |
| 13 | server | appdb | appdb | 192.168.0.102 | 5432 | 148358806406606 | 0 | UTF8 | on | ISO, MDY | Europe/Madrid | NULL | NULL | N
ULL |
| 12 | server | appdb | appdb | 192.168.0.102 | 5432 | 148356199612857 | 0 | UTF8 | on | ISO, MDY | Europe/Madrid | NULL | NULL | N
ULL |
+----+--------+-------+----------+---------------+------+-----------------+------+-----------------+-------------+-----------+---------------+----------+------------------+--
----------------+
(8 rows)
Y por último la configuración de esta base de datos virtual:
192.168.0.104:6432; postgres@pgbouncer # show config;
+---------------------------+--------------------------------------------------------+--------------------------------------------------------+------------+
| key | value | default | changeable |
+---------------------------+--------------------------------------------------------+--------------------------------------------------------+------------+
| admin_users | postgres | | yes |
| application_name_add_host | 0 | 0 | yes |
| auth_file | /etc/pgbouncer/userlist.txt | NULL | yes |
| auth_hba_file | | | yes |
| auth_query | SELECT usename, passwd FROM pg_shadow WHERE usename=$1 | SELECT usename, passwd FROM pg_shadow WHERE usename=$1 | yes |
| auth_type | trust | md5 | yes |
| auth_user | NULL | NULL | yes |
| autodb_idle_timeout | 3600 | 3600 | yes |
| client_idle_timeout | 0 | 0 | yes |
| client_login_timeout | 60 | 60 | yes |
| client_tls_ca_file | | | no |
| client_tls_cert_file | | | no |
| client_tls_ciphers | fast | fast | no |
| client_tls_dheparams | auto | auto | no |
| client_tls_ecdhcurve | auto | auto | no |
| client_tls_key_file | | | no |
| client_tls_protocols | secure | secure | no |
| client_tls_sslmode | disable | disable | no |
| conffile | /etc/pgbouncer/pgbouncer.ini | NULL | yes |
| default_pool_size | 20 | 20 | yes |
| disable_pqexec | 0 | 0 | no |
| dns_max_ttl | 15 | 15 | yes |
| dns_nxdomain_ttl | 15 | 15 | yes |
| dns_zone_check_period | 0 | 0 | yes |
| idle_transaction_timeout | 0 | 0 | yes |
| ignore_startup_parameters | | | yes |
| job_name | pgbouncer | pgbouncer | no |
| listen_addr | * | | no |
| listen_backlog | 128 | 128 | no |
| listen_port | 6432 | 6432 | no |
| log_connections | 1 | 1 | yes |
| log_disconnections | 1 | 1 | yes |
| log_pooler_errors | 1 | 1 | yes |
| log_stats | 1 | 1 | yes |
| logfile | /var/log/pgbouncer/pgbouncer.log | | yes |
| max_client_conn | 100 | 100 | yes |
| max_db_connections | 0 | 0 | yes |
| max_packet_size | 2147483647 | 2147483647 | yes |
| max_user_connections | 0 | 0 | yes |
| min_pool_size | 5 | 0 | yes |
| pidfile | /var/run/pgbouncer/pgbouncer.pid | | no |
| pkt_buf | 4096 | 4096 | no |
| pool_mode | transaction | session | yes |
| query_timeout | 0 | 0 | yes |
| query_wait_timeout | 120 | 120 | yes |
| reserve_pool_size | 5 | 0 | yes |
| reserve_pool_timeout | 3 | 5 | yes |
| resolv_conf | | | no |
| sbuf_loopcnt | 5 | 5 | yes |
| server_check_delay | 30 | 30 | yes |
| server_check_query | select 1 | select 1 | yes |
| server_connect_timeout | 15 | 15 | yes |
| server_fast_close | 0 | 0 | yes |
| server_idle_timeout | 600 | 600 | yes |
| server_lifetime | 3600 | 3600 | yes |
| server_login_retry | 15 | 15 | yes |
| server_reset_query | DISCARD ALL | DISCARD ALL | yes |
| server_reset_query_always | 0 | 0 | yes |
| server_round_robin | 0 | 0 | yes |
| server_tls_ca_file | | | no |
| server_tls_cert_file | | | no |
| server_tls_ciphers | fast | fast | no |
| server_tls_key_file | | | no |
| server_tls_protocols | secure | secure | no |
| server_tls_sslmode | disable | disable | no |
| so_reuseport | 0 | 0 | no |
| stats_period | 60 | 60 | yes |
| stats_users | postgres | | yes |
| suspend_timeout | 10 | 10 | yes |
| syslog | 0 | 0 | yes |
| syslog_facility | daemon | daemon | yes |
| syslog_ident | pgbouncer | pgbouncer | yes |
| tcp_defer_accept | 1 | NULL | yes |
| tcp_keepalive | 1 | 1 | yes |
| tcp_keepcnt | 0 | 0 | yes |
| tcp_keepidle | 0 | 0 | yes |
| tcp_keepintvl | 0 | 0 | yes |
| tcp_socket_buffer | 0 | 0 | yes |
| tcp_user_timeout | 0 | 0 | yes |
| unix_socket_dir | /tmp | /tmp | no |
| unix_socket_group | | | no |
| unix_socket_mode | 511 | 0777 | no |
| user | NULL | NULL | no |
| verbose | 0 | NULL | yes |
+---------------------------+--------------------------------------------------------+--------------------------------------------------------+------------+
(84 rows)
Ver parte 4
