Thread: Postgres and GnuPlot

Postgres and GnuPlot

From
Hrishikesh Deshmukh
Date:
Hi All,

Has anybody tried using gnuplot to plot results from queries; there
are some suggestions given in PostgreSQL Developer's Handbook but i
have not any luck so far!
Any pointers as how to send results from queries straight to a simple
plotting utility (ex:gnuplot)!

Thanks,
Hrishi

Re: Postgres and GnuPlot

From
Ben
Date:
Heh. Funny you should ask; I spent a fair amount of time yesterday
doing precisely this.

Here's my gnuplot file:

set terminal png small color picsize 1000 400
set style fill solid 1.0 border
set title "Concurrent listeners"
set xdata time
set xlabel "Time"
set ylabel "Cuncurrent Logged In Listeners"
set format x "%Y/%m/%d\n%H:%M:%S"
set timefmt "%s"
plot "< echo \"select t-7*3600,c from stats_concurrent_listener_count
(extract(epoch from '2004/12/13 20:00'::timestamp)::int,null);\" |
psql -h plur -U greenroom gr-dev -t | sed 's/|//'" using 1:2 with boxes

cat file | gnuplot > image.png works perfectly.

The bitch of it is that I seem to be having a hard time running this
as a cgi. I can't figure out. Not that this question has anything to
do with postgres, but maybe somebody else can tell me where I'm going
wrong? My CGI is:


#!/bin/bash

echo -e "Content-type: image/png\n\n";
echo -e "set terminal png small color picsize 1000 400\nset style
fill solid 1.0 border\nset title \"Concurrent listeners\"\nset xdata
time\nset xlabel \"Time\"\nset ylabel \"Cuncurrent Logged In Listeners
\"\nset format x \"%Y/%m/%d\\\n%H:%M:%S\"\nset timefmt \"%s\"\nplot
\"< echo \\\"select t-7*3600,c from stats_concurrent_listener_count
(extract(epoch from '2004/12/13 20:00'::timestamp)::int,null);\\\" | /
usr/bin/psql -h plur -U greenroom gr-dev -t | sed 's/|//'\" using 1:2
with boxes" | /usr/bin/gnuplot

...and it seems that when printing to stdout, it gives a truncated
version of the file. When redirecting, it does not. Maybe that's not
too surprising, if apache is looking for a null to see when stdout
stops.... but I don't know how to get around that.


On May 9, 2005, at 7:51 AM, Hrishikesh Deshmukh wrote:

> Hi All,
>
> Has anybody tried using gnuplot to plot results from queries; there
> are some suggestions given in PostgreSQL Developer's Handbook but i
> have not any luck so far!
> Any pointers as how to send results from queries straight to a simple
> plotting utility (ex:gnuplot)!
>
> Thanks,
> Hrishi
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>


Re: Postgres and GnuPlot

