Upgrading Aurora PostgreSQL cluster from 12.12 to 14.6. Why do we need to drop and recreate all replication slots? Post upgrade, How to ensure replication slots resume incremental changes only?

Hello,

As correctly mentioned by you, an upgrade can't occur if the instance has any logical replication slots. This is unfortunately a limitation from the engine, including the community version. "Before upgrading, make sure you know the purpose of any logical replication slots that are in use, and confirm that it's okay to delete them. If the logical replication slots are still being used, you shouldn't delete them, and you can't proceed with the upgrade" [1].

If the logical replication slots aren't needed, you can delete them using the following SQL: SELECT * FROM pg_replication_slots; SELECT pg_drop_replication_slot(slot_name);"

I also confirmed to you that we currently have an open feature request to our developer team to make replication slots to be maintained during a major version upgrade.

While I cannot confirm this feature request will be approved or if there is an estimated time for the feature to become available, I've echoed your voice to the internal team.

AWS is continuously improving its services and you can keep track of AWS latest services and products. If the request is approved, the changes will be posted on https://aws.amazon.com/blogs/aws/ and http://aws.amazon.com/new .

============================

Now to answer your second query regarding ensuring that replication restarts from where it left of (meaning, replication resumes incremental changes only) and not forces you to do FULL sync. Please note that if replication slot is inactive, it means there is no replication going on at that moment. However, it will not require FULL SYNC. In order to make sure that data is not lost, please make sure that before upgrading all changes are replicated, then perform the upgrade and right after the upgrade, re-establish the replication. For more information regarding Re-establishing logical replication after a major upgrade, you can refer AWS document [2].

References: [1] RDS User Guide - How to perform a major version upgrade - https://aws.amazon.com/premiumsupport/knowledge-center/rds-postgresql-version-upgrade-issues/ [2]Working with extensions and foreign data wrappers - Reestablishing logical replication after a major upgrade https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Appendix.PostgreSQL.CommonDBATasks.html#Appendix.PostgreSQL.CommonDBATasks.pglogical.recover-replication-after-upgrade