Problem with locks while moving table to different schema in postgres

Question

I would like to change schema of few tables in my Postgres DB. Problem is that all the time there are long running queries and as I understand schema change needs exclusive lock.

Question is how can I do it? Of course I can kill all existing queries and try to do schema rename (move table to different schema) but there is a huge chance that in the meantime new queries will appear.

Thanks for help!

"I understand schema change needs exclusive lock." - correct. You will have to block new connections until that change is through (which only takes a few seconds, as it is only a "rename" not a real physical move) — user330315
– user330315, Commented Jan 24, 2022 at 13:53

Laurenz Albe · Accepted Answer · 2022-01-24 13:53:37Z

5

run SELECT pg_backend_pid() before running the ALTER TABLE
start the ALTER TABLE statement
in a second database session, run SELECT pg_blocking_pids(12345), where 12345 is the result from the first query
cancel all the blocking transactions found with the previous query with SELECT pg_cancel_backend(23456)

answered Jan 24, 2022 at 13:53

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Problem with locks while moving table to different schema in postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related