< Niklaus.20180123-oddb-fixes | Index | Niklaus.20180122-postgres10 >>
Saved the /etc/init.d/postgresql-10.1 as Attach:postgresql_10_1.txt
The following changes were done to enable a test instance using postgresql 10. Add the following lines in /etc/yus/yus.yml
db_name: ';dbname=yus;host=localhost;port=5433' cleaner_interval: 300 server_url: drbssl://localhost:19997
Loading the dump created yesterday
su export PATH=/usr/local/pg-10_1/bin:$PATH export LD_LIBRARY_PATH=/usr/local/pg-10_1/lib:$LD_LIBRARY_PATH /usr/local/pg-10_1/bin/psql -U postgres -p 5433 -h localhost --command="create database yus;" zcat /home/ywesee/migration/yus.gz | /usr/local/pg-10_1/bin/psql -U postgres -p 5433 -h localhost yus sudo -u apache /usr/local/bin/ruby-250 /usr/local/ruby-2.5.0/bin/yusd <..> I, [2018-01-23T08:24:22.464754 #8050] INFO -- start: starting yus-server on drbssl://localhost:19997
This seems to be okay. Therefore I will now stop the service, create dump from pg 8.3, load dump into pg 10.1. use old port 9997 and restart the server
/usr/local/pg-10_1/bin/psql -U postgres -p 5433 -h localhost --command="drop database yus;" /usr/local/pg-10_1/bin/psql -U postgres -p 5433 -h localhost --command="create database yus;" svc -d /service/yus /usr/local/pg-10_1/bin/pg_dump -U postgres -p 5432 -h localhost --clean --if-exists yus | gzip > /home/ywesee/migration/yus.gz zcat /home/ywesee/migration/yus.gz | /usr/local/pg-10_1/bin/psql -U postgres -p 5433 -h localhost yus svc -u /service/yus tail -f /service/yus/log/main/current # login via ch.oddb.org and verifying aht yus reacts /usr/bin/psql -U postgres -p 5432 -h localhost --command="drop database yus;"
This seems to work fine.
Now working on migel.
The following changes were done to enable a test instance using postgresql 10. Add the following lines in /etc/migel/migel.yml
db_name: ';dbname=migel;host=localhost;port=5433' server_url: drbssl://localhost:33001 # instead of 33000
/usr/local/pg-10_1/bin/psql -U postgres -p 5433 -h localhost --command="drop database migel;" /usr/local/pg-10_1/bin/psql -U postgres -p 5433 -h localhost --command="create database migel;" /usr/local/pg-10_1/bin/pg_dump -U postgres -p 5432 -h localhost --clean --if-exists "migel.ruby193" | gzip > /home/ywesee/migration/migel.gz # zcat /home/ywesee/migration/migel.gz | /usr/local/pg-10_1/bin/psql -U postgres -p 5433 -h localhost "migel.ruby193" # ALTER DATABASE name RENAME TO new_name
After trying to load on oddb-ci2 migel with the new database name, I got
KeyError at /de/gcc/search/zone/migel/search_query/Binde key not found: "migel_fulltext_index_de" Did you mean? "migel_fulltext_index_fr"
whereas searching in french for drainage worked fine. Also getting the key error when searching in english (but only german names are displaye in alphabetical list).
Okay. Investigating the problem on thinpower shows, that the index target_id_migel_fulltext_index_fr exists only in database migel. Not migel.ruby193. Must use database migel.
migel.ruby193=# \di target_id_migel_fulltext_index_fr
Liste der Relationen
Schema | Name | Typ | Eigentümer | Tabelle
--------+-----------------------------------+-------+------------+-------------------------
public | target_id_migel_fulltext_index_fr | Index | postgres | migel_fulltext_index_fr
(1 Zeile)
migel.ruby193=# \di target_id_migel_fulltext_index_de
Keine passenden Relationen gefunden
<now on migel>
igel=# \di target_id_migel_fulltext_index_de
Liste der Relationen
Schema | Name | Typ | Eigentümer | Tabelle
--------+-----------------------------------+-------+------------+-------------------------
public | target_id_migel_fulltext_index_de | Index | migel | migel_fulltext_index_de
(1 Zeile)
migel=# \di target_id_migel_fulltext_index_fr
Liste der Relationen
Schema | Name | Typ | Eigentümer | Tabelle
--------+-----------------------------------+-------+------------+-------------------------
public | target_id_migel_fulltext_index_fr | Index | migel | migel_fulltext_index_fr
(1 Zeile)
sudo -u apache bundle-250 exec ruby-250 bin/migeld had error . Must reload correct gems in /var/www/migel using
cd /var/www/migel git checkout Gemfile Gemfile.lock bundle-250 config build.pg --with-pg-config=/usr/local/pg-10_1/bin/pg_config rm -rf vendor/ bundle-250 install --without development <..> sudo -u apache bundle-250 exec ruby-250 bin/migeld I, [2018-01-23T10:07:31.480389 #30622] INFO -- start: starting migel-server on druby://localhost:33001
Now it is time to shutdown the service and restartit using pg 10.1.
svc -d /service/migeld /usr/local/pg-10_1/bin/psql -U postgres -p 5433 -h localhost --command="drop database migel;" /usr/local/pg-10_1/bin/psql -U postgres -p 5433 -h localhost --command="create database migel;" /usr/local/pg-10_1/bin/pg_dump -U postgres -p 5432 -h localhost --clean --if-exists migel | gzip > /home/ywesee/migration/migel.gz /usr/local/pg-10_1/bin/psql -U postgres -p 5433 -h localhost --command="create database migel;" zcat /home/ywesee/migration/migel.gz | /usr/local/pg-10_1/bin/psql -U postgres -p 5433 -h localhost migel svc -u /service/migeld tail -f /service/migeld/log/main/current # @400000005a66fd3c2194ee74 I, [2018-01-23T10:15:30.563224 #32506] INFO -- start: starting migel-server on druby://localhost:33000 # Checking migel via ch.oddb.org is okay /usr/bin/psql -U postgres -p 5432 -h localhost --command='drop database migel;' /usr/bin/psql -U postgres -p 5432 -h localhost --command='drop database "migel.ruby193";'
First checking whether new service will start.
Setting db connection in /var/www/oddb.org/etc/db_connection.rb to ODBA.storage.dbi = ODBA::ConnectionPool.new('DBI:Pg:dbname=oddb.org;host=localhost;port=5433', 'postgres', '')
cd /var/www/oddb.org git pull https://github.com/ngiger/oddb.org.git bundle-250 config build.pg --with-pg-config=/usr/local/pg-10_1/bin/pg_config bundle-250 install --without development --without test --without debugger su export PATH=/usr/local/pg-10_1/bin:$PATH export LD_LIBRARY_PATH=/usr/local/pg-10_1/lib:$LD_LIBRARY_PATH time /usr/local/pg-10_1/bin/pg_dump -U postgres -p 5432 -h localhost --clean --if-exists "oddb.org.ruby193" | gzip > /home/ywesee/migration/oddb.org.gz # This takes a long time (around 15 minutes) as the resulting file is around 1.8 GB /usr/local/pg-10_1/bin/psql -U postgres -p 5433 -h localhost --command='drop database "oddb.org";' /usr/local/pg-10_1/bin/psql -U postgres -p 5433 -h localhost --command='create database "oddb.org";' zcat /home/ywesee/migration/oddb.org.gz | /usr/local/pg-10_1/bin/psql -U postgres -p 5433 -h localhost oddb.org sudo -u apache /usr/local/bin/bundle-250 exec rackup -p 8812 # This worked fine at 13:28
Full migration will need these steps
cd /var/www/oddb.org su export PATH=/usr/local/pg-10_1/bin:$PATH export LD_LIBRARY_PATH=/usr/local/pg-10_1/lib:$LD_LIBRARY_PATH cd /var/www/oddb.org /usr/local/pg-10_1/bin/psql -U postgres -p 5433 -h localhost --command='drop database "ch_oddb";' /usr/local/pg-10_1/bin/psql -U postgres -p 5433 -h localhost --command='create database "ch_oddb";' svc -d /service/ch.oddb* /usr/local/pg-10_1/bin/pg_dump -U postgres -p 5432 -h localhost --clean --if-exists oddb.org.ruby193 | gzip > /home/ywesee/migration/ch_oddb.gz /usr/local/pg-10_1/bin/psql -U postgres -p 5433 -h localhost --command='create database "ch_oddb";' zcat /home/ywesee/migration/ch_oddb.gz | /usr/local/pg-10_1/bin/psql -U postgres -p 5433 -h localhost ch_oddb svc -u /service/ch.oddb* tail -f /service/ch.oddb/log/main/current # login via ch.oddb.org and verifying that ch.oddb reacts correctly /usr/bin/psql -U postgres -p 5432 -h localhost --command='drop database "oddb.org.ruby193";'
Loading the database dump from pg 8.3 took 8,25 minutes on oddb-ci2. On thinpower it took over 40 minutes. Loading a newly created pg 10 dump took 8 minute on oddb-ci2.
Pushed my two remaining patches for postgres 10
Just remarked the following error in import_daily
2018-01-23 10:43:50 +0100: /var/www/oddb.org/src/plugin/text_info.rb:323:in `update_patinfo_lang': update_patinfo_lang #<struct Struct::SwissmedicMetaInfo iksnr="66837", authNrs=["66837"], atcCode=nil, title="Dropa pastilles pour les bronches avec codéine", authHolder="DR. BÄHLER DROPA AG", substances="Codeini phosphas hemihydricus (Mono)", type="pi", lang="fr", informationUpdate="01.2016", refdata=nil, xml_file="/var/www/oddb.org/data/details/66837_pi_fr_Dropa_pastilles_pour_les_bronches_avec_cod_ine_problem.xml", same_content_as_xml_file=false> ["fr"] ArgumentError: wrong number of arguments (given 4, expected 5) when updating index 'fachinfo_index_de' with a ODDB::Fachinfo ["/var/www/oddb.org/vendor/ruby/2.5.0/gems/odba-1.1.4/lib/odba/storage.rb:596:in `update_fulltext_index'", "/var/www/oddb.org/vendor/ruby/2.5.0/gems/odba-1.1.4/lib/odba/index.rb:388:in `do_update_index'", "/var/www/oddb.org/vendor/ruby/2.5.0/gems/odba-1.1.4/lib/odba/index.rb:190:in `block (2 levels) in update_origin'", "/var/www/oddb.org/vendor/ruby/2.5.0/gems/odba-1.1.4/lib/odba/index.rb:189:in `each'"] [...]
This must be fixed, soon. Fixed the error in odba with Fix error in update_fulltext_index. Fix in oddb.org is Updated to odba 1.1.5. Fix unit test in address2.rb
Added a pry debug and launching sudo -u apache bundle exec ruby-250 jobs/update_textinfo_swissmedicinfo fi reparse 66837
Dropping old databases from thinpower (pg 8.3) with the following psql commands
postgres=# drop database ch_oddb_org; DROP DATABASE postgres=# drop database xmlconv2; DROP DATABASE postgres=# drop database trac_db; DROP DATABASE postgres=# drop database oddb_dot_org; DROP DATABASE postgres=# drop database globomedica; DROP DATABASE postgres=# drop database globopharm_xmlconv; DROP DATABASE postgres=# drop database oddb; DROP DATABASE # The databases holidaymanager and ydpm could not be dropped postgres=# drop database holidaymanager; FEHLER: auf Datenbank »holidaymanager« wird von anderen Benutzern zugegriffen postgres=# drop database ydpm; FEHLER: auf Datenbank »ydpm« wird von anderen Benutzern zugegriffen
Must remove the services ydpm ydpm-holidaymanager. Afterwards it was possible to drop the two databases.
Parsing the XMLPublications.zip shows another error on oddb-ci2:
ODBA::Stub was unable to replace #1470346 from ODDB::Package:#1239004 Could not create: :!!sequence,01!package,001!sl_entry!limitation_text., reason: :!!sequence,01!package,001!sl_entry!limitation_text. -> undefined Method OddbPrevalence::66380() NoMethodError: undefined method `active_sequences' for #<ODDB::Substance:0x000055bce5c1d020> when updating index 'substance_index_sequence' with a ODDB::Substance ["/var/www/oddb.org/src/util/language.rb:53:in `method_missing'", "(eval):2:in `block in proc_instance_target'", "/var/www/oddb.org/vendor/ruby/2.5.0/gems/odba-1.1.5/lib/odba/index.rb:248:in `update_origin'", "/var/www/oddb.org/vendor/ruby/2.5.0/gems/odba-1.1.5/lib/odba/index.rb:162:in `update'"] [...] NoMethodError: undefined method `active_sequences' for #<ODDB::Substance:0x000055bce5c1d020> when updating index 'substance_index_sequence' with a ODDB::Substance ["/var/www/oddb.org/src/util/language.rb:53:in `method_missing'", "(eval):2:in `block in proc_instance_target'", "/var/www/oddb.org/vendor/ruby/2.5.0/gems/odba-1.1.5/lib/odba/index.rb:248:in `update_origin'", "/var/www/oddb.org/vendor/ruby/2.5.0/gems/odba-1.1.5/lib/odba/index.rb:162:in `update'"] [...] Could not create: :!registration,58388!sequence,01!005!part., reason: :!registration,58388!sequence,01!005!part. -> ODDB::Sequence::package(005) returned nil Could not create: :!registration,58388!sequence,01!006!part., reason: :!registration,58388!sequence,01!006!part. -> ODDB::Sequence::package(006) returned nil Could not create: :!registration,58388!sequence,01!005!sl_entry., reason: :!registration,58388!sequence,01!005!sl_entry. -> ODDB::Sequence::package(005) returned nil Could not create: :!registration,58388!sequence,01!006!sl_entry., reason: :!registration,58388!sequence,01!006!sl_entry. -> ODDB::Sequence::package(006) returned nil
Having a look at /usr/local/sbin/ywesee-backup , which in turn calls /usr/local/sbin/pg_backup.sh bv. This looks like copied from somewhere, author is called speedboy. Last updated is 16.01.2001. We find a newer version (as of 2014) under https://gist.github.com/spiderr/546865. It is documented under http://www.bitweaver.org/wiki/pg_backup+PostgreSQL+backup+script.
The only thing to change is changing the path to the postgres binary (variable location_binaries) from /usr/bin/ to /usr/local/pg-10_1/bin/
The backup of postgres goes to subdirectories (month-year/date) of /var/backup/thinpower/db/postgresql/. The destination is the same for Postgres 8.3 and 10.1. Logfiles older than 5 days are automatically deleted.
Found another problem with the version 1.0.0 of pg. PGconn was replaced by PG::Connection.
Added line db_driver_url: 'DBI:Pg:dbname=ydim;host=localhost;port=5433' in /etc/ydim/ydimd.yml
Commands will be
su export PATH=/usr/local/pg-10_1/bin:$PATH export LD_LIBRARY_PATH=/usr/local/pg-10_1/lib:$LD_LIBRARY_PATH cd /var/www/ydim.ywesee.com git pull https://github.com/ngiger/ydim-html.git /usr/local/bin/bundle-250 config build.pg --with-pg-config=/usr/local/pg-10_1/bin/pg_config /usr/local/bin/bundle-250 install sudo -u apache /usr/local/bin/bundle-250 exec rackup -p 8812 /usr/local/pg-10_1/bin/psql -U postgres -p 5433 -h localhost --command="drop database ydim;" /usr/local/pg-10_1/bin/psql -U postgres -p 5433 -h localhost --command="create database ydim;" svc -d /service/ydim* /usr/local/pg-10_1/bin/pg_dump -U postgres -p 5432 -h localhost --clean --if-exists ydim | gzip > /home/ywesee/migration/ydim.gz zcat /home/ywesee/migration/ydim.gz | /usr/local/pg-10_1/bin/psql -U postgres -p 5433 -h localhost ydim svc -u /service/ydim* tail -f /service/ydim*/log/main/current # login via ch.ydim.org and verifying that ch.ydim reacts correctly /usr/bin/psql -U postgres -p 5432 -h localhost --command="drop database ydim;"
Pushed commits
Now oddb-ci2 works fine with ydim-html.
Could stop the PG 8.3 server using /etc/init.d/postgresql-8.3 stop
Cleaning up unused services rm -rf /var/www/ydpm.ywesee.com