What is the best way to migrate postgres-9 database with size of hundreds GB to postgres-11 ? I'm afraid that third party tools will take too long to do this.
1
- You should consider upgrading to PostgreSQL version 16. Version 11 is already out of support. The least supported is 12, but its support will end soon too.Nikita Kipriyanov– Nikita Kipriyanov2024-01-19 18:26:40 +00:00Commented Jan 19, 2024 at 18:26
Add a comment |
1 Answer
From postgres 9 docs:
For major releases of PostgreSQL, the internal data storage format is subject to change, thus complicating upgrades. The traditional method for moving data to a new major version is to dump and reload the database
The above is the recommended approach. Otherwise, you can try an in-place upgrade via pg_upgrade:
The contrib program pg_upgrade allows an installation to be migrated in-place from one major PostgreSQL version to the next
However, upgrading two major releases can possibly ask too much from pg_upgrade.
Before doing anything, be sure to have a confirmed-good backup (I suggest both a logical dump and a physical datadir copy taken with the database stopped).
Finally, both postgres 9 and 11 are very old. Upgrade to a newer (and supported) version as soon as possible.
answered Jan 19, 2024 at 18:17
