public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ilya Anfimov <[email protected]>
To: [email protected]
Subject: Re: PG11 to PG14 Migration Slowness
Date: Thu, 12 Jan 2023 14:07:22 +0300
Message-ID: <Y7/[email protected]> (raw)
In-Reply-To: <CABQmCUksqRi2fwzSmDhx3gcdncCZt=5BPFZ6Xb+q02ykGPXcsw@mail.gmail.com>
References: <CABQmCUksqRi2fwzSmDhx3gcdncCZt=5BPFZ6Xb+q02ykGPXcsw@mail.gmail.com>
On Thu, Jan 12, 2023 at 02:45:41PM +0530, Vigneshk Kvignesh wrote:
> 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.
Yes. In fact, you have a good hardware and I would expect longer time
on average.
> 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.
I don't see any problem. Three-hour downtime every three years
for such setup... You are lucky you have only that.
But you could try some logical replication to the new server
version for upgrading, if you really want to bother. (Well, pg-
logical is my preferred on that scale, but the three general op-
tions are: internal logical replication, pglogical, slony).
> 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.
view thread (2+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: PG11 to PG14 Migration Slowness
In-Reply-To: <Y7/[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox