PostgreSQL logical replication using pglogical

Question

I am testing logical replication using Pglogical with Postgresql v9.5.

Simple configuration seems OK. It means:

• create pglogical extension in the two nodes
• create a set and add tables from public schema into this set,
• create a node and create a subscription in the other server.

But, subscription to a new replication_set containing tables from a specific schema does not work.

This command passed (in the provider node):

select pglogical.replication_set_add_all_tables(
set_name := 'new_replication_set',
schema_names := '{myschema}',
synchronize_data := 'true'
);

This command passed (in the subscriber node):

select pglogical.create_subscription(
subscription_name := 'subscription',
replication_sets := array['new_replication_set'],
provider_dsn := 'host=10.20.30.40 port=6432 dbname=production user=pglogical_prod',
synchronize_data := 'true'
);

But tables in the subscriber node are empty.

I missed something? Thanks and regards.

Additional information:

• select pglogical.alter_subscription_synchronize(...) and select pglogical.alter_subscription_resynchronize_table(...) commands were sent,

• Tables in the subscriber node are in a specific schema too,

• select * from pglogical.queue; request is not empty.

Hereunder the log messages taken from the subscriber node :

I set log_min_message = debug5 in the subscriber node.

And hereunder the error message :

792 < 2018年03月14日 11:38:56.449 CET >LOG: starting apply for subscription subscription
793 < 2018年03月14日 11:38:56.449 CET >DEBUG: StartTransaction
794 < 2018年03月14日 11:38:56.449 CET >DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1 /0, nestlvl: 1, children:
795 < 2018年03月14日 11:38:56.449 CET >DEBUG: CommitTransaction
796 < 2018年03月14日 11:38:56.449 CET >DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 0/1 /0, nestlvl: 1, children:
797 < 2018年03月14日 11:38:56.449 CET >ERROR: subscriber subscription initialization failed during nonrecoverable step (d) , please try the setup again
798 < 2018年03月14日 11:38:56.449 CET >DEBUG: shmem_exit(1): 2 before_shmem_exit callbacks to make
799 < 2018年03月14日 11:38:56.449 CET >LOG: apply worker [16870] at slot 1 generation 4 exiting with error
800 < 2018年03月14日 11:38:56.449 CET >DEBUG: shmem_exit(1): 6 on_shmem_exit callbacks to make
801 < 2018年03月14日 11:38:56.449 CET >DEBUG: proc_exit(1): 2 callbacks to make
802 < 2018年03月14日 11:38:56.449 CET >DEBUG: exit(1)
803 < 2018年03月14日 11:38:56.449 CET >DEBUG: shmem_exit(-1): 0 before_shmem_exit callbacks to make
804 < 2018年03月14日 11:38:56.449 CET >DEBUG: shmem_exit(-1): 0 on_shmem_exit callbacks to make
805 < 2018年03月14日 11:38:56.449 CET >DEBUG: proc_exit(-1): 0 callbacks to make
806 < 2018年03月14日 11:38:56.450 CET >DEBUG: reaping dead processes
807 < 2018年03月14日 11:38:56.450 CET >LOG: worker process: pglogical apply 16385:2875150205 (PID 16870) exited with exit code 1

Thanks and Regards.

Answer 1

Tables and schema names in the subscriber node have to be the same as the provider node. Hereunder the error messages if the names are differents between the two nodes:

>LOG: starting apply for subscription subscription
>INFO: initializing subscriber subscription
>INFO: synchronizing data
>ERROR: schema "myschema" does not exist
>LOG: starting apply for subscription subscription
>INFO: initializing subscriber subscription
>INFO: synchronizing data
>ERROR: relation "myschema.tblprod2" does not exist

Now replication working.