pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedwith PostgreSQL 9.1.9,the stats collector process is not work!
6+ messages / 3 participants
[nested] [flat]
* with PostgreSQL 9.1.9,the stats collector process is not work!
@ 2013-04-11 06:01 X.H.----WANG <82661063@qq.com>
2013-04-11 06:29 ` Re: with PostgreSQL 9.1.9,the stats collector process is not work! Vasilis Ventirozos <v.ventirozos@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: X.H.----WANG @ 2013-04-11 06:01 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: with PostgreSQL 9.1.9,the stats collector process is not work!
2013-04-11 06:01 with PostgreSQL 9.1.9,the stats collector process is not work! X.H.----WANG <82661063@qq.com>
@ 2013-04-11 06:29 ` Vasilis Ventirozos <v.ventirozos@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Vasilis Ventirozos @ 2013-04-11 06:29 UTC (permalink / raw)
To: X.H.----WANG <82661063@qq.com>; +Cc: pgsql-admin
On Thursday, April 11, 2013, X.H.----WANG <82661063@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
>
>
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: with PostgreSQL 9.1.9,the stats collector process is not work!
@ 2013-04-11 08:21 X.H.----WANG <82661063@qq.com>
2013-04-11 14:01 ` Re: with PostgreSQL 9.1.9,the stats collector process is not work! Prashanth Ranjalkar <prashant.ranjalkar@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: X.H.----WANG @ 2013-04-11 08:21 UTC (permalink / raw)
To: Vasilis Ventirozos <v.ventirozos@gmail.com>; +Cc: pgsql-admin
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"<v.ventirozos@gmail.com>;
Date: Thu, Apr 11, 2013 02:29 PM
To: "X.H.----WANG"<82661063@qq.com>;
Cc: "pgsql-admin"<pgsql-admin@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 <82661063@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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: with PostgreSQL 9.1.9,the stats collector process is not work!
2013-04-11 08:21 Re: with PostgreSQL 9.1.9,the stats collector process is not work! X.H.----WANG <82661063@qq.com>
@ 2013-04-11 14:01 ` Prashanth Ranjalkar <prashant.ranjalkar@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Prashanth Ranjalkar @ 2013-04-11 14:01 UTC (permalink / raw)
To: X.H.----WANG <82661063@qq.com>; +Cc: Vasilis Ventirozos <v.ventirozos@gmail.com>; pgsql-admin
*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*
On Thu, Apr 11, 2013 at 1:51 PM, X.H.----WANG <82661063@qq.com> wrote:
>
> 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"<v.ventirozos@gmail.com>;
> *Date: * Thu, Apr 11, 2013 02:29 PM
> *To: * "X.H.----WANG"<82661063@qq.com>; **
> *Cc: * "pgsql-admin"<pgsql-admin@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 <82661063@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
>>
>>
>>
>>
>>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: with PostgreSQL 9.1.9,the stats collector process is not work!
@ 2013-04-12 07:08 X.H.----WANG <82661063@qq.com>
2013-04-12 16:58 ` Re: with PostgreSQL 9.1.9,the stats collector process is not work! Prashanth Ranjalkar <prashant.ranjalkar@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: X.H.----WANG @ 2013-04-12 07:08 UTC (permalink / raw)
To: Prashanth Ranjalkar <prashant.ranjalkar@gmail.com>; +Cc: Vasilis Ventirozos <v.ventirozos@gmail.com>; pgsql-admin
OK! Thank you so much for your reply!
The Streaming Replication is used.
Due to a need,We need to switch the roles of master and slave.
So I shut down the Master at first,Then promoted the Salve to The new Master(pg_ctl promote -D).
Every thing is ok but the stats infos is null on the new Master.
I checked the pgstat.stat file in pg_stat_tmp directory.I found no changes to the pgstat.stat file!
So I removed the the pgstat.stat file but the pgstat.stat file can not be automaticly created regardless of any way.
I may not easily restart the new Master because of the product availability.
So I want to find a good idea for handle the case.
The WARNING message
(SELECTWARNING: pgstat wait timeout) exists as soon as I query the stats info on the new Master.The replication process is ok.
Thanks again!
River
------------------ Original ------------------
From: "Prashanth Ranjalkar"<prashant.ranjalkar@gmail.com>;
Date: Thu, Apr 11, 2013 10:31 PM
To: "X.H.----WANG"<82661063@qq.com>;
Cc: "Vasilis Ventirozos"<v.ventirozos@gmail.com>; "pgsql-admin"<pgsql-admin@postgresql.org>;
Subject: Re: [ADMIN] with PostgreSQL 9.1.9,the stats collector process is not work!
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
On Thu, Apr 11, 2013 at 1:51 PM, X.H.----WANG <82661063@qq.com> wrote:
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"<v.ventirozos@gmail.com>;
Date: Thu, Apr 11, 2013 02:29 PM
To: "X.H.----WANG"<82661063@qq.com>;
Cc: "pgsql-admin"<pgsql-admin@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 <82661063@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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: with PostgreSQL 9.1.9,the stats collector process is not work!
2013-04-12 07:08 Re: with PostgreSQL 9.1.9,the stats collector process is not work! X.H.----WANG <82661063@qq.com>
@ 2013-04-12 16:58 ` Prashanth Ranjalkar <prashant.ranjalkar@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Prashanth Ranjalkar @ 2013-04-12 16:58 UTC (permalink / raw)
To: X.H.----WANG <82661063@qq.com>; +Cc: Vasilis Ventirozos <v.ventirozos@gmail.com>; pgsql-admin
Thanks for the details.
pgstat.stat file get created on the start of the server or automatically
created when not found under pg_stat_tmp however it's not the case and not
possible to restart the server. I assume there are no privileges problem on
the new master server for this file creation.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2013-04-12 16:58 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-04-11 06:01 with PostgreSQL 9.1.9,the stats collector process is not work! X.H.----WANG <82661063@qq.com>
2013-04-11 06:29 ` Re: with PostgreSQL 9.1.9,the stats collector process is not work! Vasilis Ventirozos <v.ventirozos@gmail.com>
2013-04-11 08:21 Re: with PostgreSQL 9.1.9,the stats collector process is not work! X.H.----WANG <82661063@qq.com>
2013-04-11 14:01 ` Re: with PostgreSQL 9.1.9,the stats collector process is not work! Prashanth Ranjalkar <prashant.ranjalkar@gmail.com>
2013-04-12 07:08 Re: with PostgreSQL 9.1.9,the stats collector process is not work! X.H.----WANG <82661063@qq.com>
2013-04-12 16:58 ` Re: with PostgreSQL 9.1.9,the stats collector process is not work! Prashanth Ranjalkar <prashant.ranjalkar@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox