Content-Type: multipart/alternative;
 boundary="------------x0na9MWCx6LXV9Npi4JhaHPW"
Message-ID: <e2f6c1e6-d7fd-f7dc-7656-46c6bfc7bf0f@gmail.com>
Date: Mon, 24 Jul 2023 08:38:36 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Upgrade from PG12 to PG
Content-Language: nl, en-US, fr
To: Scott Ribe <scott_ribe@elevated-dev.com>
Cc: pgsql-admin@lists.postgresql.org
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>
From: Jef Mortelle <jefmortelle@gmail.com>
Disposition-Notification-To: Jef Mortelle <jefmortelle@gmail.com>
In-Reply-To: <F6998B6F-11FA-4A9F-954B-E4388B835483@elevated-dev.com>
Archived-At: 
 <https://www.postgresql.org/message-id/e2f6c1e6-d7fd-f7dc-7656-46c6bfc7bf0f%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------x0na9MWCx6LXV9Npi4JhaHPW
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit


On 20/07/2023 20:34, Scott Ribe wrote:
>> 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.

=> the pg_restore of this pg_dump takes about 7 hours ... which is 99% 
used for executing the query like:  SELECT pg_catalog.lo_unlink('oid');

>
> 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
=> 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
=> pg_restore takes 7 hours, which is 99% used for executing the query 
like:  SELECT pg_catalog.lo_unlink('oid');
>    use the link option on pg_upgrade
I used the link option in al my tests, and it takes the times

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?
>
> 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?)
The use of OID (large objects): it depends on the vendor of the 
software. I can ask the vendor to change to another type .... but 
honestly I don't believe it will changed in the near feature.
Database is 95GB, so not so big ;-) but have ~25miljon large objects in it.
--------------x0na9MWCx6LXV9Npi4JhaHPW
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p><br>
    </p>
    <div class="moz-cite-prefix">On 20/07/2023 20:34, Scott Ribe wrote:<br>
    </div>
    <blockquote type="cite"
      cite="mid:F6998B6F-11FA-4A9F-954B-E4388B835483@elevated-dev.com">
      <blockquote type="cite">
        <pre class="moz-quote-pre" wrap="">On Jul 20, 2023, at 11:05 AM, Jef Mortelle <a class="moz-txt-link-rfc2396E" href="mailto:jefmortelle@gmail.com">&lt;jefmortelle@gmail.com&gt;</a> wrote:

so, yes pg_ugrade start a pg_dump session,
</pre>
      </blockquote>
      <pre class="moz-quote-pre" wrap="">
Only for the schema, which you can see in the output you posted.</pre>
    </blockquote>
    <p>=&gt; the pg_restore of this pg_dump takes about 7 hours ...
      which is 99% used for executing the query like:  SELECT
      pg_catalog.lo_unlink('oid'); <br>
    </p>
    <blockquote type="cite"
      cite="mid:F6998B6F-11FA-4A9F-954B-E4388B835483@elevated-dev.com">
      <pre class="moz-quote-pre" wrap="">

</pre>
      <span style="white-space: pre-wrap"></span><span
      style="white-space: pre-wrap">
</span>
      <pre class="moz-quote-pre" wrap="">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</pre>
    </blockquote>
    =&gt; 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<br>
    =&gt; pg_restore takes 7 hours, which is 99% used for executing the
    query like:  SELECT pg_catalog.lo_unlink('oid');
    <blockquote type="cite"
      cite="mid:F6998B6F-11FA-4A9F-954B-E4388B835483@elevated-dev.com">
      <pre class="moz-quote-pre" wrap="">
  use the link option on pg_upgrade</pre>
    </blockquote>
    I used the link option in al my tests, and it takes the times <br>
    <br>
    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.<br>
    So after some upgrade,  it ends in a really mess with directory's?<br>
    <blockquote type="cite"
      cite="mid:F6998B6F-11FA-4A9F-954B-E4388B835483@elevated-dev.com">
      <pre class="moz-quote-pre" wrap="">

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?)</pre>
    </blockquote>
    The use of OID (large objects): it depends on the vendor of the
    software. I can ask the vendor to change to another type .... but
    honestly I don't believe it will changed in the near feature.<br>
    Database is 95GB, so not so big ;-) but have ~25miljon large objects
    in it.<br>
  </body>
</html>

--------------x0na9MWCx6LXV9Npi4JhaHPW--