Howto upgrade to current postgres version

[ngiger]

Updating to the actual Postgres 17 version is more than just using pgupgrade.
pgupgrade is not problem for yus and migel, but oddb.org still has a lot of references to oid, which is no longer supported under postgres 12 and later, see releas notes 12.
We must answer the following questions:

• do we have to rename oid to another name?
• can we replace oid by odba_id?
• how can we migrate (maybe even dumping the whole to yaml and reloading in empty database). This could eliminate cruft we have in the database

[ngiger]

To remove easily see this tip.
There you find

SELECT 'ALTER TABLE "' || n.nspname || '"."' || c.relname || '" SET WITHOUT OIDS;'
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE 1=1
  AND c.relkind = 'r'
  AND c.relhasoids = true
  AND n.nspname <> 'pg_catalog' 
order by n.nspname, c.relname;

which for ch_oddb ID results in

 ALTER TABLE "public"."analysis_alphabetical_index_de" SET WITHOUT OIDS;
 ALTER TABLE "public"."analysis_alphabetical_index_fr" SET WITHOUT OIDS;
 ALTER TABLE "public"."analysis_index_de" SET WITHOUT OIDS;
 ALTER TABLE "public"."analysis_index_fr" SET WITHOUT OIDS;
 ALTER TABLE "public"."atc_index" SET WITHOUT OIDS;
 ALTER TABLE "public"."atc_index_company" SET WITHOUT OIDS;
 ALTER TABLE "public"."atcless" SET WITHOUT OIDS;
 ALTER TABLE "public"."collection" SET WITHOUT OIDS;
 ALTER TABLE "public"."company_index" SET WITHOUT OIDS;
 ALTER TABLE "public"."date_index_registration" SET WITHOUT OIDS;
 ALTER TABLE "public"."doctor_index" SET WITHOUT OIDS;
 ALTER TABLE "public"."fachinfo_index_de" SET WITHOUT OIDS;
 ALTER TABLE "public"."fachinfo_index_fr" SET WITHOUT OIDS;
 ALTER TABLE "public"."fachinfo_name_de" SET WITHOUT OIDS;
 ALTER TABLE "public"."fachinfo_name_fr" SET WITHOUT OIDS;
 ALTER TABLE "public"."hospital_index" SET WITHOUT OIDS;
 ALTER TABLE "public"."indication_index" SET WITHOUT OIDS;
 ALTER TABLE "public"."indication_index_atc_de" SET WITHOUT OIDS;
 ALTER TABLE "public"."indication_index_atc_fr" SET WITHOUT OIDS;
 ALTER TABLE "public"."interactions_index_de" SET WITHOUT OIDS;
 ALTER TABLE "public"."interactions_index_fr" SET WITHOUT OIDS;
 ALTER TABLE "public"."narcotics_de" SET WITHOUT OIDS;
 ALTER TABLE "public"."narcotics_fr" SET WITHOUT OIDS;
 ALTER TABLE "public"."object" SET WITHOUT OIDS;
 ALTER TABLE "public"."object_connection" SET WITHOUT OIDS;
 ALTER TABLE "public"."oddb_commercialform_name" SET WITHOUT OIDS;
 ALTER TABLE "public"."oddb_galenicform_name" SET WITHOUT OIDS;
 ALTER TABLE "public"."oddb_indextherapeuticus_code" SET WITHOUT OIDS;
 ALTER TABLE "public"."oddb_minifi_publication_date" SET WITHOUT OIDS;
 ALTER TABLE "public"."oddb_package_name_with_size" SET WITHOUT OIDS;
 ALTER TABLE "public"."oddb_package_name_with_size_company_name_and_ean13" SET WITHOUT OIDS;
 ALTER TABLE "public"."oddb_package_name_with_size_company_name_ean13_fi" SET WITHOUT OIDS;
 ALTER TABLE "public"."oddb_package_pharmacode" SET WITHOUT OIDS;
 ALTER TABLE "public"."sequence_index" SET WITHOUT OIDS;
 ALTER TABLE "public"."sequence_index_atc" SET WITHOUT OIDS;
 ALTER TABLE "public"."sequence_index_exact" SET WITHOUT OIDS;
 ALTER TABLE "public"."sequence_index_substance" SET WITHOUT OIDS;
 ALTER TABLE "public"."sequence_limitation_text" SET WITHOUT OIDS;
 ALTER TABLE "public"."sequence_patinfos" SET WITHOUT OIDS;
 ALTER TABLE "public"."sequence_vaccine" SET WITHOUT OIDS;
 ALTER TABLE "public"."substance_index" SET WITHOUT OIDS;
 ALTER TABLE "public"."substance_index_atc" SET WITHOUT OIDS;
 ALTER TABLE "public"."substance_index_sequence" SET WITHOUT OIDS;
 ALTER TABLE "public"."substance_soundex_index" SET WITHOUT OIDS;
 ALTER TABLE "public"."unwanted_effects_index_de" SET WITHOUT OIDS;
 ALTER TABLE "public"."unwanted_effects_index_fr" SET WITHOUT OIDS;

[ngiger]

Running bundle exec rspec spec/smoketest_spec.rb took 6'48" and had 13 errors.

