Cómo configurar la replicación PostgreSQL en Debian 11

PostgreSQL es un sistema de gestión de bases de datos gratuito y de código abierto que se centra en la extensibilidad y el cumplimiento de SQL. PostgreSQL es un RDBMS (Sistema de Gestión de Bases de Datos Relacionales) avanzado y de categoría empresarial que admite consultas SQL (relacionales) y JSON (no relacionales).

Es un sistema de gestión de bases de datos de gran estabilidad y de categoría empresarial que proporciona diferentes soluciones que te permiten configurar la alta disponibilidad y la conmutación por error. PostgreSQL se utiliza como base de datos principal para aplicaciones web, aplicaciones móviles y aplicaciones analíticas. Tiene una gran reputación por su fiabilidad, flexibilidad, robustez de funciones y rendimiento.

En este tutorial, instalaré y configuraré la replicación lógica del servidor de bases de datos PostgreSQL mediante pglogical2 en dos servidores Debian 11. Instalarás el servidor PostgreSQL con el paquete de extensión pglogical y, a continuación, configurarás la replicación entre dos o más servidores PostgreSQL.

pglogical es una extensión de PostgreSQL que te permite configurar la replicación lógica en flujo a nivel de base de datos. Utiliza el modelo publicar/suscribir para la replicación. Con la extensión pglogical, en lugar de replicar los datos disponibles en PostgreSQL, replicarás y sincronizarás tablas específicas de tu base de datos disponible con otros servidores PostgreSQL.

Requisitos previos

Para completar esta guía, debes tener los siguientes requisitos:

• Dos o más servidores Debian 11.
• Un usuario no root con privilegios de administrador sudo/root.

Este ejemplo utiliza dos servidores Debian con el siguiente detalle:

Hostname    IP Address      Used as
------------------------------------------
pgdg01      192.168.5.25    Publisher Node
pgdg02      192.168.5.26    Subscriber Node

Cuando los servidores estén listos, ya puedes iniciar la instalación y configuración de la Replicación PostgreSQL mediante pglogical.

Configurar /etc/hosts y el Repositorio PostgreSQL

Este ejemplo supondrá que utilizas servidores Debian 11 nuevos, así que lo primero que harás será configurar el repositorio PostgreSQL e instalar PostgreSQL en todos tus sistemas Debian.

Debes ejecutar los siguientes pasos en todos tus servidores Debian.

Para empezar, abre el archivo de configuración ‘/etc/hosts‘ utilizando el siguiente comando del editor nano.

sudo nano /etc/hosts

Añade las siguientes líneas al archivo y asegúrate de cambiar el detalle de las direcciones IP y los nombres de host.

192.168.5.25    pgdg01
192.168.5.26    pgdg02

Guarda y cierra el archivo ‘/etc/hosts’ cuando hayas terminado.

A continuación, introduce el siguiente comando apt para instalar algunas dependencias básicas. Introduce y cuando se te pida y pulsa ENTER para continuar.

sudo apt install wget gnupg2 lsb-release curl apt-transport-https ca-certificates

Ahora ejecuta el siguiente comando para añadir la clave GPG de PostgreSQL y el repositorio para servidores Debian.

curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor | sudo tee /usr/share/keyrings/pgdg.gpg > /dev/null 2>&1
sudo sh -c 'echo "deb [signed-by=/usr/share/keyrings/pgdg.gpg] http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

A continuación, actualiza y refresca tu índice de paquetes para aplicar los cambios.

sudo apt update

Una vez añadidos, deberías ver el repositorio PostgreSQL en la lista.

Con el repositorio PostgreSQL añadido, lo siguiente será instalar el servidor PostgreSQL y la extensión pglogical.

Instalar PostgreSQL y la extensión pglogical

Por defecto, el repositorio oficial PostgreSQL proporciona varias versiones de PostgreSQL, como PostgreSQL 13, 14 y 15. Para este ejemplo, instalarás y configurarás la replicación PostgreSQL con la última versión de PostgreSQL 15.

