Using synchronize_structure errors while replicating create schema pglogical_origin on 9.4

[juxtin]

note: This appears to relate directly to #35, but I figured I'd report it separately since it doesn't involve upgrades.

I have an existing vagrant environment that sets up two Ubuntu 16.04 boxes running Postgresql 9.4 and sets up pglogical replication from one to the other. As of pglogical 1.2.0, I can no longer start replication on the replica node. The logs suggest that the create schema pglogical_origin command is being replicated, which fails as it is already installed on the replica.

Some other pertinent details:

• synchronize_structure is set to TRUE
• the identical steps worked with pglogical 1.1.0 (same scripts)

I appreciate any help you can offer. Thanks!

Relevant logs

2016-10-05 18:05:04 UTC [4683-30] LOG:  registering background worker "pglogical apply 16548:1763399739"
2016-10-05 18:05:04 UTC [4683-31] LOG:  starting background worker process "pglogical apply 16548:1763399739"
2016-10-05 18:05:04 UTC [5057-1] LOG:  starting apply for subscription subscription1
2016-10-05 18:05:05 UTC [5066-1] rep@test_replication ERROR:  schema "pglogical_origin" already exists
2016-10-05 18:05:05 UTC [5066-2] rep@test_replication STATEMENT:  CREATE SCHEMA pglogical_origin;



pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 7; 2615 16551 SCHEMA pglogical_origin postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  schema "pglogical_origin" already exists
    Command was: CREATE SCHEMA pglogical_origin;



2016-10-05 18:05:05 UTC [5057-2] ERROR:  could not execute command "/usr/lib/postgresql/9.4/bin/pg_restore --section="pre-data" --exit-on-error -1 -d "host=192.168.33.10 port=5432 dbname=test_replication user=rep" "/tmp/pglogical-5057.dump""
2016-10-05 18:05:05 UTC [5057-3] LOG:  apply worker [5057] at slot 1 generation 1 crashed
2016-10-05 18:05:05 UTC [4683-32] LOG:  worker process: pglogical apply 16548:1763399739 (PID 5057) exited with exit code 1

[ringerc]

I can reproduce this here.

ERROR:  schema "pglogical_origin" already exists
STATEMENT:  CREATE SCHEMA pglogical_origin;



pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 6; 2615 19683 SCHEMA pglogical_origin craig
pg_restore: [archiver (db)] could not execute query: ERROR:  schema "pglogical_origin" already exists
    Command was: CREATE SCHEMA pglogical_origin;



ERROR:  could not execute command "/home/craig/pg/94/bin/pg_restore --section="pre-data" --exit-on-error -1 -d "dbname=postgres port=5433 user=super" "/tmp/pglogical-30741.dump""

I'm looking into what's going on, and why. Thanks for the report.

[ringerc]

It looks like 5edcc89 broke this.

[ringerc]

We don't exclude the pglogical_origin schema created by CREATE EXTENSION pglogical_origin; on the upstream. So it gets dumped and gets restored on the downstream, where it's already been created. Splat.

This fix appears to have been intended to improve 9.4 <=> 9.5, but it breaks 9.4 <=> 9.4 .

It's another consequence of relying on pg_dump and pg_restore and with coping with cross-version differences.

In retrospect it'd be better to have kept the pglogical_origin extension as part of pglogical proper, just ignored and empty on !9.4. But we're stuck with it now, since merging the extension's contents into pglogical won't work when pglogical_origin is already created on a 9.4 node. We can't just remove it from pglogical_origin and add it to pglogical because of how the extension tracking machinery works.

I'm going to ask @PJMODOS what the original commit was intended to fix then go from there.

[PJMODOS]

Should be fixed by e7472fc, I tagged 1.2.1 release with that fix (no packages yet).