with PostgreSQL 9.1.9,the stats collector process is not work!

[X.H.----WANG]

Hello everybody:

After I switch the slave to the master , I can not get the stats information by the below sql and the pg_stat_reset() does not work on the New Master,
And I vacuum by hand,it's still not work! I need some help.Could you give me any idea?


the stat sql:


SELECT st.relname, n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze, vacuum_count, autovacuum_count, analyze_count, autoanalyze_count, pg_size_pretty(pg_relation_size(st.relid) + CASE WHEN cl.reltoastrelid = 0 THEN 0 ELSE pg_relation_size(cl.reltoastrelid) + COALESCE((SELECT SUM(pg_relation_size(indexrelid)) FROM pg_index WHERE indrelid=cl.reltoastrelid)::int8, 0) END + COALESCE((SELECT SUM(pg_relation_size(indexrelid)) FROM pg_index WHERE indrelid=st.relid)::int8, 0)) AS size FROM pg_stat_all_tables st JOIN pg_class cl on cl.oid=st.relid WHERE schemaname = 'public' ORDER BY relname;



My environment:


Linux 3.2.1-gentoo-r2
postgresql 9.1.9,


the paras:
"autovacuum";"on"
"track_counts";"on"


/etc/hosts:
127.0.0.1 localhost 192.168.0.22 localhost.localdomain localhost


the postgresql's processes exist:


writer process stats collector process wal writer process autovacuum launcher process wal sender process


Thank you for your time!
Best Regard!


River

[Vasilis Ventirozos]

Post by X.H.----WANG
After I switch the slave to the master , I can not get the stats
information by the below sql and the pg_stat_reset() does not work on the
New Master,
And I vacuum by hand,it's still not work! I need some help.Could you give me any idea?
SELECT st.relname, n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd,
n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, last_analyze,
last_autoanalyze, vacuum_count, autovacuum_count, analyze_count,
autoanalyze_count, pg_size_pretty(pg_relation_size(st.relid) + CASE WHEN
cl.reltoastrelid = 0 THEN 0 ELSE pg_relation_size(cl.reltoastrelid) +
COALESCE((SELECT SUM(pg_relation_size(indexrelid)) FROM pg_index WHERE
indrelid=cl.reltoastrelid)::int8, 0) END + COALESCE((SELECT
SUM(pg_relation_size(indexrelid)) FROM pg_index WHERE
indrelid=st.relid)::int8, 0)) AS size FROM pg_stat_all_tables st JOIN
pg_class cl on cl.oid=st.relid WHERE schemaname = 'public' ORDER BY relname;
Linux 3.2.1-gentoo-r2
postgresql 9.1.9,
"autovacuum";"on"
"track_counts";"on"
127.0.0.1 localhost 192.168.0.22 localhost.localdomain localhost
writer process stats collector process wal writer process autovacuum
launcher process wal sender process
Thank you for your time!
Best Regard!
River

[Hoàng Thanh Toàn - DB]

Dear all,

Please help me.

I know this is postgres-admin mail list, it is not for the discussion of
pgpool.

I'm sorry to post questions related to pgpool.

I have a problem like this:

<<

I have a temporary table is used as a literal in View objects.

in Master/Slave Mode (Pgpool): how to detect all query on this view, and
then transfer it to master.
Please give me some advices.

Thanks a lot.



--

Best Regards,

[X.H.----WANG]

I'm sorry,I forgot to attach the log info:


WARNING: pgstat wait timeout
SELECTWARNING: pgstat wait timeout
SELECTWARNING: corrupted statistics file "pg_stat_tmp/pgstat.stat"
VACUUMWARNING: pgstat wait timeout





------------------ Original ------------------
From: "Vasilis Ventirozos"<***@gmail.com>;
Date: Thu, Apr 11, 2013 02:29 PM
To: "X.H.----WANG"<***@qq.com>;
Cc: "pgsql-admin"<pgsql-***@postgresql.org>;
Subject: Re: [ADMIN] with PostgreSQL 9.1.9,the stats collector process is not work!





