El artículo se divide en las siguientes secciones:

• Consideraciones a tener en cuenta
• Instalación PostgreSQL
• Binary replication
• Logical replication
• Comprobación Binary replication
• Comprobación Logical replication
• Promocionar SECONDARY - Rep.Binaria
• Promocionar SECONDARY - Rep.Lógica

Consideraciones a tener en cuenta:

En todo servidor PostgreSQL las querys siempre se escriben en un fichero WAL antes de ser ejecutadas en la DB(independientemente de si hay replicación o no).

PostgreSQL soporta tres formas de replicación:

• Binary/Streaming Replication: Replicación binaria mediante streaming de ficheros WAL en real-time(recomendado).
• Logical Replication: Se decodifican los ficheros WAL y se envían las querys al SECONDARY(recomendado solo cuando las versiones de PostgreSQL, SO o LIBC difieren).
• pglogical : Proyecto de terceros para proporcionar replicación lógica en PostgreSQLs con versiones inferiores a 10.4(no recomendado).

Los servidores implicados en este manual son los siguientes:

PostgreSQL00: 192.168.69.22
PostgreSQL01: 192.168.69.23

Si estamos instalándolos sobre un sistema basado en jails-FreeBSD como es mi caso con Bastille , PostgreSQL dará un error sobre sysvipc:

2023-03-02 23:51:17.310 CET [63048] DETAIL:  Failed system call was shmget(key=65282, size=56, 03600).

Permitimos el acceso por jail, también se puede permitir de forma global en el host padre para todas las jails pero es preferible ser mas granular:

NOTA: No utilicéis allow.sysvipc ya que se considera deprecated y otorga mas permisos de los estrictamente necesarios.

Instalación PostgreSQL:

Ahora ya podemos instalar PostgreSQL sin problemas:

Inicializamos la base de datos:

Habilitamos y arrancamos el servicio:

Creamos en el PRIMARY la base de datos y la tabla de pruebas:

postgres=# CREATE DATABASE test_db;
postgres=# \c test_db;
test_db=# CREATE TABLE test( ID integer PRIMARY KEY NOT NULL, DATA varchar(40) NOT NULL);

Creamos en el PRIMARY un usuario de acceso regular:

test_db=# CREATE USER kr0m LOGIN ENCRYPTED PASSWORD 'PASSWORD';
test_db=# GRANT ALL PRIVILEGES ON test TO kr0m;

Permitimos el acceso del usuario kr0m:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    test_db         kr0m            192.168.69.0/24         md5

Recargamos la configuración:

Creamos en el PRIMARY el usuario de replicación:

Si vamos a utilizar la replicación binaria o replicación lógica donde vamos a indicar en la publicación las tablas a replicar, el usuario de replicación debe de ser creado con el role: REPLICATION.

En cambio si vamos a utilizar replicación lógica pero vamos a replicar todas las tablas de la DB con una publicación “FOR ALL TABLES”, el usuario de replicación debe de ser creado con el role: SUPERUSER.

En la replicación lógica indicaremos las tablas una por una o simplemente utilizaremos “FOR ALL TABLES” según nos convenga un acceso mas o menos granular.

test_db=# CREATE USER rep_user WITH REPLICATION LOGIN ENCRYPTED PASSWORD 'POSTGRE_REP_PASS';
test_db=# ALTER ROLE rep_user CONNECTION LIMIT -1;
test_db=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 kr0m      |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 rep_user  | Replication                                                | {}

test_db=# CREATE USER rep_user WITH SUPERUSER LOGIN ENCRYPTED PASSWORD 'POSTGRE_REP_PASS';
test_db=# ALTER ROLE rep_user CONNECTION LIMIT -1;
test_db=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 kr0m      |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 rep_user  | Superuser                                                  | {}

Binary replication:

El mayor inconveniente de este sistema de replicación es que se tiene que replicar el servidor entero, no se pueden elegir DBs/Tablas.

Permitimos el acceso al usuario de replicación tanto en el PRIMARY como en el SECONDARY, de este modo si cambiamos los roles tan solo deberemos resincronizar el servidor reinstalado:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    replication     rep_user        SECONDARY_IP/32             md5

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    replication     rep_user        PRIMARY_IP/32            md5

Recargamos la configuración:

Por defecto en FreeBSD el wall_level de PostgreSQL ya viene configurado con el nivel replica:

 wal_level 
-----------
 replica
(1 row)

Eliminamos en el SECONDARY cualquier dato previo que pueda tener:

Cargamos el backup en el SECONDARY:

Arrancamos el servicio:

Logical replication:

