Re: upgrade questions

On Feb 9, 2024, at 12:00 PM, David Barron <david(dot)barron(at)zencos(dot)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