Обсуждение: pg_stat_all_tables column value reseting problem.
Dear friends, I would like to know the particular table's insert,update and delete record history. I have done some googling and got to know about pg_stat_all_tables and pg_stat_user_tables. These tables having this below field , which has the tables insert,update and delete history, n_tup_ins | bigint | n_tup_upd | bigint | n_tup_del | bigint | But some time It was getting reset.. I don't know which scenario It is happening. Done the blow testing: => inserting,updating and deleting some record in a particular table => checked this pg_stat_all_tables entry, It has displayed perfect count of the history. => restarted the server for check the these (n_tup_ins,n_tup_upd,n_tup_del) entries. but values weren't reseted, history remains. => Two days after I have checked this pg_stat_all_tables entry again, but proper records wasn't there. Value of n_tup_ins,n_tup_upd and n_tup_del was 0. I don't know why it has happened. Please let me know the reason and which scenario it will get reset. Whether I have to do any configuration related changes ? Note: I am using PostgreSQL 8.3.7. -- View this message in context: http://postgresql.1045698.n5.nabble.com/pg-stat-all-tables-column-value-reseting-problem-tp3767851p3767851.html Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
On Wed, Mar 16, 2011 at 1:28 PM, leslie samuel <leslie.bks@gmail.com> wrote:
Dear friends,
I would like to know the particular table's insert,update and delete
record history.
I have done some googling and got to know about pg_stat_all_tables and
pg_stat_user_tables. These tables having this below field , which has the
tables insert,update and delete history,
n_tup_ins | bigint |
n_tup_upd | bigint |
n_tup_del | bigint |
But some time It was getting reset.. I don't know which scenario It is
happening.
Done the blow testing:
=> inserting,updating and deleting some record in a particular table
=> checked this pg_stat_all_tables entry, It has displayed perfect
count of the history.
=> restarted the server for check the these
(n_tup_ins,n_tup_upd,n_tup_del) entries.
but values weren't reseted, history remains.
=> Two days after I have checked this pg_stat_all_tables entry again,
but proper records wasn't there. Value of n_tup_ins,n_tup_upd and n_tup_del
was 0. I don't know why it has happened.
Please let me know the reason and which scenario it will get reset.
Whether I have to do any configuration related changes ?
Note:
I am using PostgreSQL 8.3.7.
In PostgreSQL, pg_stat tables shows a variety of statistical information regarding the database.
psql=# select pg_stat_reset();
Whenever you perform above command in PSQL prompt,it will clear out the gathered statistics and start from scratch. Could you please check it in your end.
--Raghu Ram
--
View this message in context: http://postgresql.1045698.n5.nabble.com/pg-stat-all-tables-column-value-reseting-problem-tp3767851p3767851.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Thanks for ur reply Ragu.. Yes, I have executed this select pg_stat_reset(); as a root user. It has reseted the values. But last time ( when I was posted this query) I didn't executed it. But values were rested automatically. I would like to know, => Whether postgres automatically running this select pg_stat_reset(); statement ? => If it runs Which scenario its executing this statement and what is the interval time between each execution ? Please let me know.. Thanks.. -- View this message in context: http://postgresql.1045698.n5.nabble.com/pg-stat-all-tables-column-value-reseting-problem-tp3767851p3933652.html Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
> -----Original Message----- > From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin- > owner@postgresql.org] On Behalf Of leslie samuel > Sent: Friday, March 18, 2011 1:17 AM > To: pgsql-admin@postgresql.org > Subject: Re: [ADMIN] pg_stat_all_tables column value reseting problem. > > Thanks for ur reply Ragu.. > > Yes, I have executed this select pg_stat_reset(); as a root user. > It has reseted the values. > > But last time ( when I was posted this query) I didn't executed it. > But values were rested automatically. > > I would like to know, > > => Whether postgres automatically running this select > pg_stat_reset(); > statement ? > => If it runs Which scenario its executing this statement and what > is the > interval time between each execution ? > > Please let me know.. > Thanks.. > If you stop/restart the database in immediate mode, or if it crashes the stats will be reset. What mode did you use forshutdown? Brad.
For stop/Restart the postmaster, I am using pg_ctlcluster --force command. I have read about pg_ctlcluster in the net they are saying this below things, pg_ctlcluste With the --force option the "fast" mode is used which rolls back all active transactions, disconnects clients immediately and thus shuts down cleanly. If that does not work, shutdown is attempted again in "immediate" mode, which can leave the cluster in an inconsistent state and thus will lead to a recovery run at the next start. -- View this message in context: http://postgresql.1045698.n5.nabble.com/pg-stat-all-tables-column-value-reseting-problem-tp3767851p4062822.html Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
> -----Original Message----- > From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin- > owner@postgresql.org] On Behalf Of leslie samuel > Sent: Saturday, March 19, 2011 8:49 AM > To: pgsql-admin@postgresql.org > Subject: Re: [ADMIN] pg_stat_all_tables column value reseting problem. > > For stop/Restart the postmaster, I am using pg_ctlcluster --force > command. > > I have read about pg_ctlcluster in the net they are saying this below > things, > > pg_ctlcluste With the --force option the "fast" mode is used which > rolls > back all active transactions, disconnects clients immediately and thus > shuts > down cleanly. If that does not work, shutdown is attempted again in > "immediate" mode, which can leave the cluster in an inconsistent state > and > thus will lead to a recovery run at the next start. Check your DB logs. If you are going through recovery on startup, that is your answer. Brad.