ingvar wrote:Hello,
Hi Ingvar,
I recently noticed that my Omero postgres database is growing quite fast, approaching 10 GB. Three tables account for 3.3 GB: event, eventlog, and session. I can not see where the other 6.5 GB are, is there some internal backup copy.
No, there shouldn't be anything OMERO-specific that's taking up space. Can you send me the output of:
- Code: Select all
SELECT
table_name,
pg_size_pretty(table_size) AS table_size,
pg_size_pretty(indexes_size) AS indexes_size,
pg_size_pretty(total_size) AS total_size
FROM (
SELECT
table_name,
pg_table_size(table_name) AS table_size,
pg_indexes_size(table_name) AS indexes_size,
pg_total_relation_size(table_name) AS total_size
FROM (
SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
FROM information_schema.tables
) AS all_tables WHERE table_name like '%public%'
ORDER BY total_size DESC
) AS pretty_sizes;
See
https://wiki.postgresql.org/wiki/Disk_Usage for more information. You may need vacuuming or similar.
The rest of the tables are all 40kB or less, so only add up to 1 MB or so.
Our systems group likes to test that my service is alive every 3 seconds or so,
This sounds extreme....
which more or less adds up to the 6 million rows in the event table since mid-June that I have.
Is it safe to prune these tables, e.g, remove all rows that are more than a week old?
Removing (all) EventLogs is always safe, assuming you don't want them for auditing purposes. You may be hard-pressed to remove the sessions and events unless you use a more elaborate query.
Even better, is there a way to configure this so that these tables are pruned automatically?
I am still on my hybrid 4.3.3 version, 2nd attempt to migrate did not go to well.
Not at the moment, no.
Kind Regards,
Ingvar
Cheers,
~Josh