Data migration between postgresql-9 and postgresql-11

Question

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.

Answer 1

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.