3

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.

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.