title_wip

Sets a comma-separated list of PostgreSQL database tables, which will be replicated via [MaterializedPostgreSQL](/docs/engines/database-engines/materialized-postgresql) database engine.

Each table can have subset of replicated columns in brackets. If subset of columns is omitted, then all columns for table will be replicated.

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

Default value: empty list — means whole PostgreSQL database will be replicated.

### `materialized_postgresql_schema`[​](#materialized-postgresql-schema "Direct link to materialized-postgresql-schema")

Default value: empty string. (Default schema is used)

### `materialized_postgresql_schema_list`[​](#materialized-postgresql-schema-list "Direct link to materialized-postgresql-schema-list")

Default value: empty list. (Default schema is used)

### `materialized_postgresql_max_block_size`[​](#materialized-postgresql-max-block-size "Direct link to materialized-postgresql-max-block-size")

Sets the number of rows collected in memory before flushing data into PostgreSQL database table.

Possible values:

- Positive integer.

Default value: `65536`.

### `materialized_postgresql_replication_slot`[​](#materialized-postgresql-replication-slot "Direct link to materialized-postgresql-replication-slot")

A user-created replication slot. Must be used together with `materialized_postgresql_snapshot`.

### `materialized_postgresql_snapshot`[​](#materialized-postgresql-snapshot "Direct link to materialized-postgresql-snapshot")

A text string identifying a snapshot, from which [initial dump of PostgreSQL tables](/docs/engines/database-engines/materialized-postgresql) will be performed. Must be used together with `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;
```

The settings can be changed, if necessary, using a DDL query. But it is impossible to change the setting `materialized_postgresql_tables_list`. To update the list of tables in this setting use the `ATTACH TABLE` query.

```
ALTER DATABASE postgres_database MODIFY SETTING materialized_postgresql_max_block_size = <new_size>;
```

### `materialized_postgresql_use_unique_replication_consumer_identifier`[​](#materialized_postgresql_use_unique_replication_consumer_identifier "Direct link to materialized_postgresql_use_unique_replication_consumer_identifier")

Use a unique replication consumer identifier for replication. Default: `0`.
If set to `1`, allows to setup several `MaterializedPostgreSQL` tables pointing to the same `PostgreSQL` table.

## Notes[​](#notes "Direct link to Notes")

### Failover of the logical replication slot[​](#logical-replication-slot-failover "Direct link to Failover of the logical replication slot")

Logical Replication Slots which exist on the primary are not available on standby replicas.
So if there is a failover, new primary (the old physical standby) won't be aware of any slots which were existing with old primary. This will lead to a broken replication from PostgreSQL.
A solution to this is to manage replication slots yourself and define a permanent replication slot (some information can be found [here](https://patroni.readthedocs.io/en/latest/SETTINGS.html)). You'll need to pass slot name via `materialized_postgresql_replication_slot` setting, and it has to be exported with `EXPORT SNAPSHOT` option. The snapshot identifier needs to be passed via `materialized_postgresql_snapshot` setting.

Please note that this should be used only if it is actually needed. If there is no real need for that or full understanding why, then it is better to allow the table engine to create and manage its own replication slot.

**Example (from [@bchrobot](https://github.com/bchrobot))**

1. Configure replication slot in 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. Wait for replication slot to be ready, then begin a transaction and export the transaction snapshot identifier:

   ```
   BEGIN;
   SELECT pg_export_snapshot();
   ```
3. In ClickHouse create database:

   ```
   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. End the PostgreSQL transaction once replication to ClickHouse DB is confirmed. Verify that replication continues after failover:

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

### Required permissions[​](#required-permissions "Direct link to Required permissions")

1. [CREATE PUBLICATION](https://postgrespro.ru/docs/postgresql/14/sql-createpublication) -- create query privilege.
2. [CREATE\_REPLICATION\_SLOT](https://postgrespro.ru/docs/postgrespro/10/protocol-replication#PROTOCOL-REPLICATION-CREATE-SLOT) -- replication privilege.
3. [pg\_drop\_replication\_slot](https://postgrespro.ru/docs/postgrespro/9.5/functions-admin#functions-replication) -- replication privilege or superuser.
4. [DROP PUBLICATION](https://postgrespro.ru/docs/postgresql/10/sql-droppublication) -- owner of publication (`username` in MaterializedPostgreSQL engine itself).

It is possible to avoid executing `2` and `3` commands and having those permissions. Use settings `materialized_postgresql_replication_slot` and `materialized_postgresql_snapshot`. But with much care.

Access to tables:

1. pg\_publication
2. pg\_replication\_slots
3. pg\_publication\_tables

- [Creating a database](#creating-a-database)- [Example of use](#example-of-use)- [Dynamically adding new tables to replication](#dynamically-adding-table-to-replication)- [Dynamically removing tables from replication](#dynamically-removing-table-from-replication)- [PostgreSQL schema](#schema)- [Requirements](#requirements)- [Settings](#settings)
              - [`materialized_postgresql_tables_list`](#materialized-postgresql-tables-list)- [`materialized_postgresql_schema`](#materialized-postgresql-schema)- [`materialized_postgresql_schema_list`](#materialized-postgresql-schema-list)- [`materialized_postgresql_max_block_size`](#materialized-postgresql-max-block-size)- [`materialized_postgresql_replication_slot`](#materialized-postgresql-replication-slot)- [`materialized_postgresql_snapshot`](#materialized-postgresql-snapshot)- [`materialized_postgresql_use_unique_replication_consumer_identifier`](#materialized_postgresql_use_unique_replication_consumer_identifier)- [Notes](#notes)
                - [Failover of the logical replication slot](#logical-replication-slot-failover)- [Required permissions](#required-permissions)

Was this page helpful?