Saltar al contenido principal
Se recomienda a los usuarios de ClickHouse Cloud que utilicen ClickPipes para la replicación de PostgreSQL a ClickHouse. Esto ofrece compatibilidad nativa con Change Data Capture (CDC) de alto rendimiento para PostgreSQL.
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 engine 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).
Este motor de base de datos es experimental. Para usarlo, establezca allow_experimental_database_materialized_postgresql en 1 en sus archivos de configuración o mediante el comando SET:
SET allow_experimental_database_materialized_postgresql=1

​
Crear una base de datos

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.

​
Ejemplo de uso

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

SHOW TABLES FROM postgres_db;

┌─name───┐
│ table1 │
└────────┘

SELECT * FROM postgresql_db.postgres_table;

​
Añadir nuevas tablas a la replicación de forma dinámica

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;
Antes de la versión 22.1, al agregar una tabla a la replicación quedaba un slot de replicación temporal sin eliminar (llamado {db_name}_ch_replication_slot_tmp). Si adjunta tablas en una versión de ClickHouse anterior a la 22.1, asegúrese de eliminarlo manualmente (SELECT pg_drop_replication_slot('{db_name}_ch_replication_slot_tmp')). De lo contrario, el uso de disco aumentará. Este problema se corrigió en la versión 22.1.

​
Eliminar tablas dinámicamente de la replicación

Es posible eliminar tablas específicas de la replicación: 
DETACH TABLE postgres_database.table_to_remove PERMANENTLY;

​
Esquema de PostgreSQL

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;
  1. 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.
  1. 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.

​
Requisitos

  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;
La replicación de valores TOAST no es compatible. Se utilizará el valor predeterminado del tipo de dato.

​
Configuración

​
materialized_postgresql_tables_list

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.

​
materialized_postgresql_schema

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

​
materialized_postgresql_schema_list

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

​
materialized_postgresql_max_block_size

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.

​
materialized_postgresql_replication_slot

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

​
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>;

​
materialized_postgresql_use_unique_replication_consumer_identifier

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.

​
Notas

​
Failover del slot de replicación lógica

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'

​
Permisos requeridos

  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
Última modificación el 10 de junio de 2026