How to set up postgreSQL on Ubuntu for WSL

If you’ve been trying to setup postgreSQL on WSL, it’s likely not going to be hiccup free experience. I am writing this because it took me 2 full days to get things working and I hope if anyone is facing the same problem, they don’t have to uninstall and reinstall Postgres incessantly like I had to.

The version of Ubuntu I’m using is 18.04 (downloaded straight from the Microsoft App Store) but this method should work for other versions as well.

Removing any older PostgreSQL instances

To check out the postgres related packages installed on your system, just use:

dpkg -l | grep postgres

This gives me the following output-

[karan@ubuntu:~]$ dpkg -l | grep postgres
ii  pgdg-keyring                   2018.2                             all          keyring for apt.postgresql.org
ii  postgresql-10                  10.7-1.pgdg18.04+1                 amd64        object-relational SQL database, version 10 server
ii  postgresql-client-10           10.7-1.pgdg18.04+1                 amd64        front-end programs for PostgreSQL 10
ii  postgresql-client-common       199.pgdg18.04+1                    all          manager for multiple PostgreSQL client versions
ii  postgresql-common              199.pgdg18.04+1                    all          PostgreSQL database-cluster manager

If you’d like to start with a clean slate, remove all postgres instances and related packages using :

sudo apt-get --purge remove postgres*

You can also remove the listed packages one by one if you’d like.

PostgreSQL on installation, creates a user named postgres by default. Not sure if this would interfere with future installations but since we’re starting with a clean slate, we might as well remove it.

The /etc/passwd/ file stores the list of users on the system. To check it out, just cat /etc/passwd/. This will return a list of users on the system.

In my case, I could still see the postgres user still being on the list even after I had the packages removed.
To remove this, I just removed the /var/lib/postgreSQL folder using

rm -rf /var/lib/postgreSQL

Doing this, removed the user but you might just prefer to carry the same task using sudo deluser postgres

Performing a fresh installation

I did a fresh installation of postgreSQL by

sudo apt-get install postgresql-10

I chose PostgreSQL 10, but it doesn’t really matter what version you choose to work with.

Now, set a password for this user (IMPORTANT)

sudo passwd postgres

It will prompt you twice for the password.

The Problem

Hopefully the setup went well.
To start working with PostgreSQL now, we just need to get into the postgres user, start a service and then get into the prompt by using the command psql .

Don’t enter the following commands now, read till the end of the section.

What I did was,

[karan@ubuntu:~]$ su - postgres
Password:
postgres@DESKTOP-1TPO09P:~$ sudo /etc/init.d/postgresql start
[sudo] password for postgres:
 * Starting PostgreSQL 10 database server                                                                                                                            [ OK ]
postgres@DESKTOP-1TPO09P:~$ psql
psql (10.7 (Ubuntu 10.7-1.pgdg18.04+1))
Type "help" for help.
postgres=#

It successfully got me into the postgreSQL prompt. But once I closed the session, and tried to restart the service, it wouldn’t let me start it. The same commands suddenly did not work anymore.

Here is the error I got:

postgres@DESKTOP-1TPO09P:~$ sudo /etc/init.d/postgresql start
 * Starting PostgreSQL 10 database server                                                                                * Error: /usr/lib/postgresql/10/bin/pg_ctl /usr/lib/postgresql/10/bin/pg_ctl start -D /var/lib/postgresql/10/main -l /var/log/postgresql/postgresql-10-main.log -s -o  -c config_file="/etc/postgresql/10/main/postgresql.conf"  exited with status 1:
2019-04-30 15:15:42.917 DST [81] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2019-04-30 15:15:42.930 DST [81] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2019-04-30 15:15:42.986 DST [82] LOG:  database system was interrupted; last known up at 2019-04-30 14:44:15 DST
2019-04-30 15:15:42.986 DST [82] PANIC:  could not flush dirty data: Function not implemented
2019-04-30 15:15:42.986 DST [81] LOG:  startup process (PID 82) was terminated by signal 6: Aborted
2019-04-30 15:15:42.986 DST [81] LOG:  aborting startup due to startup process failure
2019-04-30 15:15:42.994 DST [81] LOG:  database system is shut down
pg_ctl: could not start server
Examine the log output.
                                                                                                                 [fail]

The Solution

I tried a thousand different things from StackOverflow but to no avail.
Turns out, that the

PANIC: could not flush dirty data: Function not implemented

issue is a well known WSL issue with postgreSQL. The solution to this is to add a particular statement to the postgresql configuration file. Let’s do that.

Now you can follow along by typing the commands below.

First open the postgresql.conf file:

sudo vi /etc/postgresql/10/main/postgresql.conf

and append the following line to it

data_sync_retry = true.

N.B: For a lot of other users on the Internet, turning the fsync flag to off also helped, but for me that line was already commented out and even uncommenting and setting it to off didn’t really work. But this data_sync_retry hack worked like a charm.

Save the file and restart the terminal.

You might also want to give sudo permission to the postgres user (in case you get permission denied error message while carrying out certain commands) by:

sudo usermod -aG sudo postgres

Retrying

Let’s have a go at it again. Restart the terminal.

Change into the postgres user by-

su - postgres 

Now time to start the server(at first it worked for me without using sudo as you can see in the screenshot below, but using sudo will work everytime)-

sudo /etc/init.d/postgresql start 

You will get something like:
* Starting PostgreSQL 10 database server [ OK ]

Now type psql to enter the postgreSQL prompt and (hopefully) everything worked.

If it did, congratulations.

Let’s move on a bit.
Typing \du gives us the list of roles(that is what users are called in psql lingo)
Typing \l would give me the list of databases.

The only issue I encountered was a long list of warnings on creating a database.

But on scrolling down far enough I could see that my database was created successfully. And my database was also added to the list of databases. Hooray!?!?!

To exit the prompt just type \q and to get back to your original user on linux just type exit.
Also, remember you started a service before you entered the prompt.
Stopping the service is a simple:

sudo /etc/init.d/postgresql stop

I hope this little fix helped you. Clap Clap Clap.