Re: [lms] lms-git26 + postgres
W dniu 20.11.2020 14:13, Bartosz Bartczak napisał(a):
lms@lms:~$ psql -U lms -h localhost -p5434 Hasło użytkownika lms: psql (11.9 (Debian 11.9-0+deb10u1)) Wpisz "help" by uzyskać pomoc.
lms=> \q lms@lms:~$
faktycznie na porcie 5434 działa. Na jakim porcie ma działać aby LMS chciał z nim gadać?
Domyślny port postgresql to 5432. Może masz kilka wersji uruchomionych i każda na innym porcie. Zobacz: netstat -taunp |grep :543
On 2020-11-20 14:10, Tomasz Chiliński wrote:
W dniu 20.11.2020 14:00, Bartosz Bartczak napisał(a):
Witam,
Próbuję odpalić testowo bazę na postgresie. Baza utworzona, użytkownik utworzony, ręcznie da się zalogować do bazy, moduły php7.4-pgsql załadowane
Wynik z phpinfo:
PDO_PGSQL
PDO Driver for PostgreSQL enabled
PostgreSQL(libpq) Version 11.9 (Debian 11.9-0+deb10u1)
PGSQL
POSTGRESQL SUPPORT ENABLED
PostgreSQL(libpq) Version 11.9 (Debian 11.9-0+deb10u1)
PostgreSQL(libpq) PostgreSQL 11.9 (Debian 11.9-0+deb10u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
Multibyte character support enabled
SSL support enabled
Active Persistent Links 0
Active Links 0
DIRECTIVE LOCAL VALUE MASTER VALUE
pgsql.allow_persistent On On
pgsql.auto_reset_persistent Off Off
pgsql.ignore_notice Off Off
pgsql.log_notice Off Off
pgsql.max_links Unlimited Unlimited
pgsql.max_persistent Unlimited Unlimited
niestety nadal nie da się połączyć z bazą:
[Fri Nov 20 13:43:46.384818 2020] [php7:warn] [pid 522] [client 1.1.1.2:39026] PHP Warning: PHP Driver for "postgres" database doesn't seems to be loaded. in /var/www/html/lms-git26n/index.php on line 97, referer: http://1.1.1.3/
lms@lms:~$ netstat -ltp |grep 5434 (Not all processes could be identified, non-owned process info will not be shown, you would have to be root to see it all.) tcp 0 0 localhost:5434 0.0.0.0:* LISTEN - tcp6 0 0 localhost:5434 [::]:* LISTEN - lms@lms:~$
Słucha na porcie 5434, a łączysz się na 5432.
lecz:
psql -U lms -h localhost psql: nie można połączyć się z serwerem: Połączenie odrzucone Czy serwer działa na serwerze "localhost" (::1) i akceptuje połączenia TCP/IP na porcie 5432? nie można połączyć się z serwerem: Połączenie odrzucone Czy serwer działa na serwerze "localhost" (127.0.0.1) i akceptuje połączenia TCP/IP na porcie 5432? lms@lms:~$
ms@lms:~$ sudo cat /etc/postgresql/11/main/pg_hba.conf |grep -v "#" local all postgres md5 local all all peer host all all 127.0.0.1/32 md5 host all all ::1/128 md5 local replication all peer host replication all 127.0.0.1/32 md5 host replication all ::1/128 md5
Pomysły mi się skończyły.
Pokaż odpowiedzi według wątków
On 2020-11-20 14:18, Tomasz Chiliński wrote:
netstat -taunp |grep :543
root@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@lms:/etc/postgresql/11# cat /etc/services |grep 5434 root@lms:/etc/postgresql/11# cat /etc/services |grep 5432 postgresql 5432/tcp postgres # PostgreSQL Database postgresql 5432/udp postgres
root@lms:/etc/postgresql/11# apt list --installed postgresql
Listing... Gotowe postgresql/stable,now 11+200+deb10u4 all [installed] root@lms:/etc/postgresql/11#
aczkolwiek w systemie "widzę" jeszcze: root@lms:/etc/postgresql# ls 11 9.4 9.6
Pomimo że Debian twierdzi że nie są zainstalowane. Nie jestem ekspertem od Debiana.
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.
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@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?
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@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@lms:/etc/postgresql/11# cat /etc/services |grep 5434 root@lms:/etc/postgresql/11# cat /etc/services |grep 5432 postgresql 5432/tcp postgres # PostgreSQL Database postgresql 5432/udp postgres root@lms:/etc/postgresql/11# apt list --installed postgresql Listing... Gotowe postgresql/stable,now 11+200+deb10u4 all [installed] root@lms:/etc/postgresql/11# aczkolwiek w systemie "widzę" jeszcze: root@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 8220.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 MB1.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 414.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.092sroot@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@lists.lms.org.pl https://lists.lms.org.pl/mailman/listinfo/lms
uczestnicy (2)
-
Bartosz Bartczak -
Tomasz Chiliński