Introduce el siguiente comando‘apt install‘ para instalar el servidor PostgreSQL. Introduce y para confirmar la instalación y pulsa ENTER para continuar.

sudo apt install postgresql

Cuando finalice la instalación, introduce el siguiente comando systemctl para verificar el servidor PostgreSQL y asegurarte de que el servicio está habilitado y en ejecución.

sudo systemctl is-enabled postgresql
sudo systemctl status postgresql

La salida ‘enabled‘ confirma que el servicio PostgreSQL está habilitado y se iniciará automáticamente al arrancar el sistema. Y la salida ‘active (running)‘ confirma que el servicio PostgreSQL actual está en ejecución.

A continuación, introduce el siguiente comando apt para instalar el paquete de extensión pglogical. Si utilizas una versión diferente de PostgreSQL, debes sustituir el número de pacakge por‘postgresql-version-pglogical’.

sudo apt install postgresql-15-pglogical

Introduce y cuando se te solicite y pulsa INTRO para continuar.

Con el PostgreSQL en marcha y la extensión pglogical instalada. Estás listo para iniciar la configuración de la replicación lógica de flujos con pglogical.

Configurar el cortafuegos UFW

Antes de configurar los servidores PostgreSQL, ahora debes configurar el cortafuegos ufw que asegurará y limitará la conexión a tu servidor PostgreSQL. Con esto, sólo tendrás una conexión desde tus redes de confianza, que estará asegurada por tu despliegue PostgreSQL.

Para instalar ufw, introduce el siguiente comando apt. Cuando se te solicite, introduce y para confirmar y pulsa ENTER para continuar.

sudo apt install ufw

A continuación, introduce el siguiente comando ufw para abrir el servicio OpenSSH. La salida «Regla añadida» confirma que la nueva regla se ha añadido a ufw.

sudo ufw allow OpenSSH

Ahora introduce el siguiente comando ufw para permitir que tus redes de confianza accedan al servidor PostgreSQL 5432. Asegúrate de cambiar la dirección IP en el siguiente comando.

sudo ufw allow from 192.168.5.0/24 to any port 5432

A continuación, introduce el siguiente comando para iniciar y habilitar el cortafuegos ufw. Cuando se te pida confirmación, introduce y y pulsa INTRO para continuar.

sudo ufw enable

La salida«El cortafuegos se está ejecutando y está habilitado» confirma que el ufw se está ejecutando y también está habilitado, lo que significa que el cortafuegos ufw se iniciará automáticamente al arrancar el sistema.

Ahora puedes verificar el estado y la lista de puertos habilitados en ufw introduciendo el siguiente comando.

sudo ufw status

Deberías ver el cortafuegos ufw ‘Estado: Activo’ con el servicio OpenSSH y el puerto del servidor PostgreSQL‘5432‘ añadidos y disponibles en el cortafuegos ufw.

Configurar PostgreSQL para activar la replicación

WAL o Write-Ahead Logging es un método que garantiza la integridad de los datos en el servidor PostgreSQL. Cualquier cambio en tablas e índices debe escribirse sólo después de que se hayan registrado dichos cambios.

En PostgreSQL, hay tres métodos de WAL que puedes utilizar para activar la replicación.

• La configuración WAL por defecto es«réplica«, que te permite configurar el archivado y la replicación, y te permite ejecutar consultas de sólo lectura en el servidor en espera.
• Mínimo que te permite eliminar todo el registro excepto alguna información importante que será para recuperarte de un fallo o de un apagado inmediato.
• El método lógico te permite añadir alguna información a la WAL que admita la descodificación lógica.

En esta sección, configurarás PostgreSQL para que se ejecute en la dirección IP de la red local, configurarás el nivel de wal«lógico» mediante la extensión«pglogical» y, a continuación, habilitarás los usuarios de replicación mediante el archivo pg_hba.conf.

