Обсуждение: autovacuum question
Hello, I had a problem in my computer system yesterday. Some data has disappeared. I was looking for a program that might have deleted it accidentally. Before I found anything, the data suddenly came back. I thought it must be a transaction id wraparound problem. The system is running on FreeBSD 6.2. Postgresql version is 8.1.8 and it was installed from FreeBSD's standard ports tree. It also installed a script that vacuums all databases daily. This script was turned on, and I got emails every day, telling me that the databases were vacuumed. However, when I opened some tables from within pgadmin3, I got the message "pg_autovacuum recommened". Summary: I'm vacuuming all databases daily, but probably I had a transaction wraparound. I thought I should enable autovacuum. I read the docs and did the following: 1. Changed postgresql.conf: stats_start_collector = on stats_row_level = on autovacuum = on 2. Restarted the postgresql service. Now, I do not see a "separate autovacuum daemon" running. What did I wrong? Here are my tips: a.) atovacuum daemon is part of the "postgres" process, so I won't see it running in the process list (how can I check if it is working?) b.) there is a separate program that I should start (where?) c.) I missed some extra configuration (what should I do?) d.) something else Please help me out. Thanks, Laszlo
Autovacuum doesn't launch a separate daemon; you'll only see an autovac process when an autovac is actually working. Also, 8.1 has code to prevent wraparound, so that's not it (see backend/access/transam/varsup.c) On Mar 27, 2007, at 5:17 AM, Laszlo Nagy wrote: > > Hello, > > I had a problem in my computer system yesterday. Some data has > disappeared. I was looking for a program that might have deleted it > accidentally. Before I found anything, the data suddenly came back. > I thought it must be a transaction id wraparound problem. > > The system is running on FreeBSD 6.2. Postgresql version is 8.1.8 > and it was installed from FreeBSD's standard ports tree. It also > installed a script that vacuums all databases daily. This script > was turned on, and I got emails every day, telling me that the > databases were vacuumed. However, when I opened some tables from > within pgadmin3, I got the message "pg_autovacuum recommened". > > Summary: I'm vacuuming all databases daily, but probably I had a > transaction wraparound. > > I thought I should enable autovacuum. I read the docs and did the > following: > > 1. Changed postgresql.conf: > > stats_start_collector = on > stats_row_level = on > autovacuum = on > > 2. Restarted the postgresql service. > > Now, I do not see a "separate autovacuum daemon" running. What did > I wrong? Here are my tips: > > a.) atovacuum daemon is part of the "postgres" process, so I won't > see it running in the process list (how can I check if it is working?) > b.) there is a separate program that I should start (where?) > c.) I missed some extra configuration (what should I do?) > d.) something else > > Please help me out. > Thanks, > > Laszlo > > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
Jim Nasby írta: > Autovacuum doesn't launch a separate daemon; you'll only see an > autovac process when an autovac is actually working. Where should I see it? The output of "ps ax | grep auto" is empty. Does it mean that auto vacuum is not running? How can I check if there is a problem? > > Also, 8.1 has code to prevent wraparound, so that's not it (see > backend/access/transam/varsup.c) Oh, then do you have any idea what caused this? E.g. a postgres client seeing an older version of the data? "Older" means that the postgres client was started today and it saw the data from 2 days ago, then suddenly it started to see the fresh data. Thanks, Laszlo
Laszlo Nagy wrote: > Jim Nasby írta: > >Autovacuum doesn't launch a separate daemon; you'll only see an > >autovac process when an autovac is actually working. > Where should I see it? The output of "ps ax | grep auto" is empty. Does > it mean that auto vacuum is not running? How can I check if there is a > problem? Do "show autovacuum", and it will tell you if autovac is running. > >Also, 8.1 has code to prevent wraparound, so that's not it (see > >backend/access/transam/varsup.c) > Oh, then do you have any idea what caused this? E.g. a postgres client > seeing an older version of the data? "Older" means that the postgres > client was started today and it saw the data from 2 days ago, then > suddenly it started to see the fresh data. Maybe because they issued "commit" in the session where they had an idle open transaction? -- Alvaro Herrera http://www.PlanetPostgreSQL.org "La libertad es como el dinero; el que no la sabe emplear la pierde" (Alvarez)