Cómo configurar PostgreSQL Streaming Replication con ranuras de replicación en Debian 10

PostgreSQL es un sistema de gestión de bases de datos relacionales (RDBMS) potente y rico en funciones. Es gratuito y de código abierto y ha estado en desarrollo desde el año1996. Postgres ofrece diferentes formas de archivar y replicar datos, una de las cuales es la replicación de transmisión (streaming). En este modo, una instancia primaria (maestra) maneja la base de datos activa principal y ejecuta operaciones. La instancia secundaria (esclava) copia todos los cambios de la primaria, manteniendo una copia idéntica de la base de datos activa. El servidor secundario también puede aceptar consultas de solo lectura. Si el primario falla, el servidor secundario puede salir del modo de espera y operar como el nuevo maestro (esto se llama conmutación por error).

La replicación PostgreSQL generalmente se basa en el registro de escritura anticipada (WAL), el proceso de registrar los cambios de datos antes de escribirlos en el disco. Estos registros WAL se copian en un segundo nodo como archivos (envío de registros basado en archivos) o se transmiten directamente entre nodos (replicación de transmisión). En la mayoría de los casos, este último reduce el retraso para que el nodo en espera reciba los cambios del nodo maestro.

El problema con el uso de la replicación de transmisión sin envío de registros basado en archivos es que el servidor secundario puede perder algunos registros WAL si el primario los descarta demasiado pronto. Varios parámetros de configuración pueden reducir este riesgo, pero a menudo conllevan un costo de almacenamiento innecesario. La solución son las ranuras de replicación, una característica proporcionada por Postgres que garantiza que el servidor primario solo descarte los registros WAL después de que hayan sido recibidos por el nodo en espera.

En este tutorial explicaremos como realizar la configuración de la replicación de transmisión con ranuras de replicación en dos nodos Debian 10.

Requisitos:

• Dos instancias idénticas de Debian 10.
• Acceso de modo root a ambas instancias.
• La variable de entorno $ EDITOR debe establecerse en ambas instancias.

Paso 1: Instalar PostgreSQL

Actualice y reinicie ambos nodos:

apt update
apt upgrade -y
reboot

Instale Postgres en ambos nodos y asegúrese de que PostgreSQL esté habilitado y ejecutándose:

apt install -y postgresql
systemctl enable --now postgresql@11-main.service

NOTA: Al actualizar PostgreSQL, actualizar primero el modo de espera es la opción más segura de acuerdo con su documentación.

Paso 2: configuración inicial

Por defecto, PostgreSQL solo escucha en la interfaz loopback y no es accesible externamente. Cambie la dirección de escucha en ambos nodos editando el archivo postgresql.conf:

$EDITOR /etc/postgresql/11/main/postgresql.conf

Busque la siguiente línea:

#listen_addresses = 'localhost'

Cambiela por esta:

listen_addresses = 'node_ip_address,127.0.0.1'

Si ambos nodos comparten la misma red local, puede usar direcciones privadas para node_ip_address, aunque Postgres no tendrá acceso a Internet. De lo contrario, use direcciones públicas.

Guarde el cambio y luego reinicie ambas instancias con el siguiente comando:

systemctl restart postgresql@11-main.service

Paso 3: configuración del nodo maestro

Este paso solo pertenece al servidor primario/maestro.

Abra la terminal de Postgres:

sudo -u postgres psql

El nodo en espera utilizará un usuario para conectarse al maestro. Hay que créalo con el siguiente comando:

postgres=# CREATE ROLE replicator LOGIN REPLICATION ENCRYPTED PASSWORD 'replicator_password';

Luego creeremos una ranura de replicación y saldremos:

postgres=# SELECT * FROM pg_create_physical_replication_slot('replicator');
postgres=# \q

En aras de la simplicidad, el rol de replicación y la ranura se denominan «replicador», aunque no tienen que ser idénticos.

Luego, crearemos una entrada en el archivo pg_hba.conf para permitir que el usuario del replicador se conecte desde el nodo esclavo al maestro. Para ello abrimos el archivo:

$EDITOR /etc/postgresql/11/main/pg_hba.conf

Agregue la siguiente línea al final del archivo:

host	replication	replicator	standby_ip_address/32		md5

Para que los cambios tengan efecto reiniciamos la instancia maestra:

systemctl restart postgresql@11-main.service

Paso 4: copia de seguridad base

Los comandos en este paso se deben ejecutar en el servidor secundario/esclavo.

Primero, detenga Postgres en el nodo secundario:

systemctl stop postgresql@11-main.service

Haremos una copia de seguridad del antiguo directorio de datos:

mv /var/lib/postgresql/11/main/ /var/lib/postgresql/11/main.bak

