Re: BUG #13648: Old Records disappearing after upgrade from 9.4.1 to 9.4.4

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(at)ykwc(dot)com wrote:
> The following bug has been logged on the website:
>
> Bug reference: 13648
> Logged by: Jasper
> Email address: jasper(at)ykwc(dot)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:
> --