PostgreSQL: Cómo configurar la replicación en tiempo real.

Streaming replication in PostgreSQL.

En esta entrada te mostrare cómo configurar la replicación en tiempo real en PostgreSQL (streaming replication) paso a paso en Windows.

La idea detrás de usar este tipo de replicación es la siguiente:

Tengo una aplicación de escritorio que utiliza una base de datos local, quiero ver los datos de esta aplicación en tiempo real en una página web para monitorear ciertos indicadores.

Originalmente consideré utilizar una base de datos en la nube para la aplicación, pero descarté esta opción porque las sucursales pueden quedarse sin internet, lo que inutilizaría la aplicación. Es por esto que la replicación en tiempo real que nos ofrece PostgreSQL se presenta como una excelente opción. Este ejercicio es para probar el concepto.

Para este ejemplo ya tengo instalado la versión 14 de PostgreSQL en ambas máquinas (ambas computadoras se encuentran en la misma red local).

Una de estas máquinas es el Servidor Principal y la otra es el Servidor Replica:

Vamos a seguir los siguientes pasos:

Paso 1: Configuraciones en el servidor Principal.

En el servidor principal vamos a configurar la dirección IP para escuchar las conexiones de los clientes en el archivo postgresql.conf eliminando el “#” delante de listen_address y estableciéndolo como “*” (Lo que significa que se aceptarán conexiones de todos los clientes).

Este archivo de configuración se encuentra en “C:\Program Files\PostgreSQL\14\data\”

#----------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#----------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'	# what IP address(es) to listen on;

Ahora conéctate a PostgreSQL en el servidor principal desde la línea de comandos usando el siguiente comando:

C:\Program Files\PostgreSQL\14\bin>psql.exe -p 5433 -U postgres

Es importante notar que mi servidor principal usa el puerto “5433” y que estamos usando el usuario “postgres”

Introduce la contraseña del usuario postgres.

Ahora vamos a crear un usuario con privilegios de REPLICADOR, a este lo voy a llamar ‘replicador’ y le voy a asignar el password ‘admin@123’, por favor usa un password más fuerte para que no vayas a tener problemas de seguridad

C:\Program Files\PostgreSQL\14\bin>psql.exe -p 5433 -U postgres
Contraseña para usuario postgres:
psql (14.2)
postgres=# CREATE USER replicador WITH REPLICATION ENCRYPTED PASSWORD 'admin@123';
CREATE ROLE
postgres=#

Para salir de psql escribe \q

Ahora vamos a editar el archivo pg_hba.conf que se encuentra en la siguiente ruta:

“C:\Program Files\PostgreSQL\14\data\pg_hba.conf”

y agregamos las siguientes líneas:

# IPv4 local connections:
host all replicador 127.0.0.1/32 md5


# Allow replication connections from localhost, by a user with the
# replication privilege.
host replication replicador 192.168.0.6/24 md5

Ahora reinicia PostgreSQL en el servidor principal haciendo lo siguiente:

• Presiona la tecla de Windows + R, aparecerá la ventana “Ejecutar”.
• Escribe “services.msc” en la ventana Ejecutar y presiona Enter o el botón Aceptar.
• Se abrirá la ventana de Servicios, busca “postgresql-x64-14 – PostgreSQL Server 14”.
• Haz clic derecho en “postgresql-x64-14 – PostgreSQL Server 14” y selecciona la opción “Reiniciar”.
• Los servicios se reiniciarán ahora.

Paso 2: Configuraciones en el servidor Replica.

Tenemos que detener PostgreSQL en el servidor Replica, vamos a seguir los mismos pasos que seguimos para reiniciar el servidor Principal solo que en esta ocasión vamos a detener el servicio en lugar de reiniciarlo:

• En el servidor Replica presiona la tecla de Windows + R, aparecerá la ventana “Ejecutar”.
• Escribe “services.msc” en la ventana Ejecutar y presiona Enter o el botón Aceptar.
• Se abrirá la ventana de Servicios, busca “postgresql-x64-14 – PostgreSQL Server 14”.
• Haz clic derecho en “postgresql-x64-14 – PostgreSQL Server 14” y selecciona la opción “Detener”.

Ahora borra todo lo que se encuentra dentro del siguiente directorio (archivos y carpetas):

C:\Program Files\PostgreSQL\14\data

Ahora vamos a usar pg_basebackup para hacer un respaldo inicial de la base de datos, teclea lo siguiente en la línea de comandos:

pg_basebackup -h 192.168.0.115 -p 5433 -D "C:\Program Files\PostgreSQL\14\data" -U replicador -P -v -R -X stream -C -S slaveslot1

Luego, proporciona la contraseña para el usuario “replicador” creado en el servidor Principal.

pg_basebackup: initiating base backup, waiting for checkpoint to complete .................................... 

pg_basebackup: syncing data to disk ... 

pg_basebackup: base backup completed

Observa que se crea el archivo “standby.signal” y se añaden las configuraciones de conexión a “postgresql.auto.conf”.

Ahora, conéctate al servidor principal.

Deberías poder ver el slot de replicación llamado “slotslave1” cuando abras la vista “pg_replication_slots” de la siguiente manera:

SELECT * FROM pg_replication_slots;

Paso 3: Prueba la configuración de replicación.

Vamos a iniciar PostgreSQL en el servidor Replica de la siguiente manera:

• En el servidor Replica presiona la tecla de Windows + R, aparecerá la ventana “Ejecutar”.
• Escribe “services.msc” en la ventana Ejecutar y presiona Enter o el botón Aceptar.
• Se abrirá la ventana de Servicios, busca “postgresql-x64-14 – PostgreSQL Server 14”.
• Haz clic derecho en “postgresql-x64-14 – PostgreSQL Server 14” y selecciona la opción “Iniciar”.

Ahora, intenta crear una tabla o una base de datos en el servidor Replica. Esto generará un error ya que el servidor Replica es de solo lectura.

Podemos verificar el estado del servidor Replica utilizando el siguiente comando:

SELECT * FROM pg_stat_wal_receiver;

Para revisar que todo esté funcionando bien vamos a crear una base de datos en el servidor Principal, una vez hecho esto verifiquemos si se replico en el servidor Replica.

¡Eso es todo! Hemos configurado correctamente la replicación en tiempo real en PostgreSQL.

Si algo no funciona revisa los permisos de los puertos usados por PostgreSQL tanto en el servidor Principal como en el servidor Replica.

Comandos que pueden ser útiles:

para borrar un replication_slot:

select pg_drop_replication_slot('nombre_del_slot');