bushidocodes/Installing Postgres on WSL.md

## Installing Postgres on WSL.md

      
    Raw
  

              Installing Postgres on WSL.md
            
          
    When I went through Eliot's excellent tutorial, I decided to try to push forward with the latest-and-greatest version of Postgres to troubleshoot the directions with the newer version (the 64-bit Version 9.6.1.1 x64 Windows installer).
But first I had cleanup to do. I had mistakenly assumed that I was running postgresql under WSL and installed some packages. I removed them to make sure I didn't end up with collisions over Linux and Windows binaries sharing the same name in $PATH.
➜  ~ sudo apt-get remove postgresql
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages will be REMOVED:
  postgresql
0 upgraded, 0 newly installed, 1 to remove and 0 not upgraded.
After this operation, 59.4 kB disk space will be freed.
Do you want to continue? [Y/n] y
(Reading database ... 38341 files and directories currently installed.)
Removing postgresql (9.5+173) ...
➜  ~

When I ran the psql -p 5432 -h localhost -U postgres for the first time in BASH, I was actually able to get into the prompt using the postgres user I created during the Windows Installation wizard.
➜  ~ psql -p 5432 -h localhost -U postgres
psql (9.5.5, server 9.6.1)
WARNING: psql major version 9.5, server major version 9.6.
         Some psql features might not work.
Type "help" for help.

postgres=# \q
One of the big changes between 9.3 and 9.6 is the upgrade of pgAdmin III to pgAdmin IV. The interface seems to have changed, and I couldn't find a way to change my localhost permissions from md5 to trust in the GUI.
As I result, I just had to manually edit the configuration file as shown in the Ubuntu instructions
Accepting the defaults during the Windows installer, my pg_hba.conf file was located in C:\Program Files\PostgreSQL\9.6\data.
This can be edited in VIM like such:
vim /mnt/c/Program\ Files/PostgreSQL/9.6/data/pg_hba.conf
and can be edited to look like the following:
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
#host    replication     postgres        127.0.0.1/32           trust
#host    replication     postgres        ::1/128                trust

I wasn't clear about the command sudo upstart restart postgresql but I exported the Windows binary folder to my BASH $PATH
export PATH=$PATH:/mnt/c/Program\ Files/PostgreSQL/9.6/bin
to use pg_ctl.exe to restart the file.
pg_ctl.exe restart -D C:\\Program\ Files\\PostgreSQL\\9.6\\data\

waiting for server to shut down.... done
server stopped
server starting
2016-11-09 22:20:55 EST LOG:  redirecting log output to logging collector process
2016-11-09 22:20:55 EST HINT:  Future log output will appear in directory "pg_log".
➜  ~

I moved on to create my user to see if it would work without a restart, and I got some errors.
➜  ~ createuser --interactive spmcbride1201
Shall the new role be a superuser? (y/n) y
createuser: could not connect to database postgres: FATAL:  role "spmcbride1201" does not exist

My workaround for this was to create my userid in pgAdmin. I knew that this was successful because the createuser command now failed on my username already existing
➜  ~ createuser --interactive spmcbride1201
Shall the new role be a superuser? (y/n) y
createuser: creation of new role failed: ERROR:  role "spmcbride1201" already exists

I was then able to create a db, enter the psql shell, and create a table.
➜  ~ createdb spmcbride1201
➜  ~ psql
psql (9.5.5, server 9.6.1)
WARNING: psql major version 9.5, server major version 9.6.
         Some psql features might not work.
Type "help" for help.
spmcbride1201=# help
You are using psql, the command-line interface to PostgreSQL.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit
spmcbride1201=# CREATE TABLE films (     code        char(5) CONSTRAINT firstkey PRIMARY KEY,     title       varchar(40) NOT NULL,     did         integer NOT NULL,     date_prod   date,     kind        varchar(10),     len
interval hour to minute );
CREATE TABLE
spmcbride1201=# \q
➜  ~

Going back to pgAdmin4, I was able to see the new table after hitting refresh.
I was then successfully able to create the ~/.psqlrc file to get the custom prompt.
I then completed the rest of the Postgres prework.