From: | "Yelai, Ramkumar IN BLR STS" <ramkumar(dot)yelai(at)siemens(dot)com> |
---|---|
To: | Dave Page <dpage(at)pgadmin(dot)org> |
Cc: | "pgadmin-support(at)postgresql(dot)org" <pgadmin-support(at)postgresql(dot)org> |
Subject: | Re: Time series aggregation |
Date: | 2012-06-27 10:44:04 |
Message-ID: | 13D0F6C9B3073A4999E61CAAD61AE7ECC3B09D92DA@INBLRK77M2MSX.in002.siemens.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgadmin-support |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Kapil Kr. Khandelwal | 2012-06-27 11:15:19 | How to run query by command prompt in Postgres |
Previous Message | Dave Page | 2012-06-27 08:43:43 | Re: Time series aggregation |