Réplica síncrona en PostgreSQL y conmutación automática

Hola a tod@s, hoy queríamos revisar con vosotros como montar una réplica síncrona en PostgreSQL versión 10 aunque también aplica en versiones superiores.

Para la prueba se utiliza un servidor principal llamado pos1 con IP 192.168.1.201 y un servidor secundario llamado pos2 con IP 192.168.1.202. También se utiliza un servidor de witness llamado pos-wit y su IP será 192.168.1.203. El servidor de witness se utiliza para desempate en caso de pérdida de conectividad entre los dos servidores anteriores.

Réplica síncrona en PostgreSQL y conmutación automática con repmanager

¿Qué necesitamos para montar Réplica síncrona en PostgreSQL?

Los pasos para realizar esta configuración son los siguientes:

  1. Configuración del repmanager en los tres equpos.
  2. Configuración del servidor primario.
  3. Copia de los datos del servidor primario al secundario.
  4. Añadir en repmanager los tres servidores con los roles definidos.
  5. Convertir la réplica en síncrona, ya que por defecto es asíncrona.

Para la utilización de repmanager es necesaria su instalación en las tres máquinas. Este comando es el que se utiliza para instalar el paquere en Red Hat 7 y el repmanager para versión 10:

yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm  
yum install -y repmgr10.x86_64 

El primer comando nos configura el repositorio en la máquina y el segundo instala el paquete.

Pasos para montar réplica síncrona en PostgreSQL

Paso 1.

Para dejar el repmanager funcionando añadimos la siguiente configuración:

En el fichero /etc/repmgr/10/repmgr.conf de cada máquina añadimos:

 node_id=101                                     # 102 y 103
 node_name='pos1'                                # pos2 y pos-wit
 conninfo='host=pos1 dbname=repmgr user=repmgr'  # pos2 y pos-wit
 data_directory='/opt/postgres/'
 config_directory='/opt/postgres'
 log_file='/var/log/repmgr.log'
 repmgrd_service_start_command = '/usr/pgsql-10/bin/repmgrd -d'
 repmgrd_service_stop_command = 'kill `cat $(/usr/pgsql-10/bin/repmgrd --show-pid-file)`'
 promote_command='/usr/pgsql-10/bin/repmgr standby promote -f /etc/repmgr/10/repmgr.conf --siblings-follow --log-to-file'
 follow_command='/usr/pgsql-10/bin/repmgr standby follow -f /etc/repmgr/10/repmgr.conf --log-to-file'
 failover=automatic
 reconnect_attempts=3
 reconnect_interval=5
 ssh_options='-q -o StrictHostKeyChecking=no -o ConnectTimeout=10'
 service_start_command   = 'sudo systemctl start postgresql-10.service'
 service_stop_command    = 'sudo systemctl stop postgresql-10.service'
 service_restart_command = 'sudo systemctl restart postgresql-10.service'
 service_reload_command  = 'sudo systemctl reload postgresql-10.service'
 priority=100                                    # 0 en el nodo pos-wit
 pg_bindir='/usr/pgsql-10/bin'   
 primary_visibility_consensus=true 

Para que el software de repmanager pueda lanzar los comandos de conmutación es necesario configurar en /etc/sudoers las siguientes entradas:

Defaults:postgres !requiretty
postgres ALL = NOPASSWD: /usr/bin/systemctl stop postgresql-10.service,/usr/bin/systemctl start postgresql-10.service,/usr/bin/systemctl restart postgresql-10.service,/usr/bin/systemctl reload postgresql-10.service 

Esto permite al repmanager ejecutar los comandos de parada y arranque del servicio postgres en caso de ser necesario.

Paso 2. 

