Hi,
I'm migrating our existing PG instances from PG11.4 to PG14.3. I have around 5 Million Tables in a single database. When migrating using pg_upgrade, its taking 3 hours for the process to complete. I'm not sure if its the intended behaviour or we're missing something here.
Most of the tables (90%) in 5 Million are foreign tables. On analysis found that most of the time is spent in pg_dump (~2.3 hours). In pg_dump getTableAttrs(), dumpTable() functions take the most time, approx 1 hour each since we're processing table by table. Also, there are no columns with default values, which if present might take some time. We're using PG14's pg_upgrade binary for the process.
Since we have all these tables in one database, parallelism doesn't have any effect here. Can we make binary upgrade for a single database run in parallel ?
Kindly advise us if we have missed anything here and possible solutions for this problem.
So we're not sure on what we missed here.
Have added more info on the process below.
No. of Tables: 5 Million
Time Taken: 3 Hours
Command Used: $PG14_UPGRADE -Uroot -b $PG11_DIR/bin -B $PG14_DIR/bin -d $PG11_DIR/data -D $PG14_DIR/data -k -r -j32
Version: PG11.4 to PG14.3
Environment: CentOS machine (32 cores(Intel), 128GB RAM)
Thanks and Regards,
Vignesh K.