On Thursday, April 11, 2013, X.H.----WANG <***@qq.com> wrote:
Hello everybody:

After I switch the slave to the master , I can not get the stats information by the below sql and the pg_stat_reset() does not work on the New Master,
And I vacuum by hand,it's still not work! I need some help.Could you give me any idea?


the stat sql:


SELECT st.relname, n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze, vacuum_count, autovacuum_count, analyze_count, autoanalyze_count, pg_size_pretty(pg_relation_size(st.relid) + CASE WHEN cl.reltoastrelid = 0 THEN 0 ELSE pg_relation_size(cl.reltoastrelid) + COALESCE((SELECT SUM(pg_relation_size(indexrelid)) FROM pg_index WHERE indrelid=cl.reltoastrelid)::int8, 0) END + COALESCE((SELECT SUM(pg_relation_size(indexrelid)) FROM pg_index WHERE indrelid=st.relid)::int8, 0)) AS size FROM pg_stat_all_tables st JOIN pg_class cl on cl.oid=st.relid WHERE schemaname = 'public' ORDER BY relname;



My environment:


Linux 3.2.1-gentoo-r2
postgresql 9.1.9,


the paras:
"autovacuum";"on"
"track_counts";"on"


/etc/hosts:
127.0.0.1 localhost 192.168.0.22 localhost.localdomain localhost


the postgresql's processes exist:


writer process stats collector process wal writer process autovacuum launcher process wal sender process


Thank you for your time!
Best Regard!


River

[Prashanth Ranjalkar]

*Would you mind providing more insights on type of replication that is in
use as email states that switch performed from slave to master ?*
*
*
*Initial glance points towards catalog corruptions however would like to
understand the replication methodology used here..*

*Thanks & Regards,*
*** *
*Prashanth Ranjalkar*
*Database Consultant & Architect*
*Skype:prashanth.ranjalkar*
*www.postgresdba.net*

Post by X.H.----WANG
WARNING: pgstat wait timeout
SELECTWARNING: pgstat wait timeout
SELECTWARNING: corrupted statistics file "pg_stat_tmp/pgstat.stat"
VACUUMWARNING: pgstat wait timeout
------------------ Original ------------------
*Date: * Thu, Apr 11, 2013 02:29 PM
*Subject: * Re: [ADMIN] with PostgreSQL 9.1.9,the stats collector process
is not work!

Post by X.H.----WANG
After I switch the slave to the master , I can not get the stats
information by the below sql and the pg_stat_reset() does not work on
the New Master,
And I vacuum by hand,it's still not work! I need some help.Could you give me any idea?
SELECT st.relname, n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd,
n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, last_analyze,
last_autoanalyze, vacuum_count, autovacuum_count, analyze_count,
autoanalyze_count, pg_size_pretty(pg_relation_size(st.relid) + CASE WHEN
cl.reltoastrelid = 0 THEN 0 ELSE pg_relation_size(cl.reltoastrelid) +
COALESCE((SELECT SUM(pg_relation_size(indexrelid)) FROM pg_index WHERE
indrelid=cl.reltoastrelid)::int8, 0) END + COALESCE((SELECT
SUM(pg_relation_size(indexrelid)) FROM pg_index WHERE
indrelid=st.relid)::int8, 0)) AS size FROM pg_stat_all_tables st JOIN
pg_class cl on cl.oid=st.relid WHERE schemaname = 'public' ORDER BY relname;
Linux 3.2.1-gentoo-r2
postgresql 9.1.9,
"autovacuum";"on"
"track_counts";"on"
127.0.0.1 localhost 192.168.0.22 localhost.localdomain localhost
writer process stats collector process wal writer process autovacuum
launcher process wal sender process
Thank you for your time!
Best Regard!
River