0

We are in the process of trying to upgrade our DB from Postgres 9.5 to 14 and decided to use the postgres utility pg_upgrade --link. This was left to run for over a day and we had to abort the process as the downtime was too drastic. The cause was determined to be the number of BLOBs contained in the database as even with the --link option it had to dump and restore these objects. I've been looking into replication tools but many seem to either not work with BLOBs (ex. Slony), or can't operate between dbs of different versions.

Are there any Postgres utilities or 3rd party tools that could do this operation faster, or without bringing Postgres down?

1 Answer 1

0

It is a known fact that pg_upgrade with lots of large objects fails, see these messages and the ensuing discussion. It consumes memory and locks and transaction IDs. Unfortunately, the patch that intended to improve that never got committed.

Large objects are a legacy feature with lots of problems. I always advise people not to use them. But that won't help you.

Perhaps you can find a solution to convert the large objects into bytea columns before you upgrade. That might be the least painful way to go.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.