Discussion:
Date range for pg_stat_all_tables?
(too old to reply)
Ronit Allen
2012-11-12 22:54:17 UTC
Permalink
Hi -

I have the following query on pg_stat_all_tables to look at updates,
inserts, and deletes:

SELECT relname, n_tup_ins, n_tup_upd, n_tup_del FROM pg_stat_all_tables;

How can I add a date range to the WHERE clause to show data from a
specified date range?

I don't see anything in the table or in possible joins that will provide
dates.

Thanks for any assistance that can be provided.

Ronit
Albe Laurenz
2012-11-13 08:54:26 UTC
Permalink
Post by Ronit Allen
I have the following query on pg_stat_all_tables to look at updates,
SELECT relname, n_tup_ins, n_tup_upd, n_tup_del FROM
pg_stat_all_tables;
Post by Ronit Allen
How can I add a date range to the WHERE clause to show data from a
specified date range?
Post by Ronit Allen
I don't see anything in the table or in possible joins that will
provide dates.
Post by Ronit Allen
Thanks for any assistance that can be provided.
If you want that, you will have to take regular snapshots
of the table (e.g. with a cron job).
Then you can calculate differences.

Yours,
Laurenz Albe
--
Sent via pgsql-admin mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Ronit Allen
2012-11-15 19:35:04 UTC
Permalink
Hi -

Thanks for below.

On 9.1 with postgresql.conf left at defaults, what period of time does a
query on pg_stat_all_tables cover?

Let's say my database has been running for exactly one year.

I then issue SELECT relname, n_tup_ins FROM pg_stat_all_tables;

Do the results show me inserts for the last 7 days? 30 days? all time?..

I don't see the answer to this in
http://www.postgresql.org/docs/9.1/static/monitoring-stats.html (or maybe
it is there and I am not understanding).

Thanks once again,
Ronit
Post by Ronit Allen
Post by Ronit Allen
I have the following query on pg_stat_all_tables to look at updates,
SELECT relname, n_tup_ins, n_tup_upd, n_tup_del FROM
pg_stat_all_tables;
Post by Ronit Allen
How can I add a date range to the WHERE clause to show data from a
specified date range?
Post by Ronit Allen
I don't see anything in the table or in possible joins that will
provide dates.
Post by Ronit Allen
Thanks for any assistance that can be provided.
If you want that, you will have to take regular snapshots
of the table (e.g. with a cron job).
Then you can calculate differences.
Yours,
Laurenz Albe
Albe Laurenz
2012-11-16 09:56:08 UTC
Permalink
Post by Ronit Allen
On 9.1 with postgresql.conf left at defaults, what period of time does
a query on pg_stat_all_tables
Post by Ronit Allen
cover?
Let's say my database has been running for exactly one year.
I then issue SELECT relname, n_tup_ins FROM pg_stat_all_tables;
Do the results show me inserts for the last 7 days? 30 days? all time?..
All time.
Post by Ronit Allen
I don't see the answer to this in
http://www.postgresql.org/docs/9.1/static/monitoring-stats.html (or
Post by Ronit Allen
maybe it is there and I am not understanding).
I don't see it documented there, except maybe by omission.

Yours,
Laurenz Albe
--
Sent via pgsql-admin mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Ronit Allen
2012-11-17 20:11:26 UTC
Permalink
Hi -

Thank you Laurnez.

Ronit
Post by Albe Laurenz
Post by Ronit Allen
On 9.1 with postgresql.conf left at defaults, what period of time does
a query on pg_stat_all_tables
Post by Ronit Allen
cover?
Let's say my database has been running for exactly one year.
I then issue SELECT relname, n_tup_ins FROM pg_stat_all_tables;
Do the results show me inserts for the last 7 days? 30 days? all
time?..
All time.
Post by Ronit Allen
I don't see the answer to this in
http://www.postgresql.org/docs/9.1/static/monitoring-stats.html (or
Post by Ronit Allen
maybe it is there and I am not understanding).
I don't see it documented there, except maybe by omission.
Yours,
Laurenz Albe
Loading...