HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

PostgreSQL logical replication using pglogical

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
postgresqlreplicationusinglogicalpglogical

Problem

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 LOG: starting apply for subscription subscription
793 DEBUG: StartTransaction
794 DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1 /0, nestlvl: 1, children:
795 DEBUG: CommitTransaction
796 DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 0/1 /0, nestlvl: 1, children:
797 ERROR: subscriber subscription initialization failed during nonrecoverable step (d) , please try the setup again
798 DEBUG: shmem_exit(1): 2 before_shmem_exit callbacks to make
799 LOG: apply worker [16870] at slot 1 ge

Solution

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.

Code Snippets

>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

Context

StackExchange Database Administrators Q#200091, answer score: 3

Revisions (0)

No revisions yet.