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.