Clean Old Exported Resources From Puppetdb

Exported Resources are great, but also suck. If you are not careful how you tag them, you can easily end up in a situation where you have duplicate resources on a node. Of course this will mean that your catalogue will fail to compile.

Normally, old exported resources are cleaned up the next time the agent runs, but can be prone to failure for various reasons:

• the node no longer exists
• the moon is no longer on the right phase
• Puppet just doesn’t feel like it

This means you get a big red error such as:

Error: Could not retrieve catalog from remote server: Error 500 on SERVER: Server Error: A duplicate resource was found while collecting exported resources, with the type and title Mysql::Db[<dbname>] on node <node2>
Warning: Not using cache on failed catalog
Error: Could not retrieve catalog; skipping run

The way to get rid of them is to delete them from PuppetDB’s database. This is easy enough, but may be scary for some:

sudo -u postgres psql puppetdb -c 'delete from catalogs where certname in (select certname from certnames where certname like "<hostname1>%");'

Obviously, <hostname> is a placeholder which you need to replace as appropriate

IMPORTANT: This is applies to PuppetDB 4.0 and later as far as I can make out. Previously ( >=1.6 and <4 I believe) the column in the certnames table was called name, so the query would have been:

sudo -u postgres psql puppetdb -c 'delete from catalogs where certname in (select name from certnames where certname like "<hostname1>%");'

If you want to know what DB schema you have, the best idea is to check the certnames table:

sudo -u postgres psql puppetdb -c '\d+ certnames;'

You will get an ouput like:

                                                          Table "public.certnames"
      Column      |           Type           |                       Modifiers                       | Storage  | Stats target | Description
------------------+--------------------------+-------------------------------------------------------+----------+--------------+-------------
 id               | bigint                   | not null default nextval('certname_id_seq'::regclass) | plain    |              |
 certname         | text                     | not null                                              | extended |              |
 latest_report_id | bigint                   |                                                       | plain    |              |
 deactivated      | timestamp with time zone |                                                       | plain    |              |
 expired          | timestamp with time zone |                                                       | plain    |              |
Indexes:
    "certnames_transform_pkey" PRIMARY KEY, btree (id)
    "certnames_transform_certname_key" UNIQUE CONSTRAINT, btree (certname)
Foreign-key constraints:
    "certnames_reports_id_fkey" FOREIGN KEY (latest_report_id) REFERENCES reports(id) ON DELETE SET NULL
Referenced by:
    TABLE "catalog_resources" CONSTRAINT "catalog_resources_certname_id_fkey" FOREIGN KEY (certname_id) REFERENCES certnames(id) ON DELETE CASCADE
    TABLE "catalogs" CONSTRAINT "catalogs_certname_fkey" FOREIGN KEY (certname) REFERENCES certnames(certname) ON DELETE CASCADE
    TABLE "factsets" CONSTRAINT "factsets_certname_fk" FOREIGN KEY (certname) REFERENCES certnames(certname) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "reports" CONSTRAINT "reports_certname_fkey" FOREIGN KEY (certname) REFERENCES certnames(certname) ON DELETE CASCADE

You will have either certname or name under Column depending on your version.

Having said all that, if you are still on pre-4 PuppetDB, you really should be upgrading.