Message-ID: <19cbcef2-b7e9-e696-5a64-bd7098126cc4@gmail.com>
Date: Thu, 20 Jul 2023 15:42:37 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Upgrade from PG12 to PG
Content-Language: nl, en-US, fr
To: pgsql-admin@lists.postgresql.org
References: <3901e0da-819b-5b6d-bb57-c29f6d384725@gmail.com>
 <02E1F4FD-C346-4169-86F5-2CB17232D838@gmail.com>
From: Jef Mortelle <jefmortelle@gmail.com>
Disposition-Notification-To: Jef Mortelle <jefmortelle@gmail.com>
In-Reply-To: <02E1F4FD-C346-4169-86F5-2CB17232D838@gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/19cbcef2-b7e9-e696-5a64-bd7098126cc4%40gmail.com>
Precedence: bulk

Hi,

Alsready tried to use --link and --jobs, but you cannot ommit the 
"select  lo_unlink ...."   for every rows containing datatype text in 
your database that the pg_* program creates in the export/dump file.
These select's takes a lot of time, hours ...

The pg_dumpall => size of the dumpfile is much more than the size of 
exported database

Kind regards


On 20/07/2023 15:29, Edward J. Sabol wrote:
> On Jul 20, 2023, at 7:23 AM, Jef Mortelle <jefmortelle@gmail.com> wrote:
>> I use /usr/lib/postgresql15/bin/pg_upgrade -v -p 5431 -P 5432
>>
>> I takes a lot of time, more than 6 hours
> I'm in the middle of a similar database migration myself, but I'm coming from 10.23.
>
> Have you considered adding the --link and --jobs=NN (where NN is the number of CPU cores on your server minus 2 or something like that) to your pg_upgrade command? I wonder if that would speed things up significantly.
>
> Also, if your file system supports reflinks (Linux kernel 4.5 and XFS with reflink support activated or Btrfs or macOS APFS), then use --clone instead of --link.
>
> Good luck,
> Ed
>