Looking for auto starting procedures

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 also run 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]http://wiki.postgresql.org/wiki/PGQ_Tutorial or LISTEN/NOTIFY [2]http://www.postgresql.org/docs/current/interactive/sql-notify.html

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]http://www.postgresql.org/docs/current/interactive/sql-createrole.html 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]http://wiki.postgresql.org/wiki/PGQ_Tutorial or LISTEN/NOTIFY
[2]: http://www.postgresql.org/docs/current/interactive/sql-notify.html

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]http://www.postgresql.org/docs/current/interactive/sql-createrole.html 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