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: <e2f6c1e6-d7fd-f7dc-7656-46c6bfc7bf0f@gmail.com>
Date: Mon, 24 Jul 2023 06:52:26 -0600
Cc: pgsql-admin@lists.postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <D8E10854-1B65-4603-A401-F624B8CABED1@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>
 <F6998B6F-11FA-4A9F-954B-E4388B835483@elevated-dev.com>
 <e2f6c1e6-d7fd-f7dc-7656-46c6bfc7bf0f@gmail.com>
To: Jef Mortelle <jefmortelle@gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/D8E10854-1B65-4603-A401-F624B8CABED1%40elevated-dev.com>
Precedence: bulk

> On Jul 24, 2023, at 12:38 AM, Jef Mortelle <jefmortelle@gmail.com> =
wrote:

> For some reason Postgres creates a new subdirectory for each PG =
version (I make use of tablespaces for each database in my PG cluster), =
also with using the link option.
> So after some upgrade,  it ends in a really mess with directory's?

At the end of pg_upgrade, you can start up the old version against the =
old directory, or the new version against the new directory. (With =
--link, only until writing into the db, then you are committed to the =
running version.) Once you are comfortable that everything is good with =
the new version, you should delete the old data. Alternatively, if there =
is a problem forcing you back to the old version, you delete the new =
data.

> =3D> pg_dump schema_only, after RAM upgrade from 8GB up to 64GB =
(otherwise the query against pg_largeobject ends in a OUT of Memory =
error) runs in about 3-4 minutes
> =3D> pg_restore takes 7 hours, which is 99% used for executing the =
query like:  SELECT pg_catalog.lo_unlink('oid');

Given the tests you've run, it seems to me that it is doing something =
which it ought not when using --link.

> Database is 95GB, so not so big ;-) but have ~25miljon large objects =
in it.

I suppose the use of large objects here is an artifact of support for =
other databases which have much lower limits on varchar column length.