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.
