Cómo crear alta disponibilidad en base de datos PostgreSQL

La replicación de PostgreSQL es una forma de implementar un clúster de conmutación por error. Las instrucciones están escritas usando PostgreSQL 16 como ejemplo, pero también funcionarán para versiones anteriores de PostgreSQL.

ventajas de usar replicación:

Combina varias bases de datos en una.
Utilice condiciones especiales para la transferencia de datos al suscriptor.
Dar acceso a datos replicados a otros grupos de usuarios.
Divida datos de múltiples bases de datos entre muchas otras bases de datos.
Transmitir cambios incrementales en una o más bases de datos al momento de editar la información.
Simplifique las condiciones para configurar la replicación: se pueden utilizar diferentes versiones principales de PostgreSQL, así como plataformas de sistemas operativos.

En un escenario en el que el servidor principal deja de estar disponible, una conmutación por error a un servidor en espera puede ayudar a mantener un acceso ininterrumpido a sus datos. Esta guía describe el proceso paso a paso para realizar una conmutación manual en PostgreSQL. Si sigue esta guía, podrá realizar una transición sin problemas del servidor principal al servidor en espera mientras actualiza las configuraciones de IP.

Requisitos

Necesitará dos o más servidores Ubuntu para completar esta guía. También necesitará un usuario que no sea root con privilegios de administrador sudo/root en sus sistemas.

Este ejemplo utilizará dos servidores Ubuntu 22.04 con los siguientes detalles:

Hostname      IP Address
---------------------------
postgres01    192.168.5.121
postgres02    192.168.5.122

Cuando todos los requisitos estén listos, puedes comenzar a instalar el servidor PostgreSQL.

Configurar /etc/hosts y UFW

En esta primera sección, configurará el archivo /etc/hosts y el firewall UFW para abrir el puerto 5432 de PostgreSQL predeterminado y permitir que una subred de red específica acceda a él. Debe configurar el archivo /etc/hosts y UFW en ambos servidores.

Abra el archivo /etc/hosts usando el siguiente comando del editor vi

sudo vi /etc/hosts

Agregue las siguientes líneas al archivo y asegúrese de cambiar los detalles de la dirección IP y el nombre de host con su entorno de trabajo actual.

192.168.5.121 postgres01 
192.168.5.122 postgres02

Guarde y cierre el archivo una vez que haya terminado.

A continuación, introduzca el siguiente comando ‘ufw’ para abrir el puerto OpenSSH y el puerto 5432 del servidor PostgreSQL . Con esto, abrirá el puerto OpenSSH predeterminado 22, luego también abrirá el puerto 5432 del servidor PostgreSQL y especificará el acceso solo desde redes confiables ‘ 192.168.5.0/24 ‘.

sudo ufw allow OpenSSH
sudo ufw allow from 192.168.5.0/24 to any port 5432

Ahora ingrese el siguiente comando para iniciar y habilitar UFW. Cuando se le solicite, ingrese y para confirmar y presione ENTER para continuar.

sudo ufw enable

Una salida ‘ Firewall is active and enabled on system startup ‘ confirma que el UFW ahora se está ejecutando y se iniciará automáticamente al iniciar el sistema.

Por último, verifique el estado del firewall UFW utilizando el siguiente comando.

sudo ufw status

Debería recibir un resultado como este: El servicio OpenSSH se agregó y está disponible en la lista de reglas de UFW. Además, el puerto 5432 del servidor PostgreSQL solo es accesible desde » 192.168.5.0/24 «.

Instalación del servidor PostgreSQL

Primero, ingrese el comando ‘apt install’ para instalar algunas dependencias básicas. Ingrese y cuando se le solicite y presione ENTER para continuar.

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

A continuación, ingrese el comando ‘curl’ a continuación para descargar la clave GPG del repositorio PostgreSQL, convierta el archivo .asc a .gpg mediante el comando ‘gpg –dearmor’ y luego agregue el repositorio PostgreSQL.

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'

Ahora actualice y actualice su paquete de Ubuntu para aplicar el nuevo repositorio PostgreSQL que ha agregado.

sudo apt update

Ahora puede instalar el servidor PostgreSQL mediante el comando ‘apt install’ que aparece a continuación. Con esto, instalará la última versión del servidor PostgreSQL, que es PostgreSQL 16.

sudo apt install postgresql

Una vez instalado el servidor PostgreSQL, ingrese el siguiente comando systemctl para verificar el servicio PostgreSQL y asegurarse de que esté ejecutándose y habilitado.

sudo systemctl is-enabled postgresql
sudo systemctl status postgresql

Por último, también necesitará instalar la extensión de PostgreSQL » plperl «, que será necesaria para el software de Bucardo. Ingrese el siguiente comando «apt install» para instalar la extensión «plperl».

sudo apt install postgresql-plperl-15

La instalación debería comenzar automáticamente.

Con esto, ya ha instalado PostgreSQL en ambos servidores Ubuntu 22.04 y también ha instalado la extensión de PostgreSQL ‘plperl’ que utilizará Bucardo. En el siguiente paso, configurará el usuario y la base de datos de PostgreSQL que utilizará Bucardo y también configurará la prueba de base de datos que se utilizará para las pruebas de replicación.

Configurar base de datos y usuario

En esta sección, creará una nueva base de datos y un usuario que se utilizará para la instalación de Bucardo. También creará una nueva base de datos con el esquema para probar la replicación de PostgreSQL. Asegúrese de ejecutar el siguiente comando y las consultas en los servidores PostgreSQL de bot.

Antes de comenzar, mueva el directorio de trabajo a ‘ /var/lib/postgresql ‘.

Ahora inicie sesión en el shell de PostgreSQL mediante el siguiente comando.

sudo -u postgres psql

Una vez que haya iniciado sesión en el shell de PostgreSQL, ingrese las siguientes consultas para crear un nuevo usuario y una base de datos llamada ‘bucardo’. Esta base de datos se utilizará para almacenar datos de Bucardo y el usuario ‘ bucardo ‘ debe tener el privilegio SUPERUSER .

CREATE USER bucardo WITH SUPERUSER;
CREATE DATABASE bucardo OWNER bucardo;

Ahora, ingrese las siguientes consultas para verificar la lista de bases de datos y usuarios en PostgreSQL. Si la verificación es exitosa, debería ver la base de datos y el usuario ‘bucardo’ en la salida.

\l
\du

Lista de bases de datos en el servidor PostgreSQL.

Lista de usuarios en el servidor PostgreSQL.

Después de crear la base de datos y el usuario para Bucardo, creará una nueva base de datos para probar la replicación en su servidor PostgreSQL

Introduzca la siguiente consulta para crear una nueva base de datos ‘ testdb ‘. A continuación, conéctese a la base de datos ‘ testdb ‘ mediante la consulta ‘ \c ‘.

CREAR BASE DE DATOS testdb; 
\c testdb;

Ahora ingrese la siguiente consulta para crear una nueva tabla ‘usuarios’.

CREAR TABLA usuarios ( 
  id CLAVE PRIMARIA SERIE, 
  nombre VARCHAR(255), 
  apellido VARCHAR(255) NOT NULL, 
  ciudad VARCHAR(255) 
);

Una vez creada la tabla, ingrese las siguientes consultas para verificar el esquema de la tabla ‘usuarios’, luego verifique la lista de datos disponibles en la tabla.

\dt
select * from users;

Configurar el servidor PostgreSQL

En esta sección, configurará ambos servidores PostgreSQL para que se ejecuten en una dirección IP interna. Además, configurará la autenticación PostgreSQL para permitir conexiones entre servidores que sean de confianza para PostgreSQL.

Abra la configuración predeterminada de PostgreSQL ‘/etc/postgresql/16/main/postgresql.conf’ usando el siguiente comando del editor vi.

sudo vi /etc/postgresql/16/main/postgresql.conf

Quite el comentario del parámetro ‘listen_addresses ‘ y agregue la dirección IP interna de su servidor PostgreSQL. Con esto, configurará PostgreSQL para que se ejecute en una dirección IP interna en cada servidor.

A continuación se muestra la configuración del servidor postgres01.

listen_addresses = 'localhost, 192.168.5.121'

Y a continuación se muestra la configuración para el servidor postgres02.

listen_addresses = 'localhost, 192.168.5.122'

Guarde y cierre el archivo cuando haya terminado.

A continuación, abra el archivo de configuración de autenticación de PostgreSQL predeterminado ‘ /etc/postgresql/16/main/pg_hba.conf’ usando el siguiente comando del editor vi.

sudo vi /etc/postgresql/16/main/pg_hba.conf

En el servidor postgres01, agregue las siguientes configuraciones y asegúrese de cambiar la dirección IP con la dirección IP del servidor postgres02. Con esto, se confiará en cualquier conexión local y en las conexiones del usuario bucardo. Además, se confiarán en las conexiones de los usuarios postgres y bucardo que provengan de postgres02.

# local connection and bucardo user
local    all             all                                    trust
local    all             bucardo                                trust

# Bucardo user remote connections
host    all             postgres         192.168.5.122/24       trust
host    all             bucardo          192.168.5.122/24       trust

A continuación se muestra la configuración del servidor postgres02. Asegúrese de cambiar la dirección IP por la dirección IP del servidor postgres01.

# local connection and bucardo user
local    all             all                                    trust
local    all             bucardo                                trust

# Bucardo user remote connections
host    all             postgres         192.168.5.121/24       trust
host    all             bucardo          192.168.5.121/24       trust

Guarde y cierre el archivo cuando haya terminado.

Ahora ingrese el siguiente comando systemctl para reiniciar el servicio PostgreSQL y aplicar los cambios

Con esto, el servidor PostgreSQL debería estar ejecutándose en el host local y la dirección IP interna a través del parámetro ‘ listen_addresses ‘. Ingrese el siguiente comando para verificar la lista de puertos abiertos en cada servidor. Debería ver que PostgreSQL se está ejecutando en una dirección IP interna de los servidores postgres01 y postgres02 .

Por último, para verificar la autenticación, puede ingresar el siguiente comando para conectarse al shell de PostgreSQL.

En el servidor postgres01, ingrese el siguiente comando. Cuando lo haya hecho correctamente, deberá iniciar sesión en el shell PostgreSQL del servidor postgres02 sin ninguna autenticación, ya que se encuentra en el host y el usuario de confianza.

sudo -u postgres psql 
sudo -u postgres psql -U bucardo -h 192.168.5.122

Para el servidor postgres02, puede ingresar el siguiente comando para conectarse al shell PostgreSQL que se ejecuta en el servidor postgres01. Obtendrá un resultado similar: iniciará sesión en el servidor postgres01 sin autenticación de contraseña porque tanto el usuario como la persona conectada son confiables.

sudo -u postgres psql 
sudo -u postgres psql -U bucardo -h 192.168.5.121

Con esto, ya ha finalizado la instalación del servidor PostgreSQL 15, ha configurado la base de datos y el usuario para Bucardo, y también ha configurado las direcciones de escucha y la autenticación de PostgreSQL.

En el siguiente paso, compilará e instalará Bucardo como una aplicación que le permite crear una replicación PostgreSQL Multi-Master.

Instalación de Bucardo

Bucardo es una aplicación gratuita y de código abierto para crear sistemas de replicación de múltiples fuentes y múltiples maestros de PostgreSQL. En esta sección, instalará Bucardo manualmente desde la fuente; tampoco es necesario que instale Bucardo en todos los nodos.

Instalará Bucardo únicamente en el servidor ‘postgres01’. Por lo tanto, asegúrese de ejecutar todos los siguientes comandos en el servidor postgres01.

Primero, ingrese el siguiente comando apt para instalar las dependencias del paquete para Bucardo. Ingrese y cuando se le solicite y presione ENTER para continuar.

sudo apt install make libdbix-safe-perl libboolean-perl libdbd-mock-perl libdbd-pg-perl libanyevent-dbd-pg-perl libpg-hstore-perl libpgobject-perl libpod-parser-perl libencode-locale-perl

Ahora descargue el código fuente de Bucardo a través del comando wget a continuación.

wget -q https://bucardo.org/downloads/Bucardo-5.6.0.tar.gz

Una vez descargado, extraiga el código fuente de Bucardo y muévalo al directorio de trabajo de Bucardo.

tar xf Bucardo-5.6.0.tar.gz 
cd Bucardo-*/

