Saltar al contenido

Cómo montar la replicación lógica con PostgreSQL 10 en Ubuntu 18.04

marzo 4, 2020

 

Introducción

Al configurar una aplicación para la producción, a menudo es útil tener varias copias de su base de datos en su lugar. El proceso de mantener copias de bases de datos sincronizados se llama replicación . La replicación puede proporcionar alta disponibilidad de escalamiento horizontal para altos volúmenes de operaciones de lectura simultáneas, junto con latencias de lectura reducidos. También permite la replicación-peer-to-peer entre los servidores de bases de datos distribuidas geográficamente.

PostgreSQL es un sistema de base de datos relacional de objetos de código abierto que es altamente extensible y compatible con ACID (Atomicidad, Consistencia, Aislamiento, Durabilidad) y el estándar SQL. Versión 10.0 de PostgreSQL introdujo soporte para lógico réplica ción , además de física réplica ción . En una lógica réplica ción esquema , las operaciones de escritura de alto nivel se transmiten desde un servidor de base de datos maestro en una o más bases de datos réplica servidores. En una réplica física ción esquema , las operaciones de escritura en lugar binarios se transmiten de maestro a réplica , produciendo un byte por byte copia exacta del contenido original. En los casos en los que quiera dirigirse a un determinado subconjunto de datos, tales como la presentación de informes sin carga, parches o actualización, lógica réplica ción puede ofrecer la velocidad y la flexibilidad.

En este tutorial, se configurará la replicación lógica con PostgreSQL 10 en dos servidores de Ubuntu 18.04, con un servidor que actúa como maestro y el otro como la réplica. Al final del tutorial serás capaz de replicar los datos desde el servidor principal a la réplica mediante la replicación lógica.

Requisitos previos

Para seguir este tutorial, necesitará:

  • Dos servidores de Ubuntu 18.04, que vamos a nombre del DB-master y DB-réplica, cada juego con una cuenta de usuario y sudo regulares privilegios. Para establecer estas arriba, seguir este tutorial de instalación del servidor inicial.
  • red privada habilitada en sus servidores. una red privada permite la comunicación entre los servidores sin los riesgos de seguridad asociados con la exposición de las bases de datos a la Internet pública.
  • PostgreSQL 10 instalado en ambos servidores, siguiendo el Paso 1 de Cómo instalar y utilizar PostgreSQL en Ubuntu 18.04.

Paso 1 – Configuración de PostgreSQL para Lógico replicación

Hay varios ajustes de configuración que tendrá que modificar para permitir la replicación lógica entre sus servidores. En primer lugar, podrás configurar Postgres para escuchar en la interfaz de red privada en lugar de la pública, como la exposición de datos a través de la red pública es un riesgo para la seguridad. A continuación, podrás configurar los ajustes apropiados para permitir la replicación a db-réplica .

En db-master , /etc/postgresql/10/main/postgresql.conf abierto, el archivo de configuración del servidor principal:

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

  • sudo nano /etc/postgresql/10/main/postgresql.conf

Busque la línea siguiente:

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

descomentarla quitando el #, y añadir su db_master_private_ip_address para habilitar las conexiones de la red privada:

Nota: En este paso y los pasos que siguen, asegúrese de utilizar el privada las direcciones IP de los servidores, y no a sus direcciones IP públicas. La exposición de un servidor de base de datos a la Internet pública es un gran riesgo para la seguridad.

...
listen_addresses = 'localhost, db_master_private_ip_address'
...

Esto hace db-master escuchar las conexiones entrantes en la red privada, además de la interfaz de bucle de retorno.

A continuación, busque la línea siguiente:

...
#wal_level = replica # minimal, replica, or logical
...

descomentarla, y cambiarlo para configurar el registro de (WAL) Nivel delante de lógica PostgreSQL escritura. Esto aumenta el volumen de entradas en el registro, la adición de la información necesaria para la extracción de discrepancias o cambios en particular conjuntos de datos:

...
wal_level = logical
...

Las entradas en este registro serán consumidos por el servidor de réplica, lo que permite la replicación del alto nivel de escritura las operaciones del maestro.

