Replicación y balanceo de carga en postgresql con pgpool.
Hablaremos un poco de algunos conceptos necesarios para entender lo que vamos a realizar.
La
disponibilidad
es una de las características de
las arquitecturas empresariales que mide el grado con el que los
recursos del sistema están disponibles para su uso por el usuario
final a lo largo de un tiempo dado. Ésta no sólo se relaciona con
la prevención de caídas del sistema, sino incluso con la percepción
de "caída" desde el punto de vista del usuario: cualquier
circunstancia que nos impida trabajar productivamente con el sistema
– desde tiempos de respuesta prolongados, escasa asistencia técnica
o falta de estaciones de trabajo disponibles – es considerada como
un factor de baja disponibilidad.
Un cluster
de alta disponibilidad es
un conjunto de dos o más máquinas que
se caracterizan por mantener una serie de servicios compartidos y por
estar constantemente monitorizándose entre sí. Podemos dividirlo en
dos clases:
Alta
disponibilidad de infraestructura:
Si se produce un fallo de hardware en
alguna de las máquinas del cluster,
el software de
alta disponibilidad es capaz de arrancar automáticamente los
servicios en cualquiera de las otras máquinas
del cluster (failover).
Y cuando la máquina que ha fallado se recupera, los servicios son
nuevamente migrados a la máquina original (failback). Esta capacidad
de recuperación automática de servicios nos garantiza la alta
disponibilidad de los servicios ofrecidos por el cluster, minimizando
así la percepción del fallo por parte de los usuarios.
Alta
disponibilidad de aplicación:
Si se produce un fallo del hardware o
de las aplicaciones de alguna de las máquinas del cluster,
el software de
alta disponibilidad es capaz de arrancar automáticamente los
servicios que han fallado en cualquiera de las otras máquinas
del cluster.
Y cuando la máquina que ha fallado se recupera, los servicios son
nuevamente migrados a la máquina original. Esta capacidad de
recuperación automática de servicios nos garantiza la integridad de
la información,
ya que no hay pérdida de datos, y además evita molestias a los
usuarios, que no tienen por qué notar que se ha producido un
problema.
A
continuación se describen los pasos para la configuración de cada
una de las herramientas instaladas para ofrecer alta disponibilidad y
optimización de la plataforma de base de datos.
Para comenzar les explico la solución: tenemos tres servidores, el cual el primero será utilizado para instalar y configurar el pgpool y los otros dos cada instancia del servidor de postgresql. En este post y practica he usado la versión 9.4 de postgresql, para la públicacion de este post ya esta en versión estable la 9.6, pero aún mantengo la 9.4 como la versión de trabajo y de pgpool II la version 3.5.3, para esto también necesitamos estar logueado con el usuario root.
Esta imagen refleja como debería ser la arquitectura de la configuración de la solución.
Cada servidor tiene instalado y configurado la distribución de linux Debian jessie o versión 8, y en estas dos usadas para instalar el servicio de postgresql y en otro servidor el cliente de postgres, se le configuró el repositorio de postgresql, para eso con un editor de texto, puede ser nano, vim u otro, abrimos el archivo /etc/apt/source.list en cada servidor y se agregó la linea siguiente:
$nano
/etc/apt/sources.list
###DEBIAN
JESSIE ESTABLE###
##
Repositorio de Seguridad
##
Repositorio Multimedia
deb http://apt.postgresql.org/pub/repos/apt/ jessie-pgdg main aptitude update ó apt-get update
Ahora si todo va bien comenzamos a realizar las configuraciones en los servidores de base de datos maestro, esclavo y el del balanceador de carga, para esto entonces empezaremos por la replicación y la configuración del servidor maestro.
Servidor de base de datos Maestro
(Como
Root) instalación de un conjunto de paquetes básico para cualquier
sistema, así como una serie de utilidades que nos falta para la
gestión y el mantenimiento del clúster:
apt-get
install ntp openssl file psmisc sysstat bzip2 unzip nmap dstat rsync
wget ccze tcpdump pciutils dnsutils host
apt-get
install postgresql-9.4 postgresql-contrib-9.4 postgresql-doc-9.4 uuid
libdbd-pg-perl postgresql-9.4-pgpool2
(Como
postgres), añadiendo el usuario de base de datos (role) pgpool2,
sin contraseña en todos los nodos.
PostgreSQL
por defecto, sólo atiende peticiones provenientes de la máquina
local, es decir, de la máquina en la que está instalada. Por ello,
si se esta configurando un servidor remoto, se debe habilitar el
acceso desde otras IPs.
Para
lograrlo se debe editar el
archivo/etc/postgresql/9.4/main/postgresql.conf.
Buscar la línea siguiente:
Con
esta instrucción se le dice a PostgreSQL que escuche las peticiones
provenientes de cualquier máquina.
Editar
el archivo/etc/postgresql/9.4/main/postgresql.conf.
Buscar la siguiente línea:
Ese
directorio es donde se crearán los datos del clúster de base de
datos, anteriormente se tiene creada una partición separada para guardar los datos de las bases de datos y no utilizar las que nos trae por defecto el SGBD, esto se puede hacer cuando se está generando el servidor con el sistema operativo.
Configurar
permisos de acceso:
Para
ello se debe editar el archivo/etc/postgresql/9.4/main/pg_hba.conf
y agregar estas líneas las demás se deben comentar o borrar, esto se hace en cada servidor donde instalemos postgresql.
host all
all 127.0.0.1/32 md5
Configurar
host:
Para
ello se debe editar el archivo/etc/hosts
y agregar estas líneas las demás se deben comentar o borrar, esto se hace en cada servidor.
172.0.1.6
pgpool2
172.0.1.3
pgsqlx3
Configurar hostname:
Para
ello se debe editar el archivo/etc/hostname
y agregar esta línea las demás se deben comentar o borrar, esto se hace en cada servidor.
pgpool2
pgsqlx1
pgsqlx2
Configurar
la contraseña del usuario postgres:
Una
vez instalado PostgreSQL, se debe establecer la contraseña del
usuario postgres, que es el usuario con los máximos privilegios
dentro de la base de datos, una especie de root.
Primero
se asume la identidad del usuario postgres y se inicia el servicio de
postgresql:
A
continuación, se accede al cliente de línea de órdenes de
PostgreSQL:
El
sistema permitirá entrar sin pedir usuario, puesto que ya se
asume identidad del usuario postgres, y sin pedir contraseña,
puesto que el usuario postgres por defecto no tiene contraseña.
Una
vez dentro, escribir:
El
sistema debe responder algo como:
Creación
de los key para conexión ssh con el usuario postgres en el Servidor
1 y copiarlos para los demás servidores (2 y 3)
Registrarse
como usuario postgres, en caso de no estarlo, ejecutar el siguiente
comando:
su
postgres
Generar
el keyssh
usando una frase vacía y copiar los key generados y el fichero de
autorización a los demás servidores:
server:pgsqlx1
Entrar
como usuario a los otros dos servidores a los que se les copiaron los
key e instalar los archivos dentro de la cuenta del usuario postgres
server:pgsqlx2
Ahora
probar que la conexión ssh en ambos sentidos entre los servidores
funciona correctamente.
Desde
el pgsqlx1.:
su
postgres
Desde
el pgsqlx2:
su
postgres
su
postgres
Configuración
del servidor primario:
PostgreSQL
debería estar ya instalado y configurado. Para que
funcione se debe hacer una modificación al archivo
/etc/postgresql/9.4/main/postgresql.conf
de cada uno de los servidores de la solución. Buscar estos
parámetros en el archivo,descomentarlos y agregarle los siguientes
valores (esto debe hacerse como usuario postgres):
listen_addresses='*'
Configurar el pgpool-II en el Servidor 1 de pgpool
Como
usuario root ejecutar los siguientes pasos:
Editar
el archivo /etc/locale.gen
como usuario root:
nano
/etc/locale.gen
Ejecutar
el siguiente comando para que genere la actualización del idioma:
Descargar
desde la página web del proyecto de pgpool2 los archivos fuentes a
través del siguiente comando:
wget
http://www.pgpool.net/download.php?f=pgpool-II-3.3.4.tar.gz
-O pgpool-II-3.3.4.tar.gz
Descomprimir
el archivo descargado en el mismo directorio donde efectuó la
descarga y entrar en la carpeta pgpool-II-3.3.4/sql/pgpool-recovery/
y editar el archivo pgpool_recovery—1.0.sql y renombrar a
pgpool_recovery.sql
tar
-zxvf pgpool-II-3.3.4.tar.gz
Reemplazar
los siguientes parametros donde se encuentre 'MODULE_PATHNAME' por
'/usr/lib/postgresql/9.4/lib/pgpool-recovery'
Ahora
vamos a copiar este archivo editado anteriormente y lo copiamos a
cada uno de los servidores postgresql.
Introducir
clave de postgres creada anteriormente
Introducir
clave de postgres creada anteriormente
Introducir
clave de postgres creada anteriormente
Ahora
acceder a cada servidor postgresql y ejecutar lo siguiente para
instalar las funciones de recuperación con el usuario postgres:
psql
-f /var/lib/postrgresql/pgpool_recovery.sql template1
exit
psql
-f /var/lib/postrgresql/pgpool_recovery.sql template1
exit
psql
-f /var/lib/postrgresql/pgpool_recovery.sql template1
exit
Ahora
instalar el servicio de pgpool-II 3.3.4 y el cliente de postgresql,
pero antes se realiza la
instalación
y configuración del ambiente para los servidores
2.1
Agregar Repositorios de Debian
Root
$ nano /etc/apt/sources.list
###DEBIAN
JESSIE ESTABLE###
##
Repositorio de Seguridad
##
Repositorio Multimedia
2.2
Actualizar Repositorios Debian
apt-get
update -- error llave
multimedia
(Como
Root) instalación de un conjunto de paquetes básico para cualquier
sistema, así como una serie de utilidades que nos falta para la
gestión y el mantenimiento del clúster:
apt-get
install ntp openssl file psmisc sysstat bzip2 unzip nmap dstat rsync
wget ccze tcpdump pciutils dnsutils host
aptitude
install pgpool2 postgresql-client-9.4
Archivos de configuración
- pgpool.conf
nano
/etc/pgpool2/pgpool.conf
listen_addresses
= '*'
Las
conexiones de la comunicación para la administración (PCP)
pcp_port
= 9898
Las
conexiones de los backends
backend_hostname0
= 'pgsqlx1'
backend_hostname2
= ''pgsqlx3'
Para
la autenticación
enable_pool_hba
= on
authentication_timeout
= 60
Para
configuraciones del pool, el tamaño del pool
num_init_children
= 800
Para
el tiempo de vida de los procesos y las conexiones
child_life_time
= 300
Para
la configuración de los logs:
log_destination
= 'stderr'
syslog_facility
= 'LOCAL0'
debug_level
= 0
pid_file_name
= '/var/run/postgresql/pgpool.pid'
connection_cache
= on
replication_mode
= on
load_balance_mode
= on
master_slave_mode
= off
parallel_mode
= off
Para
activar el HEALTH CHECK del pgpool
health_check_period
= 10
failover_command
= '/data/pgpool-failover %d %h %p %D %m %M''
recovery_user
= 'postgres'
Ejecutar
comandos como usuario postgres.
Pgpool-II
tiene una interfaz para propósitos administrativos la cual obtiene
información desde los nodos de PostgreSQL, apagar el pgpool-II vía
red, etc. Para usar los comandos PCP se requiere de autenticación de
usuario. Esta autenticación de usuario es diferente a la
autenticación de usuarios del PostgreSQL. Un usuario y password es
necesario que sea definido en el archivo pcp.conf.
En este archivo, un usuario y password son guardados como pares por
cada línea y separados por el carácter de dos puntos (:). El
password es encriptado en el formado md5.
Ejemplo
de la línea en el pcp.conf:
admin:e10adc3949ba59abbe56e057f20f883e
Para
encriptar el password en el formato md5 use el comando pg_md5 el cual
está instalado como comando del pgpoo-II (/usr/bin/pg_md5).
El pg_md5 tiene como parámetro un texto que al ejecutarlo muestra el
texto en formato md5.
/usr/bin/pg_md5
password
/opt/pgpool2/bin/pg_md5
-p
Copiar
el texto encriptado y editar el archivo /etc/pgpool2/pcp.conf
y la línea en el formato usuario:password Encriptado
admin:e10adc3949ba59abbe56e057f20f883e
Control
de Acceso al pgpool-II
Además
de controlar el acceso al PostgreSQL en cada uno de los servidores en
el archivo pg_hba.conf,
se tiene que dar acceso a los clientes para que tengan permisos de
conectarse al pgpool-II.
Para
ello se ejecuta el siguiente comando
cp
/opt/pgpool2/etc/pool_hba.conf.sample /opt/pgpool2/etc/pool_hba.conf
Entonces
editar el /etc/pgpool2/pool_hba.conf
y agregar las siguientes líneas:
nano
/etc/pgpool2/pool_hba.conf
local
all all trust
Autenticación
de usuarios que acceden al pgpool
En
el modo MASTER/SLAVE son soportados los métodos de autenticación
trust, password en texto plano, pam y md5. El método md5 es
soportado usando el archivo /opt/pgpool2/etc/pool_passwd.
Pasos
para activar la autenticación md5:
-
Desde el usuario postgres en el sistema operativo ejecutar:
de
esta forma el usuario y el password encriptado en md5 serán
registrados en el archivo pool_passwd. Si el archivo no existe el
comando pg_md5 lo creará automáticamente.
-
El formato del archivo pool_passwd es “usuario:passwordEncriptado”
-
Se necesita tener entradas md5 en el archivo pool_hba.conf
-
Importante aclarar que los usuario y password registrados tienen que ser los mismos que los creados en el PostgreSQL.
Ejecutar
comandos como root.
Copiar
para la carpeta /data
los scripts
Autofailover.
Sino esta creada la carpeta /data crearla como dueño
postgres:postgres
Copiar
los siguientes scripts para el directorio de los ejecutables del
pgpool-II:
cp
/root/pgpool-failover /data/
chown
–R postgres:postgres /data/pgpool-failover
-
pgpool-failover: cuando un nodo se cae, pgpool revisa si el un nodo master o esclavo y desactiva el nodo con estatus 3 y si este fuera un master asigna a un nuevo master.
Este
script activa al servidor maestro a realizar la copia de seguridad
cambiando el parametro archive_command del archivo postgresql.conf.
Copiar el siguientes scripts para el directorio de la data de cada
servidor postgresql:
cp
/root/wall_archiving /data/
chown
–R postgres:postgres /data/wall_archiving
Este
script realiza el respaldo y copia de este al servidor caído. Copiar
el siguientes scripts para el directorio de la data de cada servidor
postgresql:
cp
/root/base-backup /data/
chown
–R postgres:postgres /data/base-backup
Este
script realiza el cambio de los xlog para comenzar a realizar las
copias de archivos wall. qCopiar el siguientes scripts para el
directorio de la data de cada servidor postgresql:
cp
/root/pgpool-recovery-pitr /data/
chown
–R postgres:postgres /data/pgpool-recovery-pitr
-
Pgpool-remote-start:
Este
script realiza el inicio del servidor postgresql caído y levanta el
servicio. Copiar el siguientes scripts para el directorio de la data
de cada servidor postgresql:
cp
/root/pgpool-remote-start /data/
Cambiarles
los permisos a los scripts:
chown
–R postgres:postgres /data/pgpool-recovery-pitr
-
Remote_start.sh:
Este
script realiza el inicio del servidor postgresql remoto caído y
levanta el servicio. el siguientes scripts se encuentra en cada
servidor postgresql en la ruta:
/root/prueba.sh
chown
–R root:root /root/remote_start.sh
Activar el pgpool-II
Para
arrancar el pgpool-II ejecutar el siguiente comando:
/etc/init.d/pgpool2
start
Para
chequear que el pgpool-II está detenido o activado ejecutar el
siguiente comando
/etc/init.d/pgpool2
status
Comprobación
del pgpool-II
tail
-f /var/log/pgpool2/pgpool.log
Conectarse
desde un cliente al servidor donde está activo el pgpool-II por el
Puerto 9999
y comprobar que no hay problemas con la conexión.
Para
detener el pgpool-II ejecutar el siguiente comando:
/etc/init.d/pgpool2
stop
Repetir
estos mismos pasos en el servidor 2 que es donde estará la otra
instancia de pgpool-II.
Excelente tutorial!
ResponderBorraryou put out the scripts you use in this manual?
ResponderBorraryes, i have the scripts for the configuration of replication of PITR, do you need this?
BorrarYes, I need please, my email is walterwrv290888@gmail.com
Borrar