Thread: Postgres and GnuPlot
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
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 >
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 > > > >
> 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.
>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.
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
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 >
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) >
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 >
[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]