patternsqlMinor
PostgreSQL logical replication using pglogical
Viewed 0 times
postgresqlreplicationusinglogicalpglogical
Problem
I am testing logical replication using Pglogical with Postgresql v9.5.
Simple configuration seems OK.
It means:
But, subscription to a new replication_set containing tables from a specific schema does not work.
This command passed (in the provider node):
This command passed (in the subscriber node):
But tables in the subscriber node are empty.
I missed something?
Thanks and regards.
Additional information:
-
-
Tables in the subscriber node are in a specific schema too,
-
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
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:
Now replication working.
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 existNow 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 existContext
StackExchange Database Administrators Q#200091, answer score: 3
Revisions (0)
No revisions yet.