Hello,
I would like to research how inefficiently we store our reports (Puppet, OpenSCAP, Ansible…) in our database. If you want to participate in this research, please share output of this command (a table):
su postgres -s /bin/bash
psql foreman
> 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
ORDER BY total_size DESC
) AS pretty_sizes;
If you find that “logs”, “reports”, “sources” or “messages” tables are big, plesse send me privately (link to) dump of the following tables if you can:
su postgres -s /bin/bash
pg_dump -Fc -t reports -t sources -t messages -t logs -f /var/tmp/reports-optimalization.pgdump foreman
This can help me to decide the new way of storing report data. I aim to optimize this in the following weeks. Many thanks!
IANAL but reports do contain sensitive data like IP addresses, hostnames and/or can leak passwords or ssh keys. I will do my best to securely investigate the data and then delete them in a secure way on my encrypted drive, however we are not under NDA contract so this will be “best effort” level. If you are a Red Hat customer, please open up new case, upload the data using Red Hat system and then let me know and I can download it from there under Red Hat contracts.
My own GPG key is at: https://lukas.zapletalovi.com/contact.html