53.30. pg_stats_vacuum_database#
The viewpg_stats_vacuum_database will contain one row for each database in the current cluster, showing statistics about vacuuming that database.
Table 53.30. 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 (iftrack_io_timing is enabled, otherwise zero) |
Time spent writing database blocks by vacuum operations performed on this database, in milliseconds (iftrack_io_timing is enabled, otherwise zero) |
Time spent sleeping in a vacuum delay point by vacuum operations performed on this database, in milliseconds (seeSection 18.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 thepg_stats_vacuum_database view as superuser who raninitdb:
For each database, including
template1, but not includingtemplate0, run:CREATE OR REPLACE VIEW pg_stats_vacuum_database ASSELECT 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.interruptsFROM pg_database db LEFT JOIN pg_stats_vacuum_database(db.oid) statsON db.oid = stats.dboid;
For the
template0database, run:\c template1ALTER DATABASE template0 ALLOW_CONNECTIONS on;\c template0CREATE OR REPLACE VIEW pg_stats_vacuum_database ASSELECT 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.interruptsFROM pg_database db LEFT JOIN pg_stats_vacuum_database(db.oid) statsON db.oid = stats.dboid;\c template1ALTER DATABASE template0 ALLOW_CONNECTIONS off;