Re: BUG #13648: Old Records disappearing after upgrade from 9.4.1 to 9.4.4 - Mailing list pgsql-bugs

Resolved.  Not a pgsql bug.  When the sysadmin upgraded the server OS he
deleted a configuration file for the application using the DB.  The
configuration change enabled a 'housekeeper' task which deletes old
records. Sorry for the false alarm.


On 2015-09-28 16:56, jasper@ykwc.com wrote:
> The following bug has been logged on the website:
>
> Bug reference:      13648
> Logged by:          Jasper
> Email address:      jasper@ykwc.com
> PostgreSQL version: 9.4.4
> Operating system:   OpenBSD 5.8
> Description:
>
> I recently upgraded from 9.4.1 to 9.4.4. Since then millions of rows
> have
> disappeared from several tables containing historical data.
>
> The severity of data loss seems to be directly proportional to the age
> of
> the records. 1-2% of the data from 10 days ago is missing, but >90% of
> the
> data from 50 days ago is missing.
>
> I haven't seen anything new in the logfile except:
> LOG:  MultiXact member wraparound protections are now enabled
>
> I've temporarily disabled autovacuum for this weekend.
>
> I'm using OpenBSD 5.8.
>
> PostgreSQL was upgrader by upgrading OpenBSD from 5.7 to 5.8.  No
> PostgreSQL
> or DB related steps such as pg_upgrade or dump-and-reload were taken.
>
> The only table on which we perform deletes is 'Hosts' and deletions
> there
> are rare (~5 per month of the ~7000 hosts in the table).
>
> Schema:
> CREATE TABLE history_uint (
>     itemid bigint NOT NULL,
>     clock integer DEFAULT 0 NOT NULL,
>     value numeric(20,0) DEFAULT (0)::numeric NOT NULL,
>     ns integer DEFAULT 0 NOT NULL
> );
>
>
> ALTER TABLE history_uint OWNER TO zabbix;
>
> --
> -- Name: history_uint_1; Type: INDEX; Schema: public; Owner: zabbix;
> Tablespace:
> --
>
> CREATE INDEX history_uint_1 ON history_uint USING btree (itemid,
> clock);
>
>
> --
> -- Name: partition_trg; Type: TRIGGER; Schema: public; Owner: zabbix
> --
>
> CREATE TRIGGER partition_trg BEFORE INSERT ON history_uint FOR EACH ROW
> EXECUTE PROCEDURE trg_partition('day');
>
>
> --
> -- Name: history_uint; Type: ACL; Schema: public; Owner: zabbix
> --
>
> REVOKE ALL ON TABLE history_uint FROM PUBLIC;
> REVOKE ALL ON TABLE history_uint FROM zabbix;
> GRANT ALL ON TABLE history_uint TO zabbix;
> GRANT SELECT ON TABLE history_uint TO rails_app;
>
>
> --
> -- PostgreSQL database dump complete
> --
>
> --
>
> SET statement_timeout = 0;
> SET lock_timeout = 0;
> SET client_encoding = 'SQL_ASCII';
> SET standard_conforming_strings = on;
> SET check_function_bodies = false;
> SET client_min_messages = warning;
>
> SET search_path = public, pg_catalog;
>
> SET default_tablespace = '';
>
> SET default_with_oids = false;
>
> --
> -- Name: items; Type: TABLE; Schema: public; Owner: zabbix; Tablespace:
> --
>
> CREATE TABLE items (
>     itemid bigint NOT NULL,
>     type integer DEFAULT 0 NOT NULL,
>     snmp_community character varying(64) DEFAULT ''::character varying
> NOT
> NULL,
>     snmp_oid character varying(255) DEFAULT ''::character varying NOT
> NULL,
>     hostid bigint NOT NULL,
>     name character varying(255) DEFAULT ''::character varying NOT NULL,
>     key_ character varying(255) DEFAULT ''::character varying NOT NULL,
>     delay integer DEFAULT 0 NOT NULL,
>     history integer DEFAULT 90 NOT NULL,
>     trends integer DEFAULT 365 NOT NULL,
>     status integer DEFAULT 0 NOT NULL,
>     value_type integer DEFAULT 0 NOT NULL,
>     trapper_hosts character varying(255) DEFAULT ''::character varying
> NOT
> NULL,
>     units character varying(255) DEFAULT ''::character varying NOT
> NULL,
>     multiplier integer DEFAULT 0 NOT NULL,
>     delta integer DEFAULT 0 NOT NULL,
>     snmpv3_securityname character varying(64) DEFAULT ''::character
> varying
> NOT NULL,
>     snmpv3_securitylevel integer DEFAULT 0 NOT NULL,
>     formula character varying(255) DEFAULT ''::character varying NOT
> NULL,
>     error character varying(2048) DEFAULT ''::character varying NOT
> NULL,
>     lastlogsize numeric(20,0) DEFAULT (0)::numeric NOT NULL,
>     logtimefmt character varying(64) DEFAULT ''::character varying NOT
> NULL,
>     templateid bigint,
>     valuemapid bigint,
>     delay_flex character varying(255) DEFAULT ''::character varying NOT
> NULL,
>     params text DEFAULT ''::text NOT NULL,
>     ipmi_sensor character varying(128) DEFAULT ''::character varying
> NOT
> NULL,
>     data_type integer DEFAULT 0 NOT NULL,
>     authtype integer DEFAULT 0 NOT NULL,
>     username character varying(64) DEFAULT ''::character varying NOT
> NULL,
>     password character varying(64) DEFAULT ''::character varying NOT
> NULL,
>     publickey character varying(64) DEFAULT ''::character varying NOT
> NULL,
>     privatekey character varying(64) DEFAULT ''::character varying NOT
> NULL,
>     mtime integer DEFAULT 0 NOT NULL,
>     flags integer DEFAULT 0 NOT NULL,
>     interfaceid bigint,
>     port character varying(64) DEFAULT ''::character varying NOT NULL,
>     description text DEFAULT ''::text NOT NULL,
>     inventory_link integer DEFAULT 0 NOT NULL,
>     lifetime character varying(64) DEFAULT '30'::character varying NOT
> NULL,
>     snmpv3_authprotocol integer DEFAULT 0 NOT NULL,
>     snmpv3_privprotocol integer DEFAULT 0 NOT NULL,
>     state integer DEFAULT 0 NOT NULL,
>     snmpv3_contextname character varying(255) DEFAULT ''::character
> varying
> NOT NULL,
>     evaltype integer DEFAULT 0 NOT NULL
> );
>
>
> ALTER TABLE items OWNER TO zabbix;
>
> --
> -- Name: items_pkey; Type: CONSTRAINT; Schema: public; Owner: zabbix;
> Tablespace:
> --
>
> ALTER TABLE ONLY items
>     ADD CONSTRAINT items_pkey PRIMARY KEY (itemid);
> --
> -- Name: items_1; Type: INDEX; Schema: public; Owner: zabbix;
> Tablespace:
> --
>
> CREATE UNIQUE INDEX items_1 ON items USING btree (hostid, key_);
>
>
> --
> -- Name: items_3; Type: INDEX; Schema: public; Owner: zabbix;
> Tablespace:
> --
>
> CREATE INDEX items_3 ON items USING btree (status);
>
>
> --
> -- Name: items_4; Type: INDEX; Schema: public; Owner: zabbix;
> Tablespace:
> --
>
> CREATE INDEX items_4 ON items USING btree (templateid);
>
>
> --
> -- Name: items_5; Type: INDEX; Schema: public; Owner: zabbix;
> Tablespace:
> --
>
> CREATE INDEX items_5 ON items USING btree (valuemapid);
>
>
> --
> -- Name: items_6; Type: INDEX; Schema: public; Owner: zabbix;
> Tablespace:
> --
>
> CREATE INDEX items_6 ON items USING btree (interfaceid);
>
>
> --
> -- Name: c_items_1; Type: FK CONSTRAINT; Schema: public; Owner: zabbix
> ALTER TABLE ONLY items
>     ADD CONSTRAINT c_items_1 FOREIGN KEY (hostid) REFERENCES
> hosts(hostid)
> ON DELETE CASCADE;
>
>
> --
> -- Name: c_items_2; Type: FK CONSTRAINT; Schema: public; Owner: zabbix
> --
>
> ALTER TABLE ONLY items
>     ADD CONSTRAINT c_items_2 FOREIGN KEY (templateid) REFERENCES
> items(itemid) ON DELETE CASCADE;
>
>
> --
> -- Name: c_items_3; Type: FK CONSTRAINT; Schema: public; Owner: zabbix
> --
>
> ALTER TABLE ONLY items
>     ADD CONSTRAINT c_items_3 FOREIGN KEY (valuemapid) REFERENCES
> valuemaps(valuemapid);
>
>
> --
> -- Name: c_items_4; Type: FK CONSTRAINT; Schema: public; Owner: zabbix
> --
>
> ALTER TABLE ONLY items
>     ADD CONSTRAINT c_items_4 FOREIGN KEY (interfaceid) REFERENCES
> interface(interfaceid);
>
>
> --
> -- Name: items; Type: ACL; Schema: public; Owner: zabbix
> --
>
> REVOKE ALL ON TABLE items FROM PUBLIC;
> REVOKE ALL ON TABLE items FROM zabbix;
> GRANT ALL ON TABLE items TO zabbix;
> GRANT SELECT ON TABLE items TO rails_app;
>
> --
>
> SET statement_timeout = 0;
> SET lock_timeout = 0;
> SET client_encoding = 'SQL_ASCII';
> SET standard_conforming_strings = on;
> SET check_function_bodies = false;
> SET client_min_messages = warning;
>
> SET search_path = public, pg_catalog;
>
> SET default_tablespace = '';
>
> SET default_with_oids = false;
>
> --
> -- Name: hosts; Type: TABLE; Schema: public; Owner: zabbix; Tablespace:
> --
>
> CREATE TABLE hosts (
>     hostid bigint NOT NULL,
>     proxy_hostid bigint,
>     host character varying(128) DEFAULT ''::character varying NOT NULL,
>     status integer DEFAULT 0 NOT NULL,
>     disable_until integer DEFAULT 0 NOT NULL,
>     error character varying(128) DEFAULT ''::character varying NOT
> NULL,
>     available integer DEFAULT 0 NOT NULL,
>     errors_from integer DEFAULT 0 NOT NULL,
>     lastaccess integer DEFAULT 0 NOT NULL,
>     ipmi_authtype integer DEFAULT 0 NOT NULL,
>     ipmi_privilege integer DEFAULT 2 NOT NULL,
>     ipmi_username character varying(16) DEFAULT ''::character varying
> NOT
> NULL,
>     ipmi_password character varying(20) DEFAULT ''::character varying
> NOT
> NULL,
>     ipmi_disable_until integer DEFAULT 0 NOT NULL,
>     ipmi_available integer DEFAULT 0 NOT NULL,
>     snmp_disable_until integer DEFAULT 0 NOT NULL,
>     snmp_available integer DEFAULT 0 NOT NULL,
>     maintenanceid bigint,
>     maintenance_type integer DEFAULT 0 NOT NULL,
>     maintenance_from integer DEFAULT 0 NOT NULL,
>     ipmi_errors_from integer DEFAULT 0 NOT NULL,
>     snmp_errors_from integer DEFAULT 0 NOT NULL,
>     ipmi_error character varying(128) DEFAULT ''::character varying NOT
> NULL,
>     snmp_error character varying(128) DEFAULT ''::character varying NOT
> NULL,
>     jmx_disable_until integer DEFAULT 0 NOT NULL,
>     jmx_available integer DEFAULT 0 NOT NULL,
>     jmx_errors_from integer DEFAULT 0 NOT NULL,
>     jmx_error character varying(128) DEFAULT ''::character varying NOT
> NULL,
>     name character varying(128) DEFAULT ''::character varying NOT NULL,
>     flags integer DEFAULT 0 NOT NULL,
>     templateid bigint,
>     description text DEFAULT ''::text NOT NULL
> );
>
>
> ALTER TABLE hosts OWNER TO zabbix;
>
> --
> -- Name: hosts_pkey; Type: CONSTRAINT; Schema: public; Owner: zabbix;
> Tablespace:
> --
>
> ALTER TABLE ONLY hosts
>     ADD CONSTRAINT hosts_pkey PRIMARY KEY (hostid);
>
>
> --
> -- Name: hosts_1; Type: INDEX; Schema: public; Owner: zabbix;
> Tablespace:
> --
>
> CREATE INDEX hosts_1 ON hosts USING btree (host);
>
>
> --
> -- Name: hosts_2; Type: INDEX; Schema: public; Owner: zabbix;
> Tablespace:
> --