Postgres connection - Githubissues

rando305 commented 8 years ago

Please use the following bug reporting template to help produce actionable and reproducible issues:

A brief description I have postgres running as a database installed in Windows10. It would be really useful to be able to access the postgres db from the bash. Commands like pgdump don't know where to look for the database.
Expected results connection the postgresql database
Actual results (with terminal output if applicable) pg_dump: [archiver (db)] connection to database "rando" failed: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
Your Windows build number 14393.51
Steps / commands required to reproduce the error pg_dump
Strace of the failing command see above
Required packages and commands to install postrgesql-client-common

See our contributing instructions for assistance.

aseering commented 8 years ago

Hi @rando305 -- you should be able to connect over TCP if you configure the PostgreSQL server to listen on localhost and the clients to connect to localhost. Are you familiar enough with it to do this? If not, and if you can't immediately figure it out by searching online, I'm sure someone here can point you in the right direction.

rando305 commented 8 years ago

Thanks Adam. I configured PostgreSQL server to listen on localhost. I'm not sure how I get the bash client to recognize where PostgreSQL is. pg_dump is looking for information in a unix socket. Any advice on how to get the pg_dump command to recognize where the server is running would be really helpful.

On Thu, Aug 18, 2016 at 6:51 PM, Adam Seering notifications@github.com wrote:

Hi @rando305 https://github.com/rando305 -- you should be able to connect over TCP if you configure the PostgreSQL server to listen on localhost and the clients to connect to localhost. Are you familiar enough with it to do this? If not, and if you can't immediately figure it out by searching online, I'm sure someone here can point you in the right direction.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/Microsoft/BashOnWindows/issues/921#issuecomment-240880788, or mute the thread https://github.com/notifications/unsubscribe-auth/AFuY42BtiyjO2DSGQAloYoYUz4I1-wzwks5qhOIPgaJpZM4Jmlby .

aseering commented 8 years ago

Hi @rando305 -- off the top of my head, try setting the environment variable export PGHOST=localhost. Does pg_dump then work?

aseering commented 8 years ago

You might also have to export PGPORT, if you're running on a port that's not the default.

joshuaflanagan commented 8 years ago

I'm having this same problem. I am not familiar with configuring postgres on Windows. I tried installing it within bash, but that didn't work, so installed it in Windows as recommended in https://github.com/Microsoft/BashOnWindows/issues/61.

I installed it in Windows using the EnterpriseDB installer mentioned on postgresql website. I can connect to the server using the pgAdmin tool, or the "SQL prompt" shortcut it provides in Windows. However, when I try to connect via psql from bash I get the error described in this issue.

The server is running in Windows, listening on 5432. Is there something I need to change in the default EnterpriseDB config to expose the server more?

joshuaflanagan commented 8 years ago

This is how I have resolved this issue for me:

