Postgres updated to 15 and not starting

Support Software & Applications
1

I had been running PG 14, but it was upgraded to PG 15 without my noticing at some point. It probably was updated with other updates, and since I don’t use the DB regularly, I only noticed that the service failed to start today.

The service has failed to start because the data format is 14. The output told me to see this Arch page, but it seemed complicated. In my case, can I use pg_upgrade?

I had not done any crazy things to Postgres 14. Probably it only has some basic tables without any complex views or scripts. In this case, isn’t there any simpler way to make PG 15 work?

2

So…

I’ll preface this by saying I am only going by the provided documentation…

But first you want postgresql-old-upgrade which will provide the pg_upgrade from the previous version (14).

First stop the service if it is somehow running:

systemctl stop postgresql

Now because you are going to use the same path for the new db you want to move the current one out of the way (again note I am using the docs … if you have a different path then use that):

sudo mv /var/lib/postgres/data /var/lib/postgres/olddata

Then we make the new directories we will use:

sudo mkdir /var/lib/postgres/data /var/lib/postgres/tmp

Set the ownership:

sudo chown postgres:postgres /var/lib/postgres/data /var/lib/postgres/tmp

And lets change to the postgres user:

sudo -iu postgres

Move to path

cd /var/lib/postgres/tmp

Now create the thing using the same flags and options you did before (again this is only an example):

initdb --locale=C.UTF-8 --encoding=UTF8 -D /var/lib/postgres/data --data-checksums

Now you can do the pg_upgrade:

pg_upgrade -b /opt/pgsql-14/bin -B /usr/bin -d /var/lib/postgres/olddata -D /var/lib/postgres/data

You may want to check and/or edit the configuration files pg_hba.conf and postgresql.conf, though it is not necessarily required.

Now you can try starting the service again:

systemctl start postgresql

You may optionally (as postgres user) run the vaccuum:

vacuumdb --all --analyze-in-stages

Hope that helps.

3

At the

pg_upgrade -b /opt/pgsql-14/bin -B /usr/bin -d /var/lib/postgres/olddata -D /var/lib/postgres/data

step, I get:

Performing Consistency Checks

Checking cluster versions ok

old cluster does not use data checksums but the new one does
Failure, exiting

What can I do?

PS: I searched the web and it seems that I need to disable the checksum. But if I do that, I shall get the same error at the next upgrade (PG 16). So instead of disabling checksum, can I create a new checksum?

4

Looks like you included this from the example creation command when you hadnt previously:

So if it made something at that path delete it and go again … remember to use the same options you did when you created the version 14 db. I dont know what they were.

5

Yeah, neither do I. :smiling_face_with_tear:

6

Well … a lot will be assumed from the system if you dont pass any arguments.
Maybe you did it with a simple method the first time. Like this:

initdb -D /var/lib/postgres/data

(from the postgres user of course)

7

It worked to use

initdb --locale=en_GB.UTF-8 --encoding=UTF8 -D /var/lib/postgres/data

instead of

initdb --locale=C.UTF-8 --encoding=UTF8 -D /var/lib/postgres/data --data-checksums

in my case. I wish pg_upgrade automatically did the whole manual process using existing database’s settings, but oh well,

8

After upgrading the data as your instruction, I have found that pg_hba.conf was not upgraded. I think it is just using the default one, not the one I had customised, and thus I had problems connecting to the DB remotely. I think I can manually edit the file, but why wasn’t the file updated? Is it normal, and the user is supposed to manually edit pg_hba.conf?

9

Yes its normal.

10

This topic was automatically closed 2 days after the last reply. New replies are no longer allowed.