Content-Type: text/plain;
	charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 11.5 \(3445.9.7\))
Subject: Re: upgrade questions
From: Ed Sabol <edwardjsabol@gmail.com>
In-Reply-To: 
 <PH0PR22MB33529D6B6891D52EAF8706FCE94B2@PH0PR22MB3352.namprd22.prod.outlook.com>
Date: Fri, 9 Feb 2024 14:49:07 -0500
Cc: "pgsql-admin@lists.postgresql.org" <pgsql-admin@lists.postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <A57355A6-E651-4111-AD48-5D266E315541@gmail.com>
References: 
 <PH0PR22MB33529D6B6891D52EAF8706FCE94B2@PH0PR22MB3352.namprd22.prod.outlook.com>
To: David Barron <david.barron@zencos.com>
Archived-At: 
 <https://www.postgresql.org/message-id/A57355A6-E651-4111-AD48-5D266E315541%40gmail.com>
Precedence: bulk

On Feb 9, 2024, at 12:00 PM, David Barron <david.barron@zencos.com> =
wrote:
> As the postgres user run these two commands
> =20
> /usr/pgsql-15/bin/initdb -D /pgdata-system/15/data
> /usr/pgsql-15/bin/pg_upgrade -b /usr/pgsql-11/bin -B /usr/pgsql-15/bin =
-d /pgdata-system/11/data -D /pgdata-system/15/data

I just went through this a few months ago myself, except I was coming =
from 10.23.

I'm not sure how large your database is, but, unless it's on the small =
side, I recommend adding the command line switch `--link`. I think this =
requires that /pgdata-system/11/data and /pgdata-system/15/data be on =
the same volume. Also, if you have multiple CPUs or CPU cores, you can =
add `--jobs=3D##` and it can do some of the stuff in parallel. These two =
additions will speed up the upgrade drastically on large databases. I =
also like to add the `-v` switch just so that you get some indication of =
what pg_upgrade is doing.

I recommend that you make sure your pg_hba.conf files in both =
/pgdata-system/11/data and /pgdata-system/15/data are set to trust local =
connections from the postgres account.

Also, before you schedule your downtime for the upgrade, run your =
pg_upgrade command with the `--check` argument. That will do a dry run =
and let you know if you have any issues. Then you should resolve those =
issues beforehand such that you can run the pg_upgrade command with =
`--check` and not encounter any errors or warnings. That way, when it =
comes time to perform the upgrade to your production system, you =
shouldn't encounter any problems.

I encountered issues with user roles and extensions. It's been a while, =
but, IIRC, I think I had to change the ownership of some things and/or =
install the extensions we use in the 15.x server before pg_upgrade would =
run correctly.

Good luck,
Ed