Guarde el archivo y ciérrelo.

A continuación, vamos a editar /etc/postgresql/10/main/pg_hba.conf, el archivo que controla permitido anfitriones, la autenticación y el acceso a bases de datos:

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

  • sudo nano / etc / postgresql / 10 / / pg_hba principal. conf

Después de la última línea, vamos a añadir una línea para permitir conexiones de red entrantes de db-réplica . Vamos a utilizar la dirección IP privada db-réplica ‘s, y especifica que se permiten las conexiones de todos los usuarios y bases de datos: ahora serán permitidos de db-réplica

...
# TYPE DATABASE USER ADDRESS METHOD
...
host all all db_replica_private_ip_address/32 md5

conexiones de red entrantes, autenticado por una contraseña de hash (MD5).

Guarde el archivo y ciérrelo.

A continuación, vamos a establecer las reglas de firewall para permitir el tráfico de db-réplica al puerto 5432 en db-master :

sudo ufw allow from db_replica_private_ip_address to any port 5432

  • sudo UFW permiten a db_replica_private_ip_address a cualquier puerto 5432

Por último, reinicie el servidor PostgreSQL para que los cambios tengan efecto:

sudo systemctl restart postgresql

  • sudo systemctl reinicio PostgreSQL

con el conjunto de configuración para permitir la replicación lógica, ahora se puede pasar a la creación de una base de datos, el papel del usuario, y una mesa.

Paso 2 – Configuración de una base de datos, la función del usuario, y la Tabla

Para probar la funcionalidad de la configuración de replicación, vamos a crear una base de datos, tabla y rol de usuario. Va a crear una base de datos ejemplo, con una tabla de ejemplo, que luego se puede utilizar para probar la replicación lógica entre sus servidores. También creará un usuario dedicado y asignarles privilegios tanto sobre la base de datos y la tabla.

En primer lugar, abra el símbolo del psql como el usuario postgres con el siguiente comando en tanto db-master y db-réplica :

sudo -u postgres psql

  • sudo -u postgres psql

sudo -u postgres psql

  • sudo -u postgres psql

Crear una nueva base de datos llamada ejemplo en ambos anfitriones:

CREATE DATABASE example;

  • ejemplo CREATE base de datos;

CREATE DATABASE example;

  • ejemplo CREATE base de datos;

Nota: El final; En estos comandos se requiere. En las sesiones interactivas, PostgreSQL no ejecutará comandos SQL hasta que se termina con un punto y coma. Los meta-comandos (los que empiezan con una barra invertida, como Q y c) controlan directamente el propio cliente psql, y por lo tanto están exentos de esta regla. Para más información sobre meta-comandos y el cliente psql, consulte la documentación de PostgreSQL.

Utilizando el Connect meta-comandos, conexión a las bases de datos que acaba de crear en cada host:

c example

  • c ejemplo

c example

  • c ejemplo

Crear una nueva tabla llamados widgets con campos arbitrarios en tanto anfitriones:

CREATE TABLE widgets
(
id SERIAL,
name TEXT,
price DECIMAL,
CONSTRAINT widgets_pkey PRIMARY KEY (id)
);

  • CREATE TABLE widgets de
  • (
  • ID de serie,
  • nombre de texto,
  • precio decimal,
  • CONSTREÑIMIENTO widgets_pkey PRIMARY KEY (id)
  • );

CREATE TABLE widgets
(
id SERIAL,
name TEXT,
price DECIMAL,
CONSTRAINT widgets_pkey PRIMARY KEY (id)
);

  • CREATE TABLE widgets de
  • (
  • ID de serie,
  • nombre de texto,
  • precio decimal,
  • CONSTREÑIMIENTO widgets_pkey PRIMARY KEY (id)
  • );

La tabla de db-réplica no necesita ser idéntico a su db-master contraparte. Sin embargo, debe contener cada columna individual presente sobre la mesa en db-master . columnas adicionales no deben tener NO NULO o de otro tipo. Si lo hacen, la replicación se producirá un error.

