Upgrade Foreman 3.6 to 3.7 but Postgresql 10

[areyus]

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:

1. foreman-installer shuts down all services when it starts
2. foreman-installer applies all the Puppet modules, which in turn starts PostgreSQL 12
3. foreman-rake performs DB migrations etc, which would run on the PostgreSQL 12 instance that’s running
4. 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.

[Neeloj]

thank you so much @areyus , very clear answer and helpful .

@areyus

puppetlabs-postgresql defaults to using PostgreSQL 12 on Ubuntu 20.04

This happens to me in the past, thats why im afraid now to upgrade to Foreman 3.7.

@areyus

My expectation what would happen in a default scenario would be:

1. foreman-installer shuts down all services when it starts
2. foreman-installer applies all the Puppet modules, which in turn starts PostgreSQL 12
3. foreman-rake performs DB migrations etc, which would run on the PostgreSQL 12 instance that’s running
4. You end up with a broken setup

You are absolute right this is what happens!

@areyus

If migrationg to PostgreSQL 12 is an option for you, I would recommend doing that, primarily to eliminate possible problems in the future.

This is best choose but how can I migrate all data from psotgresql 10 to postgresql 12 ?

So I’ll do like below:
pg_dumpall > backup.sql
psql -h localhost -U username -d postgres -f backup.sql

is that enough for all data ?

[areyus]

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.

[Neeloj]

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

[Neeloj]

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);

thanks in advance

[areyus]

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

[Neeloj]

@areyus

Try to fix this first (there’s a lot off suggestions on this in the thread)

thanks for your answer @areyus , I’ll first fix the indexing
runuser -u postgres -- reindexdb -a

and then run the pg_restore again.

[Neeloj]

so what I did:

create foreman db on psql12 and then run the command ro reindexdb

1. createdb foreman
2. 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
~$

Any idea ? @areyus

[Neeloj]

@Neeloj

1. runuser -u postgres -- reindexdb -a

after this step, when I go want go back to psql 10 it doesnt work:

~$ psql -d foreman
psql (12.16 (Ubuntu 12.16-0ubuntu0.20.04.1), server 10.20 (Ubuntu 10.20-0ubuntu0.18.04.1))
Type "help" for help

it take me to psql-12 and not to psql-10 how can I restore this until I migrate the 10 to 12 ?

because nothing is working now

[areyus]

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.

[Neeloj]

thanks @areyus yes you are right they have different name:

# systemctl stop postgresql@12-main.service ; systemctl start postgresql@10-main.service
# runuser -u postgres -- reindexdb -a
reindexdb: reindexing database "foreman"
reindexdb: error: reindexing of database "foreman" failed: ERROR:  could not create unique index "index_fact_names_on_name_and_type"
DETAIL:  Key (name, type)=(network_ISCSI Port 2, PuppetFactName) is duplicated.
(KAMP-PPT-SRV)root@atlas79:/mnt/Customers#

My foreman is down now and its really bad… can you give me any advice ?

[Neeloj]

I tun now

# cat << EOF | foreman-rake console
> conf.echo = false
> fact_name_ids = FactName.unscoped.where(:type => 'PuppetFactName').ids
> pp fact_name_ids.count
> FactValue.unscoped.where(:fact_name_id => fact_name_ids).delete_all
> FactName.unscoped.where(:id => fact_name_ids).delete_all
> pp FactName.unscoped.where(:type => 'PuppetFactName').ids.count
> EOF
Loading production environment (Rails 6.1.7.4)
Switch to inspect mode.
conf.echo = false
fact_name_ids = FactName.unscoped.where(:type => 'PuppetFactName').ids
pp fact_name_ids.count
3580
FactValue.unscoped.where(:fact_name_id => fact_name_ids).delete_all
FactName.unscoped.where(:id => fact_name_ids).delete_all
pp FactName.unscoped.where(:type => 'PuppetFactName').ids.count
0

and then
# runuser -u postgres -- reindexdb -a

Now I have to wait until is finish, as I read in other article it should take to much time

[Neeloj]

it was fast:

# runuser -u postgres -- reindexdb -a
reindexdb: reindexing database "foreman"
reindexdb: reindexing database "postgres"
reindexdb: reindexing database "template1"

what should I do now?

$ pg_restore -d foreman foreman_10.dump to should I create new dump ?

[areyus]

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.

[Neeloj]

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!

[areyus]

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.

[areyus]

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.

[Neeloj]

@areyus

show server_version;

foreman=# show server_version;
            server_version             
---------------------------------------
 12.16 (Ubuntu 12.16-0ubuntu0.20.04.1)
(1 row)

Its version 12, but what I cannot understand its, after I change it back to version 10 and I run

# runuser -u postgres -- reindexdb -a

it goes automatically to version 12! I dont know why!

[areyus]

To be honest, I have no idea how that’s happening

[Neeloj]

same here ehmmm I’ll restart the server and then check all again