we want to upgrade Foreman 3.6 to 3.7 but we still use the Postgresql 10 version, do I have to upgrade the postgresql 10 to 12 ? or whats the best choose ?
Foreman and Proxy versions: foreman 3.6.1 , foreman-proxy 3.6.1 Distribution and version: Ubuntu 20.04.4 LTS
thanks for your answer @areyus , I mean when I update foreman, does foremat automatic select postgresql 10 or it will select the postgresql 12 ? because we have both on the server…
If it select version 12 and make some changes (CRUD tables) we will dont have those changes in version 10.
I can’t give a definitive answer to this since I’m not familiar with running Foreman on Ubuntu or have multiple Postgres versions installed on the Foreman server. Though, looking at the code here is what I found:
foreman-installer uses the puppetlabs-postgresql module to manage Postgres Installation without passing any parameters explicitly.
puppetlabs-postgresql defaults to using PostgreSQL 12 on Ubuntu 20.04
To my knowledge (I don’t know where in the code this happens, but from experience with past upgrades I did), the DB changes are done via foreman-rake commands after the Puppet part of the installer finished.
Depending on how your two postgres instances and your Foreman environment are set up, I assume the DB actions will by default run agains whatever Foreman is currently using (which, by default again, should be whatever is listening on the default Foreman port).
My expectation what would happen in a default scenario would be:
foreman-installer shuts down all services when it starts
foreman-installer applies all the Puppet modules, which in turn starts PostgreSQL 12
foreman-rake performs DB migrations etc, which would run on the PostgreSQL 12 instance that’s running
You end up with a broken setup
The main/best way to stay on PostgreSQL 10 would probably be to set the Postgres version via /etc/foreman-installer/custom-hiera.yaml, the correct entry should be postgresql::globals::version = '10'.
If migrationg to PostgreSQL 12 is an option for you, I would recommend doing that, primarily to eliminate possible problems in the future.
Postgres has documentation for upgrades. The docs are a little weird (like assuming running a system without SystemD), but in generale the procedure is exactly what you mentioned. Dump the DB, stop the old one, start the new one, import the dump.
thanks a lot @areyus … Ill do that in next days! and I’ll first run foreman 3.6 with Postgresql 12 and if I see it works then Ill upgrade foreman to 3.7. to avoid any mistakes if I do all in one
hi all, I create backup from Postgresql 10 and I want restore it to Postgresql 12 but I get below error, can anyone help me:
$ pg_restore -d foreman foreman_10.dump
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 3538; 1259 17698 INDEX index_fact_names_on_name_and_type foreman
pg_restore: error: could not execute query: ERROR: could not create unique index "index_fact_names_on_name_and_type"
DETAIL: Key (name, type)=(network_ISCSI Port 2, PuppetFactName) is duplicated.
Command was: CREATE UNIQUE INDEX index_fact_names_on_name_and_type ON public.fact_names USING btree (name, type);
Basically, what has happened is that for some reason, your DB is not properly indexed.
I don’t know how this has happened to you, but you can take a look at this thread: Possible to use Leapp for Oracle 7 -> Oracle 8 migration? - #9 by evgeni
The comment I linked you to is the first in a long list of posts about this topic which came up because the docs missed a needed reindex of the DB when upgrading from EL 7 to EL8.
While you are not using EL, it looks like you ran into the same problem. Try to fix this first (there’s a lot off suggestions on this in the thread) and then redo the DB dump and you should be good.
I assume this post specificly should help you with your problem: Possible to use Leapp for Oracle 7 -> Oracle 8 migration? - #11 by evgeni
create foreman db on psql12 and then run the command ro reindexdb
createdb foreman
runuser -u postgres -- reindexdb -a
now I get below Error, and the command run to the end:
3.
~$ pg_restore -d foreman foreman_10.dump
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 3705; 2606 17776 CONSTRAINT permissions permissions_pkey foreman
pg_restore: error: could not execute query: ERROR: could not create unique index "permissions_pkey"
DETAIL: Key (id)=(8) is duplicated.
Command was: ALTER TABLE ONLY public.permissions
ADD CONSTRAINT permissions_pkey PRIMARY KEY (id);
pg_restore: from TOC entry 3538; 1259 17698 INDEX index_fact_names_on_name_and_type foreman
pg_restore: error: could not execute query: ERROR: could not create unique index "index_fact_names_on_name_and_type"
DETAIL: Key (name, type)=(network_ISCSI Port 2, PuppetFactName) is duplicated.
Command was: CREATE UNIQUE INDEX index_fact_names_on_name_and_type ON public.fact_names USING btree (name, type);
pg_restore: from TOC entry 3900; 2606 17860 FK CONSTRAINT filterings filterings_permissions_id_fk foreman
pg_restore: error: could not execute query: ERROR: there is no unique constraint matching given keys for referenced table "permissions"
Command was: ALTER TABLE ONLY public.filterings
ADD CONSTRAINT filterings_permissions_id_fk FOREIGN KEY (permission_id) REFERENCES public.permissions(id);
pg_restore: warning: errors ignored on restore: 3
~$
From the looks of it, you tried to reindex the postgres 12 DB, which is useless since it should be somewhat empty (or at least not contain the problematic entries).
You will need to go back to postgres 10 first and then fix the DB entries the reindex is complaining about (taking the information from the thread I linked).
As mentioned, I don’t have much experience with Ubuntu, but something like systemctl stop postgres-12.service; systemctl start postgres-10.service (maybe the services are named differently on Ubuntu, you’ll need to figure that out yourself) should get you back.
Yes, reindexing might take some time, depending on the size of your setup.
Afterwards, a restart of the Foreman service should bring you back up. Once you confirm everything is working again, you can continue with the DB migration.
I restart foreman but now the default psql is version 12 and not 10!!
that mean foreman try to connect to psql12 which is not right! how can I change that?
I know im distrubing you but I dont know how to do that!
I am still confused what you mean by your Foreman defaulting to one postgresql version or the other. Foreman should simply connect to whatever database is currently running on your system, so if postgres 10 is running, it should connect to that, if 12 is running, it should connect to that instead.
Either elaborate more what’s happening on your system or just ensure via systemctl commands the correct database instance is running.
I would assume it shows the version of the psql client, but I cannot confirm that myself.
You can always query the actual DB version with show server_version; in the psql shell, so check that. There should be no (intended) way postgres 12 can be the running database if the service is stopped.