Para empezar, accede al shell de PostgreSQL introduciendo el siguiente comando.

sudo -u postgres psql

A continuación, comprueba la configuración por defecto de ‘ wal_level ‘ en el servidor PostgreSQL.

select name,setting,unit from pg_settings where name in ('wal_level','max_worker_processes','max_replication_slots','max_wal_senders','shared_preload_libraries','track_commit_timestamp');

La salida que aparece a continuación confirma que el nivel wal por defecto en el servidor PostgreSQL es ‘réplica’, que es el estándar para las versiones actuales de PostgreSQL.

Ahora escribe ‘\q’ para salir del intérprete de comandos PostgreSQL.

A continuación, abre el archivo de configuración predeterminado de PostgreSQL‘/etc/postgresql/15/main/postgresql.conf ‘ utilizando el siguiente comando del editor nano.

sudo nano /etc/postgresql/15/main/postgresql.conf

Descomenta el parámetro ‘listen_addresses’ y añádele la dirección IP de tu servidor interno.

A continuación se muestra la configuración que se utilizará en el servidor‘pgdg01‘.

listen_addresses = 'localhost, 192.168.5.25'

Y a continuación la configuración que se utilizará en el servidor«pgdg02«.

listen_addresses = 'localhost, 192.168.5.26'

Ahora añade los siguientes parámetros para activar el nivel de cartera«lógico» mediante la extensión «pglogical». Ambos servidores tienen una configuración similar para los ajustes del nivel wal. El parámetro‘shared_preload_libraries = ‘pglogical» habilitará el nivel wal lógico a través de la extensión ‘pglogical‘.

max_worker_processes = 10
shared_preload_libraries = 'pglogical'
track_commit_timestamp = 'on'
wal_level = 'logical'

Guarda y cierra el archivo‘/etc/postgresql/15/main/postgresql.conf‘ cuando hayas terminado.

A continuación, abre el archivo de autenticación PostgreSQL ‘/etc/postgresql/15/main/pg_hba. conf’ utilizando el siguiente comando del editor nano.

sudo nano /etc/postgresql/15/main/pg_hba.conf

Añade las siguientes líneas al final del mismo. Esto permitirá la autenticación de cualquier usuario desde el host‘192.168.5.1/24‘ tanto para los usuarios normales de PostgreSQL como para los usuarios de replicación.

# IPv4 local connections:
host    all             all             192.168.5.1/24          scram-sha-256
# replication privilege.
host    replication     all             192.168.5.1/24          scram-sha-256

Guarda y cierra el archivo ‘ /etc/postgresql/15/main/pg_hba.conf‘ cuando hayas terminado.

Una vez finalizada la configuración de ‘ /etc/postgresql/15/main/postgresql.conf‘ y‘/etc/postgresql/15/main/pg_hba.conf‘, introduce el siguiente comando systemctl para reiniciar el servicio PostgreSQL y aplicar los cambios.

sudo systemctl restart postgresql

Ahora introduce el siguiente comando ss para asegurarte de que PostgreSQL se está ejecutando en tu dirección IP local.

ss -tulpn | grep 5432

Deberías recibir una salida como ésta – En el servidor pgdg01, el servidor PostgreSQL debería estar ejecutándose en‘192.168.5.25:5432‘. En el servidor pgdg02, el servidor PostgreSQL debería estar ejecutándose en «192.168.5.26:5432″.

Por último, introduce el siguiente comando para acceder al shell PostgreSQL y verificar el nivel de wal habilitado en tu servidor PostgreSQL.

sudo -u postgres psql

Introduce la siguiente consulta PostgreSQL para verificar el nivel wal habilitado en tu servidor PostgreSQL.

select name,setting,unit from pg_settings where name in ('wal_level','max_worker_processes','max_replication_slots','max_wal_senders','shared_preload_libraries','track_commit_timestamp');

