Re: Mysterious DB reset

From: Israel Brewster <israel(at)eraalaska(dot)net>
To: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
Cc: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: Mysterious DB reset
Date: 2014-03-07 18:50:47
Message-ID: 624FC7CB-43D9-4207-9694-10C2945DBED2@eraalaska.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

So the logs have given me a partial answer, specifically WHO and WHAT. I still have to track down WHY:

[unknown]-2014-03-07 04:40:02.407 AKST-0LOG: connection received: host=[local]
postgres-2014-03-07 04:40:02.409 AKST-0LOG: connection authorized: user=postgres database=tracking
postgres-2014-03-07 04:40:02.521 AKST-0LOG: statement: DROP INDEX public.timerecp_idx;
postgres-2014-03-07 04:40:02.548 AKST-0LOG: statement: DROP INDEX public.tail_idx;
postgres-2014-03-07 04:40:02.553 AKST-0LOG: statement: DROP INDEX public.syncd_idx;
postgres-2014-03-07 04:40:02.562 AKST-0LOG: statement: DROP INDEX public.pointtime_idx;
postgres-2014-03-07 04:40:02.571 AKST-0LOG: statement: ALTER TABLE ONLY public.tails DROP CONSTRAINT tails_pkey;
postgres-2014-03-07 04:40:02.577 AKST-0LOG: statement: ALTER TABLE ONLY public.data DROP CONSTRAINT data_pkey;
postgres-2014-03-07 04:40:02.588 AKST-0LOG: statement: ALTER TABLE public.tails ALTER COLUMN id DROP DEFAULT;
postgres-2014-03-07 04:40:02.593 AKST-0LOG: statement: ALTER TABLE public.data ALTER COLUMN id DROP DEFAULT;
postgres-2014-03-07 04:40:02.601 AKST-0LOG: statement: DROP SEQUENCE public.tails_id_seq;
postgres-2014-03-07 04:40:02.611 AKST-0LOG: statement: DROP TABLE public.tails;
postgres-2014-03-07 04:40:02.619 AKST-0LOG: statement: DROP SEQUENCE public.data_id_seq;
postgres-2014-03-07 04:40:02.627 AKST-0LOG: statement: DROP TABLE public.data;
postgres-2014-03-07 04:40:02.643 AKST-0LOG: statement: DROP EXTENSION plpgsql;
postgres-2014-03-07 04:40:02.654 AKST-0LOG: statement: DROP SCHEMA public;
postgres-2014-03-07 04:40:02.663 AKST-0LOG: statement: CREATE SCHEMA public;
postgres-2014-03-07 04:40:02.671 AKST-0LOG: statement: ALTER SCHEMA public OWNER TO postgres;
postgres-2014-03-07 04:40:02.671 AKST-0LOG: statement: COMMENT ON SCHEMA public IS 'standard public schema';
postgres-2014-03-07 04:40:02.679 AKST-0LOG: statement: CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
postgres-2014-03-07 04:40:02.689 AKST-0LOG: statement: COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
postgres-2014-03-07 04:40:02.697 AKST-0LOG: statement: CREATE TABLE data (
id bigint NOT NULL,
tail character varying(16) NOT NULL,
timerecp timestamp without time zone DEFAULT now() NOT NULL,
altitude integer,
pointtime timestamp without time zone,
lat numeric(7,5) NOT NULL,
lng numeric(8,5) NOT NULL,
speed integer,
heading integer,
source character varying(64),
syncd boolean DEFAULT false
);
postgres-2014-03-07 04:40:02.707 AKST-0LOG: statement: ALTER TABLE public.data OWNER TO tracking;
postgres-2014-03-07 04:40:02.715 AKST-0LOG: statement: CREATE SEQUENCE data_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
postgres-2014-03-07 04:40:02.723 AKST-0LOG: statement: ALTER TABLE public.data_id_seq OWNER TO tracking;
postgres-2014-03-07 04:40:02.732 AKST-0LOG: statement: ALTER SEQUENCE data_id_seq OWNED BY data.id;
postgres-2014-03-07 04:40:02.740 AKST-0LOG: statement: CREATE TABLE tails (
id integer NOT NULL,
tailnum character varying(8),
trackingdevicenumber character varying(256)
);
postgres-2014-03-07 04:40:02.749 AKST-0LOG: statement: ALTER TABLE public.tails OWNER TO tracking;
postgres-2014-03-07 04:40:02.757 AKST-0LOG: statement: CREATE SEQUENCE tails_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
postgres-2014-03-07 04:40:02.766 AKST-0LOG: statement: ALTER TABLE public.tails_id_seq OWNER TO tracking;
postgres-2014-03-07 04:40:02.774 AKST-0LOG: statement: ALTER SEQUENCE tails_id_seq OWNED BY tails.id;
postgres-2014-03-07 04:40:02.782 AKST-0LOG: statement: ALTER TABLE ONLY data ALTER COLUMN id SET DEFAULT nextval('data_id_seq'::regclass);
postgres-2014-03-07 04:40:02.791 AKST-0LOG: statement: ALTER TABLE ONLY tails ALTER COLUMN id SET DEFAULT nextval('tails_id_seq'::regclass);
postgres-2014-03-07 04:40:02.799 AKST-0LOG: statement: ALTER TABLE ONLY data
ADD CONSTRAINT data_pkey PRIMARY KEY (id);
postgres-2014-03-07 04:40:03.066 AKST-0LOG: statement: ALTER TABLE ONLY tails
ADD CONSTRAINT tails_pkey PRIMARY KEY (id);
postgres-2014-03-07 04:40:03.116 AKST-0LOG: statement: CREATE INDEX pointtime_idx ON data USING btree (pointtime);
postgres-2014-03-07 04:40:03.158 AKST-0LOG: statement: CREATE INDEX syncd_idx ON data USING btree (syncd);
postgres-2014-03-07 04:40:03.208 AKST-0LOG: statement: CREATE INDEX tail_idx ON data USING btree (tail);
postgres-2014-03-07 04:40:03.258 AKST-0LOG: statement: CREATE INDEX timerecp_idx ON data USING btree (timerecp);
postgres-2014-03-07 04:40:03.309 AKST-0LOG: statement: REVOKE ALL ON SCHEMA public FROM PUBLIC;
postgres-2014-03-07 04:40:03.317 AKST-0LOG: statement: REVOKE ALL ON SCHEMA public FROM postgres;
postgres-2014-03-07 04:40:03.325 AKST-0LOG: statement: GRANT ALL ON SCHEMA public TO postgres;
postgres-2014-03-07 04:40:03.333 AKST-0LOG: statement: GRANT ALL ON SCHEMA public TO PUBLIC;
postgres-2014-03-07 04:40:03.342 AKST-0LOG: disconnection: session time: 0:00:00.935 user=postgres database=tracking host=[local]

So that definitely answers what is happening: Every morning at 4:40, the tables get dropped an re-created. Wow. I KNOW I never wrote a script to do that! I'm sort of thinking I somehow have a pg_dump with a -c flag (or perhaps the file from such) that is being loaded in, but I still don't know how or why. Closer though. Thanks for the suggestions!
-----------------------------------------------
Israel Brewster
Computer Support Technician II
Era Alaska
5245 Airport Industrial Rd
Fairbanks, AK 99709
(907) 450-7250 x7293
-----------------------------------------------

Attachment Content-Type Size
Israel Brewster.vcf text/directory 417 bytes
unknown_filename text/plain 1.9 KB

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Israel Brewster 2014-03-07 19:08:15 Re: Mysterious DB reset
Previous Message Tom Lane 2014-03-07 18:06:56 Re: extract psql meta-commands into library?