MaterializedPostgreSQL

Crea una base de datos de ClickHouse con tablas de una base de datos de PostgreSQL. En primer lugar, la base de datos con el motor MaterializedPostgreSQL crea una instantánea de la base de datos de PostgreSQL y carga las tablas necesarias. Las tablas necesarias pueden incluir cualquier subconjunto de tablas de cualquier subconjunto de esquemas de la base de datos especificada. Junto con la instantánea, el motor de base de datos obtiene el LSN y, una vez completado el volcado inicial de las tablas, empieza a extraer actualizaciones del WAL. Después de crear la base de datos, las tablas que se añadan posteriormente a la base de datos de PostgreSQL no se incorporan automáticamente a la replicación. Deben añadirse manualmente con la consulta ATTACH TABLE db.table.

La replicación se implementa mediante el protocolo de replicación lógica de PostgreSQL, que no permite replicar DDL, pero sí detectar si se han producido cambios que rompen la replicación (cambios en el tipo de columna, adición o eliminación de columnas). Estos cambios se detectan y las tablas correspondientes dejan de recibir actualizaciones. En este caso, debe usar las consultas ATTACH/ DETACH PERMANENTLY para recargar por completo la tabla. Si el DDL no rompe la replicación (por ejemplo, al cambiar el nombre de una columna), la tabla seguirá recibiendo actualizaciones (la inserción se realiza por posición).

CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MaterializedPostgreSQL('host:port', 'database', 'user', 'password') [SETTINGS ...]

Parámetros del motor

• host:port — endpoint del servidor PostgreSQL.
• database — nombre de la base de datos de PostgreSQL.
• user — usuario de PostgreSQL.
• password — contraseña del usuario.

CREATE DATABASE postgres_db
ENGINE = MaterializedPostgreSQL('postgres1:5432', 'postgres_database', 'postgres_user', 'postgres_password');

SHOW TABLES FROM postgres_db;

┌

Después de crear la base de datos MaterializedPostgreSQL, esta no detecta automáticamente las tablas nuevas de la base de datos de PostgreSQL correspondiente. Estas tablas se pueden añadir manualmente:

ATTACH TABLE postgres_database.new_table;

Es posible eliminar tablas específicas de la replicación:

DETACH TABLE postgres_database.table_to_remove PERMANENTLY;

El esquema de PostgreSQL se puede configurar de 3 formas (a partir de la versión 21.12).

1. Un esquema por cada motor de base de datos MaterializedPostgreSQL. Requiere usar la configuración materialized_postgresql_schema. Se accede a las tablas solo por el nombre de la tabla:

CREATE DATABASE postgres_database
ENGINE = MaterializedPostgreSQL('postgres1:5432', 'postgres_database', 'postgres_user', 'postgres_password')
SETTINGS materialized_postgresql_schema = 'postgres_schema';

SELECT * FROM postgres_database.table1;

2. Cualquier cantidad de esquemas con un conjunto específico de tablas para un mismo database engine MaterializedPostgreSQL. Requiere usar la configuración materialized_postgresql_tables_list. Cada tabla se escribe junto con su esquema. Se accede a las tablas mediante el nombre del esquema y el nombre de la tabla al mismo tiempo:

CREATE DATABASE database1
ENGINE = MaterializedPostgreSQL('postgres1:5432', 'postgres_database', 'postgres_user', 'postgres_password')
SETTINGS materialized_postgresql_tables_list = 'schema1.table1,schema2.table2,schema1.table3',
         materialized_postgresql_tables_list_with_schema = 1;

SELECT * FROM database1.`schema1.table1`;
SELECT * FROM database1.`schema2.table2`;

Pero en este caso, todas las tablas de materialized_postgresql_tables_list deben escribirse con el nombre de su esquema. Requiere materialized_postgresql_tables_list_with_schema = 1.

Advertencia: en este caso no se permiten puntos en el nombre de la tabla.

3. Cualquier número de esquemas con el conjunto completo de tablas para un motor de base de datos MaterializedPostgreSQL. Requiere usar el ajuste materialized_postgresql_schema_list.

CREATE DATABASE database1
ENGINE = MaterializedPostgreSQL('postgres1:5432', 'postgres_database', 'postgres_user', 'postgres_password')
SETTINGS materialized_postgresql_schema_list = 'schema1,schema2,schema3';

SELECT * FROM database1.`schema1.table1`;
SELECT * FROM database1.`schema1.table2`;
SELECT * FROM database1.`schema2.table2`;

Advertencia: en este caso, no se permiten puntos en el nombre de la tabla.

1. La configuración wal_level debe tener asignado el valor logical, y el parámetro max_replication_slots debe tener un valor de al menos 2 en el archivo de configuración de PostgreSQL.

2. Cada tabla replicada debe tener una de las siguientes identidades de réplica:

• clave primaria (de forma predeterminada)

• índice