La salida‘wal_level – logical’ confirma que está habilitado el nivel wal‘logical’, que es a través de‘shared_preload_library – pglogical‘.

Llegados a este punto, has configurado los servidores PostgreSQL con nivel de wal ‘lógico‘ para la replicación de flujo lógico a través de ‘pglogical‘. Además, ambos servidores PostgreSQL se ejecutan en direcciones IP de red interna. En el siguiente paso, crearás un nuevo usuario y base de datos PostgreSQL, y luego activarás la extensión ‘pglogical‘.

Configurar usuario y base de datos

pglogical es una extensión de PostgreSQL que funciona a nivel de base de datos. Con ella, no estás replicando todo el sistema PostgreSQL, sino que replicas tablas o bases de datos concretas que están disponibles en PostgreSQL.

Para configurar pglogical, debes tener el privilegio/rol PostgreSQL con SUPERUSUARIO. Además, debes habilitar la extensión«pglogical» en la base de datos de destino que quieras replicar.

Accede al shell de PostgreSQL introduciendo el siguiente comando.

sudo -u postgres psql

Crea un nuevo usuario y una nueva base de datos utilizando las siguientes consultas PostgreSQL.

En este ejemplo, crearás un nuevo usuario‘appuser‘ con el privilegio ‘SUPERUSUARIO’. En el momento de escribir esto, el pglogical requería el privilegio de usuario SUPER para que funcionara. También crearás una nueva base de datos ‘appdb‘, que es la base de datos de destino sobre la que quieres habilitar la replicación.

CREATE ROLE appuser WITH LOGIN SUPERUSER ENCRYPTED PASSWORD 'Str0ngP4ssw0rd';
CREATE DATABASE appdb WITH OWNER appuser;

Comprueba la lista de usuarios y bases de datos en tu servidor PostgreSQL utilizando las siguientes consultas. Deberías obtener el nuevo usuario‘appuser‘ con privilegio SUPERUSUARIO y la nueva base de datos ‘appdb‘.

\du
\l

A continuación, conéctate a la nueva base de datos ‘appdb’ utilizando la consulta ‘\c’ o ‘\connect’. Cuando estés conectado, tu intérprete de comandos PostgreSQL será como‘appdb=#’.

\c appdb

Introduce la siguiente consulta para habilitar la extensión‘pglogical‘ en la base de datos‘appdb’.

CREATE EXTENSION pglogical;

A continuación, comprueba la lista de extensiones habilitadas en la base de datos ‘appdb‘ actual mediante la siguiente consulta. Deberías ver que la extensión «pglogical» está activada en la base de datos «appdb«.

\dx

Con el usuario y la base de datos creados y el pglogical habilitado, a continuación empezarás a configurar el pglogical para habilitar la replicación de bases de datos entre dos servidores PostgreSQL.

Habilitar la replicación de tablas mediante pglogical

En esta sección, habilitarás la replicación lógica en la tabla específica que crearás en la base de datos ‘appdb

### Configura pgdg01 como Nodo Publicador

Introduce las siguientes consultas PostgreSQL en el‘nodo editor’ –‘ servidorpgdg01 ‘ para crear una nueva tabla llamada ‘profesores‘.

CREATE TABLE teachers (
  id INT PRIMARY KEY,
  first_name VARCHAR,
  last_name VARCHAR,
  subject VARCHAR
);

Inserta nuevos datos en la tabla ‘profesores’ introduciendo las siguientes consultas.

INSERT INTO teachers(id, first_name, last_name, subject)
VALUES 
(1, 'Alice', 'Wonderland', 'Math'),
(2, 'Bob', 'Rista', 'Physics'),
(3, 'John', 'Bonas', 'English');

A continuación, recupera los nuevos datos para asegurarte de que están disponibles en el nodo editor servidor pgdg01.

select count(*) from teachers;
select * from teachers;

La salida siguiente confirma que se han añadido nuevos datos a la tabla «profesores«.

