Thread: Looking for auto starting procedures
Hello ! I am coming from Sql Server right now and have to learn about the infrastructure. What I missed first, is, to execute procedures regularly/repeatedly on a given time. I want to prevent my to write a lot external programs und use cron :-( The othing thing is, that I need some internally running procedures, which do some work. On Sql Server, I can use "auto-start stored procedures". Is there anything like this in postgresql, or what can I do? And, is it just possible, to put a message to the syslog and with my own identification string? Thanks so far! br++mabra
On 12/01/2010 11:16 PM, mabra@manfbraun.de wrote: > The othing thing is, that I need some internally running procedures, which > do some work. On Sql Server, I can use "auto-start stored procedures". > Is there anything like this in postgresql, or what can I do? There is no built-in scheduler at present. Use PgAgent. There's been talk of integrating PgAgent into core, or at least some enhancements to core to let it run without needing an external daemon. > And, is it just possible, to put a message to the syslog and with > my own identification string? I'm not aware of a syslog module, but it'd be handy to have and it'd be pretty simple to write as a C extension. Usually people use 'RAISE NOTICE' to report to the database's logging. -- Craig Ringer
On 2010-12-01 16.16, mabra@manfbraun.de wrote: > Hello ! > > I am coming from Sql Server right now and have to learn about the > infrastructure. > > What I missed first, is, to execute procedures regularly/repeatedly > on a given time. I want to prevent my to write a lot external > programs und use cron :-( cron is the standard way of scheduling reoccurring jobs on *nix systems. It's preferable to having each daemon implement scheduling on its own. > > The othing thing is, that I need some internally running procedures, which > do some work. On Sql Server, I can use "auto-start stored procedures". > Is there anything like this in postgresql, or what can I do? Is this long running processes, i.e daemons, or is it scheduled processes? For long running I would recommend writing a proper daemon. For scheduled I'd recommend cron or at. > > And, is it just possible, to put a message to the syslog and with > my own identification string? I'm pretty sure you can accomplish this in configuration if you're using syslog-ng. Other syslog alternatives probably has similar possibilities. -- Regards, Robert "roppert" Gravsjö
Hello ! And thanks to all, for answers. Naturally, cron does not operate on DB tables and if I add/remove/change a line in a control table dynamically, cron would not notice that ;-) So I had to write a daemon, which acts on that table. This might happen about ~5 - 20 seconds and cron is operates on minutes only. An auto running stored procedure would solve the problem, if therewould be a way to run this procedure on server startup automatically [as it looks, write another daemon for this]. The usual notification from postgreSQL does not allow to write an [own, better to evaluate] identifier in the syslog. This is not a matter of the syslog daemon, its on the program which logs. May be, I have just not found this. Wished, my migration would a bit easier ;-) br++mabra -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Robert Gravsjö Sent: Thursday, December 02, 2010 1:24 PM To: mabra@manfbraun.de Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Looking for auto starting procedures On 2010-12-01 16.16, mabra@manfbraun.de wrote: > Hello ! > > I am coming from Sql Server right now and have to learn about the > infrastructure. > > What I missed first, is, to execute procedures regularly/repeatedly > on a given time. I want to prevent my to write a lot external > programs und use cron :-( cron is the standard way of scheduling reoccurring jobs on *nix systems. It's preferable to having each daemon implement scheduling on its own. > > The othing thing is, that I need some internally running procedures, which > do some work. On Sql Server, I can use "auto-start stored procedures". > Is there anything like this in postgresql, or what can I do? Is this long running processes, i.e daemons, or is it scheduled processes? For long running I would recommend writing a proper daemon. For scheduled I'd recommend cron or at. > > And, is it just possible, to put a message to the syslog and with > my own identification string? I'm pretty sure you can accomplish this in configuration if you're using syslog-ng. Other syslog alternatives probably has similar possibilities. -- Regards, Robert "roppert" Gravsjö -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On Dec 2, 2010, at 11:32 AM, <mabra@manfbraun.de> <mabra@manfbraun.de> wrote: > > The usual notification from postgreSQL does not allow to write > an [own, better to evaluate] identifier in the syslog. This is > not a matter of the syslog daemon, its on the program which logs. > May be, I have just not found this. Am I missing something you require, or would this do it: raise notice 'mynotice: %', some_id; > An auto running stored procedure would solve the problem, if therewould be > a way to run this procedure on server startup automatically > [as it looks, write another daemon for this]. I've actually wanted that as well ;-) But it's not that hard to arrange for your script that starts the PG server to alsorun some SQL after the server launch. -- Scott Ribe scott_ribe@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice
Hello ! a) The idea with to test a RAISE statement with an prefix, could be good. But I am just that new, I could not make it working. I have not understand, where I can issue direct sql statements and it looks like, the RAISE is not possible with plSql: mbtest=# RAISE NOTICE 'mynotice: %', 'hello'; ERROR: syntax error at or near "RAISE" LINE 1: RAISE NOTICE 'mynotice: %', 'hello'; b) Yes, good idea to modify the startup script! Regards++mabra -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Scott Ribe Sent: Thursday, December 02, 2010 8:16 PM To: mabra@manfbraun.demabra@manfbraun.de Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Looking for auto starting procedures On Dec 2, 2010, at 11:32 AM, <mabra@manfbraun.de> <mabra@manfbraun.de> wrote: > > The usual notification from postgreSQL does not allow to write > an [own, better to evaluate] identifier in the syslog. This is > not a matter of the syslog daemon, its on the program which logs. > May be, I have just not found this. Am I missing something you require, or would this do it: raise notice 'mynotice: %', some_id; > An auto running stored procedure would solve the problem, if therewould be > a way to run this procedure on server startup automatically > [as it looks, write another daemon for this]. I've actually wanted that as well ;-) But it's not that hard to arrange for your script that starts the PG server to also run some SQL after the server launch. -- Scott Ribe scott_ribe@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On 12/02/2010 12:27 PM, mabra@manfbraun.de wrote: > Hello ! > > a) > > The idea with to test a RAISE statement with an prefix, could > be good. But I am just that new, I could not make it working. > I have not understand, where I can issue direct sql statements > and it looks like, the RAISE is not possible with plSql: > > mbtest=# RAISE NOTICE 'mynotice: %', 'hello'; > ERROR: syntax error at or near "RAISE" > LINE 1: RAISE NOTICE 'mynotice: %', 'hello'; > It is available in plpgsql: http://www.postgresql.org/docs/9.0/interactive/plpgsql-errors-and-messages.html If you are running 9.0+ you can use DO to run it at the psql prompt: http://www.postgresql.org/docs/9.0/interactive/sql-do.html -- Adrian Klaver adrian.klaver@gmail.com
Hello ! Rellay good;I see, the track is not wong ;-) But currently, I stuck in 8.4 [debian/squeeze]. I'll try to write a function and leran if and how I can call it. Thanks a lot! br++mabra -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Adrian Klaver Sent: Thursday, December 02, 2010 9:35 PM To: mabra@manfbraun.de Cc: @manfbraun.demabra@manfbraun.de'; " Subject: Re: [GENERAL] Looking for auto starting procedures On 12/02/2010 12:27 PM, mabra@manfbraun.de wrote: > Hello ! > > a) > > The idea with to test a RAISE statement with an prefix, could > be good. But I am just that new, I could not make it working. > I have not understand, where I can issue direct sql statements > and it looks like, the RAISE is not possible with plSql: > > mbtest=# RAISE NOTICE 'mynotice: %', 'hello'; > ERROR: syntax error at or near "RAISE" > LINE 1: RAISE NOTICE 'mynotice: %', 'hello'; > It is available in plpgsql: http://www.postgresql.org/docs/9.0/interactive/plpgsql-errors-and-messages.h tml If you are running 9.0+ you can use DO to run it at the psql prompt: http://www.postgresql.org/docs/9.0/interactive/sql-do.html -- Adrian Klaver adrian.klaver@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On Dec 2, 2010, at 1:27 PM, <mabra@manfbraun.de> <mabra@manfbraun.de> wrote:
>
> I have not understand, where I can issue direct sql statements
> and it looks like, the RAISE is not possible with plSql:
Right, it's not actually SQL, so you can't use it in plain SQL. It is part of the plpgsql procedural language.
So you could easily create a small stored procedure, for example:
create function myraise(msg varchar, id varchar) returns void as $$ begin
raise notice '%: %', msg, id;
end; $$ language plpgsql;
and call that from SQL:
select myraise ('mymsg', '1234');
--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice
Hello !
Very good, thanks !
I've just not understood, when I have to write a function to test sql code
and when I can do it interactively.
Your shown function compiles and works, but I do not have the
result in the logs [altough I see th executing function with
my settings to 'debug' ;-) ].
Will just configure the logging tomorrow, that way, that the
stronger ones are going to the syslog.
br++mabra
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Scott Ribe
Sent: Thursday, December 02, 2010 11:22 PM
To: mabra@manfbraun.demabra@manfbraun.de
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Looking for auto starting procedures
On Dec 2, 2010, at 1:27 PM, <mabra@manfbraun.de> <mabra@manfbraun.de> wrote:
>
> I have not understand, where I can issue direct sql statements
> and it looks like, the RAISE is not possible with plSql:
Right, it's not actually SQL, so you can't use it in plain SQL. It is part
of the plpgsql procedural language.
So you could easily create a small stored procedure, for example:
create function myraise(msg varchar, id varchar) returns void as $$ begin
raise notice '%: %', msg, id;
end; $$ language plpgsql;
and call that from SQL:
select myraise ('mymsg', '1234');
--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Excerpts from mabra's message of jue dic 02 20:04:36 -0300 2010: > I've just not understood, when I have to write a function to test sql code > and when I can do it interactively. In Postgres, PL/pgSQL and SQL are two different languages. There are things in PL/pgSQL that you cannot do in pure SQL. RAISE is one of them. -- Álvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Hello ! Yes, thanks, I am just trying to lern the difference. I am comin from Sql Server, but I am not a experienced db developer. But in Sql Server, you may hack your tests just in a direct statement and if it runs, wrap/put it in the function or procedure. Will need some time ;-) Thanks a lot! br++mabra -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Alvaro Herrera Sent: Friday, December 03, 2010 1:22 AM To: mabra Cc: pgsql-general Subject: Re: [GENERAL] Looking for auto starting procedures Excerpts from mabra's message of jue dic 02 20:04:36 -0300 2010: > I've just not understood, when I have to write a function to test sql code > and when I can do it interactively. In Postgres, PL/pgSQL and SQL are two different languages. There are things in PL/pgSQL that you cannot do in pure SQL. RAISE is one of them. -- Álvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Le 03/12/2010 21:22, manfred.braun a écrit : > [...] > Yes, thanks, I am just trying to lern the difference. > I am comin from Sql Server, but I am not a experienced > db developer. But in Sql Server, you may hack your tests > just in a direct statement and if it runs, wrap/put > it in the function or procedure. > You can use the DO command in 9.0 to do just that. -- Guillaume http://www.postgresql.fr http://dalibo.com
2010/12/2 <mabra@manfbraun.de>: > Hello ! > > And thanks to all, for answers. > > Naturally, cron does not operate on DB tables and if I add/remove/change > a line in a control table dynamically, cron would not notice that ;-) > So I had to write a daemon, which acts on that table. This might happen > about ~5 - 20 seconds and cron is operates on minutes only. Perhaps you are trying to solve something with SQL server behavior and you may find more interesting ways "à la PostgreSQL". I wonder if you are not looking after something like PgQ[1] or LISTEN/NOTIFY [2] > > An auto running stored procedure would solve the problem, if therewould be > a way to run this procedure on server startup automatically > [as it looks, write another daemon for this]. on server start ?! no daemon is necesary. > > The usual notification from postgreSQL does not allow to write > an [own, better to evaluate] identifier in the syslog. This is > not a matter of the syslog daemon, its on the program which logs. > May be, I have just not found this. Maybe you need to adjust your 'roles' [3] so that log lines can be identified to your role. Maybe you'll have better answers if you define your objectives. [1] http://wiki.postgresql.org/wiki/PGQ_Tutorial [2] http://www.postgresql.org/docs/current/interactive/sql-notify.html [3] http://www.postgresql.org/docs/current/interactive/sql-createrole.html -- Cédric Villemain 2ndQuadrant http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support
We're backing up our database using pg_dump with compression. We're selecting each database however when we tried running a pg_restore everything cept for the roles were restored. I'm digging through the pg_restore options, Is there an option I'm forgetting to include? Also we're restoring from an 8.3.8 database to a 9.0 database. Not sure if that's part of our problem. Digging through the docs we're doing 'pg_restore -C -d postgres data.dmp' Am I missing something? Thanks!
u235sentinel <u235sentinel@gmail.com> writes:
> We're backing up our database using pg_dump with compression. We're
> selecting each database however when we tried running a pg_restore
> everything cept for the roles were restored.
> I'm digging through the pg_restore options, Is there an option I'm
> forgetting to include?
pg_dumpall -g
regards, tom lane
Hello !
With server start, I mean the start of the postgresql engine, not the
machine itself.
I thought about a stored procedure to come into a LISTEN in a loop, but the
loop
must be started anyway [avoid a separate daemon {now, I know, I can use a
pgsql script}].
LISTEN looks great to me [not tried yet]. Thanks for the pointers, I have
yet not
heard about PGQ and it looks like something I missed on Sql Server! Will
study
this!
For logging:I think, I have all rights, but not postgresql configured to
log to syslog.
Much thanks!
br++mabra
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Cédric Villemain
Sent: Saturday, December 04, 2010 3:12 PM
To: mabra@manfbraun.de
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Looking for auto starting procedures
2010/12/2 <mabra@manfbraun.de>:
> Hello !
>
> And thanks to all, for answers.
>
> Naturally, cron does not operate on DB tables and if I add/remove/change
> a line in a control table dynamically, cron would not notice that ;-)
> So I had to write a daemon, which acts on that table. This might happen
> about ~5 - 20 seconds and cron is operates on minutes only.
Perhaps you are trying to solve something with SQL server behavior and
you may find more interesting ways "à la PostgreSQL".
I wonder if you are not looking after something like PgQ[1] or LISTEN/NOTIFY
[2]
>
> An auto running stored procedure would solve the problem, if therewould be
> a way to run this procedure on server startup automatically
> [as it looks, write another daemon for this].
on server start ?! no daemon is necesary.
>
> The usual notification from postgreSQL does not allow to write
> an [own, better to evaluate] identifier in the syslog. This is
> not a matter of the syslog daemon, its on the program which logs.
> May be, I have just not found this.
Maybe you need to adjust your 'roles' [3] so that log lines can be
identified to your role.
Maybe you'll have better answers if you define your objectives.
[1] http://wiki.postgresql.org/wiki/PGQ_Tutorial
[2] http://www.postgresql.org/docs/current/interactive/sql-notify.html
[3] http://www.postgresql.org/docs/current/interactive/sql-createrole.html
--
Cédric Villemain 2ndQuadrant
http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general