From: Jef Mortelle <jefmortelle@gmail.com>
To: Scott Ribe <scott_ribe@elevated-dev.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Upgrade from PG12 to PG
Date: Mon, 24 Jul 2023 15:00:48 +0200
Message-ID: <f22e1380-12eb-10ed-5b3d-cd6b0b6a6324@gmail.com> (raw)
In-Reply-To: <aedf41c4-9f91-3fdd-242e-b52068b716c2@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>
<F6998B6F-11FA-4A9F-954B-E4388B835483@elevated-dev.com>
<e2f6c1e6-d7fd-f7dc-7656-46c6bfc7bf0f@gmail.com>
<D8E10854-1B65-4603-A401-F624B8CABED1@elevated-dev.com>
<aedf41c4-9f91-3fdd-242e-b52068b716c2@gmail.com>
correction: -k = --link
On 24/07/2023 14:59, Jef Mortelle wrote:
> Hello
>
> This is the syntax:
>
> export PGDATA=/pg/PG15/system
> export PATH=/usr/lib/postgresql15/bin:/bin:/usr/bin:/usr/local/bin
>
> export PGDATAOLD=/pg/data
> export PGDATANEW=/pg/PG15/system
> export PGBINOLD=/usr/lib/postgresql12/bin
> export PGBINNEW=/usr/lib/postgresql15/bin
>
> /usr/lib/postgresql15/bin/pg_upgrade -r -v -p 5431 -P 5432 -k -j 8
>
> -r =--link
>
> Kind regards
>
> On 24/07/2023 14:52, Scott Ribe wrote:
>>> 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.
>>
>>> => 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');
>> 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.
>>
>>
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: jefmortelle@gmail.com, scott_ribe@elevated-dev.com, pgsql-admin@lists.postgresql.org
Subject: Re: Upgrade from PG12 to PG
In-Reply-To: <f22e1380-12eb-10ed-5b3d-cd6b0b6a6324@gmail.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