A continuación, para configurar la replicación en la base de datos/tabla PostgreSQL mediante pglogical, debes seguir estos pasos:

• Crea un nodo proveedor en el servidor Publisher pgdg01.
• Crea un conjunto de réplica que te permita controlar qué tablas de la base de datos se replicarán.
• Añade tablas al conjunto de réplica.

Introduce el siguiente comando para crear un nuevo nodo proveedor llamado«proveedor1«. Asegúrate de cambiar los detalles de PostgreSQL con el servidor pgdg01.

select pglogical.create_node(node_name := 'provider1', dsn := 'host=pgdg01 port=5432 dbname=appdb user=appuser');

A continuación, introduce la siguiente consulta para crear un nuevo conjunto de réplica llamado ‘conjunto_de_réplica‘.

select pglogical.create_replication_set('replication_set');

A continuación, añade la tabla‘maestros‘ al conjunto de réplica‘replication_set’ introduciendo la siguiente consulta.

select pglogical.replication_set_add_table(set_name := 'replication_set', relation := 'teachers', synchronize_data := true);

Ahora que has creado el nodo proveedor con el servidor PostgreSQL pgdg01, has creado el conjunto de réplica ‘replication_set‘, y has añadido la tabla‘teachers’ al‘replication_set‘. A continuación, crearás y configurarás un nodo suscriptor en el servidor‘pgdg02′.

Configurar pgdg02 como Nodo Suscriptor

Desplázate al servidor ‘pgdg02’ e introduce el siguiente comando del editor nano para crear un nuevo archivo de contraseñas PostgreSQL‘/var/lib/postgresql/.pgpass’. Este archivo de contraseñas se utilizará para

sudo -u postgres nano /var/lib/postgresql/.pgpass

Añade las siguientes líneas al archivo y asegúrate de cambiar el nombre de usuario y la contraseña con tus datos.

pgdg01:5432:appdb:appuser:Str0ngP4ssw0rd
pgdg01:5432:replication:appuser:Str0ngP4ssw0rd
pgdg02:5432:appdb:appuser:Str0ngP4ssw0rd
pgdg02:5432:replication:appuser:Str0ngP4ssw0rd

Guarda y cierra el archivo cuando hayas terminado.

Ahora introduce el siguiente comando para cambiar el permiso del archivo‘/var/lib/postgresql/.pgpass‘ a sólo lectura.

sudo chmod 0600 /var/lib/postgresql/.pgpass

A continuación, inicia sesión en PostgreSQL utilizando el siguiente comando.

sudo -u postgres psql

Después de iniciar sesión, introduce la siguiente consulta para recargar la configuración de tu servidor PostgreSQL y, a continuación, sal para asegurarte de que se aplican los cambios.

select pg_reload_conf();
\q

Ahora vuelve a entrar en el shell PostgreSQL del servidor pgdg02.

sudo -u postgres psql

Conéctate a la base de datos ‘appdb’ que has creado.

\c appdb

A continuación, introduce la siguiente consulta PostgreSQL para crear una nueva tabla‘maestros‘, que debe coincidir con la tabla que tienes en el nodo proveedor‘pgdg01‘.

CREATE TABLE teachers (
  id INT PRIMARY KEY,
  first_name VARCHAR,
  last_name VARCHAR,
  subject VARCHAR
);

Si compruebas los datos disponibles, deberías obtener una tabla vacía.

select count(*) from teachers;
select * from teachers;

Con el archivo de contraseñas PostgreSQL y la tabla ‘profesores’ creada, a continuación configurarás el PostgreSQL en el servidor ‘pgdg02‘ como suscriptor con los siguientes pasos:

• Crea un nodo Suscriptor en el servidor «pgdg02».
• Crea la suscripción que iniciará el proceso de sincronización y replicación en segundo plano.
• Verifica el proceso de replicación.

