Cleaning OpenResty Edge Log Server Database Data
The OpenResty Edge Log Server database primarily stores error logs, WAF logs, and dynamic metric data. Among these, WAF logs and dynamic metric data typically occupy significant space. All these data are stored using a time-series database. This article will detail how to adjust timescale database policies and perform data cleanup.
Default Time-Series Table Overview
| Table Name | Purpose | Chunk Interval | Data Retention Period |
|---|---|---|---|
errlog_tsdb | Error logs | 7 days | 1 month |
waf_request_tsdb | WAF hit requests | 7 days | 1 month |
waf_matches_tsdb | WAF hit details | 7 days | 2 months |
events | Event records | 7 days | 1 month |
cc_log_tsdb | Rate limiting request information | 1 day | 7 days |
_dymetrics_ | Dynamic metric data | 3 days | 1 month |
Database Operation Steps
Logging into the Database
/usr/local/openresty-postgresql12/bin/psql -h 127.0.0.1 -p 5432 -U postgres -d or_edge_log_server
Querying Time-Series Table Size
Basic query:
SELECT table_name, table_size, total_size
FROM timescaledb_information.hypertable
WHERE table_schema='public' AND total_size IS NOT NULL;
Enhanced readability query:
SELECT table_name, table_schema, pg_size_bytes(total_size) / 1024 / 1024 AS total_size_mb
FROM timescaledb_information.hypertable
WHERE total_size IS NOT NULL
ORDER BY total_size DESC;
Viewing Time-Series Database Cleanup Policies
SELECT * FROM timescaledb_information.drop_chunks_policies;
Example output interpretation:
| Field | Example Value | Description |
|---|---|---|
| hypertable | errlog_tsdb | Name of the hyper table to which the cleanup policy is applied |
| older_than | (t,“1 mon”,) | Delete data chunks older than 1 month |
| cascade | f | Do not cascade delete dependent objects |
| job_id | 1000 | Associated background job ID |
| schedule_interval | 1 day | Policy execution frequency |
| max_runtime | 00:05:00 | Maximum runtime for a single job |
| max_retries | -1 | Maximum retry attempts after failure (-1 means unlimited retries) |
| retry_period | 00:05:00 | Retry interval |
| cascade_to_materializations | f | Do not cascade delete materialized views |
Querying Chunk Intervals
SELECT b.table_name,
(a.interval_length / 24 / 60 / 60 / 1000000)::text || ' day(s)' AS interval
FROM _timescaledb_catalog.dimension AS a
LEFT JOIN _timescaledb_catalog.hypertable AS b ON b.id = a.hypertable_id;
Optimizing Time-Series Database Strategies
Note: The chunk interval should be at most half of the data retention time.
Using errlog_tsdb as an example, here’s how to adjust the policy to retain 14 days of data with a 2-day chunk interval.
Steps:
- Remove existing cleanup policy:
SELECT remove_drop_chunks_policy('errlog_tsdb', if_exists => TRUE);
- Add new cleanup policy:
SELECT add_drop_chunks_policy('errlog_tsdb', INTERVAL '2 week', cascade_to_materializations => FALSE);
- Modify chunk interval:
SELECT set_chunk_time_interval('errlog_tsdb', INTERVAL '2 day');
- Clean up expired data:
SELECT drop_chunks(table_name => 'errlog_tsdb', older_than => INTERVAL '2 week', cascade_to_materializations => FALSE);
By following these steps, you can effectively reduce the disk space occupied by the OpenResty Edge Log Server database, ensuring the availability of the Log Server service.