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.
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.
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
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.