Ahora ingrese el siguiente comando para compilar e instalar Bucardo en el servidor postgres01.

perl Makefile.PL 
sudo make install

Verifique la ruta binaria de la aplicación ‘bucardo’ y verifique la versión de Bucardo mediante el siguiente comando.

cual bucardo 
bucardo --version

El archivo binario ‘ bucardo ‘ se encuentra en ‘/usr/local/bin/bucardo’ , y la versión instalada actualmente es v5.6.0

Con Bucaro instalado, a continuación configurará la replicación PostgreSQL Multi-Master utilizando dos servidores PostgreSQL postgres01 y postgres02.

Configurar la replicación Multi-Mastercon Bucardo

Los siguientes comandos deben ejecutarse en el servidor postgres01.

Antes de comenzar, ingrese el siguiente comando para crear un nuevo directorio de datos y registros para Bucardo.

sudo mkdir -p /var/run/bucardo /var/log/bucardo 
touch /var/log/bucardo/log.bucardo

Ejecute el siguiente comando ‘bucardo’ para iniciar la instalación. Revise la configuración de conexión a PostgreSQL y luego ingrese P para continuar.

bucardo install

Cuando la instalación de Bucardo sea exitosa, debería aparecer un mensaje de salida como ‘Installation is now complete‘

Introduzca el siguiente comando para definir el servidor de base de datos y el nombre de la base de datos que se replicará. Esa información se almacenará como » servidor1 » para el servidor PostgreSQL ppstgres01 y » servidor2 » para el servidor PostgreSQL postgres02.

bucardo add database server1 dbname=testdb
bucardo add database server2 dbname=testdb host=192.168.5.122

Agregue el esquema de tabla que desea replicar. En este ejemplo, la tabla llamada » usuarios » se replicará y sincronizará en todos los servidores PostgreSQL.

bucardo add table public.users db=server1
bucardo add table public.users db=server2

También puede agregar todas las tablas de la base de datos mediante el comando ‘ bucardo add all tables ‘. Con esto, también creará un relgroup si aún no existe.

bucardo add all tables --her=testdbSrv1 db=server1
bucardo add all tables --her=testdbSrv2 db=server2

Ingrese el siguiente comando para verificar la lista de relgroup disponibles.

bucardo list relgroup

Luego, verifique la lista de relgroup. Debería ver los detalles como se muestra a continuación. En cada relgroup, verá el nombre del relgroup seguido del servidor PostgreSQL y la tabla disponible que se agregó al relgroup.

Una vez que haya agregado el grupo de relaciones y la tabla, comenzará el proceso de sincronización para ambos servidores PostgreSQL. Ingrese el siguiente comando para crear una nueva sincronización » testdbSrv1 » que sincronizará » server1 » y » server2 «. Y la sincronización se llamará » testdbSrv2 » y sincronizará entre » server2 » y » server1 «.

bucardo add sync testdbSrv1 relgroup=testdbSrv1 db=server1,server2
bucardo add sync testdbSrv2 relgroup=testdbSrv2 db=server2,server1

Ahora verifique la lista de sincronización en Bucardo ingresando el siguiente comando.

bucardo list sync

Verá la sincronización » testdbSrv1 » con el estado » Activo » y la sincronización es desde » servidor1 » como origen y » servidor2 » como nodo de destino. En cuanto a la sincronización » testdbSrv2 «, el estado también es » Activo «, pero la sincronización es desde » servidor2 » como origen hacia el destino » servidor1 «.

A continuación, introduzca el siguiente comando para reiniciar el proceso de sincronización. Debería ver el mensaje » Iniciando Bucardo » cuando se complete correctamente.

bucardo restart sync

Por último, verifique el estado de sincronización utilizando el siguiente comando ‘ bucardo ‘.

bucardo status

Debería recibir una salida similar a esta: Tanto ‘ testdbSrv1 ‘ como ‘ testdbSrv2 ‘ se sincronizan en el estado ‘ Good‘, lo que confirma que la sincronización está funcionando y que su configuración es exitosa.

En este punto, ya ha configurado la replicación multimaestro en PostgreSQL a través de Bucardo. A continuación, verificará la replicación multimaestro agregando nuevos datos de ambos servidores y verificará la replicación entre servidores PostgreSQL.