postgres# CREATE TABLE postgres_table (a Integer NOT NULL, b Integer, c Integer NOT NULL, d Integer, e Integer NOT NULL);
postgres# CREATE unique INDEX postgres_table_index on postgres_table(a, c, e);
postgres# ALTER TABLE postgres_table REPLICA IDENTITY USING INDEX postgres_table_index;

La clave primaria siempre se comprueba primero. Si no existe, se comprueba el índice definido como índice de identidad de réplica. Si el índice se usa como identidad de réplica, solo puede haber un único índice de este tipo en una tabla. Puede comprobar qué tipo se usa para una tabla concreta con el siguiente comando:

postgres# SELECT CASE relreplident
          WHEN 'd' THEN 'default'
          WHEN 'n' THEN 'nothing'
          WHEN 'f' THEN 'full'
          WHEN 'i' THEN 'index'
       END AS replica_identity
FROM pg_class
WHERE oid = 'postgres_table'::regclass;

Establece una lista de tablas de la base de datos PostgreSQL separadas por comas, que se replicarán mediante el motor de base de datos MaterializedPostgreSQL.

Cada tabla puede tener, entre corchetes, un subconjunto de las columnas que se replicarán. Si se omite ese subconjunto de columnas, se replicarán todas las columnas de la tabla.

materialized_postgresql_tables_list = 'table1(co1, col2),table2,table3(co3, col5, col7)

Valor predeterminado: lista vacía — significa que se replicará toda la base de datos de PostgreSQL.

Valor por defecto: cadena vacía. (Se usa el esquema por defecto)

Valor predeterminado: lista vacía. (Se usa el esquema predeterminado)

Establece el número de filas recopiladas en memoria antes de volcar los datos en la tabla de la base de datos PostgreSQL.

Posibles valores:

• Entero positivo.

Valor predeterminado: 65536.

Un slot de replicación creado por el usuario. Debe usarse junto con materialized_postgresql_snapshot.

Una cadena de texto que identifica una instantánea desde la que se realizará el volcado inicial de las tablas de PostgreSQL. Debe usarse junto con materialized_postgresql_replication_slot.

CREATE DATABASE database1
ENGINE = MaterializedPostgreSQL('postgres1:5432', 'postgres_database', 'postgres_user', 'postgres_password')
SETTINGS materialized_postgresql_tables_list = 'table1,table2,table3';

SELECT * FROM database1.table1;

Los ajustes pueden modificarse, si es necesario, mediante una consulta DDL. Sin embargo, no es posible cambiar el ajuste materialized_postgresql_tables_list. Para actualizar la lista de tablas de este ajuste, use la consulta ATTACH TABLE.

ALTER DATABASE postgres_database MODIFY SETTING materialized_postgresql_max_block_size = <new_size>;

Utilice un identificador único del consumidor de replicación. Valor predeterminado: 0. Si se establece en 1, permite configurar varias tablas MaterializedPostgreSQL que apunten a la misma tabla PostgreSQL.

Asigna los tipos date y timestamp/timestamptz de PostgreSQL a Date32 y DateTime64 de ClickHouse, que abarcan el rango de valores más amplio de los tipos de PostgreSQL. Valor predeterminado: 1. Si se establece en 0, se usan en su lugar los tipos más limitados Date y DateTime (los valores fuera de su rango o con precisión de subsegundos no se pueden representar).

Esta configuración solo controla los tipos de columna que la inferencia de tipos elige al crear las tablas anidadas, por lo que debe especificarse al ejecutar CREATE DATABASE. No se puede cambiar después con ALTER DATABASE ... MODIFY SETTING (las tablas anidadas ya creadas conservan sus tipos de columna fijos, y ese cambio se rechaza); para cambiarlo, vuelva a crear la base de datos. No se aplica al motor de tabla MaterializedPostgreSQL, donde los tipos de columna se declaran explícitamente.

Los parámetros TLS/SSL se reenvían a libpq y pueden proporcionarse mediante una colección con nombre o como argumentos de clave-valor al final de los argumentos del motor: sslmode (disable, allow, prefer, require, verify-ca o verify-full; si no se establece, se aplica el valor predeterminado de libpq, prefer), así como los certificados y la clave en una de dos formas. sslrootcert (certificado de CA), sslcert (certificado de cliente) y sslkey (clave privada del cliente) son rutas a archivos locales del servidor y solo se aceptan desde una colección con nombre definida en el archivo de configuración del servidor. En cambio, sslrootcert_pem, sslcert_pem y sslkey_pem aceptan el contenido literal del archivo correspondiente, pueden especificarse desde SQL y se ocultan en los logs y las consultas SHOW como una contraseña.

Ejemplo de conexión a un servidor PostgreSQL que exige TLS y verifica el certificado del servidor:

CREATE DATABASE postgres_db
ENGINE = MaterializedPostgreSQL('postgres-host:5432', 'postgres_database', 'postgres_user', 'postgres_password',
                                sslmode = 'verify-full', sslrootcert_pem = '-----BEGIN CERTIFICATE-----
...
-----END CERTIFICATE-----');

Los parámetros de TLS/SSL forman parte de los parámetros de conexión de PostgreSQL y se establecen al crear la base de datos; vuelva a crearla para modificarlos.

Los slots de replicación lógica que existen en el primary no están disponibles en las réplicas standby. Por lo tanto, si se produce un failover, el nuevo primary (el antiguo standby físico) no conocerá ninguno de los slots que existían en el primary anterior. Esto hará que la replicación desde PostgreSQL se interrumpa. Una solución es administrar usted mismo los slots de replicación y definir un slot de replicación permanente (puede encontrar más información aquí). Tendrá que pasar el nombre del slot mediante la configuración materialized_postgresql_replication_slot, y este debe exportarse con la opción EXPORT SNAPSHOT. El identificador de la instantánea debe pasarse mediante la configuración materialized_postgresql_snapshot.

Tenga en cuenta que esto debe usarse solo si realmente es necesario. Si no hay una necesidad real o no se entiende completamente por qué hacerlo, es mejor permitir que el motor de tabla cree y administre su propio slot de replicación.

Ejemplo (de @bchrobot)

1. Configure el slot de replicación en PostgreSQL.

   apiVersion: "acid.zalan.do/v1"
   kind: postgresql
   metadata:
     name: acid-demo-cluster
   spec:
     numberOfInstances: 2
     postgresql:
       parameters:
         wal_level: logical
     patroni:
       slots:
         clickhouse_sync:
           type: logical
           database: demodb
           plugin: pgoutput

2. Espere a que el slot de replicación esté listo; luego, inicie una transacción y exporte el identificador de la instantánea de la transacción:

   BEGIN;
   SELECT pg_export_snapshot();

3. En ClickHouse, cree la base de datos:

   CREATE DATABASE demodb
   ENGINE = MaterializedPostgreSQL('postgres1:5432', 'postgres_database', 'postgres_user', 'postgres_password')
   SETTINGS
     materialized_postgresql_replication_slot = 'clickhouse_sync',
     materialized_postgresql_snapshot = '0000000A-0000023F-3',
     materialized_postgresql_tables_list = 'table1,table2,table3';

4. Finalice la transacción de PostgreSQL una vez que se confirme la replicación en la base de datos de ClickHouse. Verifique que la replicación continúe después del failover:

   kubectl exec acid-demo-cluster-0 -c postgres -- su postgres -c 'patronictl failover --candidate acid-demo-cluster-1 --force'

1. CREATE PUBLICATION – privilegio para crear consultas.

2. CREATE_REPLICATION_SLOT – privilegio de replicación.

3. pg_drop_replication_slot – privilegio de replicación o privilegios de superusuario.

4. DROP PUBLICATION – propietario de la publicación (username en el propio motor MaterializedPostgreSQL).

Es posible evitar ejecutar los comandos 2 y 3 y no necesitar esos permisos. Use la configuración materialized_postgresql_replication_slot y materialized_postgresql_snapshot. Pero hágalo con mucho cuidado.

Acceso a las tablas:

1. pg_publication

2. pg_replication_slots

3. pg_publication_tables

Se puede hacer una copia de seguridad de una base de datos MaterializedPostgreSQL. Los datos de cada tabla replicada se almacenan en una tabla ReplacingMergeTree anidada, por lo que BACKUP DATABASE captura esos datos delegando en la tabla anidada.

BACKUP DATABASE postgres_db TO Disk('backups', 'postgres_db.zip');

No se admite restaurar una database o table de MaterializedPostgreSQL en el mismo lugar. Un object restaurado de MaterializedPostgreSQL empieza a replicarse inmediatamente desde la source activa de PostgreSQL, por lo que restaurar la instantánea de la copia de seguridad sobre él mezclaría esa instantánea con el state remoto actual. Por tanto, RESTORE falla de forma segura en este caso. En su lugar, restaure los datos capturados en tables simples ReplacingMergeTree:

• En una copia de seguridad de database, la definición almacenada de cada table ya es el ReplacingMergeTree anidado sintético (no el motor MaterializedPostgreSQL), por lo que cada table puede restaurarse directamente en una table nueva que aún no exista:

  RESTORE TABLE postgres_db.table1 AS restored_db.table1
  FROM Disk('backups', 'postgres_db.zip')
  SETTINGS allow_different_table_def = 1;

• En el caso de una copia de seguridad independiente de una table MaterializedPostgreSQL, la definición almacenada es el propio motor MaterializedPostgreSQL. Cree previamente una table ReplacingMergeTree con la misma estructura que la nested table (incluidas las columns _sign y _version) y restáurela en ella:

  RESTORE TABLE src AS existing_replacing_mergetree
  FROM Disk('backups', 'table.zip')
  SETTINGS allow_different_table_def = 1;