Replicación y balanceo de carga en postgresql con pgpool.

Replicación y balanceo de carga en postgresql con pgpool.

Hace poco tiempo me tocó la configuración de replicación de datos y estructura en PostgreSQL de forma nativa y balanceo de carga utilizando pgpool-II. Revisando documentación online (internet) y en libros, trato de resumir lo que he realizado en este post con la esperanza de que ayude a alguien más a utilizar estas estrategia de soluciones para mejorar nuestro ambiente de datos.

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 Oficial

deb http://http.us.debian.org/debian/ jessie main contrib non-free

deb-src http://http.us.debian.org/debian/ jessie main contrib non-free

## Repositorio de Seguridad

deb http://security.debian.org/ jessie/updates main contrib non-free

deb-src http://security.debian.org/ jessie/updates main contrib non-free

## Repositorio Multimedia

deb http://www.deb-multimedia.org jessie main non-free

deb-src http://www.deb-multimedia.org jessie main non-free

deb http://apt.postgresql.org/pub/repos/apt/ jessie-pgdg main

guardamos, salimos del editor de texto y ejecutamos el siguiente comando:

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

(Como Root), instalamos las librería de PostgreSQL, el paquete de desarrollo de PostgreSQL

apt-get install libpq-dev postgresql-server-dev-9.4 bison build-essential

(Como Root), instalacion de Postgres en los nodos

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.

su - postgres

createuser --superuser pgpool2

Configurar permisos de acceso y acceso remoto a PostgreSQL:

Configurar interfaces de acceso:

Registrarse como usuario postgres:

su postgres

Se detiene el demonio de postgresql:

/etc/init.d/postgresql stop

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:

listen-address = 'localhost'

copiarla debajo, descomentarla, y modificarla quedando :

listen-address = '*'

Con esta instrucción se le dice a PostgreSQL que escuche las peticiones provenientes de cualquier máquina.

1.2.2 Cambiar el directorio del clúster:

Editar el archivo/etc/postgresql/9.4/main/postgresql.conf. Buscar la siguiente línea:

data_directory = '/data'

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.

Editar el archivo /etc/locale.gen como usuario root:

nano /etc/locale.gen

y descomentar la siguiente línea:

es_VE ISO-8859-1

Ejecutar el siguiente comando para que genere la actualización del idioma:

locale-gen

Ejecutar los siguientes parámetros:

update-alternatives --install /usr/bin/pg_ctlpg_ctl /usr/lib/postgresql/9.4/bin/pg_ctl 10

update-alternatives --install /usr/bin/initdbinitdb /usr/lib/postgresql/9.4/bin/initdb 10

Creación del nuevo clúster de base de datos en el nuevo directorio /data

chown -R postgres:postgres /data

chmod –R 700 /data

su postgres

initdb -D /data -U postgres --locale=es_VE.ISO8859-1

cp /var/lib/postgresql/9.4/main/server.* /data

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

host all postgres 172.0.1.1/24 trust

host all postgres 172.0.1.2/24 trust

host all postgres 172.0.1.3/24 trust

host all pgpool2 172.0.1.4/24 trust

host all pgpool2 172.0.1.5/24 trust

 

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.4 pgpoolA

172.0.1.5 pgpoolB

172.0.1.1 pgsqlx1

172.0.1.2 pgsqlx2

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

pgpoolA

pgpoolB

pgsqlx1

pgsqlx2

pgsqlx3

  

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:

su postgres

/etc/init.d/postgresql start

A continuación, se accede al cliente de línea de órdenes de PostgreSQL:

psql

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:

postgres=# alter user postgres with password ‘passwd’ ;

El sistema debe responder algo como:

ALTER ROLE

postgres=#

Nota: Este punto (1.3) debe ser ejecutarlo sobre los servidores de PostgreSQL.

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

Passwd -> introducir password

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

ssh-keygen -t rsa -b 4096 presionar enter hasta el final

ssh-copy-id postgres@psqlx2

ssh-copy-id root@psqlx2

meter clave postgres,root

ssh-copy-id postgres@psqlx3

ssh-copy-id postgres@psqlx3

