Postgres is stuck when creating a database?

Question

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.

Please share more details - is this even related to programming? — Nico Haase
– Nico Haase, Commented Nov 2, 2021 at 10:30
What kind of details? I installed Postgres, then the next step is creating a database, but it's hanging — Leo Jiang
– Leo Jiang, Commented Nov 2, 2021 at 10:38
If this is a pure problem with Postgres, it might be better suited at serverfault.com or dba.stackexchange.com — Nico Haase
– Nico Haase, Commented Nov 2, 2021 at 10:50
@LeoJiang Have you found an answer? I'm also getting this behaviour in WSL1 with PostgreSQL 14 (13 works fine). I don't want to switch to WSL2. — Furgas
– Furgas, Commented Mar 2, 2022 at 21:23
I don't remember, probably just deleted the data directory and reinstalled — Leo Jiang
– Leo Jiang, Commented Mar 3, 2022 at 11:26

Vaggelis Spi · Accepted Answer · 2022-01-24 15:10:24Z

6

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.

answered Jan 24, 2022 at 15:10

Vaggelis Spi

1642 silver badges11 bronze badges

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

2 Comments

DanielHefti Over a year ago

I had to use the command wsl --set-default-version *distribution_name* 2 elsewhise it did not work on my computer. With the command wsl -l -v one can see which distribution is installed with which wsl version.

Robert Over a year ago

yes wsl 2 will not work for me see this error message. C:\Users\rlc>wsl --set-version Ubuntu-22.04 2 For information on key differences with WSL 2 please visit aka.ms/wsl2 Conversion in progress, this may take a few minutes. The operation could not be started because a required feature is not installed. Error code: Wsl/Service/CreateVm/HCS_E_SERVICE_NOT_AVAILABLE I have other virtual machines and wsl 2 is not possible. I need a wsl 1 solution.

vidur punj · Accepted Answer · 2023-08-29 07:49:56Z

3

There is a work around: Check you distros version by:

wsl -l -v

update the wsl version of you distros to 2 and then create the database it will get created.

Also other work arounds:

if you need to update the ubuntu switch back to wsl 1 and then update the ubuntu

sudo apt update
sudo apt upgrade

after doing it switch back to wsl2

To update or degrade use the following commands:

Move to version 2:

wsl --set-version Ubuntu-22.04 2

Back to version 1:

wsl --set-version Ubuntu-22.04 1

answered Aug 29, 2023 at 7:49

vidur punj

6,0095 gold badges53 silver badges73 bronze badges

2 Comments

Wojciech Kaczmarek Over a year ago

I am not sure why your answer got downvoted. Actually one can change WSL version without destroying the whole distro, as you say. The conversion can take about 5 minutes but it's fine. It saves a lot of time that would be otherwise spend on installing & configuring everything again. So, thank you for this answer, it should get upvoted more.

vidur punj Over a year ago

@WojciechKaczmarek this is the trick we can apply as a work around, Thanks for support.

Laurenz Albe · Accepted Answer · 2021-11-02 11:51:51Z

2

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.

answered Nov 2, 2021 at 11:51

Laurenz Albe

257k22 gold badges311 silver badges387 bronze badges

Comments

zodman · Accepted Answer · 2022-01-08 05:30:52Z

2

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

answered Jan 8, 2022 at 5:30

zodman

404 bronze badges

2 Comments

Vaggelis Spi Over a year ago

But how do you solve it? I seem to have the same issue as well

zodman Over a year ago

I stop using wsl!

Collectives™ on Stack Overflow

Postgres is stuck when creating a database?

4 Answers 4

2 Comments

2 Comments

Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

2 Comments

Comments

2 Comments

Linked

Related