2

We have a Django application running on Ubuntu using a Postgres DB on the same server. We are backing up our database every day with the following command:

pg_dump -Fc -U user1 -w db_name > ${filepath}`

The problem is that is seems to be blocking read/write access to the same database with the same user (user1) no our Django app. Checking the documentation it states that

pg_dump does not block other users accessing the database (readers or writers)

Which makes me think that maybe is it blocking access to the DB because the Django app is using the same user. Can this happen? I didn't find any documentation that clarifies it.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
Storo
  • 988
  • 1
  • 7
  • 31
  • What is the error you are seeing? Are there errors connecting, which might imply max_connections or the connection limit of the user is too low or are you seeing the queries being blocked by pg_dump? If the latter, check pg_stat_statements to verify that your queries are waiting. – Jeremy Apr 25 '19 at 18:31

1 Answers1

4

If access is actually blocked, i.e. you get no error, but the application hangs, it must be that the application takes ACCESS EXCLUSIVE locks on tables.

That can happen if your workload has a lot of TRUNCATE or ALTER TABLE statements or similar. Those would be blocked until pg_dump, which runs in a single transaction for consistency, is done.

It could also be explicit table locks taken with LOCK tablename, which are usually a sign of bad design.

Try to reduce the ACCESS EXCLUSIVE locks in your workload, they can also prevent autovacuum and thus damage your database.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263