Обсуждение: Vacuum does not show in pg_stat_all_tables
Hello everyone,
today when I do select relname, last_autovacuum, last_vacuum from
pg_stat_all_tables I see last_vacuum and autovacuum fields is empty.
This its seems strange for me, because every night crontab start at
01:10 am a vacuum script that do:
reindex, vacuum full and vacuum analyze. I run vacuumdb not vacuum from
command line. I use posgresql 9.1.3.
My question is: Is this a bug or may be my vacuum never is completed ?
Here is my vacuum script if some want a look at it.
#!/bin/sh
PSQL="/usr/bin/psql -U postgres my_db -h 10.0.0.1"
su - postgres -c "/usr/bin/vacuumdb --dbname=my_db --host=10.0.0.1 >>
/var/lib/postgres/vacuum.log 2>&1";
# reindex
tables=`$PSQL -t -c "SELECT DISTINCT c.oid::pg_catalog.regclass FROM
pg_catalog.pg_index x JOIN pg_catalog.pg_class c ON c.oid = x.indrelid
JOIN pg_catalog.pg_namespace n O
N c.relnamespace = n.oid WHERE nspname NOT LIKE 'pg_%'"`
for table in $tables; do
$PSQL -c "SET autocommit TO 'on'; REINDEX TABLE $table"
[ "$?" -ne 0 ] && exit 1
done
/usr/bin/vacuumdb --full --dbname=my_db --host=10.0.0.1 >>
/var/lib/postgres/vacuum.log 2>&1
su - postgres -c "/usr/bin/vacuumdb --analyze --dbname=my_db
--host=10.0.0.1 >> /var/lib/postgres/vacuum.log 2>&1"
Any one can tell me why this is happened ?
Regards,
Condor
Condor <condor@stz-bg.com> writes:
> today when I do select relname, last_autovacuum, last_vacuum from
> pg_stat_all_tables I see last_vacuum and autovacuum fields is empty.
> This its seems strange for me, because every night crontab start at
> 01:10 am a vacuum script that do:
> reindex, vacuum full and vacuum analyze. I run vacuumdb not vacuum from
> command line. I use posgresql 9.1.3.
I think last_vacuum tracks regular vacuums, not vacuum full.
This maintenance procedure seems like something that would have been
appropriate back with postgres 7.something, anyway. Do you have any
evidence that you need it at all? autovacuum works reasonably well
for most people, and in any case it seems unlikely that you need a
daily vacuum full or reindex.
regards, tom lane
On 15.05.2012 14:07, Tom Lane wrote: > Condor <condor@stz-bg.com> writes: >> today when I do select relname, last_autovacuum, last_vacuum from >> pg_stat_all_tables I see last_vacuum and autovacuum fields is empty. >> This its seems strange for me, because every night crontab start at >> 01:10 am a vacuum script that do: >> reindex, vacuum full and vacuum analyze. I run vacuumdb not vacuum >> from >> command line. I use posgresql 9.1.3. > > I think last_vacuum tracks regular vacuums, not vacuum full. > > This maintenance procedure seems like something that would have been > appropriate back with postgres 7.something, anyway. Do you have any > evidence that you need it at all? autovacuum works reasonably well > for most people, and in any case it seems unlikely that you need a > daily vacuum full or reindex. > > regards, tom lane I use vacuum full because I have huge tables that every night is deleted (truncated) and I want my space back. I did not use autovacuum because in past some times I lost data when is inserted. In past is happened once every month or two some record just missing. Cheers, C.
On 05/15/2012 05:30 AM, Condor wrote: > O > > > I use vacuum full because I have huge tables that every night is > deleted (truncated) > and I want my space back. Truncate does reclaim space. Bulk deletes do not. If you are doing bulk deletes since you need to delete *almost* everything consider using "cluster" to clean up. It is much faster and gives you new unbloated indexes. You may find pg_reorg of interest as well. > I did not use autovacuum because in past some times I lost > data when is inserted. In past is happened once every month or two > some record just missing. > > This issue, perhaps?: http://www.databasesoup.com/2012/03/postgres-update-release-data-loss-and.html In which case the data was not actually lost but could be missing from the index (with the obvious potential for resulting corruption). It has been fixed in current versions. Cheers, Steve