From: Ed Sabol <edwardjsabol@gmail.com>
To: David Barron <david.barron@zencos.com>
Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: upgrade questions
Date: Fri, 9 Feb 2024 14:49:07 -0500
Message-ID: <A57355A6-E651-4111-AD48-5D266E315541@gmail.com> (raw)
In-Reply-To: <PH0PR22MB33529D6B6891D52EAF8706FCE94B2@PH0PR22MB3352.namprd22.prod.outlook.com>
References: <PH0PR22MB33529D6B6891D52EAF8706FCE94B2@PH0PR22MB3352.namprd22.prod.outlook.com>
On Feb 9, 2024, at 12:00 PM, David Barron <david.barron@zencos.com> wrote:
> As the postgres user run these two commands
>
> /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=##` 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
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: edwardjsabol@gmail.com, david.barron@zencos.com, pgsql-admin@lists.postgresql.org
Subject: Re: upgrade questions
In-Reply-To: <A57355A6-E651-4111-AD48-5D266E315541@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