Обсуждение: Automatic SQL command execution
Dear all, I am new to PostgreSQL and have just started using it for a few weeks. I have a (probably very simple) question regarding an execution of a SQL command which is automatically repeated every period of time. For example, I want to copy a txt file into a table (e.g. with command COPY traffic FROM 'C:/TEMP/flow.txt' ) every 5 minutes. The 'flow.txt' file is produced by an external system and updated (by overwriting it) every 5 minutes. Can anyone let me know how to do it or where to put the command in PostgreSQL to automate the execution of the copy command according to time? Many thanks in advance. Yos
On Fri, Jun 27, 2008 at 5:08 AM, Yos Sunitiyoso <yos.sunitiyoso@gmail.com> wrote: > Dear all, > > I am new to PostgreSQL and have just started using it for a few weeks. > I have a (probably very simple) question regarding an execution of a > SQL command which is automatically repeated every period of time. For > example, I want to copy a txt file into a table (e.g. with command > COPY traffic FROM 'C:/TEMP/flow.txt' ) every 5 minutes. The > 'flow.txt' file is produced by an external system and updated (by > overwriting it) every 5 minutes. Can anyone let me know how to do it > or where to put the command in PostgreSQL to automate the execution of > the copy command according to time? > Many thanks in advance. Postgresql doesn't have tools to do that directly. If you are on Linux or Unix, you can use cron; Windows probably has something similar to automate tasks. Sean
--- On Fri, 6/27/08, Sean Davis <sdavis2@mail.nih.gov> wrote: > Postgresql doesn't have tools to do that directly. If > you are on > Linux or Unix, you can use cron; Windows probably has > something > similar to automate tasks. > I use nncron lite. Free and cron file compatible. http://www.nncron.ru/nncronlt/help/help.htm Lewis R Cunningham An Expert's Guide to Oracle Technology http://blogs.ittoolbox.com/oracle/guide/ Postgres Forums http://postgres.enterprisedb.com/forum.do
--- On Fri, 6/27/08, Sean Davis <sdavis2@mail.nih.gov> wrote: >> Postgresql doesn't have tools to do that directly. If >> you are on >> Linux or Unix, you can use cron; Windows probably has >> something >> similar to automate tasks. >> > I use nncron lite. Free and cron file compatible. > http://www.nncron.ru/nncronlt/help/help.htm > Lewis R Cunningham > An Expert's Guide to Oracle Technology > http://blogs.ittoolbox.com/oracle/guide/ > Postgres Forums > http://postgres.enterprisedb.com/forum.do Actually you can use PgAgent which comes packaged with PgAdmin III. I've used it successfully on both windows and RedHat linux. I think it works anywhere PostgreSQL works. The nice thing about PgAgent is that you can see the status of jobs from the PgAdmin administrative tool and trigger a manual run of a scheduled job from PgAdmin tool from anywhere. http://www.pgadmin.org/docs/1.8/pgagent.html Hope that helps, Regina ----------------------------------------- The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer.
Dear Sean, Lewis and Regina, Many thanks for your suggestions. It looks there are several options doing automatic tasks on PostgreSQL. I found that Windows has a task scheduler. I tried to use it but still have problem in connecting with the server (and database). I may need to learn setting the correct parameters. I will try to use the PgAgent or nncronlite as they seem to be better options. I hope you would help me again if I have further questions. Many thanks. Kind regards, Yos On Tue, Jul 1, 2008 at 8:30 PM, Obe, Regina <robe.dnd@cityofboston.gov> wrote: > > --- On Fri, 6/27/08, Sean Davis <sdavis2@mail.nih.gov> wrote: > >>> Postgresql doesn't have tools to do that directly. If >>> you are on >>> Linux or Unix, you can use cron; Windows probably has >>> something >>> similar to automate tasks. >>> > >> I use nncron lite. Free and cron file compatible. > >> http://www.nncron.ru/nncronlt/help/help.htm > >> Lewis R Cunningham > >> An Expert's Guide to Oracle Technology >> http://blogs.ittoolbox.com/oracle/guide/ > >> Postgres Forums >> http://postgres.enterprisedb.com/forum.do > > > Actually you can use PgAgent which comes packaged with PgAdmin III. > I've used it successfully on both windows and RedHat linux. I think it > works anywhere PostgreSQL works. The nice thing about PgAgent is that > you can see the status of jobs from the PgAdmin administrative tool and > trigger a manual run of a scheduled job from PgAdmin tool from anywhere. > > > http://www.pgadmin.org/docs/1.8/pgagent.html > > Hope that helps, > Regina > ----------------------------------------- > The substance of this message, including any attachments, may be > confidential, legally privileged and/or exempt from disclosure > pursuant to Massachusetts law. It is intended > solely for the addressee. If you received this in error, please > contact the sender and delete the material from any computer. > >