2013-11-28T13:57:27Z PunBB https://forum.postgresql.org.pl/viewtopic.php?id=1911 https://forum.postgresql.org.pl/profile.php?id=1564 2013-11-28T13:57:27Z https://forum.postgresql.org.pl/viewtopic.php?pid=4845#p4845 https://forum.postgresql.org.pl/profile.php?id=1512 2013-11-28T10:25:11Z https://forum.postgresql.org.pl/viewtopic.php?pid=4844#p4844 https://forum.postgresql.org.pl/profile.php?id=1564 2013-11-27T12:06:34Z https://forum.postgresql.org.pl/viewtopic.php?pid=4841#p4841 https://forum.postgresql.org.pl/profile.php?id=1512 2013-11-27T12:02:06Z https://forum.postgresql.org.pl/viewtopic.php?pid=4840#p4840 https://forum.postgresql.org.pl/profile.php?id=1564 2013-11-26T15:23:42Z https://forum.postgresql.org.pl/viewtopic.php?pid=4839#p4839 select clock from trends_uint order by clock asc; clock ------------ 179208 230115 230157 230157 234472 530140160 530263552 zabbix=> delete from trends_uint where clock = '234472'; DELETE 0 zabbix=> delete from trends_uint where clock = '230157'; DELETE 2 zabbix=> delete from trends_uint where clock = '230157'; DELETE 0 zabbix=> delete from trends_uint where clock = '230115'; DELETE 0 zabbix=> delete from trends_uint where clock = '179208'; DELETE 0 Poszukam, bo widzę że jeszcze inne wiersze są "uwalone". Dam znać o ostatecznym rozwiązaniu. Proszę podpowiedz, czy te dane są w jakiś sposób do odzyskania? W czym upatrywać problemu, czy to nawaliły np. dyski, ram ... jak się przed tym ustrzec na przyszłość? Piotr]]> https://forum.postgresql.org.pl/profile.php?id=1512 2013-11-26T15:22:00Z https://forum.postgresql.org.pl/viewtopic.php?pid=4838#p4838 230116; I potem, jak już będziesz miał idy *wszystkich* popsutych rekordów, to zrób to co czym pisałem tu: [url]http://forum.postgresql.org.pl/viewtopic.php?pid=4820#p4820[/url]]]> https://forum.postgresql.org.pl/profile.php?id=1564 2013-11-26T15:08:40Z https://forum.postgresql.org.pl/viewtopic.php?pid=4837#p4837 set enable_indexscan = false; SET zabbix=> set enable_bitmapscan = false; SET zabbix=> select * from trends_uint where clock < 1385463600; ERROR: compressed data is corrupt zabbix=> select * from trends_uint where clock < 138546360; ERROR: invalid memory alloc request size 18446744073709551610 Udało mi się dojść jeszcze do czegoś takiego: zabbix=> select * from trends_uint where clock < 230116; ERROR: invalid memory alloc request size 18446744073709551610 zabbix=> select * from trends_uint where clock < 230115; itemid | clock | num | value_min | value_avg | value_max -----------+--------+-----+-----------+-----------+----------- 530532352 | 179208 | 0 | | | (1 row) Wydaje mi się że chodzi o wiersz 230116]]> https://forum.postgresql.org.pl/profile.php?id=1512 2013-11-26T15:03:39Z https://forum.postgresql.org.pl/viewtopic.php?pid=4836#p4836 https://forum.postgresql.org.pl/profile.php?id=1564 2013-11-26T14:50:14Z https://forum.postgresql.org.pl/viewtopic.php?pid=4835#p4835 select * from trends_uint where clock < 1385463600; ERROR: compressed data is corrupt zabbix=> select * from trends_uint where clock < 138546360; ERROR: invalid memory alloc request size 18446744073709551610 Maxymalna i minimalna wartość clocka to 1385463600 530140160 Kończą mi się pomysły na zapytania sqlowe... Czy ta usterka może mieć związek z z małą ilością miejsca na dysku? Jest wolne około 10Gb na tej partycji.]]> https://forum.postgresql.org.pl/profile.php?id=1512 2013-11-26T14:29:42Z https://forum.postgresql.org.pl/viewtopic.php?pid=4834#p4834 https://forum.postgresql.org.pl/profile.php?id=1564 2013-11-25T11:25:07Z https://forum.postgresql.org.pl/viewtopic.php?pid=4829#p4829 select count(itemid) from trends_uint; count ---------- 10640026 (1 row) zabbix=> select count(itemid) from trends_uint; count ---------- 10640026 (1 row) zabbix=> select count(clock) from trends_uint; count ---------- 10640026 (1 row) zabbix=> select count(num) from trends_uint; count ---------- 10640025 (1 row) zabbix=> select count(value_min) from trends_uint; count ---------- 10640024 (1 row) zabbix=> select count(value_avg) from trends_uint; count ---------- 10640024 (1 row) zabbix=> select count(value_max) from trends_uint; count ---------- 10640024 (1 row) zabbix=> SELECT relname, n_tup_ins - n_tup_del as rowcount FROM pg_stat_all_tables; relname | rowcount -------------------------+---------- maintenances_groups | 0 pg_toast_4131296 | 0 usrgrp | 0 sql_parts | 0 pg_toast_4132252 | 0 ids | 0 pg_language | 0 opmessage_grp | 0 hosts_groups | 0 history | 71608 pg_toast_4131435 | 0 pg_toast_12394 | 0 sysmaps | 0 httptestitem | 0 pg_attrdef | 0 images | 0 opconditions | 0 pg_default_acl | 0 history_log | 10 pgbench_accounts | 0 service_alarms | 0 history_uint_sync | 0 pg_toast_12389 | 0 dhosts | 0 pg_attribute | 0 pg_statistic | 0 services_links | 0 users | 0 trends_uint | 6078 pg_toast_2618 | 0 pg_toast_3596 | 0 auditlog_details | 1 pg_depend | 0 pg_toast_4132043 | 0 pg_toast_4132163 | 0 pg_toast_4132059 | 0 pgbench_branches | 0 autoreg_host | 0 graphs_items | 0 opcommand_grp | 0 pg_toast_4131906 | 0 sql_implementation_info | 0 pg_type | 0 sql_features | 0 pg_toast_4131414 | 0 slideshows | 0 pg_foreign_data_wrapper | 0 trigger_discovery | 0 maintenances_windows | 0 valuemaps | 0 pgbench_history | 0 events | -396 items_applications | 0 pg_shdescription | 0 pg_tablespace | 0 opcommand_hst | 0 httpstep | 0 pg_toast_4131342 | 0 pg_toast_4131365 | 0 pg_toast_4132027 | 0 pg_toast_2964 | 0 auditlog | 1 pg_amproc | 0 pg_collation | 0 pg_constraint | 0 media | 0 pg_largeobject | 0 hosts | 0 pg_namespace | 0 expressions | 0 maintenances_hosts | 0 pg_toast_4132286 | 0 icon_mapping | 0 sql_sizing | 0 pg_toast_2396 | 0 httptest | 0 history_text | -2574 sysmaps_links | 0 pg_toast_12379 | 0 pg_seclabel | 0 pg_toast_2609 | 0 sysmaps_elements | 0 pg_toast_1255 | 0 pg_toast_4131939 | 0 proxy_history | 0 user_history | 0 pg_rewrite | 0 pg_ts_config | 0 pg_extension | 0 pg_cast | 0 interface | 0 item_discovery | 0 pg_toast_4131312 | 0 proxy_autoreg_host | 0 opcommand | 0 opmessage | 0 sql_sizing_profiles | 0 pg_toast_12404 | 0 pg_auth_members | 0 pg_operator | 0 nodes | 0 profiles | 0 pg_aggregate | 0 pg_pltemplate | 0 pg_toast_12399 | 0 graph_discovery | 0 housekeeper | 0 pg_toast_4131446 | 0 opgroup | 0 node_cksum | 0 graph_theme | 0 hosts_templates | 0 pg_description | 0 httpstepitem | 0 pg_toast_4131803 | 0 pg_toast_4131977 | 0 pgbench_tellers | 0 pg_ts_parser | 0 pg_foreign_table | 0 proxy_dhistory | 0 items | 0 icon_map | 0 mappings | 0 trigger_depends | 0 pg_ts_template | 0 pg_foreign_server | 0 pg_toast_4131769 | 0 functions | 0 pg_toast_1262 | 0 users_groups | 0 pg_toast_4132155 | 0 pg_toast_4131568 | 0 sessions | 0 screens_items | 0 dchecks | 0 groups | 0 help_items | 0 media_type | 0 actions | 0 slides | 0 pg_opclass | 0 pg_proc | 0 pg_toast_4131874 | 0 hostmacro | 0 pg_trigger | 0 pg_toast_4131603 | 367 conditions | 0 pg_am | 0 pg_largeobject_metadata | 0 pg_ts_config_map | 0 pg_toast_4131752 | 0 pg_toast_4131708 | 0 pg_toast_4131922 | 0 pg_toast_4132270 | 0 graphs | 0 pg_shdepend | 0 history_uint | 150931 pg_conversion | 0 pg_opfamily | 0 trends | 13530 config | 0 sql_languages | 0 regexps | 0 pg_toast_4131827 | 0 globalvars | 0 pg_inherits | 0 pg_toast_2606 | 0 triggers | 0 pg_database | 0 host_inventory | 0 pg_toast_2620 | 0 alerts | -292 optemplate | 0 operations | 0 dservices | 0 sysmaps_link_triggers | 0 pg_toast_12384 | 0 sysmap_url | 0 applications | 0 history_str | 1205 pg_toast_4131626 | 0 drules | 0 pg_user_mapping | 0 pg_toast_4132148 | 0 scripts | 0 services_times | 0 sysmap_element_url | 0 globalmacro | 0 pg_authid | 0 pg_index | 0 pg_ts_dict | 0 acknowledges | 0 escalations | -6 timeperiods | 0 pg_class | 0 sql_packages | 0 maintenances | 0 screens | 0 services | 0 pg_enum | 0 pg_db_role_setting | 0 opmessage_usr | 0 rights | 0 pg_toast_4131553 | 0 pg_toast_4132094 | 0 history_sync | 0 pg_toast_12409 | 0 pg_amop | 0 pg_toast_2619 | -1 history_str_sync | 0 pg_toast_2604 | 0 (211 rows) Nie wiem tylko dlaczego w drugim zapytaniu są inaczej liczone wiersze. --EDIT-- W logu postgresa wciąż widać: 2013-11-25 09:03:04.468 CET postgres 00000LOG: duration: 461639.997 ms statement: COPY public.history_uint (itemid, clock, value, ns) TO stdout; 2013-11-25 09:03:26.892 CET postgres 00000LOG: duration: 22181.789 ms statement: COPY public.trends (itemid, clock, num, value_min, value_avg, value_max) TO stdout; 2013-11-25 09:03:29.723 CET postgres XX000ERROR: invalid memory alloc request size 18446744073709551610 2013-11-25 09:03:29.723 CET postgres XX000STATEMENT: COPY public.trends_uint (itemid, clock, num, value_min, value_avg, value_max) TO stdout; ----- EDIT ----- Próbowałem zrobić vaccum full tej tabeli, dostaję ciekawy komunikat. INFO: vacuuming "public.trends_uint" WARNING: concurrent delete in progress within table "trends_uint" WARNING: concurrent delete in progress within table "trends_uint" WARNING: concurrent delete in progress within table "trends_uint" WARNING: concurrent delete in progress within table "trends_uint" WARNING: concurrent delete in progress within table "trends_uint" WARNING: concurrent delete in progress within table "trends_uint" WARNING: concurrent delete in progress within table "trends_uint" WARNING: concurrent delete in progress within table "trends_uint" ERROR: row is too big: size 33747856, maximum size 8160 ERROR: row is too big: size 33747856, maximum size 8160 Czy wiesz jak określić row_size? Łatwiej było by mi znaleźć ten konkretny row. Albo może masz jakiś inny pomysł na zalezienie tego wiersza?]]> https://forum.postgresql.org.pl/profile.php?id=1512 2013-11-25T07:43:32Z https://forum.postgresql.org.pl/viewtopic.php?pid=4825#p4825 select count(itemid) from trends_uint; WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded. zabbix=> select count(clock) from trends_uint; count ---------- 10638280 (1 row) zabbix=> select count(num) from trends_uint; count ---------- 10638279 (1 row) zabbix=> select count(value_min) from trends_uint; count ---------- 10638278 (1 row) zabbix=> select count(value_avg) from trends_uint; count ---------- 10638278 (1 row) zabbix=> select count(value_max) from trends_uint; count ---------- 10638278 (1 row)]]> https://forum.postgresql.org.pl/profile.php?id=1512 2013-11-25T06:05:18Z https://forum.postgresql.org.pl/viewtopic.php?pid=4822#p4822 ... and clock <> ...) to '/tmp/copy.data'), skasuj dane (truncate trends_uint) i załaduj z kopii.]]> https://forum.postgresql.org.pl/profile.php?id=1564 2013-11-24T21:40:32Z https://forum.postgresql.org.pl/viewtopic.php?pid=4820#p4820 https://forum.postgresql.org.pl/profile.php?id=1512 2013-11-24T21:34:19Z https://forum.postgresql.org.pl/viewtopic.php?pid=4819#p4819