From be1c7e50e1e8809ea56f2c9d472eccd8ffd73a97 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Fri, 19 Apr 2024 04:57:58 +0200 Subject: Adding upstream version 1.44.3. Signed-off-by: Daniel Baumann --- .../postgres_table_last_autovacuum_time.md | 50 ++++++++++++++++++++++ 1 file changed, 50 insertions(+) create mode 100644 health/guides/postgres/postgres_table_last_autovacuum_time.md (limited to 'health/guides/postgres/postgres_table_last_autovacuum_time.md') diff --git a/health/guides/postgres/postgres_table_last_autovacuum_time.md b/health/guides/postgres/postgres_table_last_autovacuum_time.md new file mode 100644 index 00000000..8a79b0d3 --- /dev/null +++ b/health/guides/postgres/postgres_table_last_autovacuum_time.md @@ -0,0 +1,50 @@ +### Understand the alert + +This alert is related to the PostgreSQL database and checks the time since the last autovacuum operation occurred on a specific table. If you receive this alert, it means that the table has not been vacuumed by the autovacuum daemon for more than a week (7 days). + +### What is autovacuum in PostgreSQL? + +Autovacuum is a feature in PostgreSQL that automates the maintenance of the database by reclaiming storage, optimizing the performance of the database, and updating statistics. It operates on individual tables and performs the following tasks: + +1. Reclaims storage occupied by dead rows and updates the Free Space Map. +2. Optimizes the performance by updating statistics and executing the `ANALYZE` command. +3. Removes dead rows and updates the visibility map in order to reduce the need for vacuuming. + +### Troubleshoot the alert + +- Check the autovacuum status + +To check if the autovacuum daemon is running for the PostgreSQL instance, run the following SQL command: + + ``` + SHOW autovacuum; + ``` + +If the result is "off", then the autovacuum is disabled for the PostgreSQL instance. You can enable it by modifying the `postgresql.conf` configuration file and setting `autovacuum = on`. + +- Verify table-specific autovacuum settings + +Sometimes, autovacuum settings might be altered for individual tables. To check the autovacuum settings for the specific table mentioned in the alert, run the following SQL command: + + ``` + SELECT relname, reloptions FROM pg_class JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace WHERE relname = '' AND nspname = ''; + ``` + +Look for any custom `autovacuum_*` settings in the `reloptions` column and adjust them accordingly to allow the autovacuum daemon to run on the table. + +- Monitor the PostgreSQL logs + +Inspect the PostgreSQL logs for any error messages or unusual behavior related to autovacuum. The log file location depends on your PostgreSQL installation and configuration. + +- Manually vacuum the table + +If the autovacuum daemon has not run for a long time on the table, you can manually vacuum the table to reclaim storage and update statistics. To perform a manual vacuum, run the following SQL command: + + ``` + VACUUM (VERBOSE, ANALYZE) .; + ``` + +### Useful resources + +1. [PostgreSQL: Autovacuum](https://www.postgresql.org/docs/current/runtime-config-autovacuum.html) +2. [PostgreSQL: Routine Vacuuming](https://www.postgresql.org/docs/current/routine-vacuuming.html) -- cgit v1.2.3