pglogical.replicate_ddl_command making the subscription to down.

[gowthamakanthan]

Hello Team,

We have enabled the pglogical replication between the on-prem(Postgresql 13.6, pglogical 2.4) and RDS aurora (Postgresql 12.4, pglogical 2.3.2) which is working fine. But when we execute the DDL locally on the source, to send the specified command to the replication queue for execution on subscribers, which moves the subscription to a down state. please check below for more details.

on the Source server: (Covered only the major steps)- Onprem server

• Created the node interface
• created the replication set (tb_rtp_qa_replication_set)
• Added all the tables of public schema into it.

Target: (Covered only the major steps) - RDS

• Taking schema level pg_dump and restoring the same in the target.
• Created the node interface
• Created the subscription using the source replication_set(tb_rtp_qa_replication_set) and source dsn.

By following the above steps, the replication is enabled successfully and the tables are getting replicated.

On the Source:(On prem server)

nubi=#  SELECT * FROM pglogical.replication_set;
   set_id   | set_nodeid |         set_name          | replicate_insert | replicate_update | replicate_delete | replicate_truncate
------------+------------+---------------------------+------------------+------------------+------------------+--------------------
  732257976 |  666469698 | default                   | t                | t                | t                | t
 4245626882 |  666469698 | default_insert_only       | t                | f                | f                | t
 1209637731 |  666469698 | ddl_sql                   | t                | f                | f                | f
 1976788511 |  666469698 | tb_rtp_qa_replication_set | t                | t                | t                | t
(4 rows)

nubi=# SELECT pglogical.replication_set_add_all_tables('tb_rtp_qa_replication_set', ARRAY['public'],true);
 replication_set_add_all_tables
--------------------------------
 t
(1 row)

nubi=# select * from pglogical.node_interface;
   if_id    |         if_name         | if_nodeid |                                                              if_dsn
------------+-------------------------+-----------+----------------------------------------------------------------------------------------------------------------------------------
 1532082545 | tb_rtp_qa_provider_node | 666469698 | host=<source_ip> port=5432 dbname=nubi user=replicator password=<passwd>
(1 row)

On the target: (RDS)

nubi_rtp_qa=> SELECT pglogical.create_subscription(subscription_name := 'aws_tb_rtp_qa_replica_sub', replication_sets := array['tb_rtp_qa_replication_set'], provider_dsn := 'host=<source ip> port=5432 dbname=nubi password=<password> user=replicator');
 create_subscription
---------------------
          3197542095
(1 row)

nubi_rtp_qa=>  select subscription_name, status FROM pglogical.show_subscription_status();
     subscription_name     |   status
---------------------------+-------------
 aws_tb_rtp_qa_replica_sub | replicating
(1 row)

The problem occurs when we try to create a new view on the source public schema using ddl command, which makes the subscription down on the target with the following messages. Any suggestions?

For example:On Source:

nubi=# select pglogical.replicate_ddl_command('create view public.testuid as select pkuserid from public.duser where pkuserid = ''testuser'';','{default}'::text[]);
 replicate_ddl_command
-----------------------
 t
(1 row)

nubi=#
nubi=# select * from public.testuid;
 pkuserid
----------
 testuser
(1 row)

nubi=#

On the target(RDS)

nubi_rtp_qa=>  select subscription_name, status FROM pglogical.show_subscription_status();
     subscription_name     | status
---------------------------+--------
 aws_tb_rtp_qa_replica_sub | down
(1 row)

nubi_rtp_qa=> select pglogical.alter_subscription_disable(subscription_name := 'aws_tb_rtp_qa_replica_sub', immediate := 'true');
 alter_subscription_disable
----------------------------
 t
(1 row)

nubi_rtp_qa=>  select subscription_name, status FROM pglogical.show_subscription_status();
     subscription_name     |  status
---------------------------+----------
 aws_tb_rtp_qa_replica_sub | disabled
(1 row)

nubi_rtp_qa=> select pglogical.alter_subscription_enable(subscription_name := 'aws_tb_rtp_qa_replica_sub', immediate := 'true');
 alter_subscription_enable
---------------------------
 t
(1 row)

nubi_rtp_qa=>  select subscription_name, status FROM pglogical.show_subscription_status();
     subscription_name     | status
---------------------------+--------
 aws_tb_rtp_qa_replica_sub | down
