24

I would like to install Rails 5/Postgres within Bash On Ubuntu On Windows and also be able to access Postgres database via PgAdmin for Windows.

Am quiet happy to install all my rails dependencies through Bash on Ubuntu on Windows, but I am aware that I can't run any standard GUI tools from Bash and was wondering if it is possible to install Rails and PostGres on the Linux Subsystem and then query my databases using the Windows version of PgAdmin

I had a look at this tutorial how-to-install-ruby-on-rails-on-windows-10-with-postgresql but it seems that he installs Postgres into windows rather then the Linux Subsystem.

I was wondering if this is the only way to go about getting all these tools working nicely together.

Basically I want my Windows installed PgAdmin to communicate to my Bash On Ubuntu On Windows Postgres DB

David Cruwys
  • 6,262
  • 12
  • 45
  • 91
  • 1
    I have just learnt that bash for Windows existed with your thread :) maybe saving headaches as Rails on Windows is not an easy setup (yet perfectly working)... But I am pretty curious to see how Rails on Bash on Windows would fare (as I know Microsoft, an extra layer is still an extra layer).. – Maxence Aug 16 '17 at 07:51
  • @David Cruwys were you able to figure this out? – Taylor Nov 24 '18 at 20:46

7 Answers7

35

Here's what I did to connect Postgres DB installed in WSL Ubuntu from Windows pgAdmin.

  1. Launch Ubuntu in Windows.
  2. Start postgres in Ubuntu terminal: sudo service postgresql start
  3. Download the latest pgAdmin and install in Windows.
  4. Launch pgAdmin, a new tab in browser opens; click on Add New Server link.
  5. In the popup Create - Server window in the browser:
    1. General tab: I set Name to localhost
    2. Connection tab: I set Host name/address to localhost, set Password to postgres, which is the default, click on Save password?
    3. I save the setting, leaving the rest of the fields as is
  6. That's it, I can see the DB created in Postgres immediately.

Browser screenshot on adding a server

kiatng
  • 2,847
  • 32
  • 39
  • 1
    Will this work the other way around, i.e. if postgres was installed on Windows, can we still access it (not through pgAdmin) in WSL, using say R or Python? – Chintan Pathak Sep 25 '19 at 00:23
  • I had to first create a password for the `postgres` database user with the command `psql -c "alter user postgres with password 'my00pass'"` (as described here: https://linuxhint.com/postgresql_installation_guide_ubuntu_20-04/) – Abel Wenning Aug 01 '22 at 05:03
  • 1
    Got timeout? Even if on Linux you use "localhost", on Windows pgAdmin4 use "127.0.0.1"... – Fellipe Sanches Jun 27 '23 at 16:30
12

posting in case someone had my issue

In my case I was getting Connection refused, I was able to resolve by changing listen_addresses property to '*' inside postgresql.conf file

To locate conf file on ubuntu

sudo -u postgres psql -c 'SHOW config_file'

once you find the file, you need to change listen_addresses like this

listen_addresses = '*'

Then restart postgres using

sudo service postgresql restart

you might need to change the permission of the file before you can edit it, don't forget to revert this change once done.

tito.300
  • 976
  • 1
  • 9
  • 22
  • This answer allowed me to connect to the DB, but I was still getting an auth error for the postgres user, this answer fixed that for me: https://stackoverflow.com/a/7696398/1864403 – Jazcash May 09 '21 at 17:06
  • You might also need to adjust pg_hba.conf and add ` host all all all md5` as a first line. See more https://dba.stackexchange.com/questions/83984/connect-to-postgresql-server-fatal-no-pg-hba-conf-entry-for-host – Ren Jul 16 '21 at 11:33
10

The answer provided by @kiatng helped me too. However, I was getting a socket not connected error when I was trying to access the data in my tables. Through another Google search, I found I needed to change localhost to 127.0.0.1 and then I was able to access the data. Just putting this here in case it helps someone else. I would have replied to kiatng's answer but I don't quite have enough 'reputation' yet to do so!

Catriona Bennett
  • 145
  • 1
  • 7
  • 4
    I did change from `localhost` to `127.0.0.1`, but now getting another error `FATAL: password authentication failed for user "postgres"`. To setup postgres in WSL, I followed [Microsoft docs](https://learn.microsoft.com/en-us/windows/wsl/tutorials/wsl-database) and also changed the password accordingly. – Prateek Jha Dec 17 '20 at 07:50
  • THANK YOU VERY MUCH!! I repeat this information to help to anothers: my PgAdmin working perfect with connection to database con wsl2 however the querys to select records of tables is very slow (I don't receive response of the request) and I create a new server connection with 127.0.0.1 instead of localhost and the requests work perfect! in real time. Thanks thanks thanks! I love you haha – Juan Camilo Camacho Beltrán Jan 02 '21 at 08:04
  • 2
    @PrateekJha I had the same issue, and I tried to change the password with these commands https://stackoverflow.com/a/12721095/10592134，and tried to create db again in PgAdmin, and it worked! – Chloe Sun Jan 06 '21 at 00:13
  • 1
    @ChloeSun I resolved this issue by setting allow all connection inside postgresql.conf file. – Prateek Jha Jan 08 '21 at 05:17
5

I had a similar issue with postgresql running on WSL2 - bash and wanting connect from pgadmin running on windows.

The following settings will get the job done (as it did for me)

in /etc/postgreql//main/postgresql.conf have

listen_addresses = '*'

And then in /etc/postgreql//main/pg_hba.conf have

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    all             all             172.0.0.0/8             md5

restart postgresql

sudo service postgresql restart

This allows both localhost access and WSL ip access.

In your pgadmin, in connection setting try out localhost as the host settings (usually works , if it does not, you can give the bash ip which is usually in 172 range)

IndikaM
  • 409
  • 7
  • 14
2

Ubuntu has X server, where you can run pgAdmin III GUI (I never tried IV, but quite sure it will work on linux even better). Still if you want to setup db on some virtual machine on Windows and use Windows client - yes - it will work. You need to setup some network bridge between your virtual machine and hosting Windows and that is it - use Ubuntu IP to connect to Postgres. (also you will need to allow connections in pg_hba.conf and set listen_address to *, but this needs to be done for external connects anyway)

Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
2

In my case WSL2, localhost forwarding doesn't seem to work so the solutions mentioned were not working. I had to do a Network Reset, which worked for me.

Reference: WSL Issue on Github

If the above solutions are not working for you, try to do a Network Reset as shown in the image below, and then try the above answers.

enter image description here

PR7
  • 1,524
  • 1
  • 13
  • 24
-1

First, run

sudo -u postgres psql -c 'SHOW config_file'

Open the resulting file, uncomment listen_addresses, and change to it this:

listen_addresses = '*'`

Then run

sudo service postgresql restart
sadiq rashid
  • 468
  • 5
  • 8