[Satellite6] How to generate postgres+mongo tables to understand databases content?

Environment

• Red Hat Satellite 6

Issue

• investigating a (customer) problem, one needs to understand foreman/candlepin/pulp databases schema for Satellite6
• how to get content of every databases table in plaintext?

Resolution

Foreman

Foreman stores data in postgres database and it's content can be generated by:

mkdir foreman_db
cd foreman_db
su - postgres -c "psql foreman -c '\dt *.*'" | grep foreman | awk '{ print $3 }' > _tables.txt
for i in $(cat _tables.txt ); do echo $i; su - postgres -c "psql foreman -c 'select * from $i;'" > ${i}.txt; done

(for rich task history, it makes sense to skip collecting tables dynflow_steps and dynflow_actions)

Candlepin

Candlepin stores data in postgres as well and it's content can be generated by:

mkdir candlepin_db
cd candlepin_db
su - postgres -c "psql candlepin -c '\dt *.*'" | grep candlepin | awk '{ print $3 }' > _tables.txt
for i in $(cat _tables.txt ); do echo $i; su - postgres -c "psql candlepin -c 'select * from $i;'" > ${i}.txt; done

Pulp (Satellite 6.4 - 6.9)

Pulp stores data in mongo database and it's content can be generated by:

mkdir pulp_db
cd pulp_db
echo "DBQuery.shellBatchSize = 10000000" > .mongorc.js
mongo pulp_database --eval "db.getCollectionNames()" | grep , | tr ',' '\n' | cut -d\" -f2 > _tables.txt
for i in $(cat _tables.txt ); do echo $i; mongo pulp_database --eval "load('.mongorc.js'); db.${i}.find().shellPrint()" > ${i}.txt; done
for i in $(cat _tables.txt ); do echo $i; mongo pulp_database --eval "load('.mongorc.js'); db.${i}.find().pretty().shellPrint()" > ${i}.pretty.txt; done

Pulp (Satellite 6.10 or newer)

Pulp stores data in pulpcore database of postgres, that cen be generated by:

mkdir pulp_db
cd pulp_db
su - postgres -c "psql pulpcore -c '\dt *.*'" | grep pulp | awk '{ print $3 }' > _tables.txt
for i in $(cat _tables.txt ); do echo $i; su - postgres -c "psql pulpcore -c 'select * from $i;'" > ${i}.txt; done

In all above cases, the directory contains:

• _tables.txt file with the list of database tables
• <table>.txt with the content of given table
• for pulp case and mongo db, <table>.pretty.txt shows "pretty" (more human readable) content of the table

For more KB articles/solutions related to Red Hat Satellite 6.x Pulp 2.0 Issues, please refer to the Consolidated Troubleshooting Article for Red Hat Satellite 6.x Pulp 2.0-related Issues

This solution is part of Red Hat’s fast-track publication program, providing a huge library of solutions that Red Hat engineers have created while supporting our customers. To give you the knowledge you need the instant it becomes available, these articles may be presented in a raw and unedited form.