PuppetDB is awesome. Here's some tips on accessing the data in the Postgresql database...most of 'm just reminders for myself.

puppetdb_postgres_hacks.md

NB: The following examples where done on Puppet Enterprise 3.0 running on Centos 6. Should be similar for open source versions, except for some file locations.

1. Logging into the PuppetDB PostgresQL database on Linux

The easiest way to snoop around in de actual PuppetDB postgres database is using the command prompt. You have to be the peadmin user though. Couldn't get it working just under root.

[root@master bin]# sudo su - pe-postgres -s /bin/bash
-bash-4.1$ /opt/puppet/bin/psql
psql (9.2.4)
Type "help" for help.

pe-postgres=# \c pe-puppetdb
You are now connected to database "pe-puppetdb" as user "pe-postgres".
pe-puppetdb=#

2. Snooping around the data

Using the REST interface is the way to go, but for some testing purposes I needed to check and/or manipulate some data. Just use the psql console for this:

a. List all tables:

pe-puppetdb=# \d
                   List of relations
 Schema |          Name           | Type  |    Owner
--------+-------------------------+-------+-------------
 public | catalog_resources       | table | pe-puppetdb
 public | catalogs                | table | pe-puppetdb
 public | certname_catalogs       | table | pe-puppetdb
 public | certname_facts          | table | pe-puppetdb
 public | certname_facts_metadata | table | pe-puppetdb
 public | certnames               | table | pe-puppetdb
 public | edges                   | table | pe-puppetdb
 public | reports                 | table | pe-puppetdb
 public | resource_events         | table | pe-puppetdb
 public | resource_params         | table | pe-puppetdb
 public | schema_migrations       | table | pe-puppetdb

b. Get all nodes:

pe-puppetdb=# SELECT * FROM certnames;
    name    | deactivated
------------+-------------
 agent      |
 dummy174   |
 dummy76    |
 agent2     |
 dummy25    |
...

c. Get some resources for a specific node. First get the catalog hash for a node:

pe-puppetdb=# SELECT catalog FROM certname_catalogs WHERE certname = 'agent';
                 catalog
------------------------------------------
 42115d6afda63764e16536153441f55efd83c8f4

Then use that hash to get the resources:

pe-puppetdb=# SELECT title FROM catalog_resources WHERE catalog = '42115d6afda63764e16536153441f55efd83c8f4';
                               title
--------------------------------------------------------------------
 peadmin-public.pem
 /etc/puppetlabs/mcollective/ssl/clients
 pe-mcollective-metadata
 Settings
 /opt/puppet/libexec/mcollective/mcollective/agent/puppetd.rb
 default
 /etc/puppetlabs/mcollective/ssl
 /etc/puppetlabs/mcollective/ssl/clients/mcollective-public.pem
 Pe_mcollective::Server::Plugins
 Pe_mcollective::Shared_key_files
 /opt/puppet/libexec/mcollective/mcollective/agent/puppetd.ddl
 Pe_mcollective
 main
 /etc/puppetlabs/mcollective/server.cfg
 Pe_mcollective::Server
 Pe_mcollective::Params
 mcollective-private.pem
 pe-mcollective
 main
 /opt/puppet/libexec/mcollective/mcollective
 /opt/puppet/libexec/mcollective/mcollective/application/puppetd.rb
 mcollective-cacert.pem
 /opt/puppet/sbin/refresh-mcollective-metadata
 main
 mcollective-cert.pem
 Pe_mcollective::Role::Agent
 puppet-dashboard-public.pem
 mcollective-public.pem
(28 rows)

or in a one-liner:

SELECT title FROM catalog_resources INNER JOIN certname_catalogs ON catalog_resources.catalog = certname_catalogs.catalog WHERE certname_catalogs.certname = 'agent';

3. Same thing, but now with facts

Get some nice fact data, or manipulate it for testing or modelling:

SELECT * FROM certname_facts WHERE certname = 'agent';

Or, for example, get all the eth1 addresses for all nodes starting with dummy*:

pe-puppetdb=# SELECT value FROM certname_facts WHERE name = 'network_eth1' AND certname LIKE 'dummy%';
   value
-----------
 10.20.1.0
 10.20.1.0
 10.20.1.0
 10.20.1.0
 10.20.1.0
 10.20.1.0
 10.20.1.0
 10.20.1.0

Update some facts:

UPDATE certname_facts SET value = '634.64 MB' WHERE name = 'memorysize' AND certname LIKE 'dummy%';