[SOLVED] Help with Upgrading postgresql 14 to 15

Topic: [SOLVED] Help with Upgrading postgresql 14 to 15 (Read 2604 times) previous topic - next topic

0 Members and 2 Guests are viewing this topic.

[SOLVED] Help with Upgrading postgresql 14 to 15

29 January 2023, 08:04:08

Hi all.

I hate to be asking this of the developers, but could someone provide a guide on how to migrate postgresql 14 to 15? I know this was a very recent change, but the only documentation I could find on this was on a Debian based system, which has different bin directories for postgresql as far as I can tell.

This tutorial does a fine job explaining what needs to be done using postgres's pg_upgrade cli tool, but the --old-bindir section is throwing me off. I doubt he can be referring to the actual postgresql binary...

The code in particular I'm referring to is:

Code: [Select]

/usr/lib/postgresql/15/bin/pg_upgrade \
  --old-datadir=/var/lib/postgresql/14/main \
  --new-datadir=/var/lib/postgresql/15/main \
  --old-bindir=/usr/lib/postgresql/14/bin \
  --new-bindir=/usr/lib/postgresql/15/bin \
  --old-options '-c config_file=/etc/postgresql/14/main/postgresql.conf' \
  --new-options '-c config_file=/etc/postgresql/15/main/postgresql.conf' \
  --check

I can of course reach out to this individual, but I thought I might ask the community if there was any insight into where the old-bindir might be on Artix.

Any help would be greatly appreciated, and as always, thanks for taking a look at this query and all you devs do.

Re: Help with Upgrading postgresql 14 to 15

Reply #1 – 29 January 2023, 17:06:32

As the pacman packages don't use version numbers for sub folders are you sure you need to do anything at all ?

You don't say what you have done already ?
Have you already upgraded to postgresql-15.1-1 and it's not working with your database ?

Re: Help with Upgrading postgresql 14 to 15

Reply #2 – 31 January 2023, 04:01:00

Quote from: gripped – on 29 January 2023, 17:06:32

You don't say what you have done already ?
Have you already upgraded to postgresql-15.1-1 and it's not working with your database ?

Yes, I have simply tried to log into my postgresql database using psql and the error message indicated that the database could not be found. I have already reverted back to the previous version for now, but I was hoping someone had encountered and troubleshooted this same issue. I have yet to find any documentation on how to convert over on Arch based distributions.

Re: Help with Upgrading postgresql 14 to 15

Reply #3 – 31 January 2023, 05:00:43

Quote from: z3rOR0ne – on 31 January 2023, 04:01:00

I have yet to find any documentation on how to convert over on Arch based distributions.

https://www.vultr.com/docs/how-to-install-configure-and-upgrade-postgresql-on-arch-linux/
https://www.postgresql.org/docs/current/upgrading.html
https://www.postgresql.org/docs/current/pgupgrade.html

Re: Help with Upgrading postgresql 14 to 15

Reply #4 – 31 January 2023, 06:37:12

Here is the procedure from Arch PostgreSQL wiki entry updating a major version (systemd free).
Please backup the database before updating anything!

It is recommended that the postgresql packages be placed on the IgnorePkg list in /etc/pacman.conf to prevent an inaccessible database caused by an major version update.

Code: [Select]

IgnorePkg=postgresql postgresql-libs

If you need to find out the installed version.

Code: [Select]

# cat /var/lib/postgres/data/PG_VERSION

Note: Commands that should be run as the postgres user are prefixed by [postgres]$.
Start the database if it is not running, check the running database for the initdb encoding and locale arguments used to create it.

Code: [Select]

[postgres]$ psql -l

Once you have the values, stop the postgresql daemon.
Install the postgresql, lib and the old-upgrade packages.

Code: [Select]

# pacman -Syu postgresql postgresql-libs postgresql-old-upgrade

The "postgresql-old-upgrade" will provide the previous version for the update procedure.

Code: [Select]

$ ls /opt/pgsql-14/
bin  include  lib  share

Move data and create new locations.

Code: [Select]

# mv /var/lib/postgres/data /var/lib/postgres/olddata
# mkdir /var/lib/postgres/data /var/lib/postgres/tmp
# chown postgres:postgres /var/lib/postgres/data /var/lib/postgres/tmp
[postgres]$ cd /var/lib/postgres/tmp

Initialize using the same initdb arguments as were used originally.

Code: [Select]

[postgres]$ initdb -D /var/lib/postgres/data --locale=xy_XY.UTF-8 --encoding=UTF8 --data-checksums

Please use the input the values specific to your database.

Upgrade, replacing PG_VERSION below, with the old PostgreSQL version number which in this case is 14.

Code: [Select]

[postgres]$ pg_upgrade -b /opt/pgsql-<PG_VERSION>/bin -B /usr/bin -d /var/lib/postgres/olddata -D /var/lib/postgres/data

Some addition considerations:

Quote