meter clave postgres, root

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

ssh-keygen -t rsa -b 4096 presionar enter hasta el final

ssh-copy-id postgres@psqlx1

ssh-copy-id root@psqlx1

meter clave postgres,root

ssh-copy-id postgres@psqlx3

ssh-copy-id postgres@psqlx3

meter clave postgres, root

Ahora probar que la conexión ssh en ambos sentidos entre los servidores funciona correctamente.

Desde el pgsqlx1.:

su postgres

ssh postgres@pgsqlx2

ssh postgres@pgsqlx3

Desde el pgsqlx2:

su postgres

ssh postgres@pgsqlx1

ssh postgres@ pgsqlx3

Desde el pgsqlx3:

su postgres

ssh postgres@ pgsqlx1

ssh postgres@ pgsqlx2

 

 

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='*'

wal_level = 'archive'

archive_mode = on'

archive_command = 'exit 0'

#archive_command = /bin/cp %p /var/lib/postgresql/pg_xlog_archive/%f'

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

y descomentar la siguiente línea:

es_VE ISO-8859-1

Ejecutar el siguiente comando para que genere la actualización del idioma:

locale-gen

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

cd pgpool-II-3.3.4/sql/pgpool-recovery/

mv pgpool_recovery—1.0-sql pgpool_recovery.sql

nano pgpool_recovery.sql

Reemplazar los siguientes parametros donde se encuentre 'MODULE_PATHNAME' por '/usr/lib/postgresql/9.4/lib/pgpool-recovery'

Guardar con ctrl + o

Salir con ctrl + x

Ahora vamos a copiar este archivo editado anteriormente y lo copiamos a cada uno de los servidores postgresql.

scp pgpool_recovery.sql postgres@pgsqlx1:.

Introducir clave de postgres creada anteriormente

scp pgpool_recovery.sql postgres@pgsqlx2:.

Introducir clave de postgres creada anteriormente

scp pgpool_recovery.sql postgres@pgsqlx3:.

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:

ssh postgres@pgsqlx1;

psql -f /var/lib/postrgresql/pgpool_recovery.sql template1

exit

ssh postgres@pgsqlx2;

psql -f /var/lib/postrgresql/pgpool_recovery.sql template1

exit

ssh postgres@pgsqlx3;

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 Oficial

deb http://http.us.debian.org/debian/ jessie main contrib non-free

deb-src http://http.us.debian.org/debian/ jessie main contrib non-free

## Repositorio de Seguridad

deb http://security.debian.org/ jessie/updates main contrib non-free

deb-src http://security.debian.org/ jessie/updates main contrib non-free

## Repositorio Multimedia

deb http://www.deb-multimedia.org jessie main non-free

deb-src http://www.deb-multimedia.org jessie main non-free

2.2 Actualizar Repositorios Debian

apt-get update -- error llave multimedia

apt-get install aptitude -- instalar aptitude

aptitude install deb-multimedia-keyring -- instalar llave publica

aptitude update -- repositorios actualizado

