Thread: Looking for auto starting procedures

Looking for auto starting procedures

From
Date:
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



Re: Looking for auto starting procedures

From
Craig Ringer
Date:
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

Re: Looking for auto starting procedures

From
Robert Gravsjö
Date:

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ö

Re: Looking for auto starting procedures

From
Date:
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



Re: Looking for auto starting procedures

From
Scott Ribe
Date:
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





Re: Looking for auto starting procedures

From
Date:
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



Re: Looking for auto starting procedures

From
Adrian Klaver
Date:
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

Re: Looking for auto starting procedures

From
Date:
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



Re: Looking for auto starting procedures

From
Scott Ribe
Date:
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





Re: Looking for auto starting procedures

From
Date:
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



Re: Looking for auto starting procedures

From
Alvaro Herrera
Date:
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

Re: Looking for auto starting procedures

From
"manfred.braun"
Date:
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



Re: Looking for auto starting procedures

From
Guillaume Lelarge
Date:
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

Re: Looking for auto starting procedures

From
Cédric Villemain
Date:
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

pg_restore question

From
u235sentinel
Date:
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!

Re: pg_restore question

From
Tom Lane
Date:
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

Re: Looking for auto starting procedures

From
Date:
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