From
Hrishikesh Deshmukh
Date:
Hmmmm......looks like i simply can't send the query results to gnuplot
without an intermediary!! So far my search results have not yielded
anything....i guess intermediary is the only way to !! :(((

Hrishi

On 5/9/05, Ben <bench@silentmedia.com> wrote:
> Heh. Funny you should ask; I spent a fair amount of time yesterday
> doing precisely this.
>
> Here's my gnuplot file:
>
> set terminal png small color picsize 1000 400
> set style fill solid 1.0 border
> set title "Concurrent listeners"
> set xdata time
> set xlabel "Time"
> set ylabel "Cuncurrent Logged In Listeners"
> set format x "%Y/%m/%d\n%H:%M:%S"
> set timefmt "%s"
> plot "< echo \"select t-7*3600,c from stats_concurrent_listener_count
> (extract(epoch from '2004/12/13 20:00'::timestamp)::int,null);\" |
> psql -h plur -U greenroom gr-dev -t | sed 's/|//'" using 1:2 with boxes
>
> cat file | gnuplot > image.png works perfectly.
>
> The bitch of it is that I seem to be having a hard time running this
> as a cgi. I can't figure out. Not that this question has anything to
> do with postgres, but maybe somebody else can tell me where I'm going
> wrong? My CGI is:
>
> #!/bin/bash
>
> echo -e "Content-type: image/png\n\n";
> echo -e "set terminal png small color picsize 1000 400\nset style
> fill solid 1.0 border\nset title \"Concurrent listeners\"\nset xdata
> time\nset xlabel \"Time\"\nset ylabel \"Cuncurrent Logged In Listeners
> \"\nset format x \"%Y/%m/%d\\\n%H:%M:%S\"\nset timefmt \"%s\"\nplot
> \"< echo \\\"select t-7*3600,c from stats_concurrent_listener_count
> (extract(epoch from '2004/12/13 20:00'::timestamp)::int,null);\\\" | /
> usr/bin/psql -h plur -U greenroom gr-dev -t | sed 's/|//'\" using 1:2
> with boxes" | /usr/bin/gnuplot
>
> ...and it seems that when printing to stdout, it gives a truncated
> version of the file. When redirecting, it does not. Maybe that's not
> too surprising, if apache is looking for a null to see when stdout
> stops.... but I don't know how to get around that.
>
>
> On May 9, 2005, at 7:51 AM, Hrishikesh Deshmukh wrote:
>
> > Hi All,
> >
> > Has anybody tried using gnuplot to plot results from queries; there
> > are some suggestions given in PostgreSQL Developer's Handbook but i
> > have not any luck so far!
> > Any pointers as how to send results from queries straight to a simple
> > plotting utility (ex:gnuplot)!
> >
> > Thanks,
> > Hrishi
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> >
>
>

Re: Postgres and GnuPlot

From
Mila Boldareva
Date:
> Has anybody tried using gnuplot to plot results from queries; there
> are some suggestions given in PostgreSQL Developer's Handbook but i
> have not any luck so far!
> Any pointers as how to send results from queries straight to a simple
> plotting utility (ex:gnuplot)!

I use a text file as an intermediary.
You only need to slightly modify (postgres output) and/or (gnuplot input) options:

for postgres, put something like this before the query:

\pset null '?9'
--  or other impossible beginning char to indicate missing values to gnuplot

\t
--  will show only data rows

\f '\t'  -- make sure columns are tab-separated

-- don't align the output:
\a

Instead of the last two, you may change in the gnuplot script:

set datafile separator '|'

Additionally, let gnuplot know your missing columns:
set datafile missing '?'

Hope this helps.

Re: Postgres and GnuPlot

From
Mila Boldareva
Date:
>Has anybody tried using gnuplot to plot results from queries; there
>are some suggestions given in PostgreSQL Developer's Handbook but i
>have not any luck so far!
>Any pointers as how to send results from queries straight to a simple
>plotting utility (ex:gnuplot)!
>
>
>
I use a text file as an intermediary.
You only need to slightly modify (postgres output) and/or (gnuplot
input) options:

for postgres, put something like this before the query:

\pset null '?9'
--  or other impossible beginning char to indicate missing values correctly

\t
--  will show only data rows

\f '\t'
-- make sure columns are tab-separated

-- don't align the output:
\a

Instead of the last two, you may change in the gnuplot script:

set datafile separator '|'

Additionally, let gnuplot know your missing columns:
set datafile missing '?'

Hope this helps.

Re: Postgres and GnuPlot

From
"Sean Davis"
Date:
As an alternative, be sure to check out pl/R.  Everything happens inside the
server except for the actual plot which goes to a file.

Sean

----- Original Message -----
From: "Hrishikesh Deshmukh" <hdeshmuk@gmail.com>
To: "Ben" <bench@silentmedia.com>
Cc: "Postgresql-General" <pgsql-general@postgresql.org>
Sent: Monday, May 09, 2005 11:40 AM
Subject: Re: [GENERAL] Postgres and GnuPlot


Hmmmm......looks like i simply can't send the query results to gnuplot
without an intermediary!! So far my search results have not yielded
anything....i guess intermediary is the only way to !! :(((

Hrishi

On 5/9/05, Ben <bench@silentmedia.com> wrote:
> Heh. Funny you should ask; I spent a fair amount of time yesterday
> doing precisely this.
>
> Here's my gnuplot file:
>
> set terminal png small color picsize 1000 400
> set style fill solid 1.0 border
> set title "Concurrent listeners"
> set xdata time
> set xlabel "Time"
> set ylabel "Cuncurrent Logged In Listeners"
> set format x "%Y/%m/%d\n%H:%M:%S"
> set timefmt "%s"
> plot "< echo \"select t-7*3600,c from stats_concurrent_listener_count
> (extract(epoch from '2004/12/13 20:00'::timestamp)::int,null);\" |
> psql -h plur -U greenroom gr-dev -t | sed 's/|//'" using 1:2 with boxes
>
> cat file | gnuplot > image.png works perfectly.
>
> The bitch of it is that I seem to be having a hard time running this
> as a cgi. I can't figure out. Not that this question has anything to
> do with postgres, but maybe somebody else can tell me where I'm going
> wrong? My CGI is:
>
> #!/bin/bash
>
> echo -e "Content-type: image/png\n\n";
> echo -e "set terminal png small color picsize 1000 400\nset style
> fill solid 1.0 border\nset title \"Concurrent listeners\"\nset xdata
> time\nset xlabel \"Time\"\nset ylabel \"Cuncurrent Logged In Listeners
> \"\nset format x \"%Y/%m/%d\\\n%H:%M:%S\"\nset timefmt \"%s\"\nplot
> \"< echo \\\"select t-7*3600,c from stats_concurrent_listener_count
> (extract(epoch from '2004/12/13 20:00'::timestamp)::int,null);\\\" | /
> usr/bin/psql -h plur -U greenroom gr-dev -t | sed 's/|//'\" using 1:2
> with boxes" | /usr/bin/gnuplot
>
> ...and it seems that when printing to stdout, it gives a truncated
> version of the file. When redirecting, it does not. Maybe that's not
> too surprising, if apache is looking for a null to see when stdout
> stops.... but I don't know how to get around that.
>
>
> On May 9, 2005, at 7:51 AM, Hrishikesh Deshmukh wrote:
>
> > Hi All,
> >
> > Has anybody tried using gnuplot to plot results from queries; there
> > are some suggestions given in PostgreSQL Developer's Handbook but i
> > have not any luck so far!
> > Any pointers as how to send results from queries straight to a simple
> > plotting utility (ex:gnuplot)!
> >
> > Thanks,
> > Hrishi
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> >
>
>

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org



Re: Postgres and GnuPlot

From
Ben
Date:
Well, there is this:

http://www.varlena.com/varlena/GeneralBits/Tidbits/bernier/art_66/graphingWithR.html

I thought it was overkill for me, so I went with the gnuplot method.

On Mon, 9 May 2005, Hrishikesh Deshmukh wrote:

> Hmmmm......looks like i simply can't send the query results to gnuplot
> without an intermediary!! So far my search results have not yielded
> anything....i guess intermediary is the only way to !! :(((
>
> Hrishi
>
> On 5/9/05, Ben <bench@silentmedia.com> wrote:
> > Heh. Funny you should ask; I spent a fair amount of time yesterday
> > doing precisely this.
> >
> > Here's my gnuplot file:
> >
> > set terminal png small color picsize 1000 400
> > set style fill solid 1.0 border
> > set title "Concurrent listeners"
> > set xdata time
> > set xlabel "Time"
> > set ylabel "Cuncurrent Logged In Listeners"
> > set format x "%Y/%m/%d\n%H:%M:%S"
> > set timefmt "%s"
> > plot "< echo \"select t-7*3600,c from stats_concurrent_listener_count
> > (extract(epoch from '2004/12/13 20:00'::timestamp)::int,null);\" |
> > psql -h plur -U greenroom gr-dev -t | sed 's/|//'" using 1:2 with boxes
> >
> > cat file | gnuplot > image.png works perfectly.
> >
> > The bitch of it is that I seem to be having a hard time running this
> > as a cgi. I can't figure out. Not that this question has anything to
> > do with postgres, but maybe somebody else can tell me where I'm going
> > wrong? My CGI is:
> >
> > #!/bin/bash
> >
> > echo -e "Content-type: image/png\n\n";
> > echo -e "set terminal png small color picsize 1000 400\nset style
> > fill solid 1.0 border\nset title \"Concurrent listeners\"\nset xdata
> > time\nset xlabel \"Time\"\nset ylabel \"Cuncurrent Logged In Listeners
> > \"\nset format x \"%Y/%m/%d\\\n%H:%M:%S\"\nset timefmt \"%s\"\nplot
> > \"< echo \\\"select t-7*3600,c from stats_concurrent_listener_count
> > (extract(epoch from '2004/12/13 20:00'::timestamp)::int,null);\\\" | /
> > usr/bin/psql -h plur -U greenroom gr-dev -t | sed 's/|//'\" using 1:2
> > with boxes" | /usr/bin/gnuplot
> >
> > ...and it seems that when printing to stdout, it gives a truncated
> > version of the file. When redirecting, it does not. Maybe that's not
> > too surprising, if apache is looking for a null to see when stdout
> > stops.... but I don't know how to get around that.
> >
> >
> > On May 9, 2005, at 7:51 AM, Hrishikesh Deshmukh wrote:
> >
> > > Hi All,
> > >
> > > Has anybody tried using gnuplot to plot results from queries; there
> > > are some suggestions given in PostgreSQL Developer's Handbook but i
> > > have not any luck so far!
> > > Any pointers as how to send results from queries straight to a simple
> > > plotting utility (ex:gnuplot)!
> > >
> > > Thanks,
> > > Hrishi
> > >
> > > ---------------------------(end of
> > > broadcast)---------------------------
> > > TIP 4: Don't 'kill -9' the postmaster
> > >
> >
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org
>



backup compress...blobs/insert commands/verbose messages

From
"Zlatko Matic"
Date:
Can someone explain me the following options while using pgAdimn III for
backup:

blobs
insert commands
verbose messages

Thanks.


----- Original Message -----
From: "Sean Davis" <sdavis2@mail.nih.gov>
To: "Hrishikesh Deshmukh" <hdeshmuk@gmail.com>; "Ben"
<bench@silentmedia.com>
Cc: "Postgresql-General" <pgsql-general@postgresql.org>
Sent: Monday, May 09, 2005 6:03 PM
Subject: Re: [GENERAL] Postgres and GnuPlot


> As an alternative, be sure to check out pl/R.  Everything happens inside
> the server except for the actual plot which goes to a file.
>
> Sean
>
> ----- Original Message -----
> From: "Hrishikesh Deshmukh" <hdeshmuk@gmail.com>
> To: "Ben" <bench@silentmedia.com>
> Cc: "Postgresql-General" <pgsql-general@postgresql.org>
> Sent: Monday, May 09, 2005 11:40 AM
> Subject: Re: [GENERAL] Postgres and GnuPlot
>
>
> Hmmmm......looks like i simply can't send the query results to gnuplot
> without an intermediary!! So far my search results have not yielded
> anything....i guess intermediary is the only way to !! :(((
>
> Hrishi
>
> On 5/9/05, Ben <bench@silentmedia.com> wrote:
>> Heh. Funny you should ask; I spent a fair amount of time yesterday
>> doing precisely this.
>>
>> Here's my gnuplot file:
>>
>> set terminal png small color picsize 1000 400
>> set style fill solid 1.0 border
>> set title "Concurrent listeners"
>> set xdata time
>> set xlabel "Time"
>> set ylabel "Cuncurrent Logged In Listeners"
>> set format x "%Y/%m/%d\n%H:%M:%S"
>> set timefmt "%s"
>> plot "< echo \"select t-7*3600,c from stats_concurrent_listener_count
>> (extract(epoch from '2004/12/13 20:00'::timestamp)::int,null);\" |
>> psql -h plur -U greenroom gr-dev -t | sed 's/|//'" using 1:2 with boxes
>>
>> cat file | gnuplot > image.png works perfectly.
>>
>> The bitch of it is that I seem to be having a hard time running this
>> as a cgi. I can't figure out. Not that this question has anything to
>> do with postgres, but maybe somebody else can tell me where I'm going
>> wrong? My CGI is:
>>
>> #!/bin/bash
>>
>> echo -e "Content-type: image/png\n\n";
>> echo -e "set terminal png small color picsize 1000 400\nset style
>> fill solid 1.0 border\nset title \"Concurrent listeners\"\nset xdata
>> time\nset xlabel \"Time\"\nset ylabel \"Cuncurrent Logged In Listeners
>> \"\nset format x \"%Y/%m/%d\\\n%H:%M:%S\"\nset timefmt \"%s\"\nplot
>> \"< echo \\\"select t-7*3600,c from stats_concurrent_listener_count
>> (extract(epoch from '2004/12/13 20:00'::timestamp)::int,null);\\\" | /
>> usr/bin/psql -h plur -U greenroom gr-dev -t | sed 's/|//'\" using 1:2
>> with boxes" | /usr/bin/gnuplot
>>
>> ...and it seems that when printing to stdout, it gives a truncated
>> version of the file. When redirecting, it does not. Maybe that's not
>> too surprising, if apache is looking for a null to see when stdout
>> stops.... but I don't know how to get around that.
>>
>>
>> On May 9, 2005, at 7:51 AM, Hrishikesh Deshmukh wrote:
>>
>> > Hi All,
>> >
>> > Has anybody tried using gnuplot to plot results from queries; there
>> > are some suggestions given in PostgreSQL Developer's Handbook but i
>> > have not any luck so far!
>> > Any pointers as how to send results from queries straight to a simple
>> > plotting utility (ex:gnuplot)!
>> >
>> > Thanks,
>> > Hrishi
>> >
>> > ---------------------------(end of
>> > broadcast)---------------------------
>> > TIP 4: Don't 'kill -9' the postmaster
>> >
>>
>>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>               http://archives.postgresql.org
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>


Re: Postgres and GnuPlot

From
Ben
Date:
For the sake of the archives, I just found out that my problem was that:

echo -e "Content-type: image/png\n\n";

...actually throws in 3 newlines, not two. (Doh!) Fixing that means I now
have a CGI that generates gnuplots from postgres entirely in the pipeline,
withouut any annoying temp files. pl/R is certainly more powerful, but, as
far as I could tell, requires temp files.

On Mon, 9 May 2005, Ben wrote:

> Heh. Funny you should ask; I spent a fair amount of time yesterday
> doing precisely this.
>
> Here's my gnuplot file:
>
> set terminal png small color picsize 1000 400
> set style fill solid 1.0 border
> set title "Concurrent listeners"
> set xdata time
> set xlabel "Time"
> set ylabel "Cuncurrent Logged In Listeners"
> set format x "%Y/%m/%d\n%H:%M:%S"
> set timefmt "%s"
> plot "< echo \"select t-7*3600,c from stats_concurrent_listener_count
> (extract(epoch from '2004/12/13 20:00'::timestamp)::int,null);\" |
> psql -h plur -U greenroom gr-dev -t | sed 's/|//'" using 1:2 with boxes
>
> cat file | gnuplot > image.png works perfectly.
>
> The bitch of it is that I seem to be having a hard time running this
> as a cgi. I can't figure out. Not that this question has anything to
> do with postgres, but maybe somebody else can tell me where I'm going
> wrong? My CGI is:
>
>
> #!/bin/bash
>
> echo -e "Content-type: image/png\n\n";
> echo -e "set terminal png small color picsize 1000 400\nset style
> fill solid 1.0 border\nset title \"Concurrent listeners\"\nset xdata
> time\nset xlabel \"Time\"\nset ylabel \"Cuncurrent Logged In Listeners
> \"\nset format x \"%Y/%m/%d\\\n%H:%M:%S\"\nset timefmt \"%s\"\nplot
> \"< echo \\\"select t-7*3600,c from stats_concurrent_listener_count
> (extract(epoch from '2004/12/13 20:00'::timestamp)::int,null);\\\" | /
> usr/bin/psql -h plur -U greenroom gr-dev -t | sed 's/|//'\" using 1:2
> with boxes" | /usr/bin/gnuplot
>
> ...and it seems that when printing to stdout, it gives a truncated
> version of the file. When redirecting, it does not. Maybe that's not
> too surprising, if apache is looking for a null to see when stdout
> stops.... but I don't know how to get around that.
>
>
> On May 9, 2005, at 7:51 AM, Hrishikesh Deshmukh wrote:
>
> > Hi All,
> >
> > Has anybody tried using gnuplot to plot results from queries; there
> > are some suggestions given in PostgreSQL Developer's Handbook but i
> > have not any luck so far!
> > Any pointers as how to send results from queries straight to a simple
> > plotting utility (ex:gnuplot)!
> >
> > Thanks,
> > Hrishi
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>



Re: backup compress...blobs/insert commands/verbose

From
Ragnar Hafstað
Date:
[note: it is better to create a new thread than to hijack an
       unrelated one]

On Mon, 2005-05-09 at 18:45 +0200, Zlatko Matic wrote:
> Can someone explain me the following options while using pgAdimn III for
> backup:
>
my guess is...

> blobs
include blobs in backup. blobs are not included
by default (I think)

> insert commands
text file backups create a file containing a series
of SQL commands. data is imported with COPY, which is effective,
but you might prefer a series of INSERT commands, if you want to
keep it more portable between database systems, or need to
import it into old postgresql versions.

gnari

[snip quote of an entire unrelated email]