From: Scott Ribe <scott_ribe@elevated-dev.com>
To: Jef Mortelle <jefmortelle@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Upgrade from PG12 to PG
Date: Thu, 20 Jul 2023 12:34:18 -0600
Message-ID: <F6998B6F-11FA-4A9F-954B-E4388B835483@elevated-dev.com> (raw)
In-Reply-To: <1cb1652e-286a-077e-950d-516970ecf499@gmail.com>
References: <3901e0da-819b-5b6d-bb57-c29f6d384725@gmail.com>
<CAG95seWz3dJxKQPg4_3EAEBCXUuJTDpZBUKAp0+bqP2amQaZ9g@mail.gmail.com>
<dc88d14d-6d0f-2d67-ecfc-c7495bf1c22b@gmail.com>
<B6D3FD80-5794-47B8-9074-E10C4945951B@elevated-dev.com>
<1cb1652e-286a-077e-950d-516970ecf499@gmail.com>
> On Jul 20, 2023, at 11:05 AM, Jef Mortelle <jefmortelle@gmail.com> wrote:
>
> so, yes pg_ugrade start a pg_dump session,
Only for the schema, which you can see in the output you posted.
> Server is a VM server, my VM has 64GB SuseSLES attached to a SAN with SSD disk (Hp3Par)
VM + SAN can perform well, or introduce all sorts of issues: busy neighbor, poor VM drivers, SAN only fast for large sequential writes, etc.
> On Jul 20, 2023, at 11:22 AM, Ron <ronljohnsonjr@gmail.com> wrote:
>
> Note also that there's a known issue with pg_upgrade and millions of Large Objects (not bytea or text, but lo_* columns).
Good to know, but it would be weird to have millions of large objects in a 1TB database. (Then again, I found an old post about 3M large objects taking 5.5GB...)
Try:
time a run of that pg_dump command, then time a run of pg_restore of the schema only dump
time a file copy of the db to a location on the SAN--purpose is not to produce a usable backup, but rather to check IO throughput
use the link option on pg_upgrade
Searching on this subject turns up some posts about slow restore of large objects under much older versions of PG--not sure if any of it still applies.
Finally given the earlier confusion between text and large objects, your apparent belief that text columns correlated to large objects, and that text could hold more data than varchar, it's worth asking: do you actually need large objects at all? (Is this even under your control?)
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: pgsql-admin@postgresql.org
Cc: scott_ribe@elevated-dev.com, jefmortelle@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Upgrade from PG12 to PG
In-Reply-To: <F6998B6F-11FA-4A9F-954B-E4388B835483@elevated-dev.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox