Alembic migration stuck with postgresql?

Question

I wrote a migration script which works fine on sqlite, but if i try to apply the same to postgres it is stuck forever. With a simple ps i can see the postres stuck on "create table waiting". There are any best practice?

Answer 1

If it's really stuck on a lock, you need to see what it's waiting for. It'd be pretty odd for CREATE TABLE to be stuck on a lock, but it's not impossible.

Get the stuck process id

Get the process ID of the waiting backend. You can find it in ps, or by SELECTing from pg_stat_activity, looking for processes with waiting true, to find the command you're interested in:

SELECT * FROM pg_stat_activity WHERE waiting;

Figure out what lock it's waiting on

Take a look at the lock the stuck pid is waiting on by querying pg_locks:

SELECT * FROM pg_locks WHERE pid = <the-waiting-pid> AND NOT granted;

You can combine both these steps with:

\x

SELECT * 
FROM pg_locks l
INNER JOIN pg_stat_activity s ON (l.pid = s.pid)
WHERE waiting
AND NOT granted;

then look through the results, or use a LIKE filter on the s.query field to find the query you're trying to identify locking issues for.

Find who holds that lock

You can now query pg_locks to find out which process(es) find that lock, and what they're doing.

Say we found that the create was waiting for a locktype=relation lock of mode=AccessExclusiveLock on relation=14421 to be granted. We want to find locks held by other sessions on that relation:

SELECT * 
FROM pg_locks l 
INNER JOIN pg_stat_activity s ON (l.pid = s.pid)
WHERE locktype = 'relation'
AND   relation = 14421;

This should tell you what's blocking the create.

Wrinkles

There's a handy lock monitoring query on the PostgreSQL wiki, but it'll only find row level locks. So it's generally not helpful for DDL.

Also, I've intentionally not combined the whole lot into a single query. It'd be simple enough to find lock holders that block a given backend by pid in the case of an AccessExclusiveLock, but for weaker lock requests, it's not so simple - I'd have to write out the rules about which locks conflict with which in SQL, and that's pretty complicated. Better to just eyeball it.

Answer 2

You can always just restart postgresql.

Answer 3

For anyone who faces this problem, here is what fixed it for me:

The standard env.py for alembic contains the passage

with context.begin_transaction():
    logger.info("Running Transaction.")
    context.run_migrations()

However, if you then introduce the engine in your migration script with

config = op.get_context().config
engine = engine_from_config(config.get_section(
                            config.config_ini_section), prefix='sqlalchemy.')

the transaction will be stuck in "transaction in idle". I am not quite sure why, but it looks like transactions within transactions and the outer transaction is never committed.

To fix this, just remove the outer begin_transaction()

# with context.begin_transaction():
logger.info("Running Transaction.")
context.run_migrations()

and you should be good to go.

Answer 4

your database is most likely locked by another query.

Especially if you do stuff with their GUI pgAdmin, this can happen a lot I found. (truncating tables is especially tricky, sometimes pgAdmin crashes and the db gets stuck)

what you want to do is to restart the complete postgresql service and try again.

Make sure that you:

1. minimize the usage of GUI pgadmin
2. close your cursors/dbs with psycopg2 if you don't need them