En el servidor primario (pos1) realizamos los siguientes cambios:

 /opt/postgres/pg_hba.conf
 host replication repmgr 192.168.1.201 255.255.255.255 trust
 host replication repmgr 192.168.1.202 255.255.255.255 trust
 host replication repmgr 192.168.1.203 255.255.255.255 trust
 host repmgr repmgr 192.168.1.201 255.255.255.255 trust
 host repmgr repmgr 192.168.1.202 255.255.255.255 trust
 host repmgr repmgr 192.168.1.203 255.255.255.255 trust
 host    all         all         192.168.1.0/24          md5 

Las 6 primeras entradas son para el acceso por repmanager y la última se utiliza para el acceso con contraseña a nuestro servidor postgres.

 /opt/postgres/postgresql.conf
 listen_addresses = '*'
 shared_preload_libraries = 'repmgr'
 wal_level = replica
 archive_mode = on
 max_wal_senders = 10
 hot_standby = on
 archive_command = 'cp -i %p /opt/postgres/archive/%f' 

Con los cambios hechos, reiniciamos el servicio de postgres:

 systemctl stop postgresql-10.service
 systemctl start postgresql-10.service 

También necesitamos crear un usuario para repmanager y una base de datos. Lo ejecutamos en pos1 y pos-wit:

 su - postgres -c 'createuser --replication --createdb --createrole --superuser repmgr'
 su - postgres -c "psql -c 'ALTER USER repmgr SET search_path TO repmgr, \"\$user\", public;'"
 su - postgres -c 'createdb repmgr --owner=repmgr' 

En pos2 no es necesario lanzarlo porque vamos a replicar todos los datos desde el servidor pos1.

Paso 3.

