[lms] lms-git26 + postgres

Tomasz Chiliński tomasz.chilinski w chilan.com
Pią, 20 Lis 2020, 14:56:15 CET


W dniu 20.11.2020 14:46, Bartosz Bartczak napisał(a):
> On 2020-11-20 14:18, Tomasz Chiliński wrote:
> 
>> netstat -taunp |grep :543
> 
> root w lms:/etc/postgresql/11# netstat -taunp |grep :543
> tcp        0      0 127.0.0.1:5434          0.0.0.0:*
> LISTEN      2368/postgres
> tcp6       0      0 ::1:5434                :::*
> LISTEN      2368/postgres
>  root w lms:/etc/postgresql/11# cat /etc/services |grep 5434
> root w lms:/etc/postgresql/11# cat /etc/services |grep 5432
> postgresql    5432/tcp    postgres    # PostgreSQL Database
> postgresql    5432/udp    postgres
> root w lms:/etc/postgresql/11# apt list --installed postgresql
> Listing... Gotowe
> postgresql/stable,now 11+200+deb10u4 all [installed]
> root w lms:/etc/postgresql/11#
> aczkolwiek w systemie "widzę" jeszcze:
> root w lms:/etc/postgresql# ls
> 11  9.4  9.6
> 
> Pomimo że Debian twierdzi że nie są zainstalowane.
> Nie jestem ekspertem od Debiana.

Ja również, dlatego używam dystrybucji w których nie wynajduje się koła
za każdym razem na nowo.

> Z jakiegoś powodu nie lubi portu 5434 ;)
> 
> Ok nie wiem jak to się stało że 5434 siedział w postgresql.conf .
> Po zmianie na 5432 LMS "działa.
> 
> Jaka metodę wgrania bazy z MYSQL sugerujecie?
> Czytałem że jedyna niezawodna to eksport z lms i import w LMSie
> przez GUI ale ktoś tam mówił że trwa to wieki.

Zależy z której wersji eskport. Nowsze wersje eksportują rekordy
w postaci multi-insertów.

> Ok wybrałem pgloader:
> 2020-11-20T13:53:13.020000Z LOG pgloader version "3.6.1"
> 2020-11-20T13:53:13.056000Z LOG Migrating from #<MYSQL-CONNECTION
> mysql://lms@localhost:3306/lms {1005BDF7A3}>
> 2020-11-20T13:53:13.056000Z LOG Migrating into #<PGSQL-CONNECTION
> pgsql://lms@localhost:5432/lms {1005DDB143}>
> 2020-11-20T13:53:31.567000Z LOG report summary reset
>                      table name     errors       rows      bytes
> total time
> -------------------------------  ---------  ---------  ---------
> --------------
>                 fetch meta data          0        822
>    0.251s
>                  Create Schemas          0          0
>    0.003s
>                Create SQL Types          0          0
>    0.006s
>                   Create tables          0        274
>    0.401s
>                  Set Table OIDs          0        137
>    0.010s
> -------------------------------  ---------  ---------  ---------
> --------------
>                   lms.addresses          0      28197     2.1 MB
>    1.533s
>            lms.aliasassignments          0          0
>    0.038s
>                 lms.assignments          0        376    27.5 kB
>    0.124s
>                  lms.cashimport          0          0
>    0.137s
>                    lms.cashregs          0          2     0.1 kB
>    0.184s
>                 lms.cashsources          0          0
>    0.201s
>                   lms.countries          0          7     0.1 kB
>    0.276s
>         lms.customerassignments          0        514     5.3 kB
>    0.428s
>            lms.customerconsents          0       3244    34.4 kB
>    0.530s
>              lms.customergroups          0         40     1.0 kB
>    0.484s
>                   lms.customers          0        574   163.5 kB
>    0.627s
>                lms.daemonconfig          0          0
>    0.591s
>                      lms.dbinfo          0          7     0.2 kB
>    0.646s
>                   lms.divisions          0          1     0.2 kB
>    0.720s
>         lms.documentattachments          0          0
>    0.757s
>                   lms.documents          0      54067    17.8 MB
>   11.568s
>                     lms.aliases          0          0
>    0.125s
>                        lms.cash          0      56510     6.2 MB
>    3.588s
>                  lms.cashreglog          0          0
>    0.149s
>                  lms.cashrights          0          6     0.1 kB
>    0.407s
>                    lms.comments          0          0
>    0.535s
>                  lms.cryptokeys          0          0
>    0.618s
>            lms.customerbalances          0        536     5.5 kB
>    0.775s
>            lms.customercontacts          0        995    25.9 kB
>    0.901s
>               lms.customernotes          0          0
>    0.959s
>          lms.customer_addresses          0       1102    14.9 kB
>    1.163s
>             lms.daemoninstances          0          0
>    1.189s
>           lms.debitnotecontents          0          0
>    1.210s
>                   lms.docrights          0         42     0.5 kB
>    1.254s
>            lms.documentcontents          0       1899    71.6 kB
>    1.366s
>              lms.domainmetadata          0          0
>    1.120s
>            lms.eventassignments          0       2491    15.8 kB
>    1.192s
>                lms.ewx_channels          0          0
>    1.047s
>            lms.ewx_stm_channels          0          0
>    1.079s
>              lms.excludedgroups          0          0
>    1.068s
>                       lms.files          0          0
>    1.107s
>             lms.invoicecontents          0      28864     3.0 MB
>    2.230s
>                 lms.liabilities          0         79     4.0 kB
>    0.339s
>          lms.location_buildings          0          0
>    0.378s
>         lms.location_city_types          0          0
>    0.454s
>             lms.location_states          0          0
>    0.517s
>       lms.location_street_types          0          0
>    0.559s
>              lms.logmessagekeys          0          0
>    0.594s
>             lms.logtransactions          0          0
>    0.625s
>              lms.managementurls          0          0
>    0.673s
>                    lms.messages          0        221   120.1 kB
>    0.741s
>             lms.netdevicemodels          0        319     9.6 kB
>    0.782s
>                  lms.netdevices          0          2     0.2 kB
>    0.807s
>                    lms.netnodes          0          0
>    0.859s
>                    lms.networks          0         11     0.7 kB
>    0.920s
>        lms.nodegroupassignments          0          1     0.0 kB
>    0.976s
>                   lms.nodelocks          0          0
>    1.015s
>                lms.nodesessions          0          0
>    1.058s
>                 lms.numberplans          0          3     0.1 kB
>    1.100s
>               lms.passwdhistory          0          1     0.0 kB
>    1.200s
>                         lms.pna          0          0
>    1.234s
>                  lms.promotions          0          0
>    1.297s
>             lms.receiptcontents          0      27223     1.2 MB
>    2.052s
>              lms.routednetworks          0          0
>    0.739s
>                lms.rtcategories          0          1     0.1 kB
>    0.775s
>                  lms.rtmessages          0       4582     1.5 MB
>    1.226s
>                    lms.rtqueues          0          5     0.4 kB
>    0.833s
>            lms.rttemplatequeues          0          0
>    0.879s
>          lms.rtticketcategories          0          0
>    0.929s
>                   lms.rttickets          0       1391   187.9 kB
>    1.061s
>                 lms.sourcefiles          0          0
>    1.039s
>                       lms.stats          0          0
>    1.087s
>           lms.tariffassignments          0          0
>    1.147s
>                  lms.tarifftags          0          0
>    1.202s
>                   lms.templates          0          0
>    1.228s
>    lms.twofactorauthcodehistory          0          0
>    1.255s
>                    lms.uiconfig          0        199    10.1 kB
>    1.320s
>                     lms.up_help          0          0
>    1.340s
>                   lms.up_rights          0          4     0.3 kB
>    1.387s
>             lms.userassignments          0          0
>    1.409s
>                  lms.usergroups          0          0
>    1.436s
>                lms.voipaccounts          0          0
>    1.467s
>      lms.voip_emergency_numbers          0          0
>    1.508s
>                lms.voip_numbers          0          0
>    1.546s
>           lms.voip_pool_numbers          0          0
>    1.571s
>          lms.voip_prefix_groups          0          0
>    1.606s
>                  lms.voip_rules          0          0
>    1.624s
>            lms.voip_rule_states          0          0
>    1.652s
>                    lms.zipcodes          0         25     0.3 kB
>    1.683s
>                     lms.domains          0          0
>    0.005s
>                      lms.events          0       2115   361.1 kB
>    1.122s
>               lms.ewx_pt_config          0          0
>    0.008s
>               lms.ewx_stm_nodes          0          0
>    0.002s
>              lms.filecontainers          0          0
>    0.087s
>                       lms.hosts          0          0
>    0.071s
>                 lms.invprojects          0          2     0.0 kB
>    0.288s
>           lms.location_boroughs          0          0
>    0.094s
>             lms.location_cities          0          0
>    0.156s
>          lms.location_districts          0          0
>    0.138s
>            lms.location_streets          0          0
>    0.183s
>              lms.logmessagedata          0          0
>    0.243s
>                 lms.logmessages          0          0
>    0.242s
>                        lms.macs          0         20     0.5 kB
>    0.295s
>                lms.messageitems          0       1480    92.8 kB
>    0.439s
>                    lms.nastypes          0         13     0.1 kB
>    0.246s
>          lms.netdeviceproducers          0          2     0.0 kB
>    0.284s
>                    lms.netlinks          0          0
>    0.266s
>             lms.netradiosectors          0          0
>    0.318s
>             lms.nodeassignments          0          0
>    0.297s
>                  lms.nodegroups          0          2     0.1 kB
>    0.344s
>                       lms.nodes          0         20     2.0 kB
>    0.401s
>       lms.numberplanassignments          0          3     0.0 kB
>    0.297s
>                      lms.passwd          0          0
>    0.331s
>                    lms.payments          0          0
>    0.364s
>        lms.promotionassignments          0          0
>    0.362s
>            lms.promotionschemas          0          0
>    0.420s
>                     lms.records          0          0
>    0.462s
>               lms.rtattachments          0          0
>    0.440s
>             lms.rtcategoryusers          0          0
>    0.480s
>           lms.rtqueuecategories          0          0
>    0.504s
>                    lms.rtrights          0         15     0.2 kB
>    0.532s
>             lms.rttemplatetypes          0          0
>    0.532s
>            lms.rtticketlastview          0      15301   260.4 kB
>    1.042s
>                    lms.sessions          0          1     0.6 kB
>    0.013s
>                      lms.states          0          1     0.0 kB
>    0.022s
>                lms.supermasters          0          0
>    0.044s
>                     lms.tariffs          0         40     6.1 kB
>    0.068s
>                       lms.taxes          0          2     0.1 kB
>    0.058s
>                    lms.tsigkeys          0          0
>    0.116s
> lms.twofactorauthtrusteddevices          0          0
>    0.078s
>                lms.up_customers          0        153     6.0 kB
>    0.234s
>             lms.up_info_changes          0          0
>    0.156s
>       lms.up_rights_assignments          0          0
>    0.171s
>               lms.userdivisions          0         11     0.1 kB
>    0.210s
>                       lms.users          0         11     4.0 kB
>    0.267s
>                    lms.voip_cdr          0          0
>    0.204s
> lms.voip_group_rule_assignments          0          0
>    0.207s
>     lms.voip_number_assignments          0          0
>    0.259s
>               lms.voip_prefixes          0          0
>    0.278s
>           lms.voip_price_groups          0          0
>    0.288s
>            lms.voip_rule_groups          0          0
>    0.317s
>                lms.voip_tariffs          0          0
>    0.332s
> -------------------------------  ---------  ---------  ---------
> --------------
>         COPY Threads Completion          0          4
>   14.364s
>                  Create Indexes          0        437
>   40.519s
>          Index Build Completion          0        437
>    0.128s
>                 Reset Sequences          0        123
>    0.191s
>                    Primary Keys          0        125
>    0.070s
>             Create Foreign Keys          0        248
>    0.820s
>                 Create Triggers          0          0
>    0.000s
>                Install Comments          0          0
>    0.000s
> -------------------------------  ---------  ---------  ---------
> --------------
>               Total import time          ✓     232733    33.2 MB
>     56.092s
> root w lms:/etc/postgresql#
> 
> Niby wszystko ok ale przy próbie zalogowania się do bazy LMS przez
> GUI mam błędy:
> Wystąpiły błędy w obsłudze bazy danych!
> Zapytanie: SELECT * FROM uiconfig WHERE disabled = 0
> Błąd: BŁĄD: operator nie istnieje: boolean = integer LINE 1:
> SELECT * FROM uiconfig WHERE disabled = 0 ^ HINT: No operator matches
> the given name and argument types. You might need to add explicit type
> casts.
> 
> Źle to robiłem?
> Jak to zrobić poprawnie?
> _______________________________________________
> lms mailing list
> lms w lists.lms.org.pl
> https://lists.lms.org.pl/mailman/listinfo/lms

-- 
Pozdrawiam
Tomasz Chiliński, Chilan
opiekun projektu LMS - https://lms.org.pl
kierownik projektu LMS Plus / LMS+ - https://lms-plus.org


Więcej informacji o liście lms