Ver configuración parte 1
Ver configuración parte 2
Ver configuración parte 1 daemon repmgr
Instalamos el pgbouncer en el nodo «testigo» (postgres4):
[root@postgres4 ~]# dnf install pgbouncer
Para hacer las pruebas de conexión de clientes, vamos a crear una nueva base de datos en el cluster (appdb) a la que se conectarán los clientes.
Añadimos la siguiente entrada en el pg_hba.conf del resto de nodos (postgres1, postgres2, postgres3) y recargamos:
host appdb appdb 192.168.0.0/24 trust
sudo systemctl reload postgresql-13
Añadimos las siguientes entradas al fichero /etc/pgbouncer/pgbouncer.ini:
[pgbouncer]
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
listen_addr = *
listen_port = 6432
unix_socket_dir = /tmp
auth_type = trust
auth_file = /etc/pgbouncer/userlist.txt
auth_hba_file = /etc/pgbouncer/pg_hba.conf
admin_users = postgres
stats_users = postgres
pool_mode = transaction
server_reset_query = DISCARD ALL
max_client_conn = 100
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
%include /etc/pgbouncer/pgbouncer.database.ini
El fichero /etc/pgbouncer/pgbouncer.database.ini apunta al nodo primario del cluster (postgres2 en este caso):
[root@postgres4 ~]# cat /etc/pgbouncer/pgbouncer.database.ini
[databases]
appdb= host=postgres2 dbname=appdb
Y creamos el fichero /etc/pgbouncer/userlist.txt para el usuario y su password:
[root@postgres4 ~]# cat /etc/pgbouncer/userlist.txt
"appdb" "appdb"
Añadimos un fichero pg_hba.conf para el pgbouncer (para poder parar y arrancar desde el resto de nodos el pool de conexiones en el failover):
[root@postgres4 ~]# chown pgbouncer:pgbouncer /etc/pgbouncer/pg_hba.conf
[root@postgres4 ~]# cat /etc/pgbouncer/pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
host pgbouncer pgbouncer 192.168.0.0/24 trust
Añadimos al fichero /etc/sudoers las siguientes entradas, por comodidad:
## Allow root to run any commands anywhere
root ALL=(ALL) ALL
postgres ALL= NOPASSWD: /bin/systemctl stop postgresql-13, \
/bin/systemctl start postgresql-13, \
/bin/systemctl status postgresql-13, \
/bin/systemctl restart postgresql-13, \
/bin/systemctl reload postgresql-13, \
/bin/systemctl start repmgr13, \
/bin/systemctl status repmgr13, \
/bin/systemctl restart repmgr13, \
/bin/systemctl reload repmgr13, \
/bin/systemctl stop repmgr13, \
/bin/systemctl start pgbouncer, \
/bin/systemctl restart pgbouncer, \
/bin/systemctl reload pgbouncer, \
/bin/systemctl stop pgbouncer
Cambiamos permisos a los ficheros:
[root@postgres4 ~]# chown postgres:postgres /etc/pgbouncer /etc/pgbouncer/*.ini
[root@postgres4 pgbouncer]# ls -l
total 24
-rwx------. 1 root root 819 nov 24 11:03 mkauth.py
-rw-r--r--. 1 postgres postgres 34 ene 9 09:31 pgbouncer.database.ini
-rw-r--r--. 1 postgres postgres 9349 ene 9 08:54 pgbouncer.ini
-rw-r--r--. 1 root root 16 ene 9 09:33 userlist.txt
Y reiniciamos pgbouncer:
[root@postgres4 ~]# systemctl restart pgbouncer
Creamos la base de datos «appdb» en el cluster (en el nodo primario postgres2) a la que se conectarán los clientes:
[local]:5432; postgres@postgres # create role appdb login;
CREATE ROLE
[local]:5432; postgres@postgres # \password appdb
Enter new password:
Enter it again:
********* QUERY **********
ALTER USER appdb PASSWORD 'SCRAM-SHA-256$4096:Hu2zNyVgzoEcZXrh1X4sKA==$XVOkHMvfcwPTf74abgWgS4tKtoC1lSz8rsBrbFwEHbs=:UMmvFFvb6FO8qta6y9cBMTZ9AzJ/FGoV0DXpA7mJlDo='
**************************
[local]:5432; postgres@postgres # create database appdb owner appdb;
CREATE DATABASE
Añadimos en el firewall del nodo del pgbouncer:
[root@postgres4 ~]# firewall-cmd --permanent --zone=public --add-rich-rule='rule family=ipv4 source address=192.168.0.0/24 port port=6432 protocol=tcp accept'
[root@postgres4 ~]# firewall-cmd --reload
Y hacemos pruebas de conexión desde el nodo cliente (postgres4) a la base de datos «appdb», del nodo postgres2 en modo r/w, a través del pgbouncer, y creamos una tabla de prueba:
[postgres@postgres4 ~]$ psql 'host=192.168.0.104 user=appdb port=6432'
192.168.0.104:6432; appdb@appdb # create table prueba(columna char(20));
CREATE TABLE
[postgres@postgres4 ~]$ psql 'host=192.168.0.104 user=appdb port=6432'
192.168.0.104:6432; appdb@appdb # \d prueba
Table "public.prueba"
+---------+---------------+-----------+----------+---------+
| Column | Type | Collation | Nullable | Default |
+---------+---------------+-----------+----------+---------+
| columna | character(20) | | | |
+---------+---------------+-----------+----------+---------+
[postgres@postgres4 ~]$ psql --host=192.168.0.104 --username=appdb --port=6432
192.168.0.104:6432; appdb@appdb # select inet_server_addr(), inet_server_port();
+------------------+------------------+
| inet_server_addr | inet_server_port |
+------------------+------------------+
| 192.168.0.102 | 5432 |
+------------------+------------------+
(1 row)
Creamos un script para integrarlo con el repmgr y que provoque el failover entre los nodos, teniendo el control del pgbouncer y su pool de conexiones:
[root@postgres1 ~]# mkdir -p /var/lib/postgres/repmgr/
[root@postgres1 ~]# chmod 700 /var/lib/postgres/repmgr/promote.sh
[root@postgres1 ~]# chown postgres:postgres /var/lib/postgres/repmgr/promote.sh
[postgres@postgres1 ~]$ cat /var/lib/postgres/repmgr/promote.sh
#!/bin/bash
set -u
set -e
# Configurable items
PGBOUNCER_HOSTS="postgres4"
PGBOUNCER_DATABASE_INI="/etc/pgbouncer/pgbouncer.database.ini"
PGBOUNCER_DATABASES="appdb"
PGBOUNCER_PORT=6432
REPMGR_DB="repmgr"
REPMGR_USER="repmgr"
# 0.- Pause pgbouncer
for HOST in ${PGBOUNCER_HOSTS}
do
for DB in ${PGBOUNCER_DATABASES}
do
/usr/pgsql-13/bin/psql -U postgres -h ${HOST} -p ${PGBOUNCER_PORT} pgbouncer -tc "pause ${DB}"
done
done
# 1. Promote server
/usr/pgsql-13/bin/repmgr standby promote -f /etc/repmgr/13/repmgr.conf --log-to-file
# 2. Reconfigure pgbouncer instances
PGBOUNCER_DATABASE_INI_NEW="/tmp/pgbouncer.database.ini"
for HOST in ${PGBOUNCER_HOSTS}
do
for DB in ${PGBOUNCER_DATABASES}
do
# Recreate the pgbouncer config file
echo -e "[databases]" > ${PGBOUNCER_DATABASE_INI_NEW}
echo -e "${DB}= host=$(hostname -f) dbname=${DB}" >> ${PGBOUNCER_DATABASE_INI_NEW}
# Copy new config file
scp ${PGBOUNCER_DATABASE_INI_NEW} ${HOST}:${PGBOUNCER_DATABASE_INI}
# Reload
/usr/pgsql-13/bin/psql -U postgres -h ${HOST} -p ${PGBOUNCER_PORT} pgbouncer -tc "reload"
# Resume
/usr/pgsql-13/bin/psql -U postgres -h ${HOST} -p ${PGBOUNCER_PORT} pgbouncer -tc "resume ${DB}"
done
done
# 3. Clean up generated file
rm ${PGBOUNCER_DATABASE_INI_NEW}
Ahora, en el fichero /etc/repmgr/13/repmgr.conf, de los nodos del cluster, cambiamos la línea del promote_command para indicarle nuestro script:
promote_command='/var/lib/postgres/repmgr/promote.sh'
Y reiniciamos el demonio en todos los nodos:
sudo systemctl restart repmgr13
Ver parte 2
