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

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