Resolve an “ERROR: deadlock detected” error message in PuppetDB when upgrading to Puppet Enterprise 2019.8 or later from a previous version

Migrating PuppetDB fails, and you get an error message in /opt/puppetlabs/bin/puppetdb delete-reports when upgrading to Puppet Enterprise 2019.8 or later from a previous version.

Error messages and logs

You get this error message in PuppetDB.

[HOSTNAME]# /opt/puppetlabs/bin/puppetdb delete-reports 
Stopping puppetdb... 
Redirecting to /bin/systemctl stop pe-puppetdb.service 
Stopped puppetdb. 
BEGIN 
psql:/tmp/tmp.ubsUGzSvdX/delete_reports.sql:11: ERROR:  deadlock detected 
DETAIL:  Process 106331 waits for AccessExclusiveLock on relation 33843855 of database 16398; blocked by process 106130. 
Process 106130 waits for ShareLock on transaction 55408649; blocked by process 106331. 
HINT:  See server log for query details. 
CONTEXT:  SQL statement "DROP TABLE reports_20220609z" 
PL/pgSQL function inline_code_block line 5 at EXECUTE 
ROLLBACK 

Version and installation information

PE version: 2019.8 or later

Solution

A lock on the table in PuppetDB prevents you from completing the upgrade to PE. If the database is getting updated, wait until the updates are saved before continuing with the upgrade.

To close an active connection and unlock the table, restart PE PostgreSQL: service pe-postgresql restart. Then, continue migrating PuppetDB and upgrading PE using our documentation.