Funciona mediante un sistema de publicación/suscripción a nivel de tablas, las principales ventajas son:

• Replicación entre diferentes major versions de PostgreSQL. -> Migración entre versiones de PostgreSQL, es la única manera de evitar el problema de collations .
• Replicación entre instancias PostgreSQL en diferentes plataformas. -> Migración de Linux a FreeBSD.
• Mayor flexibilidad para replicar solo ciertos comandos, por ejemplo no replicar DELETES.
• Podemos formar cadenas de servidores con PRIMARY/SECONDARY en mitad de la cadena.

Los inconvenientes son:

• Los comandos DDL , como create database, rename table y demás no se replican.
  Como los comandos DDL no se replican, la estructura de las tablas de un PRIMARY no tiene porque ser la misma que en el SECONDARY, siempre que no entre en conflicto, por ejemplo en el SECONDARY se podrían añadir columnas que el PRIMARY no tenga.
  Una recomendación es si se van a hacer cambios, primero aplicarlos en el SECONDARY y luego en el PRIMARY, así nunca habrá problemas.
• Los IDs autogenerados podrían ser distintos entre el PRIMARY/SECONDARY, por ejemplo si la tabla tiene campos autoincrementales, el SECONDARY tenía datos y en el PRIMARY se ejecuta un INSERT en el SECONDARY se hace el INSERT pero el ID incremental será distinto.
• No soporta la replicación de views, materialized views, partition root tables, o foreign tables.
• Hay que configurar el pg_hba.conf y los usuarios creados por la CLI de PostgreSQL de forma manual en ambos servidores.

Esta replicación es recomendable para hacer migraciones entre versiones del SO y montarlo todo con el SO nuevo por replicación binaria, solo es un paso intermedio de transición.

Debemos tener en cuenta que la replicación lógica permite dos tipos de publicaciones una en la que indicamos tabla a tabla las tablas a publicar y otro modo en el que indicamos todas las tablas que contenga la base de datos sin necesidad de especificarlas(FOR ALL TABLES), según el tipo de publicación los pasos a seguir varían ligeramente.

Permitimos el acceso al usuario de replicación a cada una de las bases de datos a replicar en este caso test_db, tanto en el PRIMARY como en el SECONDARY, de este modo si cambiamos los roles tan solo deberemos resincronizar el servidor reinstalado:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    test_db         rep_user        SECONDARY_IP/32             md5

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    test_db         rep_user        PRIMARY_IP/32            md5

Si vamos a indicar que tablas queremos replicar el usuario de replicación tan solo necesita el grant SELECT sobre cada una de las tablas, en cambio si vamos a crear la publicación mediante “FOR ALL TABLES” tendremos que darle grants de SELECT en todas las tablas del schema public.

Asignamos los GRANTs en el PRIMARY:

test_db=# GRANT SELECT ON test TO rep_user;

test_db=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO rep_user;

Recargamos la configuración:

Para utilizar replicación lógica debemos cambiar el wal_level a logical, lo mejor es dejarlo configurado tanto en el PRIMARY como en el SECONDARY, de este modo si cambiamos el role todo queda preparado para poder reenganchar el equipo reinstalado:

wal_level = logical

Reinciamos el servicio:

Sacamos en el PRIMARY un dump de la estructura y los usuarios de las bases de datos:

Copiamos el dump al SECONDARY:

Las publicaciones nos permiten un amplio abanico de posibilidades:

CREATE PUBLICATION mypublication FOR TABLE users, departments;                                       -> Replicar varias tablas.
CREATE PUBLICATION active_departments FOR TABLE departments WHERE (active IS TRUE);                  -> Replicar los datos de una tabla que cumplan una condición concreta.
CREATE PUBLICATION alltables FOR ALL TABLES;                                                         -> Replicar todas las tablas incluidas las tablas creadas en un futuro(solo afecta al PRIMARY, el SECONDARY sigue necesitando configuración).
CREATE PUBLICATION insert_only FOR TABLE mydata WITH (publish = 'insert');                           -> Replicar solo los INSERTs de una tabla.
CREATE PUBLICATION production_publication FOR TABLE users, departments, TABLES IN SCHEMA production; -> Replicar varias tablas y las tablas que estén en el schema(recordad que un schema NO es una DB en PostgreSQL) indicado.
CREATE PUBLICATION sales_publication FOR TABLES IN SCHEMA marketing, sales;                          -> Replicar las tablas que estén en los schemas indicados.
CREATE PUBLICATION users_filtered FOR TABLE users (user_id, firstname);                              -> Replicar X campos de una tabla.

Vamos a crear una publicación por cada base de datos pero teniendo en cuenta que las bases de datos template y postgres cumplen propósitos especiales:

• postgres: Base de datos a la que conectan por defecto los clientes PostgreSQL, si la eliminamos debemos indicar en cada conexión a que base de datos queremos conectar, además de que muchos programas tendrán hardcodeada esta base de datos por defecto y dejarán de funcionar.
• template1: Se hace una copia al crear bases de datos nuevas, puede ser modificada para que las bases de datos nuevas tengán algún tipo de modificación que deseemos por defecto.
• template0: Mismo contenido que template1 si template1 no ha sido modificado, template0 nunca debe ser modificado.

Consultamos las bases de datos en el PRIMARY:

test_db=# \l
                                             List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  | ICU Locale | Locale Provider |   Access privileges   
-----------+----------+----------+---------+---------+------------+-----------------+-----------------------
 postgres  | postgres | UTF8     | C       | C.UTF-8 |            | libc            | 
 template0 | postgres | UTF8     | C       | C.UTF-8 |            | libc            | =c/postgres          +
           |          |          |         |         |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | C       | C.UTF-8 |            | libc            | =c/postgres          +
           |          |          |         |         |            |                 | postgres=CTc/postgres
 test_db   | postgres | UTF8     | C       | C.UTF-8 |            | libc            | 
(4 rows)

Creamos la publicación de test_db en el PRIMARY, según el tipo de publicación que vayamos a utilizar procederemos de un modo u otro:

test_db=# CREATE PUBLICATION test_db_publication FOR TABLE test;

test_db=# CREATE PUBLICATION test_db_publication FOR ALL TABLES;

Si tuviésemos mas bases de datos crearíamos una publicación por cada una de ellas:

test_db=# \c DB_NAME
DB_NAME=# CREATE PUBLICATION test_db_publication FOR TABLE test;

test_db=# \c DB_NAME
DB_NAME=# CREATE PUBLICATION test_db_publication FOR ALL TABLES;

Podemos ver las publicaciones:

test_db=# \dRp+
                      Publication test_db_publication
  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root 
----------+------------+---------+---------+---------+-----------+----------
 postgres | f          | t       | t       | t       | t         | f
Tables:
    "public.test"

test_db=# \dRp+
                      Publication test_db_publication
  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root 
----------+------------+---------+---------+---------+-----------+----------
 postgres | t          | t       | t       | t       | t         | f
(1 row)

Cargamos el dump de los esquemas y los usuarios en el SECONDARY:

Creamos la suscripción en el SECONDARY:

test_db=# CREATE SUBSCRIPTION test_db_subscription CONNECTION 'host=192.168.69.22 dbname=test_db user=rep_user password=POSTGRE_REP_PASS' PUBLICATION test_db_publication;

Podemos ver las suscripciones, tanto si utilizamos replicación lógica indicando las tablas a publicar como si utilizamos “FOR ALL TABLES” la salida será la misma en el SECONDARY:

test_db=# \dRs+
                                                                                                          List of subscriptions
         Name         |  Owner   | Enabled |      Publication      | Binary | Streaming | Two-phase commit | Disable on error | Synchronous commit |                                 Conninfo                                  | Skip LSN 
----------------------+----------+---------+-----------------------+--------+-----------+------------------+------------------+--------------------+---------------------------------------------------------------------------+----------
 test_db_subscription | postgres | t       | {test_db_publication} | f      | f         | d                | f                | off                | host=192.168.69.22 dbname=test_db user=rep_user password=POSTGRE_REP_PASS | 0/0
(1 row)

En este momento las replicación estará funcionando, pero debemos tener en cuenta que incluso habiendo configurado la publicación con “FOR ALL TABLES”, al crear tablas en el PRIMARY estas no son replicadas de forma automática, para que se repliquen siempre tendremos que crearlas manualmente en el SECONDARY y según como hayamos creado la publicación ejecutar unos comandos adicionales.

PRIMARY:
test_db=# CREATE TABLE NOMBRE_NUEVA_TABLA( ID integer PRIMARY KEY NOT NULL, DATA varchar(40) NOT NULL);
test_db=# ALTER PUBLICATION test_db_publication ADD TABLE NOMBRE_NUEVA_TABLA;
test_db=# GRANT SELECT ON NOMBRE_NUEVA_TABLA TO rep_user;

SECONDARY:
test_db=# CREATE TABLE NOMBRE_NUEVA_TABLA( ID integer PRIMARY KEY NOT NULL, DATA varchar(40) NOT NULL);
test_db=# ALTER SUBSCRIPTION test_db_subscription REFRESH PUBLICATION;
test_db=# GRANT SELECT ON NOMBRE_NUEVA_TABLA TO rep_user;

