Is it possible to replicate just one schema in postgresql?

Question

NOTE: a similar Q. was asked but was not answered here: How do I replicate a single schema in postgresql?

I am trying to replicate just one schema in a postgresql (13) database using pglogical to another server.

In my test setup on the provider node I have 2 schemas "shared" and "not_shared".

On the subscriber node I have one existing schema called "not_shared".

I want to replicate "shared" from the provider to the subscriber, I have already set up the nodes on both servers.

On the provider I create a replication set for just the "shared" schema (in theory) called "shared_repl":

select pglogical.create_replication_set('shared_repl', true, true, true, true );
SELECT pglogical.replication_set_add_all_tables('shared_repl', '{shared}'::text[]);

On the subscriber I create a subscription to the "shared_repl" replication set:

SELECT pglogical.create_subscription(
    subscription_name := 'shared',
    provider_dsn := 'host=providerhost port=5432 dbname=db user=myuser password=mypassword',
    replication_sets := '{shared_repl}'::text[], synchronize_structure := TRUE
);

Looking at the status this subscription shows as "down":

select * from pglogical.show_subscription_status();

Looking in the logs I see something like this:

pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 5; 2615 16863 SCHEMA not_shared myuser
pg_restore: error: could not execute query: ERROR:  schema "not_shared" already exists
Command was: CREATE SCHEMA not_shared;

So why is it trying to replicate the "not_shared" schema when I have specifically only selected the "shared" schema?

Am I doing something wrong here or is pglogical not capable of replicating just one schema?

If pglogical is not going to allow this is there some other option to replicate, including structure, one schema in postgresql?

Thanks.

Answer 1

synchronize_structure just doesn't do what you want. You can copy the structure of just one schema from provider to subscriber yourself, using pg_dump -n shared -s for example, then don't specify synchronize_structure := TRUE when you create the subscription.