Introduce la siguiente consulta para crear un nuevo nodo suscriptor con el servidor PostgreSQL pgdg02. Asegúrate de cambiar los detalles nombre de host, nombre de base de datos y usuario.

select pglogical.create_node(node_name := 'subscriber1', dsn := 'host=pgdg02 port=5432 dbname=appdb user=appuser');

A continuación, crea una nueva suscripción llamada «suscripción1». Asegúrate de introducir el conjunto de réplica llamado ‘conjunto_replicación’ y el proveedor es el PostgreSQL del servidor pgdg01. Con esto, la replicación de la tabla‘maestros‘ se iniciará y ejecutará en segundo plano.

select pglogical.create_subscription(subscription_name := 'subscription1', replication_sets := array['replication_set'], provider_dsn := 'host=pgdg01 port=5432 dbname=appdb user=appuser' );

Introduce la siguiente consulta para verificar el estado de la suscripción.

select *  FROM pglogical.show_subscription_status();

Deberías recibir una salida como ésta – El estado de‘suscripción1‘ se está replicando al proveedor‘proveedor1‘ y al conjunto de réplica‘conjunto_replicación’.

Ahora introduce la siguiente consulta para esperar a que la suscripción comience a replicarse de forma asíncrona y los datos se sincronicen.

SELECT pglogical.wait_for_subscription_sync_complete('subscription1');

Por último, puedes verificar los datos de la tabla ‘maestros‘ que están disponibles en el servidor PostgreSQL pgdg2.

select count(*) from teachers;
select * from teachers;

Si la replicación se realiza correctamente, tus datos deberían estar ahora disponibles y replicados en el servidor PostgreSQL pgdg02.

Probar la replicación

En esta sección, verificarás la replicación en el servidor PostgreSQL insertando nuevos datos desde el nodo Editor pgdg01. A continuación, verificarás los datos desde el nodo Suscriptor para asegurarte de que los datos se replican.

En primer lugar, conéctate a la base de datos«appdb» del servidor pgdg01 introduciendo el siguiente comando.

sudo -u postgres psql -U appuser -h pgdg01 -d appdb

Introduce las siguientes consultas para insertar nuevos datos en la tabla ‘profesores‘.

INSERT INTO teachers(id, first_name, last_name, subject)
VALUES 
(4, 'Ian', 'Gibson', 'Geography'),
(5, 'Tom', 'Riddle', 'Biology'),
(6, 'Jared', 'Dunn', 'Chemistry');

Comprueba la tabla «profesores» para asegurarte de que se han añadido los nuevos datos.

select count(*) from teachers;
select * from teachers;

A continuación, pasa al servidor pgdg02 e introduce el siguiente comando para conectarte a la base de datos‘appdb‘.

sudo -u postgres psql -U appuser -h pgdg02 -d appdb

Introduce la siguiente consulta para verificar los datos disponibles en la tabla ‘profesores‘. Si la replicación se ha realizado correctamente, deberías ver los datos recién actualizados disponibles en el servidor pgdg02.

select count(*) from teachers;
select * from teachers;

Con esto, ya has terminado la configuración de la replicación de flujos lógicos mediante pglogical en servidores PostgreSQL.

Conclusión

En este tutorial, has instalado y configurado la replicación de flujos lógicos mediante pglogical en servidores Debian 11. Con ello, también has aprendido el uso básico de la extensión pglogical para crear y gestionar la replicación PostgreSQL a nivel de base de datos.

Además, también has terminado la instalación de la última versión de PostgreSQL 15 en el servidor Debian a través del repositorio oficial de PostgreSQL y has asegurado el servidor PostgreSQL a través del cortafuegos UFW.

Con esto, ya puedes añadir otro servidor PostgreSQL y configurar la extensión pglogical para permitir la replicación en varios servidores. También puedes configurar el equilibrio de carga mediante HAPROXY, o configurar la conmutación por error de los servidores PostgreSQL. Para obtener información detallada, visita la documentación oficial de PostgreSQL.