Re: Time series aggregation

On Wed, Jun 27, 2012 at 7:15 AM, Yelai, Ramkumar IN BLR STS
<ramkumar(dot)yelai(at)siemens(dot)com> wrote:
> Hi,
>
> In my current project,  we are keep inserting  device related data in 5
> minutes basis  in our archiving tables. Soon we added more devices, more
> number of  rows will be inserted.
>
> When we are generating report for 1000 devices for around 1 week of date
> range, It takes more time to aggregate the data and compute the business
> logic. Hence we thought to go for time series aggregation like, aggregate
> the device data by Hourly, daily and monthly and store in to another table.
> Since Postgresql is supporting cron job in pgAgent, so I created a jobs to
> run hourly , daily and monthly basis to aggregate the data.  The jobs are
> working fine, but if postgresql server is shutdown and resume after some
> time like 2 hours or 1 day then job are not running from the  last run
> instead it is running from the current time and return to the normal
> schedule.

That's by design (following a long discussion here many years ago).

> Here, how do I run the jobs from last run to current time as per the
> schedules based on hourly, daily and monthly.

The easiest way would probably be to reset the
pgagent.pga_job.jobnextrun value to whatever you want it to be when
the server restarts, before pgAgent is restarted.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company