rspec ./spec/smoketest_spec.rb:741 # ch.oddb.org should not display expired drugs, when search says active drugs only
rspec ./spec/smoketest_spec.rb[1:5] # ch.oddb.org limiting the search to search_limitation_A using Fosfolag
rspec ./spec/smoketest_spec.rb[1:6] # ch.oddb.org limiting the search to search_limitation_B using Allergo-X
rspec ./spec/smoketest_spec.rb[1:7] # ch.oddb.org limiting the search to search_limitation_D using Elmex
rspec ./spec/smoketest_spec.rb[1:8] # ch.oddb.org limiting the search to search_limitation_E using Holunder
rspec ./spec/smoketest_spec.rb[1:9] # ch.oddb.org limiting the search to search_limitation_SL_only using Soolantra 10 mg
rspec ./spec/smoketest_spec.rb[1:10] # ch.oddb.org limiting the search to search_limitation_SL_only using Methotrexat
rspec ./spec/smoketest_spec.rb[1:11] # ch.oddb.org limiting the search to search_limitation_SL_only using mephadolor
rspec ./spec/smoketest_spec.rb[1:12] # ch.oddb.org limiting the search to search_limitation_SL_only using Omeprazol MUT Sandoz
rspec ./spec/smoketest_spec.rb:809 # ch.oddb.org should display a result for homeopathy Similasan Arnica
rspec ./spec/smoketest_spec.rb:420 # ch.oddb.org desitin should have a valid link for a Preis Anfrage
rspec ./spec/smoketest_spec.rb[1:3:38] # ch.oddb.org desitin should have a working instant search for Inderal and going back
rspec ./spec/smoketest_spec.rb:693 # ch.oddb.org desitin should have a working status page RSpec::ExampleGroups::ChOddbOrg::Desitin

Dropped the column oid from yus and migel DB.
ch_oddb works as before.
Running bundle exec rspec spec/smoketest_spec.rb took now 6'29" and had 12 errors.

Finished in 6 minutes 29 seconds (files took 1.76 seconds to load)
87 examples, 12 failures, 31 pending
Failed examples:
rspec ./spec/smoketest_spec.rb:741 # ch.oddb.org should not display expired drugs, when search says active drugs only
rspec ./spec/smoketest_spec.rb[1:6] # ch.oddb.org limiting the search to search_limitation_B using Allergo-X
rspec ./spec/smoketest_spec.rb[1:7] # ch.oddb.org limiting the search to search_limitation_D using Elmex
rspec ./spec/smoketest_spec.rb[1:8] # ch.oddb.org limiting the search to search_limitation_E using Holunder
rspec ./spec/smoketest_spec.rb[1:9] # ch.oddb.org limiting the search to search_limitation_SL_only using Soolantra 10 mg
rspec ./spec/smoketest_spec.rb[1:10] # ch.oddb.org limiting the search to search_limitation_SL_only using Methotrexat
rspec ./spec/smoketest_spec.rb[1:12] # ch.oddb.org limiting the search to search_limitation_SL_only using Omeprazol MUT Sandoz
rspec ./spec/smoketest_spec.rb:809 # ch.oddb.org should display a result for homeopathy Similasan Arnica
rspec ./spec/smoketest_spec.rb:345 # ch.oddb.org desitin should trigger the limitation after maximal 5 queries
rspec ./spec/smoketest_spec.rb:420 # ch.oddb.org desitin should have a valid link for a Preis Anfrage
rspec ./spec/smoketest_spec.rb[1:3:38] # ch.oddb.org desitin should have a working instant search for Inderal and going back
rspec ./spec/smoketest_spec.rb:693 # ch.oddb.org desitin should have a working status page RSpec::ExampleGroups::ChOddbOrg::Desitin

[ngiger]

After dropping all oid columns in yus, migel and ch_oddb, the smoketest worked the same but finished in about 15% less time.

Finished in 5 minutes 31 seconds (files took 1.97 seconds to load)
87 examples, 12 failures, 31 pending
Failed examples:
rspec ./spec/smoketest_spec.rb:741 # ch.oddb.org should not display expired drugs, when search says active drugs only
rspec ./spec/smoketest_spec.rb[1:5] # ch.oddb.org limiting the search to search_limitation_A using Fosfolag
rspec ./spec/smoketest_spec.rb[1:6] # ch.oddb.org limiting the search to search_limitation_B using Allergo-X
rspec ./spec/smoketest_spec.rb[1:7] # ch.oddb.org limiting the search to search_limitation_D using Elmex
rspec ./spec/smoketest_spec.rb[1:8] # ch.oddb.org limiting the search to search_limitation_E using Holunder
rspec ./spec/smoketest_spec.rb[1:9] # ch.oddb.org limiting the search to search_limitation_SL_only using Soolantra 10 mg
rspec ./spec/smoketest_spec.rb[1:10] # ch.oddb.org limiting the search to search_limitation_SL_only using Methotrexat
rspec ./spec/smoketest_spec.rb:809 # ch.oddb.org should display a result for homeopathy Similasan Arnica
rspec ./spec/smoketest_spec.rb:345 # ch.oddb.org desitin should trigger the limitation after maximal 5 queries
rspec ./spec/smoketest_spec.rb:420 # ch.oddb.org desitin should have a valid link for a Preis Anfrage
rspec ./spec/smoketest_spec.rb[1:3:38] # ch.oddb.org desitin should have a working instant search for Inderal and going back
rspec ./spec/smoketest_spec.rb:693 # ch.oddb.org desitin should have a working status page RSpec::ExampleGroups::ChOddbOrg::Desitin

[ngiger]

Created https://dev.ywesee.com/Niklaus/20260112-pgUpgrade
The following two script can be easily adapted to run the upgrade under Ubuntu/gentoo

• prepare_pg_upgrade.fish
• run_pg_upgrade