Reports DB dump needed for testing (Foreman 2.3+)

Hello,

our production Foreman instance is still on 1.25 (yeah) and it only contains about 6k reports and not many hosts. We are working on a migration script for new host report format and I’d appreciate a database dump from a Foreman 2.3 or above (which includes some schema changes). Ideally Foreman 3.0 or 3.1 if possible.

We are looking for Ansible and Puppet reports at the moment.

Here is what I am looking for:

su postgres -c "pg_dump -a -Fc -t messages -t sources -t logs -t reports -v -Z9 foreman > /tmp/reports.dump"

Please contact me or send me the data directly to lzap_redhat_com - we will of course treat the data privately. Thanks for help!

Correction, here is a better way to export the data that will ignore OpenSCAP reports:

mkdir /pgdumps
chown postgres:postgres /pgdumps
cd /pgdumps
su postgres -c "psql foreman"

And in the postgres cli:

copy(select id,value from messages) to '/pgdumps/messages.csv';
copy(select id,value from sources) to '/pgdumps/sources.csv';
copy(select id,source_id,message_id,report_id,level_id from logs) to '/pgdumps/logs.csv';
copy(select id,1 as host_id,created_at,updated_at,reported_at,status,metrics,type,origin from reports where origin = 'Puppet' or origin = 'Ansible') to '/pgdumps/reports.csv';

And send them over to us:

xz -9 *csv

For completeness, here is how to load the data into Foreman that either have or don’t have OpenSCAP plugin (which changes reports DDL dramatically):

truncate sources,messages,logs,reports;
\copy messages(id,value) from './messages.csv';
\copy sources(id,value) from './sources.csv';
\copy reports(id,host_id,created_at,updated_at,reported_at,status,metrics,type,origin) from './reports.csv';
\copy logs(id,source_id,message_id,report_id,level_id) from './logs.csv';

Note that all reports are exported in a way that host_id is 1 therefore there must be a host with id=1.