Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1q1TF3-0000F8-Dp for pgsql-general@arkaria.postgresql.org; Tue, 23 May 2023 14:44:01 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1q1TF2-0001aC-8O for pgsql-general@arkaria.postgresql.org; Tue, 23 May 2023 14:44:00 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1q1TF1-0001a3-Tk for pgsql-general@lists.postgresql.org; Tue, 23 May 2023 14:43:59 +0000 Received: from luna.openvistas.net ([207.158.15.156] helo=openvistas.net) by magus.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1q1TEy-001fZ2-73 for pgsql-general@lists.postgresql.org; Tue, 23 May 2023 14:43:59 +0000 Received: (qmail 49207 invoked from network); 23 May 2023 14:43:55 -0000 Received: from unknown (HELO ?10.0.26.32?) (jross@154.27.106.60) de/crypted with TLSv1.3: AEAD-AES128-GCM-SHA256 [128/128] DN=none by mail.openvistas.net with ESMTPSA; 23 May 2023 14:43:55 -0000 Content-Type: multipart/alternative; boundary="------------Z7abV9dMscqozxn70Ak06UP3" Message-ID: <79a10835-e830-51f0-bdc1-596807aa5b7a@openvistas.net> Date: Tue, 23 May 2023 08:43:54 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:102.0) Gecko/20100101 Thunderbird/102.11.0 Subject: Re: 15 pg_upgrade with -j Content-Language: en-US To: pgsql-general@lists.postgresql.org References: <85a18793-627a-2a8b-1296-c965505694e6@openvistas.net> <13b3d746-a980-22d9-c88b-e74451536a11@aklaver.com> <5fdaee55-81df-4be8-73c9-8e496bd02368@openvistas.net> <8b4034c6-ca1b-b0bb-e067-685235775395@aklaver.com> From: Jeff Ross In-Reply-To: <8b4034c6-ca1b-b0bb-e067-685235775395@aklaver.com> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------Z7abV9dMscqozxn70Ak06UP3 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 5/22/23 5:43 PM, Adrian Klaver wrote: > >>> >>> From docs: >>> >>> https://www.postgresql.org/docs/current/pgupgrade.html >>> >>> The --jobs option allows multiple CPU cores to be used for >>> copying/linking of files and to dump and restore database schemas in >>> parallel; a good place to start is the maximum of the number of CPU >>> cores and tablespaces. This option can dramatically reduce the time >>> to upgrade a multi-database server running on a multiprocessor machine. >>> >>> So is the 1400G mostly in one database in the cluster? >>> >>>> >>>> The full commands we are using for pg_upgrade are pretty stock: > >> Yes, one big database with about 80 schemas and several other smaller >> databases so -j should help, right? > > > As I understand it no. That the parallelism is between databases not > within a database. Further that 'database schemas' refers to schema as > the overall database object definitions not the namespaces known as > schemas in the database. Thanks Adrian.  That "restore database schemas in parallel" phrase seems like it would be really easy to read like we did and expect it to work with one database and multiple schemas. Maybe it should be changed to "restore multiple databases in parallel" instead? Jeff --------------Z7abV9dMscqozxn70Ak06UP3 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit On 5/22/23 5:43 PM, Adrian Klaver wrote:


From docs:

https://www.postgresql.org/docs/current/pgupgrade.html

The --jobs option allows multiple CPU cores to be used for copying/linking of files and to dump and restore database schemas in parallel; a good place to start is the maximum of the number of CPU cores and tablespaces. This option can dramatically reduce the time to upgrade a multi-database server running on a multiprocessor machine.

So is the 1400G mostly in one database in the cluster?


The full commands we are using for pg_upgrade are pretty stock:

Yes, one big database with about 80 schemas and several other smaller databases so -j should help, right?


As I understand it no. That the parallelism is between databases not within a database. Further that 'database schemas' refers to schema as the overall database object definitions not the namespaces known as schemas in the database.

Thanks Adrian.  That "restore database schemas in parallel" phrase seems like it would be really easy to read like we did and expect it to work with one database and multiple schemas.

Maybe it should be changed to "restore multiple databases in parallel" instead?

Jeff
--------------Z7abV9dMscqozxn70Ak06UP3--