Cannot enable subscription since pg_upgrade to v13 and pglogical 2.3.3 : ERROR: replication origin does not exist

[rechtem]

I have a PG12 cluster which was replicating with a PG10 primary.

I disabled the subscription, then pg_upgraded from PG12 to PG13, installed pglogical 2.3.3, and tried to enable the subscription again.

SELECT pglogical.alter_subscription_enable('bdu_cp_clb2x_cl');                                
 alter_subscription_enable
---------------------------
 t

In the PG13 log:

2020-10-06 13:37:03 GMT [658012:2] user=[unknown],db=bdcp,app=pglogical apply 17376:1283755237,client= ERROR:  replication 
origin "pgl_bdcp_clib2x_clm_node_bdu_cp_clb2x_cl" does not exist

The subscription status has gone to down:

SELECT * FROM pglogical.show_subscription_status();
 subscription_name | status |  provider_node  |                       provider_dsn                        |                
slot_name                 | replication_sets | forward_origins 
-------------------+--------+-----------------+-----------------------------------------------------------+----------------
--------------------------+------------------+-----------------
 bdu_cp_clb2x_cl   | down   | clib2x_clm_node | host=172.24.130.26 port=5432 dbname=bdclim user=pglogical | pgl_bdcp_clib2x
_clm_node_bdu_cp_clb2x_cl | {bdu_bdcp_rs}    | {all}

There is absolutely no trace of connection tentative on the PG10 primary log. The slot is still there:

select * from pg_replication_slots ;
                 slot_name                 |      plugin      | slot_type | datoid | database | temporary | active | active
_pid | xmin | catalog_xmin |  restart_lsn  | confirmed_flush_lsn 
-------------------------------------------+------------------+-----------+--------+----------+-----------+--------+-------
-----+------+--------------+---------------+---------------------
 pgl_bdcp_clib2x_clm_node_bdu_cp_clb2x_cl  | pglogical_output | logical   |  16384 | bdclim   | f         | f      |       
     |      |   3039290167 | 1DB4/BF4858A8 | 1DB4/BF485C80

What could be the cause ?

Thanks

[martinmarques]

Hi,

I have a PG12 cluster which was replicating with a PG10 primary.

I disabled the subscription, then pg_upgraded from PG12 to PG13, installed pglogical 2.3.3, and tried to enable the subscription again.

SELECT pglogical.alter_subscription_enable('bdu_cp_clb2x_cl'); alter_subscription_enable

t

In the PG13 log:

2020-10-06 13:37:03 GMT [658012:2] user=[unknown],db=bdcp,app=pglogical apply 17376:1283755237,client= ERROR: replication origin "pgl_bdcp_clib2x_clm_node_bdu_cp_clb2x_cl" does not exist

Did you recreate the replication origin after running pg_upgrade? Those are not passed over from the old cluster to the new one when upgrading with pg_upgrade.

[rechtem]

Hello Martin, Yes creating the missing replication origin allowed to start replication. Now, is there a risk to have missed transactions replication while the system was down ? Thanks a lot.

[dejavu1219]

Same issue after upgrading PG to v13 via pg_upgrade, @rechtem @martinmarques have you found any workaround to resolve the issue?

[rechtem]

As mentioned @martinmarques, one has to create the replication origin on the replica side. See https://www.postgresql.org/docs/current/replication-origins.html

[dejavu1219]

Thanks @rechtem , it works in local test env. And have you encountered missing transactions issue as you mentioned in your comment?

[srl295]

@rechtem how did the replication origin get created in the first place? Is there a way to reset the status so that the origins get recreated automatically?
When I try to resynchronize one table, I get an error that replication origin pgl_postgres_provider1_subscription1_<random hex number> does not exist. Do I create those also? How do I know what those origins are?

[kuzaxak]

For anyone who will search this. After an upgrade call SELECT slot_name FROM pglogical.show_subscription_status(); in each DB where you had replication. And then create origin for every slot via SELECT pg_replication_origin_create(‘<slot_name>’);

In our case <slot_name> was pgl_databse_target_f3ab9f4_receiver_mc9d8484