Yet another Foreman upgrade fail

[gvde]

@ekohl

And if I look at /usr/bin/postgresql-setup then it’s replaced @SU_POSTGRES@ with /usr/sbin/runuser -s /bin/sh -l postgres. In other words, if we didn’t use runuser in the installer then postgresql-setup would still do the same.

Yes. You are right. Even the postgresql-13-setup script from postgresql does it with runuser -l.

But by now, it seems we have found the reason and a potential workaround.

Looking the last katello.log on my server I see this line:

[configure] /Stage[main]/Postgresql::Server::Initdb/Postgresql::Server::Instance::Initdb[main]/Exec[postgresql_initdb_instance_main]: '/usr/bin/initdb --pgdata '/var/lib/pgsql/data' --username 'postgres' ' won't be executed because of failed check 'creates'

This looks to me as if foreman-installer initializes the database as user root, calling initdb directly, which probably means in that step the current user’s locale applies.

The upgrade runs through runuser -l which uses the system locale.

Both together would suggest that foreman-installer causes this, because it doesn’t use runuser -l for initdb (and doesn’t use postgresql-setup --initdb either). So this would make it a foreman-installer bug, because it relies on the system locale being used by the user running foreman-installer.

I would say it would be good if foreman-installer would check the locale of the database and the system locale and would raise an error if it finds an inconsistency. That would be better than people trying to find the right log file with the right piece of information pointing to the cause of the problem…

[ekohl]

@gvde

[configure] /Stage[main]/Postgresql::Server::Initdb/Postgresql::Server::Instance::Initdb[main]/Exec[postgresql_initdb_instance_main]: '/usr/bin/initdb --pgdata '/var/lib/pgsql/data' --username 'postgres' ' won't be executed because of failed check 'creates'

That’s puppetlabs-postgresql/manifests/server/initdb.pp at main · puppetlabs/puppetlabs-postgresql · GitHub which calls puppetlabs-postgresql/manifests/server/instance/initdb.pp at main · puppetlabs/puppetlabs-postgresql · GitHub.

I think for most Puppet users this isn’t a problem because Puppet runs as a daemon with the system locale, but our installer is different. I’m wondering if it would be better to let the installer ensure Puppet runs with the system locale.

[techietubby]

I am using AlmaLinux 8.10 with Foreman 3.10. and the upgrade failed with:

lc_collate values for database “postgres” do not match:

I simply changed the locale and upgraded the database and that seems to work.

# localectl set-locale C.UTF-8
# localectl status
   System Locale: LANG=C.UTF-8
       VC Keymap: gb
      X11 Layout: gb
# postgresql-setup --upgrade
 * Upgrading database.
 * Upgraded OK.

I then ran: foreman-installer and everything worked as expected. I have left the new locale and will see if it makes any difference.

[JakeBreitfeller]

For anyone experiencing issues with locales I found a fix that I was able to leverage for my systems

If you list out the databases for your postgres servers you can see the locale config
make sure you are logged in as the postgres user

su postgres
psql 
\l

My locales were being set as C.UTF-8 for some reason, I suspect it is because of an update to one of the postgres packages

Regardless, after updating the postgres databases for my foreman and smartproxy servers the pg_upgrade ran successfully

You can update each table with

update pg_database set datcollate='en_US.UTF-8', datctype='en_US.UTF-8' where datname='postgres';

I also updated these tables on my smartproxy servers

You can execute these from a file as well

psql -f filename

My foreman upgrade ran successfully after this

[voorman]

This solved the issue for me!

[gvde]

@JakeBreitfeller

You can update each table with

update pg_database set datcollate='en_US.UTF-8', datctype='en_US.UTF-8' where datname='postgres';

Please do not do that! Never do that! That is very dangerous! Technically your database is corrupted after that because you just told postgresql that the main postgres database has a different collation and ctype from what it actually does. It may just work for you because you are coming from C.UTF-8 and thus any strings in the database should directly map to en_US.UTF-8 without modification.

For someone else coming from a different collation it may cause serious issues.

