@Ron: with 'parallel' you mean the -jX part of the pg_dump/pg_restore commands you're suggesting, which would utilize multiple processes on the server, right?
Correct.
@rajeshkumar: It would also technically be possible to run a plain text dump and pipe it through to psql on the fly. Something like
That way you have only one single action going on (on the target server). You loose the option to interfere between dump and restore though.
But is:
1) single-threaded, and
2) means you can't do in-place upgrades.
You check the out file (/path/to/transfer.out) produced by this for any errors (grep -iE 'warning|error|fatal|notice' /path/to/transfer.out) afterwards. Also you need to compare password_encryption on both ends and, if it might be different (like md5 on the source, scram-sha-256 on the target), set the passwords once again manually. Assuming you have both servers running in parallel you can test those options and see which one suits you while the source server is still operating.
I have traditional servers with postgres with replication setup (primary - standby). OS team want to upgrade from rhel 8.10 to 10.
As a dba, what is the suggestion we need to give. How do we proceed ? Should we stop the posygres servers? Should we get new servers with rhel 10 and migrate Data?
That's certainly a safe method.
What's the best procedure
The main problem is collation change driven by the newer glibc version.
1. How did you install PG (from the RHEL repository, or from the PGDG repository)?
2. How big are your databases?
3. How big is your outage window?
4. Do you plan on upgrading Postgresql at the same time?
