Is it advisable to use pg_dumpall to take backup and psql utility to restore so that along with data(multiple DBs) roles, tablespaces if any are also copied and then restored.
In our environment, PostgreSQL is currently running on lower versions, ranging from 9 to 11.
PG 9.x is presumably running on an EOL version of the OS, meaning you'll migrate to a current version of the OS. That means you'll use pg_dump/pg_restore OR Logical Replication, instead of pg_upgrade.
You should probably do the same on the server running PG 11.
We are planning to upgrade to the latest version, and I have a few questions regarding the process.
What are the key considerations or checks we need to perform before the upgrade?
Can we directly upgrade from version 9 to 15, or is a step-by-step upgrade required?
How should we handle extensions during the upgrade process?
Depends on the extension, and how you install Postgresql. Since we install from RPM packages, it was as simple as installing the latest versions of those packages, and then running pg_restore.
What challenges or issues should we anticipate during or after the upgrade?
You might need to update db drivers like JDBC at the same time.
If anyone with experience in upgrading Postgres, please share your guidance.
Since pg_dump can be performed online, test the process beforehand. I'd bet that your app servers are also running on old servers, too. Upgrade them, and you'll have a perfect chance to test the system beforehand.
The only hiccup we faced when moving from PG 9.6 to 14 was that the updated JDBC drivers on the application servers choked on some strings with quotes in them.
