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).