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
-----------------------------------------------
BEGIN:VCARD
VERSION:3.0
N:Brewster;Israel;;;
FN:Israel Brewster
ORG:Frontier Flying Service;MIS
TITLE:PC Support Tech II
EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com
TEL;type=WORK;type=pref:907-450-7293
item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701;
item1.X-ABADR:us
CATEGORIES:General
X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson
END:VCARD
On Mar 6, 2014, at 10:34 AM, Adrian Klaver <adrian.kla...@aklaver.com> wrote:
> On 03/06/2014 11:09 AM, Israel Brewster wrote:
>> On Mar 6, 2014, at 10:03 AM, Adrian Klaver <adrian.kla...@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 you do 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 in case 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 rather than 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 end result 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 us are beginners/learning in
> something.
>
>>
>
>>
>> -----------------------------------------------
>> Israel Brewster
>> Computer Support Technician II
>> Era Alaska
>> 5245 Airport Industrial Rd
>> Fairbanks, AK 99709
>> (907) 450-7250 x7293
>> -----------------------------------------------
>>
>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
