Re: Mysterious DB reset - Mailing list 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
Inever 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
fromsuch) 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
-----------------------------------------------



On Mar 6, 2014, at 10:34 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

> On 03/06/2014 11:09 AM, Israel Brewster wrote:
>> On Mar 6, 2014, at 10:03 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>>
>
>>>
>>> Are all the scripts running from one machine?
>>> If so, have you checked that the times are set correctly on the various machines?
>>
>> Three different machines (due to OS requirements), but yeah the times all appear to be correct.
>
> So it appears you will have to wait to see what the logging reports. Should have mentioned that you need to make sure
youdo something like pg_ctl reload on the Postgres server to get the postgressql.conf changes to take effect. 
>
>
> Another thought. Might not be bad idea to grep your scripts for ALTER, SEQUENCE or any other relevant keywords. Just
incase something slipped in you where not aware of. 
>
>>
>>>
>>>
>>>>
>>>> Make sense? Probably not the best setup, but then that's what happens when you figure out stuff for yourself
ratherthan having formal training :-) I'm DEFINITELY open to suggestions :-) 
>>>
>>> 'Makes sense' is context sensitive. It really depends on what you want to achieve. My procedure is to define the
endresult first and then work backwards from there. 
>>
>> Good point. However, I was asking more in the general "did I explain it well enough to be understood" rather than in
the"is it a sensible setup" aspect. The rest of the sentence was just me being insecure :-D 
>
> I understood the basics of what you are doing. The details probably can wait pending the log information. Hey, all of
usare beginners/learning in something. 
>
>>
>
>>
>> -----------------------------------------------
>> Israel Brewster
>> Computer Support Technician II
>> Era Alaska
>> 5245 Airport Industrial Rd
>> Fairbanks, AK 99709
>> (907) 450-7250 x7293
>> -----------------------------------------------
>>
>