BUG #15044: materialized views incompatibility with logicalreplication in postgres 10
От | PG Bug reporting form |
---|---|
Тема | BUG #15044: materialized views incompatibility with logicalreplication in postgres 10 |
Дата | |
Msg-id | 151753299917.1235.5710750500940066850@wrigleys.postgresql.org обсуждение исходный текст |
Ответы |
BUG #15044: materialized views incompatibility with logicalreplication in postgres 10
|
Список | pgsql-bugs |
The following bug has been logged on the website: Bug reference: 15044 Logged by: Chad T Email address: chad@iris.washington.edu PostgreSQL version: 10.1 Operating system: CentOS 7.4.1708 and macOS 10.13.3 Description: The built-in logical replication in postgres 10 is documented as not being able to replication materialized views, notably here: https://www.postgresql.org/docs/10/static/logical-replication-restrictions.html Unfortunately, there appears to be an incompatibility with logical replication and materialized views. Here is a procedure to illustrate the problem: # Create data directories for publisher and subscriber and initialize mkdir pub sub initdb pub initdb sub echo "wal_level = logical" >> pub/postgresql.conf echo "wal_level = logical" >> sub/postgresql.conf # Start servers: pg_ctl -D pub -l pub.log -o "-p 5433" start pg_ctl -D sub -l sub.log -o "-p 5434" start # Create tables on both publisher and subscriber psql -p 5433 -d postgres -c "CREATE TABLE testtable (id int,value text);" psql -p 5434 -d postgres -c "CREATE TABLE testtable (id int,value text);" # Create publication and subscription psql -p 5433 -d postgres -c "CREATE PUBLICATION pub FOR ALL TABLES;" psql -p 5434 -d postgres -c "CREATE SUBSCRIPTION sub CONNECTION 'host=localhost port=5433 dbname=postgres' PUBLICATION pub;" # Insert a row into the test table and verify that replication is in a streaming state psql -p 5433 -d postgres -c "INSERT INTO testtable (id,value) VALUES (1,'string');" psql -p 5433 -d postgres -c "SELECT state,sent_lsn,write_lsn,flush_lsn,replay_lsn FROM pg_stat_replication;" # Create materialized view on publisher psql -p 5433 -d postgres -c "CREATE MATERIALIZED VIEW mvid AS SELECT id FROM testtable;" With that CREATE MATERIALIZED VIEW statement the replication broken, with these errors in the subscriber's log: 2018-02-01 16:34:27.639 PST [68409] ERROR: logical replication target relation "public.mvid" does not exist 2018-02-01 16:34:27.642 PST [68391] LOG: worker process: logical replication worker for subscription 16390 (PID 68409) exited with exit code 1 I have tried variations to work around this, all in vain, that include 1) creating a regular table on the subscriber (allows replication to stream, but the table is not populated) and 2) creating the MATERIALIZED VIEW before creating the publication/subscription link (then it breaks on a REFRESH).
В списке pgsql-bugs по дате отправления: