logical replication for major PostgreSQL upgrades - sequences and schema aren't replicated
Summary
We have a documented procedure for using logical replication for PostgreSQL major upgrades where Patroni is involved.
It's marked as Experiment.
I saw the pgconf.eu 2023 presentation by one of our SREs (video, slides etc.) during which some relevant issues were highlighted, notably sequences.
Sequences are used in our schema to advance all the primary keys: almost every GitLab table has a sequence associated with it. Watch from 15:33. The initial seeding of the schema will provide the sequences, but they won't increment as projects, users, CI jobs, pipelines etc etc get created, causing big issues when the new database is put live.
Schema changes aren't replicated either either .. so its not going to cope with partitioning either; the mechanisms in GitLab to auto-create partitions need to be put on hold.
Steps to reproduce
Attempt to use logical replication for PostgreSQL upgrade, and generate work in GitLab while logical replication is in use.
Based on the presentation, when GitLab is switched to the new environment, it's going to run into issues were sequences cannot be advanced.
For example: creating new projects will fail because the next one already exists in the database, or it already exists in Gitaly.
What is the current bug behavior?
Documented procedure does not include warnings or steps related to the limitations of logical replication.
What is the expected correct behavior?
Documented procedure includes warnings or steps related to the limitations of logical replication.