Re: Mysterious DB reset

On 03/05/2014 10:22 AM, Israel Brewster wrote:
> I have a Postgresql 9.2.4 database containing real-time tracking data
> for our aircraft for the past week (theoretically). It is populated by
> two different processes: one that runs every few minutes, retrieving
> data from a number of sources and storing it in the DB, and one that has
> an "always on" connection to the DB streaming data into the database in
> realtime (often several records per second). To keep the database size
> manageable I have a cron job that runs every evening to delete all
> records that are more than a week old, after archiving a subset of them
> in permanent storage.
>
> This morning my boss e-mailed me, complaining that only a couple of
> aircraft were showing up in the list (SELECT distinct(tail) FROM data
> being the command that populates the list). Upon looking at the data I
> saw that it only went back to 4am this morning, rather than the week I
> was expecting. My first thought was "Oh, I must have a typo in my
> cleanup routine, such that it is deleting all records rather than only
> those a week old, and it's just that no one has noticed until now". So I
> looked at that, but changing the delete to a select appeared to produce
> the proper results, in that no records were selected:

Well it would, if the records only go back to 4 AM this morning. In
other words if no records exist before 4 AM today, no records exist
before 7 days ago also or am I missing something?

>
> DELETE FROM data WHERE pointtime<now() AT TIME ZONE 'UTC'-interval '7 days';
>
> Then I noticed something even more odd. My database has an id column,
> which is defined as a SERIAL. As we all know, a serial is a
> monotonically increasing number that is not affected by deletes.
> However, the oldest record in my database, from 4am this morning, had an
> id of 1. Even though I KNOW there was data in the system yesterday. Even
> if my DELETE command was wrong and deleted ALL records, that shouldn't
> have reset the SERIAL column to 1! I also know that I have not been in
> the database mucking around with the sequence value - to be completely
> honest, I don't even know the exact command to reset it - I'd have to
> google it if I wanted to.

A sequence is just a special table.

So what does SELECT * from the sequence show?

>
> Also odd is that my cleanup script runs at 1am. I have records of there
> being new data in the database up to 3:51am, but the oldest record
> currently in the DB is from 4:45am (as specified by the default of now()
> on the column). So I know records were added after my delete command
> ran, but before this reset occurred.

I am not sure what you are calling the 'reset'?
Did something happen between 3:51 AM and 4:45 AM?
Also not sure why you call the 4:45 AM record the oldest, when you say
you can identify records from 3:51 AM?

>
> So my question is, aside from someone going in and mucking about in the
> wee hours of the morning, what could possibly cause this behavior? What
> sort of event could cause all data to be deleted from the table, and the
> sequence to be reset? Especially while there is an active connection?
> Thanks for any ideas, however wild or off the wall :-)

What is in the Postgres/system logs for the time period(s) you mention?

>
> -----------------------------------------------
> Israel Brewster
> Computer Support Technician II
> Era Alaska
> 5245 Airport Industrial Rd
> Fairbanks, AK 99709
> (907) 450-7250 x7293
> -----------------------------------------------
>
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com