https://forum.postgresql.org.pl/viewtopic.php?id=1911 Thu, 28 Nov 2013 13:57:27 +0000 PunBB https://forum.postgresql.org.pl/viewtopic.php?pid=4845#p4845 Thu, 28 Nov 2013 13:57:27 +0000 https://forum.postgresql.org.pl/viewtopic.php?pid=4845#p4845 https://forum.postgresql.org.pl/viewtopic.php?pid=4844#p4844 Thu, 28 Nov 2013 10:25:11 +0000 https://forum.postgresql.org.pl/viewtopic.php?pid=4844#p4844 https://forum.postgresql.org.pl/viewtopic.php?pid=4841#p4841 Wed, 27 Nov 2013 12:06:34 +0000 https://forum.postgresql.org.pl/viewtopic.php?pid=4841#p4841 https://forum.postgresql.org.pl/viewtopic.php?pid=4840#p4840 Wed, 27 Nov 2013 12:02:06 +0000 https://forum.postgresql.org.pl/viewtopic.php?pid=4840#p4840 https://forum.postgresql.org.pl/viewtopic.php?pid=4839#p4839 Tue, 26 Nov 2013 15:23:42 +0000 https://forum.postgresql.org.pl/viewtopic.php?pid=4839#p4839 https://forum.postgresql.org.pl/viewtopic.php?pid=4838#p4838 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]]> Tue, 26 Nov 2013 15:22:00 +0000 https://forum.postgresql.org.pl/viewtopic.php?pid=4838#p4838 https://forum.postgresql.org.pl/viewtopic.php?pid=4837#p4837 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]]]> Tue, 26 Nov 2013 15:08:40 +0000 https://forum.postgresql.org.pl/viewtopic.php?pid=4837#p4837 https://forum.postgresql.org.pl/viewtopic.php?pid=4836#p4836 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]]> Tue, 26 Nov 2013 15:03:39 +0000 https://forum.postgresql.org.pl/viewtopic.php?pid=4836#p4836 https://forum.postgresql.org.pl/viewtopic.php?pid=4835#p4835 Tue, 26 Nov 2013 14:50:14 +0000 https://forum.postgresql.org.pl/viewtopic.php?pid=4835#p4835 https://forum.postgresql.org.pl/viewtopic.php?pid=4834#p4834 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.]]> Tue, 26 Nov 2013 14:29:42 +0000 https://forum.postgresql.org.pl/viewtopic.php?pid=4834#p4834 https://forum.postgresql.org.pl/viewtopic.php?pid=4829#p4829 Mon, 25 Nov 2013 11:25:07 +0000 https://forum.postgresql.org.pl/viewtopic.php?pid=4829#p4829 https://forum.postgresql.org.pl/viewtopic.php?pid=4825#p4825 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?]]> Mon, 25 Nov 2013 07:43:32 +0000 https://forum.postgresql.org.pl/viewtopic.php?pid=4825#p4825 https://forum.postgresql.org.pl/viewtopic.php?pid=4822#p4822 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)]]> Mon, 25 Nov 2013 06:05:18 +0000 https://forum.postgresql.org.pl/viewtopic.php?pid=4822#p4822 https://forum.postgresql.org.pl/viewtopic.php?pid=4820#p4820 ... and clock <> ...) to '/tmp/copy.data'), skasuj dane (truncate trends_uint) i załaduj z kopii.]]> Sun, 24 Nov 2013 21:40:32 +0000 https://forum.postgresql.org.pl/viewtopic.php?pid=4820#p4820 https://forum.postgresql.org.pl/viewtopic.php?pid=4819#p4819 Sun, 24 Nov 2013 21:34:19 +0000 https://forum.postgresql.org.pl/viewtopic.php?pid=4819#p4819