PRIMARY:
test_db=# CREATE TABLE NOMBRE_NUEVA_TABLA( ID integer PRIMARY KEY NOT NULL, DATA varchar(40) NOT NULL);

SECONDARY:
test_db=# CREATE TABLE NOMBRE_NUEVA_TABLA( ID integer PRIMARY KEY NOT NULL, DATA varchar(40) NOT NULL);
test_db=# ALTER SUBSCRIPTION test_db_subscription REFRESH PUBLICATION;

NOTA: En la replicación lógica indicando tablas, el GRANT se ejecuta en los dos nodos de este modo si cambiamos de ROLE los GRANTs ya estarán asignados.

Comprobación Binary replication:

Comprobamos que funcione creando una base de datos en el PRIMARY y viendo como se replica en el SECONDARY.

postgres=# CREATE DATABASE testkr0m;
postgres=# \l
                                             List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  | ICU Locale | Locale Provider |   Access privileges   
-----------+----------+----------+---------+---------+------------+-----------------+-----------------------
 testkr0m  | postgres | UTF8     | C       | C.UTF-8 |            | libc            | 

En el PRIMARY podemos ver el SECONDARY conectado:

postgres=# \x
postgres=# SELECT * FROM pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 65460
usesysid         | 16384
usename          | rep_user
application_name | walreceiver
client_addr      | 192.168.69.23
client_hostname  | 
client_port      | 30872
backend_start    | 2023-04-29 16:58:58.961358+02
backend_xmin     | 
state            | streaming
sent_lsn         | 0/348CEE0
write_lsn        | 0/348CEE0
flush_lsn        | 0/348CEE0
replay_lsn       | 0/348CEE0
write_lag        | 00:00:00.000104
flush_lag        | 00:00:00.002399
replay_lag       | 00:00:00.002429
sync_priority    | 0
sync_state       | async
reply_time       | 2023-04-29 17:02:37.54732+02

En el SECONDARY podemos ver de donde lee los datos:

postgres=# \x
postgres=# SELECT pg_is_in_recovery(),pg_is_wal_replay_paused(), pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(), pg_last_xact_replay_timestamp();
-[ RECORD 1 ]-----------------+------------------------------
pg_is_in_recovery             | t
pg_is_wal_replay_paused       | f
pg_last_wal_receive_lsn       | 0/349C428
pg_last_wal_replay_lsn        | 0/349C428
pg_last_xact_replay_timestamp | 2023-04-29 17:03:22.893567+02

NOTA: Si el servidor está en modo standalone(no se ha configurado replicación), ninguna de las dos querys retorna datos. Si la replicación está rota el PRIMARY no retorna nada.

Comprobación Logical replication:

Comprobamos que funcione insertando datos en el PRIMARY y viendo como se replica en el SECONDARY.

test_db=# INSERT INTO test (ID,DATA) VALUES(11,22);

test_db=# SELECT * FROM test;
 id | data 
----+------
 11 | 22
(1 row)

En el PRIMARY podemos ver el SECONDARY conectado:

postgres=# \x
postgres=# SELECT * FROM pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 31051
usesysid         | 16391
usename          | rep_user
application_name | test_db_subscription
client_addr      | 192.168.69.23
client_hostname  | 
client_port      | 38663
backend_start    | 2023-05-06 15:39:34.43372+02
backend_xmin     | 
state            | streaming
sent_lsn         | 0/1EDDEE0
write_lsn        | 0/1EDDEE0
flush_lsn        | 0/1EDDEE0
replay_lsn       | 0/1EDDEE0
write_lag        | 
flush_lag        | 
replay_lag       | 
sync_priority    | 0
sync_state       | async
reply_time       | 2023-05-06 16:37:02.234299+02

Promocionar SECONDARY - Rep.Binaria:

Por defecto un SECONDARY está en modo RO(hot_standby), si el PRIMARY llega a tener algún problema debemos promocionar el SECONDARY y pasará a modo RW.

Comprobamos que sea un SECONDARY:

postgres=# SELECT pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 t
(1 row)

Lo promocionamos:

postgres=# SELECT pg_promote();
 pg_promote 
------------
 t
(1 row)

Comprobamos que se haya promocionado:

postgres=# SELECT pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 f
(1 row)

Ahora si intentamos escribir en el SECONDARY promocionado, nos lo permitirá.

Promocionar SECONDARY - Rep.Lógica:

En la replicación lógica el SECONDARY siempre ha sido escrivible, así que no hay que hacer nada, tan solo utilizarlo.