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
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.
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:
Show quoted text
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
Show quoted text
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 boxescat 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.
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 boxescat 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?
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:
Show quoted text
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 boxescat 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?
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
Show quoted text
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 GnuPlotHmmmm......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 boxescat 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:
Show quoted text
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 boxescat 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]