(1 row)

nubi_rtp_qa=>

Errors (Source server):

2022-03-29 06:20:23.766 UTC [18473] LOG:  unexpected EOF on standby connection
2022-03-29 06:20:23.766 UTC [18473] STATEMENT:  START_REPLICATION SLOT "pgl_nubi_rtp_qa_tb_rtp_q27b9854_aws_tb_rbe969ec" LOGICAL 0/A77660C8 (expected_encoding 'UTF8', min_proto_version '1', max_proto_version '1', startup_params_format '1', "binary.want_internal_basetypes" '1', "binary.want_binary_basetypes" '1', "binary.basetypes_major_version" '1200', "binary.sizeof_datum" '8', "binary.sizeof_int" '4', "binary.sizeof_long" '8', "binary.bigendian" '0', "binary.float4_byval" '1', "binary.float8_byval" '1', "binary.integer_datetimes" '0', "hooks.setup_function" 'pglogical.pglogical_hooks_setup', "pglogical.forward_origins" '"all"', "pglogical.replication_set_names" '"default"', "relmeta_cache_size" '-1', pg_version '120004', pglogical_version '2.3.2', pglogical_version_num '20302', pglogical_apply_pid '31299')
2022-03-29 06:20:28.807 UTC [19560] LOG:  connection received: host=100.96.199.21 port=39254
2022-03-29 06:20:28.817 UTC [19560] LOG:  replication connection authorized: user=replicator application_name=aws_tb_rtp_qa_replica_sub
2022-03-29 06:20:28.839 UTC [19560] LOG:  starting logical decoding for slot "pgl_nubi_rtp_qa_tb_rtp_q27b9854_aws_tb_rbe969ec"
2022-03-29 06:20:28.839 UTC [19560] DETAIL:  Streaming transactions committing after 0/A78DF7C0, reading WAL from 0/A78DF788.
2022-03-29 06:20:28.839 UTC [19560] STATEMENT:  START_REPLICATION SLOT "pgl_nubi_rtp_qa_tb_rtp_q27b9854_aws_tb_rbe969ec" LOGICAL 0/A779F4D8 (expected_encoding 'UTF8', min_proto_version '1', max_proto_version '1', startup_params_format '1', "binary.want_internal_basetypes" '1', "binary.want_binary_basetypes" '1', "binary.basetypes_major_version" '1200', "binary.sizeof_datum" '8', "binary.sizeof_int" '4', "binary.sizeof_long" '8', "binary.bigendian" '0', "binary.float4_byval" '1', "binary.float8_byval" '1', "binary.integer_datetimes" '0', "hooks.setup_function" 'pglogical.pglogical_hooks_setup', "pglogical.forward_origins" '"all"', "pglogical.replication_set_names" '"default"', "relmeta_cache_size" '-1', pg_version '120004', pglogical_version '2.3.2', pglogical_version_num '20302', pglogical_apply_pid '10146')
2022-03-29 06:20:28.839 UTC [19560] LOG:  logical decoding found consistent point at 0/A78DF788
2022-03-29 06:20:28.839 UTC [19560] DETAIL:  There are no running transactions.
2022-03-29 06:20:28.839 UTC [19560] STATEMENT:  START_REPLICATION SLOT "pgl_nubi_rtp_qa_tb_rtp_q27b9854_aws_tb_rbe969ec" LOGICAL 0/A779F4D8 (expected_encoding 'UTF8', min_proto_version '1', max_proto_version '1', startup_params_format '1', "binary.want_internal_basetypes" '1', "binary.want_binary_basetypes" '1', "binary.basetypes_major_version" '1200', "binary.sizeof_datum" '8', "binary.sizeof_int" '4', "binary.sizeof_long" '8', "binary.bigendian" '0', "binary.float4_byval" '1', "binary.float8_byval" '1', "binary.integer_datetimes" '0', "hooks.setup_function" 'pglogical.pglogical_hooks_setup', "pglogical.forward_origins" '"all"', "pglogical.replication_set_names" '"default"', "relmeta_cache_size" '-1', pg_version '120004', pglogical_version '2.3.2', pglogical_version_num '20302', pglogical_apply_pid '10146')
2022-03-29 06:20:28.851 UTC [19560] LOG:  could not receive data from client: Connection reset by peer