What's the proper way to use synchronize_structure?

[jjb]

I'd like to use synchronize_structure to simplify my migration recipe. Using recipes in the docs and various blog posts, I can get things working without synchronize_structure and doing a manual dump and restore of the schema. But trying to use the same recipe with synchronize_structure and no dump/restore, the schema does not automagically replicate over as I had hoped.

Do I need to do something to "kick off" the structure replication?

[jjb]

Here: https://github.com/2ndQuadrant/pglogical#subscription-management

synchronize_structure - specifies if to synchronize structure from provider to the subscriber, default none. all for all objects, relations_only for replicated tables and sequences only (in this last case schemas themselves are not created on the subscriber).

What does "schemas themselves are not created on the subscriber" mean? What is created then?

[jjb]

When trying with 'all', I encountered this:

2020-04-03 03:54:36 UTC INFO:  synchronizing structure
2020-04-03 03:54:38 UTC NOTICE:  extension "plpgsql" already exists, skipping
2020-04-03 03:54:38 UTC ERROR:  schema "pglogical" does not exist
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 2; 3079 63170 EXTENSION pglogical (no owner)
pg_restore: error: could not execute query: ERROR:  schema "pglogical" does not exist
Command was: CREATE EXTENSION IF NOT EXISTS pglogical WITH SCHEMA pglogical;
2020-04-03 03:54:38 UTC ERROR:  could not execute command ""/usr/lib/postgresql/12/bin/pg_restore" --section="pre-data" --exit-on-error -1 -d "" "/tmp/pglogical-199.dump""

but if i try this manually at psql, it succeeds

CREATE EXTENSION IF NOT EXISTS pglogical WITH SCHEMA pglogical' 

I confirmed that all users are superusers

[petere]

Please try again with version 2.3.1. There were some fixes in this area. It's not clear whether it's related, but better start from a clean state.

[pratmeht]

Hi "@jjb", not sure if you got the answer. I am using pglogical 2.3.2. On Subscription node, I do not see 'extension getting created' however my tables in the replication set are on named schemas. So I experience that on Subscription node, SYNC STRUCTURE tries to create the named schema as well.

For my setup to work, on Subscription node, I have to drop schema before I create the Subscription. So, schema and all the tables gets created. Only issue is, that it creates all the tables present on the Provider node database (irrespective of what /what not is added in replication set). I am trying to understand whether this is normal or not ?