Postgres database deadlock issue

Question

I am using Cloudera Manager to monitor and configure Hadoop. I choose PostgreSQL as metastore of HUE - a web portal to submit your SQL to Hadoop.

When I submit a SQL, the HUE says the database is locked. I don't have any database management experience.

So, how to find the root cause and diagnose?

I try to connect to the database postgres which I believe this is the meta-database of PostgreSQL itself. I expect I can find the processes, events and lock information in this database.

postgres=> \c postgres
Password for user hue:
You are now connected to database "postgres" as user "hue".
postgres=> \d
No relations found.
postgres=>

I connect to this database - 'postgres' as user - 'HUE'. But I didn't find any tables under it. Am I using the wrong user?

How can I access this database successfully? How can I discover why the database is locked? Where is it locked? On which table and which database?

• ypercubeᵀᴹ: Read on the Postgres documentation online: Lock Monitoring

Answer 1

Your software is most likely not using the default postgres database (that is a database that is normally reserved only to store the list of databases, users, and other globals).

From psql, run \l to list the available databases. Your database will be something other than postgres, template0, or template1. Try connecting to one that is not one of the above (\c dbname) and then run \d again to list tables.

• cdhit: Thanks. I know the user database should not be postgres, template0, template1. What I want to know is how to analysis deadlock problem. I believe that I can get some informations from some system databases and tables about the dead lock.
• Ezequiel Tolnay: With PgAdmin3, menu Tools / Server Status, you can see what's going on and what statement is locking which resources and/or other statements, refreshed periodically.
• cdhit: I can see the real time deadlock through the tool/server status, how can I know how many dead locks in the history?
• Ezequiel Tolnay: @cdhit you can't, deadlocks are not (normally, or AFAIK not at all possibly) logged. Have a look at this, or see what you find googling "postgresql log deadlock"