Aurora PG reset aurora_stat_statements statistics daily

To reset the statistics collected by aurora_stat_statements, you should use the standard PostgreSQL function pg_stat_statements_reset(). However, before you can use this function, you need to ensure that the pg_stat_statements extension is properly loaded.

The error message you're seeing indicates that the pg_stat_statements extension might not be loaded in your database. The aurora_stat_statements function builds upon the pg_stat_statements extension and requires it to be enabled.

Here are the steps to resolve this issue:

1. First, check if the pg_stat_statements extension is included in your shared_preload_libraries parameter in your DB parameter group.

2. If it's not included, you'll need to add it to the shared_preload_libraries parameter and restart your DB instance.

3. After ensuring the extension is loaded, you need to create the extension in your database:

CREATE EXTENSION pg_stat_statements;

4. Once the extension is properly loaded and created, you should be able to use pg_stat_statements_reset() to reset the statistics.

According to the documentation, you can reset stats for the aurora_stat_statements function using pg_stat_statements_reset(). This will reset all the statistics gathered, including the Aurora-specific metrics like storage_blks_read, orcache_blks_hit, and timing information.

For your use case of gathering daily snapshots, you could set up a scheduled task that:

1. Queries aurora_stat_statements() to capture the current statistics
2. Forwards this data to Graylog
3. Calls pg_stat_statements_reset() to reset the statistics for the next day's collection
   Sources
   aurora_stat_statements - Amazon Aurora
   Amazon Aurora PostgreSQL parameters - Amazon Aurora