Re: [lms] upgrade z wersji 1.11

11 Wrz 2018


      OK, to dla potomnych migracja z MySQL do PostgreSQL, obecna wersjia LMS git
mysqldump --no-create-info --skip-triggers --compact
--compatible=postgresql --skip-opt --complete-insert --skip-quote-names
--extended-insert -p lms users countries location_states location_districts
location_boroughs location_cities location_street_types location_streets
location_buildings addresses divisions customers numberplans states
zipcodes customer_addresses documents documentcontents documentattachments
cashregs receiptcontents taxes voipaccounts voip_rule_groups
voip_prefix_groups voip_rules voip_tariffs voip_rule_states voip_prefixes
voip_cdr voip_price_groups tariffs voip_numbers voip_pool_numbers
voip_emergency_numbers liabilities assignments voip_number_assignments
invoicecontents debitnotecontents cashsources sourcefiles cashimport cash
pna ewx_channels ewx_stm_channels hosts networks invprojects netnodes
netdeviceproducers netdevicemodels netdevices netradiosectors nodes
ewx_stm_nodes nodelocks macs nodegroups nodegroupassignments
nodeassignments tarifftags tariffassignments promotions promotionschemas
promotionassignments payments numberplanassignments customergroups
customerassignments nodesessions stats netlinks rtqueues rttickets
rtmessages rtrights rtattachments rtcategories rtcategoryusers
rtticketcategories rtqueuecategories domains passwd records domainmetadata
supermasters comments cryptokeys tsigkeys aliases aliasassignments uiconfig
events eventassignments sessions daemoninstances daemonconfig docrights
cashrights cashreglog ewx_pt_config dbinfo customercontacts excludedgroups
messages messageitems nastypes managementurls logtransactions logmessages
logmessagekeys logmessagedata templates usergroups userassignments
passwdhistory up_rights up_rights_assignments up_customers up_help
up_info_changes > /tmp/lms.sql
sed -i -e "s/\'/''/gm" /tmp/lms.sql
sed -i -e "s/\'/''/gm" /tmp/lms.sql
sed -i -e 's/\"/""/gm' /tmp/lms.sql
psql -d lms -U lms -f /tmp/lms.sql
no i pozostało zaktualizować sekwencje
SELECT setval('users_id_seq', coalesce((select max(id) from users), 1),
true);
SELECT setval('countries_id_seq', coalesce((select max(id) from countries),
1), true);
SELECT setval('location_states_id_seq', coalesce((select max(id) from
location_states), 1), true);
SELECT setval('location_districts_id_seq', coalesce((select max(id) from
location_districts), 1), true);
SELECT setval('location_boroughs_id_seq', coalesce((select max(id) from
location_boroughs), 1), true);
SELECT setval('location_cities_id_seq', coalesce((select max(id) from
location_cities), 1), true);
SELECT setval('location_street_types_id_seq', coalesce((select max(id) from
location_street_types), 1), true);
SELECT setval('location_streets_id_seq', coalesce((select max(id) from
location_streets), 1), true);
SELECT setval('location_buildings_id_seq', coalesce((select max(id) from
location_buildings), 1), true);
SELECT setval('addresses_id_seq', coalesce((select max(id) from addresses),
1), true);
SELECT setval('divisions_id_seq', coalesce((select max(id) from divisions),
1), true);
SELECT setval('customers_id_seq', coalesce((select max(id) from customers),
1), true);
SELECT setval('numberplans_id_seq', coalesce((select max(id) from
numberplans), 1), true);
SELECT setval('states_id_seq', coalesce((select max(id) from states), 1),
true);
SELECT setval('zipcodes_id_seq', coalesce((select max(id) from zipcodes),
1), true);
SELECT setval('customer_addresses_id_seq', coalesce((select max(id) from
customer_addresses), 1), true);
SELECT setval('documents_id_seq', coalesce((select max(id) from documents),
1), true);
SELECT setval('documentattachments_id_seq', coalesce((select max(id) from
documentattachments), 1), true);
SELECT setval('cashregs_id_seq', coalesce((select max(id) from cashregs),
1), true);
SELECT setval('taxes_id_seq', coalesce((select max(id) from taxes), 1),
true);
SELECT setval('voipaccounts_id_seq', coalesce((select max(id) from
voipaccounts), 1), true);
SELECT setval('voip_rule_groups_id_seq', coalesce((select max(id) from
voip_rule_groups), 1), true);
SELECT setval('voip_prefix_groups_id_seq', coalesce((select max(id) from
voip_prefix_groups), 1), true);
SELECT setval('voip_rules_id_seq', coalesce((select max(id) from
voip_rules), 1), true);
SELECT setval('voip_tariffs_id_seq', coalesce((select max(id) from
voip_tariffs), 1), true);
SELECT setval('voip_rule_states_id_seq', coalesce((select max(id) from
voip_rule_states), 1), true);
SELECT setval('voip_prefixes_id_seq', coalesce((select max(id) from
voip_prefixes), 1), true);
SELECT setval('voip_cdr_id_seq', coalesce((select max(id) from voip_cdr),
1), true);
SELECT setval('voip_price_groups_id_seq', coalesce((select max(id) from
voip_price_groups), 1), true);
SELECT setval('tariffs_id_seq', coalesce((select max(id) from tariffs), 1),
true);
SELECT setval('voip_numbers_id_seq', coalesce((select max(id) from
voip_numbers), 1), true);
SELECT setval('voip_pool_numbers_id_seq', coalesce((select max(id) from
voip_pool_numbers), 1), true);
SELECT setval('liabilities_id_seq', coalesce((select max(id) from
liabilities), 1), true);
SELECT setval('assignments_id_seq', coalesce((select max(id) from
assignments), 1), true);
SELECT setval('voip_number_assignments_id_seq', coalesce((select max(id)
from voip_number_assignments), 1), true);
SELECT setval('debitnotecontents_id_seq', coalesce((select max(id) from
debitnotecontents), 1), true);
SELECT setval('cashsources_id_seq', coalesce((select max(id) from
cashsources), 1), true);
SELECT setval('sourcefiles_id_seq', coalesce((select max(id) from
sourcefiles), 1), true);
SELECT setval('cashimport_id_seq', coalesce((select max(id) from
cashimport), 1), true);
SELECT setval('cash_id_seq', coalesce((select max(id) from cash), 1), true);
SELECT setval('pna_id_seq', coalesce((select max(id) from pna), 1), true);
SELECT setval('ewx_channels_id_seq', coalesce((select max(id) from
ewx_channels), 1), true);
SELECT setval('ewx_stm_channels_id_seq', coalesce((select max(id) from
ewx_stm_channels), 1), true);
SELECT setval('hosts_id_seq', coalesce((select max(id) from hosts), 1),
true);
SELECT setval('networks_id_seq', coalesce((select max(id) from networks),
1), true);
SELECT setval('invprojects_id_seq', coalesce((select max(id) from
invprojects), 1), true);
SELECT setval('netnodes_id_seq', coalesce((select max(id) from netnodes),
1), true);
SELECT setval('netdeviceproducers_id_seq', coalesce((select max(id) from
netdeviceproducers), 1), true);
SELECT setval('netdevicemodels_id_seq', coalesce((select max(id) from
netdevicemodels), 1), true);
SELECT setval('netdevices_id_seq', coalesce((select max(id) from
netdevices), 1), true);
SELECT setval('netradiosectors_id_seq', coalesce((select max(id) from
netradiosectors), 1), true);
SELECT setval('nodes_id_seq', coalesce((select max(id) from nodes), 1),
true);
SELECT setval('ewx_stm_nodes_id_seq', coalesce((select max(id) from
ewx_stm_nodes), 1), true);
SELECT setval('nodelocks_id_seq', coalesce((select max(id) from nodelocks),
1), true);
SELECT setval('macs_id_seq', coalesce((select max(id) from macs), 1), true);
SELECT setval('nodegroups_id_seq', coalesce((select max(id) from
nodegroups), 1), true);
SELECT setval('nodegroupassignments_id_seq', coalesce((select max(id) from
nodegroupassignments), 1), true);
SELECT setval('nodeassignments_id_seq', coalesce((select max(id) from
nodeassignments), 1), true);
SELECT setval('tarifftags_id_seq', coalesce((select max(id) from
tarifftags), 1), true);
SELECT setval('tariffassignments_id_seq', coalesce((select max(id) from
tariffassignments), 1), true);
SELECT setval('promotions_id_seq', coalesce((select max(id) from
promotions), 1), true);
SELECT setval('promotionschemas_id_seq', coalesce((select max(id) from
promotionschemas), 1), true);
SELECT setval('promotionassignments_id_seq', coalesce((select max(id) from
promotionassignments), 1), true);
SELECT setval('payments_id_seq', coalesce((select max(id) from payments),
1), true);
SELECT setval('numberplanassignments_id_seq', coalesce((select max(id) from
numberplanassignments), 1), true);
SELECT setval('customergroups_id_seq', coalesce((select max(id) from
customergroups), 1), true);
SELECT setval('customerassignments_id_seq', coalesce((select max(id) from
customerassignments), 1), true);
SELECT setval('nodesessions_id_seq', coalesce((select max(id) from
nodesessions), 1), true);
SELECT setval('netlinks_id_seq', coalesce((select max(id) from netlinks),
1), true);
SELECT setval('rtqueues_id_seq', coalesce((select max(id) from rtqueues),
1), true);
SELECT setval('rttickets_id_seq', coalesce((select max(id) from rttickets),
1), true);
SELECT setval('rtmessages_id_seq', coalesce((select max(id) from
rtmessages), 1), true);
SELECT setval('rtrights_id_seq', coalesce((select max(id) from rtrights),
1), true);
SELECT setval('rtcategories_id_seq', coalesce((select max(id) from
rtcategories), 1), true);
SELECT setval('rtcategoryusers_id_seq', coalesce((select max(id) from
rtcategoryusers), 1), true);
SELECT setval('rtticketcategories_id_seq', coalesce((select max(id) from
rtticketcategories), 1), true);
SELECT setval('rtqueuecategories_id_seq', coalesce((select max(id) from
rtqueuecategories), 1), true);
SELECT setval('domains_id_seq', coalesce((select max(id) from domains), 1),
true);
SELECT setval('passwd_id_seq', coalesce((select max(id) from passwd), 1),
true);
SELECT setval('records_id_seq', coalesce((select max(id) from records), 1),
true);
SELECT setval('supermasters_id_seq', coalesce((select max(id) from
supermasters), 1), true);
SELECT setval('comments_id_seq', coalesce((select max(id) from comments),
1), true);
SELECT setval('cryptokeys_id_seq', coalesce((select max(id) from
cryptokeys), 1), true);
SELECT setval('tsigkeys_id_seq', coalesce((select max(id) from tsigkeys),
1), true);
SELECT setval('aliases_id_seq', coalesce((select max(id) from aliases), 1),
true);
SELECT setval('aliasassignments_id_seq', coalesce((select max(id) from
aliasassignments), 1), true);
SELECT setval('uiconfig_id_seq', coalesce((select max(id) from uiconfig),
1), true);
SELECT setval('events_id_seq', coalesce((select max(id) from events), 1),
true);
SELECT setval('daemoninstances_id_seq', coalesce((select max(id) from
daemoninstances), 1), true);
SELECT setval('daemonconfig_id_seq', coalesce((select max(id) from
daemonconfig), 1), true);
SELECT setval('docrights_id_seq', coalesce((select max(id) from docrights),
1), true);
SELECT setval('cashrights_id_seq', coalesce((select max(id) from
cashrights), 1), true);
SELECT setval('cashreglog_id_seq', coalesce((select max(id) from
cashreglog), 1), true);
SELECT setval('ewx_pt_config_id_seq', coalesce((select max(id) from
ewx_pt_config), 1), true);
SELECT setval('customercontacts_id_seq', coalesce((select max(id) from
customercontacts), 1), true);
SELECT setval('excludedgroups_id_seq', coalesce((select max(id) from
excludedgroups), 1), true);
SELECT setval('messages_id_seq', coalesce((select max(id) from messages),
1), true);
SELECT setval('messageitems_id_seq', coalesce((select max(id) from
messageitems), 1), true);
SELECT setval('nastypes_id_seq', coalesce((select max(id) from nastypes),
1), true);
SELECT setval('managementurls_id_seq', coalesce((select max(id) from
managementurls), 1), true);
SELECT setval('logtransactions_id_seq', coalesce((select max(id) from
logtransactions), 1), true);
SELECT setval('logmessages_id_seq', coalesce((select max(id) from
logmessages), 1), true);
SELECT setval('templates_id_seq', coalesce((select max(id) from templates),
1), true);
SELECT setval('usergroups_id_seq', coalesce((select max(id) from
usergroups), 1), true);
SELECT setval('userassignments_id_seq', coalesce((select max(id) from
userassignments), 1), true);
SELECT setval('passwdhistory_id_seq', coalesce((select max(id) from
passwdhistory), 1), true);
SELECT setval('up_rights_id_seq', coalesce((select max(id) from up_rights),
1), true);
SELECT setval('up_rights_assignments_id_seq', coalesce((select max(id) from
up_rights_assignments), 1), true);
SELECT setval('up_customers_id_seq', coalesce((select max(id) from
up_customers), 1), true);
SELECT setval('up_help_id_seq', coalesce((select max(id) from up_help), 1),
true);
SELECT setval('up_info_changes_id_seq', coalesce((select max(id) from
up_info_changes), 1), true);
Może się komuś przyda
W dniu 7 września 2018 17:18 użytkownik Marcin marcin@nicram.net napisał:
...
pt., 7 wrz 2018, 16:25 użytkownik Tomasz Chiliński <
tomasz.chilinski@chilan.com> napisał:
...
W dniu 07.09.2018 14:56, Marcin napisał(a):
...
W dniu 14 sierpnia 2018 14:29 użytkownik Tomasz Chiliński
tomasz.chilinski@chilan.com napisał:
...
W dniu 14.08.2018 14:07, Dariusz Raczkowski napisał(a):
...
ok, jak mogę migrować bazę lmsa do postgresql w miarę
bezboleśnie?
Zrobić eksport rekordów w postaci INSERT INTO w postaci jak
najmniej mysqlowej
(mysqldump pozwala na to) i ładować to do czystej bazy (z samym
schematem) pgsql.
No tak łatwo nie jest. Jest problem z kluczami obcymi, z NULLami mimo
ustawienia opcji --compatible=postgresql
Kilkadziesiąt razy to robiłem już i zawsze idzie sprawnie.
Dużo oczywiście zależy czy jest dużo lokalnych modyfikacji schematu
względem
wersji waniliowej.
Hmm, to jakich opcji dla mysqldump używasz? Bo jak zrzucalem to zawsze
miałem problemy z importem, głównie z kluczami obcymi. Baza "waniliowa" bez
dodatków.
Robiłem dumpa tabel na poszczególne pliki i importowałem w odpowiedniej
kolejności.
...
...
...
Pozdrawiam
DARIUSZ RACZKOWSKI
www.atrium-pc.pl [1] [1]
ul. Głogowska 181
60-121 Poznań
gg: 11995886
tel/phone: +48 61 662 12 21
tel kom./cellphone: +48 664 167 788
On 14.08.2018 14:02, Tomasz Chiliński wrote:
W dniu 14.08.2018 13:33, Dariusz Raczkowski napisał(a):
Witam
Witam,
Chciałbym zrobić upgrade do najnowszej wersji, czy mam robić
wszystkie wersje po kolei?
po wgraniu plików wersji 1.11.13 mam błąd
Błąd: Unknown column 'discount' in 'field list'
Czy lms robi upgrade bazy?
Robi, ale lepiej od razu przejść na postgresql i na nim robić
aktualizację.
--
Pozdrawiam
DARIUSZ RACZKOWSKI
Links:
[1] http://www.atrium-pc.pl
--
Pozdrawiam
Tomasz Chiliński, Chilan
opiekun projektu LMS - http://lms.org.pl
kierownik projektu LMS Plus / LMS+ - http://lms-plus.org
_______________________________________________
lms mailing list
lms@lists.lms.org.pl
http://lists.lms.org.pl/mailman/listinfo/lms [2]
--
Pozdrawiam
Marcin / nicraM
Links:
[1] http://www.atrium-pc.pl
[2] http://lists.lms.org.pl/mailman/listinfo/lms
_______________________________________________
lms mailing list
lms@lists.lms.org.pl
http://lists.lms.org.pl/mailman/listinfo/lms
--
Pozdrawiam
Tomasz Chiliński, Chilan
opiekun projektu LMS - http://lms.org.pl
kierownik projektu LMS Plus / LMS+ - http://lms-plus.org
_______________________________________________
lms mailing list
lms@lists.lms.org.pl
http://lists.lms.org.pl/mailman/listinfo/lms
-- 
Pozdrawiam
Marcin / nicraM