PostgreSQL In Windows Subsystem for Linux (WSL)

To start refresh clean up the previous install using :

sudo apt-get remove postgresql
[sudo] password for harshityadav95:
Reading package lists... Done
Building dependency tree
Reading state information... Done
Package 'postgresql' is not installed, so not removed
The following package was automatically installed and is no longer required:
  libfreetype6
Use 'sudo apt autoremove' to remove it.
0 upgraded, 0 newly installed, 0 to remove and 0 not upgraded.

Now Install fresh using

sudo apt-get install postgresql

Postgres User Setup

postgresql-10 runs under the user postgres. We need to give this user a password so that postgres can allow this user to connect to the database.

1. To set the password for postgres, type sudo passwd postgres
2. You will get a prompt to enter in your password. It will not show when you are typing, but it is still registering your key-strokes.
3. Close and repoen the terminal.

Using psql

After your first install, and each time you restart your machine you will have to also restart the postgres service, or else you will get a Is the server running? error.

1. To start the service, type sudo service postgresql start.
2. To conntect to postgres, type sudo -u postgres psql

You should get a prompt asking for your password. If this doesn’t work, then you can try the second option listed below.

1. Switch users to postgres by typing su - postgres.
2. Type psql.

When this is successful you will see the command line change to look like this postgres=#

Creating user

$ sudo -u postgres createuser <username>

Creating Database

$ sudo -u postgres createdb <dbname>
if getting error:WARNING:  could not flush dirty data: Function not implemented [Ignore for now]

Giving the user a password

$ sudo -u postgres psql
psql=# alter user <username> with encrypted password '<password>';

Granting privileges on database

psql=# grant all privileges on database <dbname> to <username> ; 

Run a normal arithematic query to see if everything is working , now

Connecting Postgresql Instance to GUI Client in Windows

There are GUI Clients which specially designed for PgAdmin4 or some general managemnt studio which supports different kind of database like

1. Dbeaver [https://dbeaver.io]
2. Adminer [https://www.adminer.org]
3. Table Plus [https://tableplus.io]
4. heidisql [https://www.heidisql.com/]
5. OmniDB [https://omnidb.org/en/
6. squirrelsql[http://www.squirrelsql.org]

Among all the one with stable release and portable package to run without installation I chose Table Plus now to connect Launch the .exe

click on create new connection and enter the settings :

Replace the Username , password and database with your config

or if using PgAdmin