Troubleshooting Postgres queries getting stuck in LWLock state after running SystemUpdate job

Original Slack Thread

datahub version: V0.10.5
postgres version: 12.3

Postgres queries get stuck in LWLock state as soon i run the SystemUpdate job.
Due to this the datahub UI stops responding.

HI - could you elaborate the situation? what errors /logs are you seeing?

I was trying to restore pg backup from one datahub instance to another (PROD –> TEST). The restore procedure failed to create unique constraints due to duplicate entries.
After manually removing the duplicate records everything worked fine.

Please help me identify what caused these duplicate records and how we can prevent them ?

This was caused due to failure of index creation during database restore.