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

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:
--