Note: If necessary, adjust the configuration files of new cluster (e.g. pg_hba.conf and postgresql.conf) to match the old cluster.
Note: If pg_upgrade fails with The source cluster was not shut down cleanly, PostgreSQL was not stopped before attempting the upgrade. Stop it, then restart the cluster with old database binaries to recover old cluster files:
Code: [Select]
[postgres]$ /opt/pgsql-PG_VERSION/bin/pg_ctl start -D /var/lib/postgres/olddata && /opt/pgsql-PG_VERSION/bin/pg_ctl stop -D /var/lib/postgres/olddata
You can now safely retry the pg_upgrade command. If the upgrade process still fails, shut down any database processes, downgrade PostgreSQL packages to their previous versions, recover your previous cluster data from backups and restart the upgrade process.

Start the postgresql daemon and the database should be accessible.

Tune and clean up stuff as desired once everything is running and verified sound.

Quote

Optional: Run [postgres]$ vacuumdb --all --analyze-in-stages to recalculate query analyzer statistics, which should improve query performance shortly after the upgrade. (Adding --jobs=NUMBER_OF_CPU_CORES argument may improve this command's performance.)

Optional: Back up the /var/lib/postgres/olddata directory in case you need to restore a previous PostgreSQL version.

Delete the /var/lib/postgres/olddata directory
Delete the /var/lib/postgres/tmp

Re: Help with Upgrading postgresql 14 to 15

Reply #5 – 01 February 2023, 04:24:58

Thanks to all in the community, @jspaces who gave an excellent synopsis of the documentation. I do apologize to the community for not doing enough research before posting my previous topics, I've been jumping the gun when asking for help and not rtfm thoroughly enough. I realize now I need to slow down when trying to troubleshoot things and take more time before turning to you guys.

That said, I can't thank you all enough for taking the time to help me out here. I did get postgresql 15 working and it's thanks to the amazing community here and the excellent documentation over at the ArchWiki. Peace.

Re: Help with Upgrading postgresql 14 to 15

Reply #6 – 07 February 2023, 22:12:10

jspaces:

Quote

Note: Commands that should be run as the postgres user are prefixed by [postgres]$.
Start the database if it is not running, check the running database for the initdb arguments used to create it.
Code: [Select]
[postgres]$ psql -l

I've run into problems with the update from 14=>15 as well. I downgraded and am trying to follow instructions to find out what arguments were initially passed to initdb to create the database. The above seems to imply that "psql -l" gives this information but i don't see any connection. Searching online, i can't find an answer to this question, either.
Can anyone enlighten me on this?

Re: Help with Upgrading postgresql 14 to 15

Reply #7 – 08 February 2023, 03:45:10

If you just start the postgres daemon manually, the "psql -l" command will fail due to a socket creation failure.
The postgres daemon has to started with the init service installed on the system which should provide the socket.

These commands were run as the postgres user after the init service for postgres was started.
Once started, the socket should exist:

Code: [Select]

postgres]:~$ ls -a /run/postgresql/
.  ..  .s.PGSQL.5432  .s.PGSQL.5432.lock

Then the psql -l will show the information:

Code: [Select]

postgres]:~$ psql -l
                                             List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  | ICU Locale | Locale Provider |   Access privileges   
-----------+----------+----------+---------+---------+------------+-----------------+-----------------------
 jspaces   | postgres | UTF8     | C.UTF-8 | C.UTF-8 |            | libc            | 
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 |            | libc            | 
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 |            | libc            | =c/postgres          +
           |          |          |         |         |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 |            | libc            | =c/postgres          +
           |          |          |         |         |            |                 | postgres=CTc/postgres
(4 rows)
[code]

I for one now keep the initialization options in an archived file for the next major version update.

Re: Help with Upgrading postgresql 14 to 15

Reply #8 – 08 February 2023, 05:24:47

Yes, the postgres daemon was running and i saw pretty much what you posted. What has that got to do with finding the initdb parameters the db was created with, though? I know the init parameters *can* specify locale type things, but the output you posted doesn't *seem* (that i can see) to show the init parameters in question.

Re: Help with Upgrading postgresql 14 to 15

Reply #9 – 08 February 2023, 07:29:17

Please check the "Collate" and the "Encoding" values in the example output.
The example database was created with these following initialization:

Code: [Select]

postgres]:~$ initdb --locale=C.UTF-8 --encoding=UTF8 -D /<Database Path> --data-checksums

The "psql -l" command does show how the database was initialized with what locale and encoding options.
What else do you need?

Re: Help with Upgrading postgresql 14 to 15

Reply #10 – 08 February 2023, 19:22:33

Quote

What else do you need?

The information you provided. Thank you very much. There's a bit of knowledge needed to know how the info from "psql -l" provides the (some) initialization information. initdb actually can take many options, though, not all related to locale/encoding. Which is why i was somewhat puzzled. And still am, a bit, but thanks for the info.

Re: Help with Upgrading postgresql 14 to 15

Reply #11 – 08 February 2023, 19:39:09

Have you checked the environmental settings from the database itself for those other configured options?

The critical encoding and locale settings are only shown through the "psql -l" command and not viewable in the database settings.

In hindsight, it appears that keeping a hard copy of the initialization command for future major upgrades looks like a great idea.

Quote

"psql -l" provides the (some) initialization information

I have updated the post to highlight that only encoding and locale information is provide by "psql -l" command for clarification.