pglogical
pglogical copied to clipboard
Cannot enable subscription since pg_upgrade to v13 and pglogical 2.3.3 : ERROR: replication origin does not exist
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
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.
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.
Same issue after upgrading PG to v13 via pg_upgrade, @rechtem @martinmarques have you found any workaround to resolve the issue?
As mentioned @martinmarques, one has to create the replication origin on the replica side. See https://www.postgresql.org/docs/current/replication-origins.html
Thanks @rechtem , it works in local test env. And have you encountered missing transactions issue as you mentioned in your comment?
@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?
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