Desde el servidor pos2 nos traemos todos los datos del servidor pos1. Tenemos que reiniciar el directorio donde está instalado PostgreSQL(/opt/postgres) y copiamos desde el servidor primario, por lo que primero paramos:

 systemctl stop postgresql-10.service
 rm -rf /opt/postgres/*
 su - postgres -c "repmgr -h pos1 -U repmgr -d repmgr standby clone" 

Una vez copiado todo arrancamos el servicio de nuevo y vemos que está correcto:

 systemctl start postgresql-10.service
 systemctl status postgresql-10.service 

Paso 4.

Configuramos los tres servidores en repmanager con los roles que tienen.

En pos1:

 su - postgres -c 'repmgr primary register'
 su - postgres -c 'repmgr daemon stop'
 su - postgres -c 'repmgr daemon start'
 su - postgres -c 'repmgr daemon status' 

En pos2:

 su - postgres -c 'repmgr standby register -h pos1 -U repmgr'
 su - postgres -c 'repmgr daemon start'
 su - postgres -c 'repmgr daemon status' 

En pos-wit:

 su - postgres -c 'repmgr witness register -h pos1 -U repmgr'
 su - postgres -c 'repmgr daemon start'
 su - postgres -c 'repmgr daemon status' 

Para ver el estado de nuestro clúster:

[root@pos-wit ~]# su - postgres -c 'repmgr daemon status'
  ID  | Name     | Role    | Status    | Upstream | repmgrd | PID  | Paused? | Upstream last seen
 -----+----------+---------+-----------+----------+---------+------+---------+-------------------
  101 | pos1     | primary | * running |          | running | 2026 | no      | n/a
  102 | pos2     | standby |   running | pos1     | running | 2593 | no      | 0 second(s) ago
  103 | pos-wit  | witness | * running | pos1     | running | 2047 | no      | 0 second(s) ago 

Revisamos el estado de la réplica síncrona en PostgreSQL

Ya están los tres servidores añadidos. El estado es:

  •                 pos1. Servidor primario
  •                 pos2. Servidor de standby recibiendo datos de pos1 (upstream).
  •                 pos-wit. Servidor de witness.

Si comprobamos el estado de la réplica vemos que está en modo asíncrono:

postgres=# SELECT * FROM pg_stat_replication;
  pid  | usesysid | usename | application_name |  client_addr  | client_hostname | client_port |         backend_start         | backend_xmin |   state   |  sent_lsn  | write_lsn  | flush_ls
 n  | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state
 ------+----------+---------+------------------+---------------+-----------------+-------------+-------------------------------+--------------+-----------+------------+------------+---------
 ---+------------+-----------+-----------+------------+---------------+------------
  1225 |    16384 | repmgr  | pos-wit         | 192.168.1.203 |                 |       48688 | 2020-11-09 08:38:12.478105-04 |              | streaming | 0/16000680 | 0/16000680 | 0/160006
 80 | 0/16000680 |           |           |            |             0 | async
 1395 |    16384 | repmgr  | pos2             | 192.168.1.202 |                 |       47672 | 2020-11-09 08:42:57.220347-04 |              | streaming | 0/16000680 | 0/16000680 | 0/160006
 80 | 0/16000680 |           |           |            |             1 | async
 (2 rows) 

Paso 5.

Ya solo nos falta convertir la réplica en síncrona. Con un alter system podemos hacerlo en “caliente”:

En pos1:

alter system set synchronous_standby_names = '"pos2"';

En pos2:

alter system set synchronous_standby_names = '"pos1"';

Como podéis ver, los nombres de los servidores se cruzan para que en caso de conmutación, la réplica de pos2 a pos1 siga siendo síncrona. También se utiliza el formato ‘” “’ para que en caso de que nuestro nombre de servidor tenga guiones o caracteres similares no nos falle. El comando genera un fichero que nos permite el cambio sin reiniciar el servicio:

[root@pos1 postgres]# cat postgresql.auto.conf
 # Do not edit this file manually!
 # It will be overwritten by the ALTER SYSTEM command.
 synchronous_standby_names = '”pos2”' 

Para que los parámetros tengan efecto se ejecuta desde dentro de la base de datos:

postgres=# SELECT pg_reload_conf();

Si volvemos a ejecutar la query anterior vemos que ya la tenemos en modo síncrono:

postgres=# SELECT * FROM pg_stat_replication;
  pid  | usesysid | usename | application_name |  client_addr  | client_hostname | client_port |         backend_start         | backend_xmin |   state   |  sent_lsn  | write_lsn  | flush_ls
 n  | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state
 ------+----------+---------+------------------+---------------+-----------------+-------------+-------------------------------+--------------+-----------+------------+------------+---------
 ---+------------+-----------+-----------+------------+---------------+------------
  1225 |    16384 | repmgr  | pos-wit         | 192.168.1.203 |                 |       48688 | 2020-11-09 08:38:12.478105-04 |              | streaming | 0/16000680 | 0/16000680 | 0/160006
 80 | 0/16000680 |           |           |            |             0 | async
 1395 |    16384 | repmgr  | pos2             | 192.168.1.202 |                 |       47672 | 2020-11-09 08:42:57.220347-04 |              | streaming | 0/16000680 | 0/16000680 | 0/160006
 80 | 0/16000680 |           |           |            |             1 | sync
 (2 rows) 

De esta forma tan simple tenemos nuestros servidores replicando todos los cambios, incluyendo la creación de base de datos nuevas, usuarios, objetos, etc. La réplica síncrona además nos permite tener una copia exacta del servidor principal, pero hay que tener cuidado. Con esta configuración, la confirmación de los cambios en la base de datos se realiza en dos fases, esto quiere decir que cualquier cambio realizado en el servidor principal no será confirmado al usuario hasta que no se realice en el secundario, por lo que si este servidor se para, no se podrán cambios en el servidor principal.

Para solucionar este problema, podemos poner la réplica en modo asíncrono de la misma forma:

En pos1 o pos2:

alter system set synchronous_standby_names = '';

Bueno, pues esto ha sido todo por hoy. Esperamos que os sea de utilidad y en entradas posteriores veremos cómo realizar conmutaciones entre los servidores y como solucionar problemas relacionados con esa operativa.

No dudes en suscribirte a nuestra newsletter para no perderte ninguna de nuestras entradas o ponerte en contacto con nosotros si quieres que revisemos tu caso.