En db-master , vamos a crear una nueva función de usuario con la opción de replicación y una contraseña de inicio de sesión. El atributo de replicación debe ser asignado a cualquier papel utilizado para la replicación. Vamos a llamar a nuestro usuario Sammy, pero se puede sustituir esto con su propio nombre de usuario. Asegúrese de sustituir también my_password con su propia contraseña segura:

CREATE ROLE sammy WITH REPLICATION LOGIN PASSWORD 'my_password';

  • crear función de Sammy con la replicación LOGIN CONTRASEÑA ‘my_password’;

Tome nota de su contraseña, como lo va a usar más adelante db-réplica a configurar la replicación.

Todavía en db-master , conceder privilegios completos en la base de datos de ejemplo para la función de usuario que acaba de crear:

GRANT ALL PRIVILEGES ON DATABASE example TO sammy;

  • conceder a todos los privilegios en el ejemplo BASE DE DATOS PARA Sammy;

A continuación, conceder privilegios sobre todas las tablas contenidas en la base de datos a su usuario: PRIVILEGIOS

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO sammy;

  • GRANT ALL en todas las mesas en el esquema público a Sammy;

El esquema público es un esquema predeterminado en cada base de datos en las tablas que se colocan automáticamente.

Con estos privilegios establecidos, que ahora puede pasar a hacer las tablas de su base de datos de ejemplo disponibles para la replicación.

Paso 3 – Configuración de una publicación

Publicaciones son el mecanismo que utiliza PostgreSQL para hacer mesas disponibles para la replicación. El servidor de base de datos se mantendrá un registro interno del estado de la conexión y reproducción de cualquier servidor de réplica asociados con una publicación determinada. En db-master , va a crear una publicación, my_publication, que funcionarán como una copia maestra de los datos que serán enviados a sus suscriptores – en nuestro caso, db-réplica .

En db-master , crear una publicación llamada my_publication:

CREATE PUBLICATION my_publication;

  • CREAR my_publication publicación;

Añadir widgets de la tabla creada anteriormente a la misma:

ALTER PUBLICATION my_publication ADD TABLE widgets;

  • ALTER PUBLICACIÓN my_publication los widgets Agregar tabla;

Con su publicación en su lugar, ahora se puede añadir un suscriptor que se tire de los datos de ella.

Paso 4 – Crear una suscripción

Suscripciones son utilizados por PostgreSQL para conectar a las publicaciones existentes. Una publicación puede tener muchas suscripciones a través de diferentes servidores de réplica, y servidores de réplica también puede tener sus propias publicaciones con los suscriptores. Para acceder a los datos de la tabla que creó en db-master , tendrá que crear una suscripción a la publicación que ha creado en el paso anterior, my_publication.

En db-réplica , vamos a crear una suscripción llamado my_subscription. El comando CREATE Suscripción nombrará a la suscripción, mientras que el parámetro de conexión definirá la cadena de conexión a la editorial. Esta cadena incluirá detalles de la conexión del servidor maestro y credenciales de acceso, incluidos el nombre de usuario y la contraseña que ha definido anteriormente, junto con el nombre de la base de datos de ejemplo. Una vez más, recuerde que debe utilizar la dirección IP privada s, y reemplazar my_password con su propia contraseña:

CREATE SUBSCRIPTION my_subscription CONNECTION 'host=db_master_private_ip_address port=5432 password=my_password user=sammy dbname=example' PUBLICATION my_publication;

  • CREAR Suscripción my_subscription CONEXIÓN ‘ db-master host = puerto db_master_private_ip_address = 5432 password = usuario my_password = nombredb Sammy = ejemplo’ my_publication publicación;

Usted verá el siguiente resultado que confirma la suscripción:

OutputNOTICE: created replication slot "my_subscription" on publisher
CREATE SUBSCRIPTION

Al crear una suscripción, PostgreSQL se sincronizarán automáticamente todos los datos preexistentes desde el maestro a la réplica. En nuestro caso no hay datos de sincronización desde la mesa de widgets está vacío, pero esto es una característica útil cuando se añaden nuevas suscripciones a una base de datos existente.

Con una suscripción en su lugar, vamos a probar la configuración mediante la adición de algunos datos de demostración para la mesa de widgets.

