I had an annoying problem in my test environment when working Puppet’s exported resources , which was caused by a unique constraint violation.
I ended up having to clear my PuppetDB data and in this blogpost I’ll show you a few ways to do that.
The clean way: puppet node deactivate#
If you just want one node (and its exported resources) gone, the right tool is puppet node deactivate. Run it on your Puppet primary/master, no database surgery required:
$ sudo puppet node deactivate your.hostname.tld
That marks the node as deactivated in PuppetDB, so its exported resources stop showing up in the catalogs served to your other nodes. Note that deactivating doesn’t actively remove the resources those exported resources created elsewhere; it only stops PuppetDB handing them out. If you want to actually destroy them, you purge that resource type with the resources metatype. A node also reactivates automatically the moment PuppetDB receives a fresh catalog or facts for it, so re-run the agent on a host you genuinely retired.
If that doesn’t get you out of trouble (or you’re on an old setup that predates it), the two heavier-handed methods I originally used follow below.
The problem: unique constraint violation#
Here’s the problem I was facing:
$ puppet agent -t
...
Error: Could not retrieve catalog from remote server: Error 400 on SERVER: Failed to submit 'replace facts' command for $::fqdn to PuppetDB at $::puppetmaster:8081: [404 Not Found]
Problem accessing /v3/commands. Reason: Not Found
...
Warning: Not using cache on failed catalog
Error: Could not retrieve catalog; skipping run
On the PuppetDB logs, I could see signs of integrity violations in the database.
...
org.hsqldb.HsqlException: error in script file line: 9621 org.hsqldb.HsqlException: integrity constraint violation: unique constraint or index violation; SYS_PK_10029 table: RESOURCE_PARAMS
...
In other situations, I’ve found duplicate resources were being exported into PuppetDB as well.
$ puppet agent -vt
...
Error: Could not retrieve catalog from remote server: Error 400 on SERVER: A duplicate resource was found while collecting exported resources, with the type and title Zabbix_host[your.hostname.tld] on node monitor.domain.tld
Warning: Not using cache on failed catalog
Error: Could not retrieve catalog; skipping run
Since this was a test environment and I didn’t really feel like debugging this all too much, I considered it easier to just remove all data from the PuppetDB built-in storage and start anew, thinking it was most likely caused by a user-error on my side.
Remove all data in PuppetDB with puppet’s built-in storage engine#
Note (2026): this section only applies to the old built-in HSQLDB storage engine, which was removed in PuppetDB 4.0 back in 2016. Every supported PuppetDB now runs on PostgreSQL, so skip ahead to the PostgreSQL section unless you’re maintaining a pre-4.0 install.
To clear all data from your PuppetDB, take the following steps. It’ll stop PuppetDB, remove the data-directory and restart PuppetDB so it can rebuild its data structures.
$ service puppetdb stop
$ cd /var/lib/puppetdb
$ mv db db.old
$ service puppetdb start
After the new start, your PuppetDB logs will contain the following kind of entries.
INFO [c.p.p.s.migrate] Applying database migration version 1
INFO [c.p.p.s.migrate] Applying database migration version 2
INFO [c.p.p.s.migrate] Applying database migration version 3
...
INFO [c.p.p.c.services] Starting broker
In the end, your directory structures will be similar to these.
$ ls -alh /var/lib/puppetdb/
drwxr-xr-x 6 puppetdb puppetdb 4.0K Jan 29 09:01 .
drwxr-xr-x. 30 root root 4.0K Jul 2 2014 ..
lrwxrwxrwx 1 puppetdb puppetdb 20 Jan 14 13:05 config -> /etc/puppetdb/conf.d
drwxr-xr-x 3 puppetdb puppetdb 4.0K Jan 29 09:03 db
drwxr-xr-x 3 puppetdb puppetdb 4.0K Jan 29 09:01 db.old
drwxr-xr-x 3 puppetdb puppetdb 4.0K Oct 21 20:38 mq
drwxr-xr-x 2 puppetdb puppetdb 4.0K Oct 21 20:38 state
The db.old can be removed if you no longer need it, or placed back into place to debug whichever problem you had in the first place.
Remove the data from a particular node with PostgreSQL as the backend#
Since the default PuppetDB storage engine doesn’t really scale well, many use a PostgreSQL backend in PuppetDB. This is a relational database that allows you to manipulate the data in PuppetDB by using a few simple queries.
In order to remove the data from one particular node, follow these steps;
$ su - postgres
$ psql puppetdb
puppetdb=# delete from catalog_resources where title like '%your.hostname.tld%';
DELETE 98
puppetdb=# delete from resource_params where value like '%your.hostname.tld%';
DELETE 12
No need to restart your PuppetDB, all exported resources for that hostname are now removed.
These DELETE statements are a bit tricky, though: they require you use the $fqdn fact in your resource name. If you don’t, you can modify the query to remove the actual resource name you defined in Puppet.