Postgres is stuck when creating a database?

user_599184 0
Viewed 1140

I just installed Postgres on Ubuntu 20 on WSL. Both of the following are stuck forever:

CREATE DATABASE foo
WITH
OWNER = postgres      
ENCODING = 'UTF8'     
CONNECTION LIMIT = -1;

And:

sudo -u postgres createdb foo

The log doesn't have anything:

postgres@postgres ERROR:  canceling statement due to user request
postgres@postgres STATEMENT:  CREATE DATABASE foo
            WITH
            OWNER = postgres
            ENCODING = 'UTF8'
            CONNECTION LIMIT = -1;

What are some possible causes for this?

Restarting Postgres or restarting the computer doesn't help and there's no locks. I also tried manually killing every lock.

postgresql
3 Answers

While the statement is hanging, connect to the postgres database and look in pg_stat_activity for the wait_event and wait_event_type of the hanging connection.

Very likely it is waiting for a lock. In that case, end the transaction that is holding the lock.

user_64643080

take a look of

select * from pg_stat_activity sa inner join pg_locks l on sa.pid = l.pid

In my case I use wsl and get lock by AccessShareLock
its more a problem with wsl than postgresql

user_10039080

I had the same issue with you. It seems that it has to do with the wsl version. I was running wsl 1 so I had to uninstall ubuntu 20.04 wsl, then set the verion to 2 with the following command

wsl --set-default-version 2

and then install ubuntu 20.04 wsl again. Now everything seems to be in order and I am able to create databases with no issues.

user_58459290
Related