when you do not specify a hostname, postgresql clients (psql, pg_dump) will try to connect via a Unix socket, not TCP. Connection from Bash on Windows to Postgresql running on Windows must be via TCP (the error message mentioned that it could not connect on a Unix domain socket, but it hadn't clicked in my head). To force your connection to use TCP you must specify a hostname. The host can be specified with a command line flag -h localhost, via PGHOST environment variable, or via .psqlrc file. I added export PGHOST=localhost to the bottom of my ~/.bashrc file.
The second issue I had was that I expected psql to be able to connect to the local host without specifying a password, just as I do when running on Mac OSX. This was fixed by changing the Windows Postgres server's pg_hba.conf file. The default line in my installation for 127.0.0.1 was configured for md5 connection method. I changed it to trust, to match the configuration on my OSX installation. That makes the pg server trust any connection that can be made.

rando305 commented 8 years ago

Joshua,

thanks! this helps alot. I will test this. I'm assuming the best thing is to set this up in my .psqlrc file. Where is (or should be) this file. And what should it have in it?

Thanks again for your contribution

On Sun, Aug 21, 2016 at 11:04 AM, Joshua Flanagan notifications@github.com wrote:

This is how I have resolved this issue for me:

-

when you do not specify a hostname, postgresql clients (psql, pg_dump) will try to connect via a Unix socket, not TCP. Connection from Bash on Windows to Postgresql running on Windows must be via TCP (the error message mentioned that it could not connect on a Unix domain socket, but it hadn't clicked in my head). To force your connection to use TCP you must specify a hostname. The host can be specified with a command line flag -h localhost, via PGHOST environment variable, or via .psqlrc file.

The second issue I had was that I expected psql to be able to connect to the local host without specifying a password, just as I do when running on Mac OSX. This was fixed by changing the Windows Postgres server's pg_hba.conf file. The default line in my installation for 127.0.0.1 was configured for md5 connection method. I changed it to trust, to match the configuration on my OSX installation. That makes the pg server trust any connection that can be made.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/Microsoft/BashOnWindows/issues/921#issuecomment-241262767, or mute the thread https://github.com/notifications/unsubscribe-auth/AFuY44WlquDGGmqT3gADHI4Smqo4OcERks5qiGjugaJpZM4Jmlby .

rando305 commented 8 years ago

I ran psql - h localhost on bash

The result was: psql: FATAL: could not load pg_hba.conf

the file is at: "C:\Program Files\PostgreSQL\9.3\data\pg_hba.conf" how do I get everything point to the right place?

Thanks again

On Sun, Aug 21, 2016 at 11:04 AM, Joshua Flanagan notifications@github.com wrote:

This is how I have resolved this issue for me:

-

when you do not specify a hostname, postgresql clients (psql, pg_dump) will try to connect via a Unix socket, not TCP. Connection from Bash on Windows to Postgresql running on Windows must be via TCP (the error message mentioned that it could not connect on a Unix domain socket, but it hadn't clicked in my head). To force your connection to use TCP you must specify a hostname. The host can be specified with a command line flag -h localhost, via PGHOST environment variable, or via .psqlrc file.

The second issue I had was that I expected psql to be able to connect to the local host without specifying a password, just as I do when running on Mac OSX. This was fixed by changing the Windows Postgres server's pg_hba.conf file. The default line in my installation for 127.0.0.1 was configured for md5 connection method. I changed it to trust, to match the configuration on my OSX installation. That makes the pg server trust any connection that can be made.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/Microsoft/BashOnWindows/issues/921#issuecomment-241262767, or mute the thread https://github.com/notifications/unsubscribe-auth/AFuY44WlquDGGmqT3gADHI4Smqo4OcERks5qiGjugaJpZM4Jmlby .

rando305 commented 8 years ago

When I do the following: export PGHOST=localhost I run psql and I get the error that it can't find my pg_hba.conf file (which for now is good)

I put the line: PGHOST=localhost in /etc/environment - and I get the socket error

I put that line in ~/.bashrc and I get the same socket error.

I'm close...

On Sun, Aug 21, 2016 at 9:31 PM, Randy Ostler rando305@gmail.com wrote:

I ran psql - h localhost on bash

The result was: psql: FATAL: could not load pg_hba.conf

the file is at: "C:\Program Files\PostgreSQL\9.3\data\pg_hba.conf" how do I get everything point to the right place?

Thanks again

On Sun, Aug 21, 2016 at 11:04 AM, Joshua Flanagan < notifications@github.com> wrote:

This is how I have resolved this issue for me:

-

when you do not specify a hostname, postgresql clients (psql, pg_dump) will try to connect via a Unix socket, not TCP. Connection from Bash on Windows to Postgresql running on Windows must be via TCP (the error message mentioned that it could not connect on a Unix domain socket, but it hadn't clicked in my head). To force your connection to use TCP you must specify a hostname. The host can be specified with a command line flag -h localhost, via PGHOST environment variable, or via .psqlrc file.

The second issue I had was that I expected psql to be able to connect to the local host without specifying a password, just as I do when running on Mac OSX. This was fixed by changing the Windows Postgres server's pg_hba.conf file. The default line in my installation for 127.0.0.1 was configured for md5 connection method. I changed it to trust, to match the configuration on my OSX installation. That makes the pg server trust any connection that can be made.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/Microsoft/BashOnWindows/issues/921#issuecomment-241262767, or mute the thread https://github.com/notifications/unsubscribe-auth/AFuY44WlquDGGmqT3gADHI4Smqo4OcERks5qiGjugaJpZM4Jmlby .

joshuaflanagan commented 8 years ago

Actually, now I'm not sure you can set the default host from .psqlrc. I saw that suggestion in a forum post, but after reading the documentation, I don't see how that is possible.

Regarding your pg_hba.conf file: do you already have the postgresql server working and running on Windows? Have you verified you can connect to it from Windows (completely leave Bash on Windows out of it)? If you installed it using EnterpriseDB's installer, you can use pgAdmin to connect to the database, run queries, and edit the pg_hba.conf file. It also comes with a "SQL Prompt" shortcut in your start menu that will launch the psql command line for you. I would definitely make sure psql/pg_dump, etc all work from Windows before bringing Bash into the picture.

rando305 commented 8 years ago

got my pg_hba.conf file working correctly by using the editor in pg_adminIII. That helped get the syntax correct. I got my remote pg_dump to work and was able to upload it into my local db from the bash. Thanks for your help.

On Mon, Aug 22, 2016 at 12:17 AM, Joshua Flanagan notifications@github.com wrote:

Actually, now I'm not sure you can set the default host from .psqlrc. I saw that suggestion in a forum post, but after reading the documentation, I don't see how that is possible.

Regarding your pg_hba.conf file: do you already have the postgresql server working and running on Windows? Have you verified you can connect to it from Windows (completely leave Bash on Windows out of it)? If you installed it using EnterpriseDB's installer, you can use pgAdmin to connect to the database, run queries, and edit the pg_hba.conf file. It also comes with a "SQL Prompt" shortcut in your start menu that will launch the psql command line for you. I would definitely make sure psql/pg_dump, etc all work from Windows before bringing Bash into the picture.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/Microsoft/BashOnWindows/issues/921#issuecomment-241311899, or mute the thread https://github.com/notifications/unsubscribe-auth/AFuY43frIA0WbJtBKpVZOwMEkzvQ9WY-ks5qiSLvgaJpZM4Jmlby .

joshuaflanagan commented 8 years ago

I think this issue can be closed, as it does not describe a BashOnWindows bug - it was just us trying to work out how to connect to Windows Postgres from BashOnWindows.

There is already an existing issue https://github.com/Microsoft/BashOnWindows/issues/61 to track getting Postgres to work within BashOnWindows.

dalgrande commented 4 years ago

I'm using postgres inside a container, inside a docker inside wsl2 to develop at home. I've managed to connect to DB using any DBMS using the IPv6 loopback. jdbc:postgresql://[::1]:5432/database_name I hope it helps someone. To find this use netstat -ltnp

Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 0.0.0.0:8443            0.0.0.0:*               LISTEN      5321/python
tcp6       0      0 :::5432                 :::*                    LISTEN      23171/docker-proxy

ZeeStorm commented 4 years ago

I'm using postgres inside a container, inside a docker inside wsl2 to develop at home. I've managed to connect to DB using any DBMS using the IPv6 loopback. jdbc:postgresql://[::1]:5432/database_name I hope it helps someone. To find this use netstat -ltnp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 0.0.0.0:8443            0.0.0.0:*               LISTEN      5321/python
tcp6       0      0 :::5432                 :::*                    LISTEN      23171/docker-proxy

A million thanks. Been banging my head on my desk for days, this worked perfectly!

daniel-rodrigues commented 3 years ago

I solved this problem by configuring the ports when creating the container, example: $ docker run --name postgres-sever -p 5432:5432 -e POSTGRES_PASSWORD=secretpassword -d postgres

microsoft / WSL

Postgres connection #921