Content-Type: text/plain;
	charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3731.600.7\))
Subject: Re: Upgrade from PG12 to PG
From: Scott Ribe <scott_ribe@elevated-dev.com>
In-Reply-To: <1cb1652e-286a-077e-950d-516970ecf499@gmail.com>
Date: Thu, 20 Jul 2023 12:34:18 -0600
Cc: pgsql-admin@lists.postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <F6998B6F-11FA-4A9F-954B-E4388B835483@elevated-dev.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>
To: Jef Mortelle <jefmortelle@gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/F6998B6F-11FA-4A9F-954B-E4388B835483%40elevated-dev.com>
Precedence: bulk

> On Jul 20, 2023, at 11:05 AM, Jef Mortelle <jefmortelle@gmail.com> =
wrote:
>=20
> 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:
>=20
> 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?)=