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 2 ways to do that: clear everything with Puppet’s built-in storage or by removing a particular node using the PostgreSQL backend.
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
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: if requires 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.