Paso 5 – Pruebas y solución de problemas

Para la replicación de prueba entre nuestro maestro y réplica, vamos a añadir algunos datos a la tabla widgets y verificar que reproduzca correctamente.

En db-maestro , inserto los siguientes datos en la tabla de widgets: los widgets

INSERT INTO widgets (name, price) VALUES ('Hammer', 4.50), ('Coffee Mug', 6.20), ('Cupholder', 3.80);

  • INSERT INTO (nombre, precio) VALUES ( ‘Hammer’, 4,50), ( ‘taza’, 6,20), (‘ Portabebidas’, 3,80);

En db-réplica , ejecute la siguiente consulta para obtener todos las entradas en esta tabla:

SELECT * FROM widgets;

  • SELECT * FROM reproductores;

Ahora debería ver:

Output id | name | price
----+------------+-------
1 | Hammer | 4.50
2 | Coffee Mug | 6.20
3 | Cupholder | 3.80
(3 rows)

éxito! Las entradas se han replicado con éxito de db-master a db-réplica . A partir de ahora, todos los de inserción, actualización y consultas elimine se replica a través de los servidores de forma unidireccional.

Una cosa a la nota acerca de las consultas de escritura en los servidores de réplica es que no se replican de nuevo al servidor maestro. PostgreSQL actualmente tiene soporte limitado para resolver conflictos cuando los datos entre los servidores diverge. Si hay un conflicto, la reproducción se detendrá y PostgreSQL esperará hasta que el problema se corrige manualmente por el administrador de la base de datos. Por esa razón, la mayoría de las aplicaciones dirigirá todas las operaciones de escritura en el servidor maestro, lee y distribuir entre los servidores de réplica disponibles.

Ahora puede salir de la línea de psql en ambos servidores:

q

  • q

q

  • q

Ahora que ha terminado de probar la configuración, puede añadir y replicar los datos por su cuenta. Solución de problemas de replicación

Si no parece estar funcionando, un buen primer paso es comprobar el registro de PostgreSQL en db-réplica de los posibles errores:

tail /var/log/postgresql/postgresql-10-main.log

  • cola / var / log / PostgreSQL / postgresql-10 -main.log

Estos son algunos problemas comunes que pueden prevenir la replicación de trabajo: redes

  • privada no está habilitado en ambos servidores, o los servidores están en diferentes redes;
  • db-master no está configurado para escuchar las conexiones en la red privada IP correcta;
  • The Write Ahead Nivel de registro sobre db-maestro se configura de forma incorrecta (que se debe establecer en lógica);
  • db-maestro no está configurado para aceptar conexiones entrantes desde la dirección IP privada correcta db-réplica;
  • Un servidor de seguridad como UFW está bloqueando las conexiones entrantes en el puerto de PostgreSQL 5432;
  • Hay nombres de tabla no coincidentes o campos entre db-master y DB-réplica; función de base de
  • El Sammy le faltan los permisos necesarios para acceder a la base de datos de ejemplo sobre db-maestro; función de base de
  • El Sammy no se encuentra la opción de replicación en db-maestro; función de base de
  • El Sammy le faltan los permisos necesarios para acceder a la tabla widgets en db-maestro;
  • La tabla no se añadió a la publicación sobre db-master.

Después de resolver el problema existente (s), la replicación debería tener lugar de forma automática. Si no es así, utilice el comando siguiente para quitar la suscripción existente antes de volver a crearlo:

DROP SUBSCRIPTION my_subscription;

  • DROP Suscripción my_subscription;

Conclusión

En este tutorial usted ha instalado correctamente PostgreSQL 10 en dos servidores de Ubuntu 18.04 y se configura la replicación lógica entre ellos.

Ahora tiene el conocimiento necesario para experimentar con la escala horizontal de lectura, la alta disponibilidad y la distribución geográfica de su base de datos PostgreSQL mediante la adición de servidores de réplica adicionales.

Para obtener más información acerca de la replicación lógica en PostgreSQL 10, se puede leer el capítulo sobre el tema en la documentación oficial de PostgreSQL, así como las entradas manuales en la creación de publicaciones y CREATE comandos suscripción.