Re: Time series aggregation

Thanks Dave.

I have reset the pgagent.pga_job.jobnextrun when server restarts, however it will be reset by pgAgent, when it starts.

Following are the steps, I have done.

1. Around 2:45 PM, I have stopped the server
2. I have started the server at 4:03 PM and I reset the jobnextrun ( from pgAdmin UI ) to 3:00PM.
3. Soon I started the pgAgent at 4:04PM, the jobnextrun is changed to 4:04 PM and it run once.

Here, I lost the 2:00 PM to 3:00 PM aggregation.

Please let me know is there any additional steps I have to be done.

Thanks & Regards,
Ramkumar

-----Original Message-----
From: Dave Page [mailto:dpage(at)pgadmin(dot)org]
Sent: Wednesday, June 27, 2012 2:14 PM
To: Yelai, Ramkumar IN BLR STS
Cc: pgadmin-support(at)postgresql(dot)org
Subject: Re: [pgadmin-support] 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