54.31. pg_stats_vacuum_database #
The view pg_stats_vacuum_database will contain one row for each database in the current cluster, showing statistics about vacuuming that database.
Table 54.31. pg_stats_vacuum_database Columns
Column Type Description |
|---|
OID of a database |
Number of database blocks read by vacuum operations performed on this database |
Number of times database blocks were found in the buffer cache by vacuum operations performed on this database |
Number of database blocks dirtied by vacuum operations performed on this database |
Number of database blocks written by vacuum operations performed on this database |
Total number of WAL records generated by vacuum operations performed on this database |
Total number of WAL full page images generated by vacuum operations performed on this database |
Total amount of WAL bytes generated by vacuum operations performed on this database |
Time spent reading database blocks by vacuum operations performed on this database, in milliseconds (if track_io_timing is enabled, otherwise zero) |
Time spent writing database blocks by vacuum operations performed on this database, in milliseconds (if track_io_timing is enabled, otherwise zero) |
Time spent sleeping in a vacuum delay point by vacuum operations performed on this database, in milliseconds (see Section 19.4.4 for details) |
System CPU time of vacuuming this database, in milliseconds |
User CPU time of vacuuming this database, in milliseconds |
Total time of vacuuming this database, in milliseconds |
Number of times vacuum operations performed on this database were interrupted on any errors |
Note
On upgrading your server just by replacing the executables, update the pg_stats_vacuum_database view as superuser who ran initdb:
For each database, including
template1, but not includingtemplate0, run:CREATE OR REPLACE VIEW pg_stats_vacuum_database AS SELECT db.oid as dboid, stats.db_blks_read, stats.db_blks_hit, stats.total_blks_dirtied, stats.total_blks_written, stats.wal_records, stats.wal_fpi, stats.wal_bytes, stats.blk_read_time, stats.blk_write_time, stats.delay_time, stats.system_time, stats.user_time, stats.total_time, stats.interrupts FROM pg_database db LEFT JOIN pg_stats_vacuum_database(db.oid) stats ON db.oid = stats.dboid;
For the
template0database, run:\c template1 ALTER DATABASE template0 ALLOW_CONNECTIONS on; \c template0 CREATE OR REPLACE VIEW pg_stats_vacuum_database AS SELECT db.oid as dboid, stats.db_blks_read, stats.db_blks_hit, stats.total_blks_dirtied, stats.total_blks_written, stats.wal_records, stats.wal_fpi, stats.wal_bytes, stats.blk_read_time, stats.blk_write_time, stats.delay_time, stats.system_time, stats.user_time, stats.total_time, stats.interrupts FROM pg_database db LEFT JOIN pg_stats_vacuum_database(db.oid) stats ON db.oid = stats.dboid; \c template1 ALTER DATABASE template0 ALLOW_CONNECTIONS off;