Message-ID: <aedf41c4-9f91-3fdd-242e-b52068b716c2@gmail.com>
Date: Mon, 24 Jul 2023 14:59:29 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Upgrade from PG12 to PG
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>
 <e2f6c1e6-d7fd-f7dc-7656-46c6bfc7bf0f@gmail.com>
 <D8E10854-1B65-4603-A401-F624B8CABED1@elevated-dev.com>
Content-Language: nl, en-US, fr
From: Jef Mortelle <jefmortelle@gmail.com>
Disposition-Notification-To: Jef Mortelle <jefmortelle@gmail.com>
In-Reply-To: <D8E10854-1B65-4603-A401-F624B8CABED1@elevated-dev.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/aedf41c4-9f91-3fdd-242e-b52068b716c2%40gmail.com>
Precedence: bulk

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.
>
>