There are generally two ways to do a PostreSQL database upgrade:
- Use pg_dump/restore, but the LSNs will change using this approach.
- Use pg_upgrade. This is full of all kinds of black-box behavior. In short, it does a dump/restore for the schema only; then, it moves the data back in under the new schema.
In either case, the safest approach here is generally to consider the following steps with Debezium:
- Guarantee that all changes have been read from the replication slot, putting the database in effective read-only mode.
- Stop Debezium and the database.
- Perform the upgrade using an approved PG procedure.
- Remove the connector offsets.
- Restart the connector, using snapshot.mode=never.
- Open the database for writes by users.
Since replication slots are not retained as part of the upgrade procedure, it's generally best that offsets are removed and a new schema snapshot is taken but without data, assuming that the user can guarantee that no data changed throughout the upgrade process and that no changes were lost before the upgrade.
If that cannot be guaranteed, then it'll be essential to note that a new snapshot should be preferred using snapshot.mode=initial to ensure that no data loss occurs.