(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

1. pgpool.conf

Como usuario root editar el archivo pgpool.conf:

nano /etc/pgpool2/pgpool.conf

Modificar los siguientes parámetros para las conexiones:

listen_addresses = '*'

port = 9999

socket_dir = '/var/run/postgresql'

Las conexiones de la comunicación para la administración (PCP)

pcp_port = 9898

pcp_socket_dir = '/var/run/postgresql'

Las conexiones de los backends

backend_hostname0 = 'pgsqlx1'

backend_port0 = 5432

backend_weight0 = 1

backend_data_directory0 = '/data/'

backend_flag0 = 'ALLOW_TO_FAILOVER'

backend_hostname1 = 'pgsqlx2'

backend_port1 = 5432

backend_weight1 = 1

backend_data_directory1 = '/data/'

backend_flag1 = 'ALLOW_TO_FAILOVER'

backend_hostname2 = ''pgsqlx3'

backend_port2 = 5432

backend_weight2 = 1

backend_data_directory2 = '/data'

backend_flag2 = 'ALLOW_TO_FAILOVER'

Para la autenticación

enable_pool_hba = on

pool_passwd = 'pool_passwd'

authentication_timeout = 60

Para configuraciones del pool, el tamaño del pool

num_init_children = 800

max_pool = 1

Para el tiempo de vida de los procesos y las conexiones

child_life_time = 300

child_max_connections = 0

connection_life_time = 0

client_idle_limit = 0

Para la configuración de los logs:

log_destination = 'stderr'

print_timestamp = on

log_connections = on

log_hostname = on

log_statement = on

log_per_node_statement = on

log_standby_delay = 'none'

Como en el parámetro log_destinationse usará syslog hay que configurar estos dos parámetros:

syslog_facility = 'LOCAL0'

syslog_ident = 'pgpool'

Para los niveles de Debug

debug_level = 0

Localizaciones de archivos

pid_file_name = '/var/run/postgresql/pgpool.pid'

logdir = '/var/log/postgresql'

Para activar el pool de conexiones:

connection_cache = on

reset_query_list = 'ABORT; DISCARD ALL'

Para poner el modo de replicación del pgpool activo.

replication_mode = on

replicate_select = off

insert_lock = on

lobj_lock_table = ''

replication_stop_on_mismatch = on

failover_if_affected_tuples_mismatch = off

Para activar el modo de balanceador del pgpool

load_balance_mode = on

ignore_leading_white_space = on

white_function_list = ''

black_function_list = 'nextval,setval'

Para activar el modo MASTER/SLAVE del pgpool

master_slave_mode = off

master_slave_sub_mode = slony'

sr_check_period = 0

sr_check_user = 'nobody'

sr_check_password = ''

delay_threshold = 0

follow_master_command = ''

Para desactivar el modo de ejecución en paralelo y de cache de consultas

parallel_mode = off

enable_query_cache = off

Para activar el HEALTH CHECK del pgpool

health_check_period = 10

health_check_timeout = 20

health_check_user = 'pgpool2'

health_check_password = ''

Para las caracteristicas de failover y failback

failover_command = '/data/pgpool-failover %d %h %p %D %m %M''

failback_command = '/data/pgpool-failback %d %h %p %D %m %M''

fail_over_on_backend_error = off

Para las caracteristicas de ONLINE RECOVERY

recovery_user = 'postgres'

recovery_password = ''

recovery_1st_stage_command = 'base-backup'

recovery_2nd_stage_command = 'pgpool-recovery-pitr'

Configuración de los comandos PCP

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

e10adc3949ba59abbe56e057f20f883e

O Puede también ejecutar el comando pg_md5 con el parámetro “-p” para que el password sea pedido

/opt/pgpool2/bin/pg_md5 -p

password: e10adc3949ba59abbe56e057f20f883e

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

Agregar los siguientes datos:

local all all trust

# IPv4 local connections:

Host all all 0.0.0.0/0 md5

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:

/opt/pgpool2/bin/pg_md5 --md5auth --username=postgres

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.

Configuración para el autofailover

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/

cp /root/pgpool-failback /data/

Cambiarles los permisos a los scripts:

chown –R postgres:postgres /data/pgpool-failover

chown –R postgres:postgres /data/pgool-failback

chmod –R 775 /data/pgool-failback

chmod –R 775 /data/pgpool-failover

Descripción de los siguientes scripts:

• 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.
• Pgpool-failback: restablece al nodo caído al estatus activo (2).
• wall_archiving:

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/

Cambiarles los permisos a los scripts:

chown –R postgres:postgres /data/wall_archiving

chmod –R 775 /data/wall_archiving

• Base-backup:

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/

Cambiarles los permisos a los scripts:

chown –R postgres:postgres /data/base-backup

chmod –R 775 /data/base-backup

• Pgpool-recovery-pitr:

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/

Cambiarles los permisos a los scripts:

chown –R postgres:postgres /data/pgpool-recovery-pitr

chmod –R 775 /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

chmod –R 775 /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

Cambiarles los permisos a los scripts:

chown –R root:root /root/remote_start.sh

chmod –R 775 /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

Revisar los logs del pgpool-II, este comando ejecutarlo como root:

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.