Use el siguiente comando para clonar el directorio de datos del maestro al esclavo:

pg_basebackup -h master_ip_address -U replicator -D /var/lib/postgresql/11/main/ -P --password --slot replicator

Se te solicitará una contraseña. Ingrese la contraseña que eligió para el rol de replicador durante la creación del usuario en el maestro. Una vez que se complete la transferencia, otorgue la propiedad del directorio de datos al usuario de postgres con el siguiente comando:

chown -R postgres:postgres /var/lib/postgresql/11/main

Paso 5: Configuración del nodo esclavo

Este paso solo pertenece al servidor secundario/esclavo.

Habilite el modo de espera activa en el archivo postgresql.conf:

$EDITOR /etc/postgresql/11/main/postgresql.conf

Busca y descomenta la siguiente línea:

#hot_standby = on

Cree el archivo recovery.conf en el directorio de datos de Postgres:

$EDITOR /var/lib/postgresql/11/main/recovery.conf

Para habilitar el modo standby debemos tipear esto:

standby_mode = 'on'

Establezca los parámetros de conexión de replicación utilizando las credenciales creadas en el maestro:

primary_conninfo = 'host=master_ip_address port=5432 user=replicator password=replicator_password'

Establezca el nombre de la ranura de replicación que ha creado en el maestro:

primary_slot_name = 'replicator'

Establezca la ruta a un archivo de activación de conmutación por error:

trigger_file = '/var/lib/postgresql/11/main/failover.trigger'

Si se establece el parámetro trigger_file, Postgres saldrá del modo de espera e iniciará el funcionamiento normal como servidor primario cuando se cree este archivo desencadenante. Este parámetro no es obligatorio.

Después de crear recovery.conf, otorgue la propiedad al usuario de postgres con el siguiente comando:

chown postgres:postgres /var/lib/postgresql/11/main/recovery.conf

Ahora ya podemos iniciar Postgres:

systemctl start postgresql@11-main.service

El nodo esclavo, está ahora en modo de espera y debería replicar cualquier transacción nueva.

Probando la replicación

Para probar la replicación, realice cualquier acción de escritura en el maestro. Por ejemplo, cree una nueva base de datos en el maestro:

sudo -u postgres psql -c "CREATE DATABASE replitest"

Espere unos segundos y luego enumere las bases de datos en el esclavo:

sudo -u postgres psql -c "\l"

Debería ver que la base de datos replitest fue efectivamente replicada por el servidor en esclavo:

List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 replitest | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

Prueba de conmutación por error

NOTA: La prueba de conmutación por error como se muestra aquí requerirá restablecer el servidor esclavo en modo de standby después de la conmutación por error.

Dado que Postgres está en modo de espera, no debería poder realizar ninguna operación de escritura en el nodo secundario antes de la conmutación por error. Por ejemplo, ejecute el siguiente comando:

sudo -u postgres psql -c "CREATE DATABASE test"

El comando debería fallar con el siguiente mensaje:

ERROR:  cannot execute CREATE DATABASE in a read-only transaction

Para señalar la conmutación por error, cree el archivo de activación especificado en recovery.conf

touch /var/lib/postgresql/11/main/failover.trigger

Espere unos segundos, luego intente realizar una operación de escritura. Como por ejemplo crear una nueva base de datos:

sudo -u postgres psql -c "CREATE DATABASE test2"

Dado que Postgres ya no funciona como un modo de espera, la operación tendrá éxito. Postgres también cambiará el nombre de su archivo recovery.conf a recovery.done y eliminará el archivo desencadenante.

Para volver al modo de espera, detenga Postgres en el nodo (anterior) secundario:

systemctl stop postgresql@11-main.service

Restablecer el directorio de datos:

mv /var/lib/postgresql/11/main/ /var/lib/postgresql/11/main.2.bak
pg_basebackup -h master_ip_address -U replicator -D /var/lib/postgresql/11/main/ -P --password --slot replicator
chown -R postgres:postgres /var/lib/postgresql/11/main

Y recreamos el archivo recovery.conf:

cp /var/lib/postgresql/11/main.2.bak/recovery.done /var/lib/postgresql/11/main/recovery.conf

Finalmente, reinicie Postgres:

systemctl start postgresql@11-main.service

La instancia secundaria ahora vuelve al modo de espera. Es posible que desee volver a probar la replicación en este momento.

Terminando

Elimine las bases de datos innecesarias en el nodo maestro, por ejemplo:

sudo -u postgres psql
postgres=# DROP DATABASE replitest;

Y elimine los directorios de datos antiguos en su nodo en espera:

rm /var/lib/postgresql/11/main.bak -r
rm /var/lib/postgresql/11/main.2.bak -r