But again: technically it’s corrupted! Do not attempt it. String may be broken. Indexes may be broken. Anything string related may be broken. You may not even notice where it’s broken until much later when you cannot find the texts you are looking for or worse it’s causes internal crashes/exceptions because a text cannot be interpreted as it’s the wrong locale.

Anything supported to be changed, can be changed through ALTER commands. Messing in the tables in postgres database can have serious effects.

The only supported way to “change” the collation or ctype is by dumping the database and restoring the dump into a new database created with the correct locale.

Otherwise, change the system locale to match and do the upgrade of the database. After that, it doesn’t matter. This is a bug of the redhat postgresql-setup script. Don’t break the database as workaround to this bug…

[voorman]

Okay, so I did use this fix. Is there a way to fix the database if it is indeed broken? Not sure what the locale’s were before I did this.

[gvde]

Only a postgresql developer might know that. It’s an inconsistent state. I don’t know where the information in the pg_database table is used. But it’s not a trivial change otherwise there would probably an ALTER DATABASE option for that.

You can only revert to a snapshot before the upgrade or check a backup to find out the locale before. But even then, it’s not safe to assume that simply changing it back would bring it into a consistent state again. You have changed it in postgresql 12 and upgraded to 13 with this. Even if I would guess that it’s not an issue because the system postgres database will hopefully hold mostly ascii strings, unless someone know exactly the implications of these two columns in the pg_database table it’s impossible to predict. Chances are it doesn’t matter.

To be safe, revert to a snapshot or backup before the upgrade and upgrade again.

[JakeBreitfeller]

I’ve taken your suggestion and I developed a different solution where I dump the old database and create a new database with the appropriate locales

/* create db */
create database postgres_new with owner=postgres encoding='UTF-8' lc_collate='en_US.UTF-8'
alter database postgres_new with allow_connections=true;

/* dump old db and restore in new db */
pg_dump -d postgres > /tmp/postgresbak.sql
psql -d postgres_new < /tmp/postgresbak.sql
 
/* drop old db */
lc_ctype='en_US.UTF-8' template template0;
alter database postgres rename to postgres_old;
alter database postgres_new rename to postgres;
drop database postgres_old

This solution also worked for me however I’m trying to understand how this is any different than my original solution when we have always used the en_US.UTF-8 locales.

Whatever changed in the recent version of this package to cause the locales to be set to C.UTF-8 for all users seems to be done in the interest of ensuring everyone has a POSIX standard locale that will be compatible for upgrade but this only seems to have created a mismatch for every user. What is the philosophy behind this?

[gvde]

I don’t think that is any better than messing with the postgres internal table. The database postgres is the internal table. I wouldn’t mess with it. I am surprised that this is working at all because when you rename the database you don’t have a database postgres for a moment. It wonder how it works at all.

Did you check after the drop database if there are still old database files opened which have been deleted. Did you restart the postgresql server since then? My guess would be that it wrote the information into files which were already open…

If you dump and restore to change the locale of the whole database, you have to dump all databases on the server, run initdb with the correct locale options and then restore.

I can only repeat: set the correct locale for the upgrade. It’s a bug of the postgresql-setup that it doesn’t use the current database locale for the inplace upgrade. With the correct locale set, at least for the duration of the upgrade, it’ll work. Don’t try to change something internally which isn’t broken only because something else is broken.

With your change you never know what the impact may be. As far as I understand postgresql initializes the postgres, template0 and template1 database with the same locale. Possibly with your change, you’ll end up with a database which cannot be upgraded anymore because those three aren’t matching. Or whatever.

Don’t try to tweak internals to work around an issue, which can be handled by setting the correct locale while running the database upgrade, until the upgrade script has been fixed by redhat to handle this case correctly.

I don’t understand why you don’t want to set the locale but rather mess with internals for which you don’t understand the consequences.

[evgeni]

We’re working on a “correct” fix in Fixes #37797 - ensure PostgreSQL upgrade uses the right locales by evgeni · Pull Request #971 · theforeman/foreman-installer · GitHub