InMotion Hosting Support Center

Establish remote connection to PostGreSQL database

Category: Databases

DaveAlbaugh
Asked:
2015-01-12 10:45 pm EST

Hits: 2,135
I asked the following question recently: "Is it possible to connect to my PostGreSQL database remotely?"
The answer I received from Scott was: "Thank you for your PostGreSQL connection question. You can certainly connect to the database. As long as you have a connection tool such as HeidiSQL. You can also connect via SSH by using the command: <CODE>psql DBNAME USERNAME</CODE>"
Unfortunately, Scott left out quite a bit of that command, and it doesn't work anyway. That would be the command that I would use if I wanted to connect to the PostGreSQL databaseon my local computer (actually it would be "psql -d DBNAME -U USERNAME"), but I guess I wasn't real clear in my question. I have an account with InMotionHosting for my website (http://www.flagstaffinfocomm.com). I have created a database using PostGreSQL on that site for a perl CGI program that I have created. I need to connect to that database with my computer to create some new tables and insert data into them that is stored in .csv files on my local computer. I have tried the psql command without success. Here is the command as it is normally used to connect to a PostGreSQL database on a remote server:
<CODE>psql -h <SERVER> -p <PORT> -U <USERNAME> -d <DATABASE></CODE>
It sits for about 10 minutes before I finally just kill the process.
I understand that PostGreSQL is a recent add-on so I will try to provide as much info as I can. Are there any other suggestions?

You must login before you can ask a follow up question.

You must login before you can submit an answer.

Best answer chosen by User

0

JeffMa
Staff
11,186 Points
2015-01-13 9:42 am EST
Hello DaveAlbaugh,

I apologize for the mixup in information on your previous question. To clarify things a bit, you can connect to PostgreSQL via command line after already connecting the server via SSH, but cannot directly connect to PostgreSQL. This is because the post that PostgreSQL runs on, 5432, is closed to outside traffic. Unlike MySQL, we do not have an option currently to add your IP to the allowed remote connections list yet to directly connect using that port.

Instead, you may simply connect to the server via SSH, then connect to PostgreSQL from inside the server using the following command:

psql -d DBNAME -U USER



Best regards,
JeffMa

You must login before you can post a comment about this answer.

Thank you, JeffMa. That was indeed where I was lost. I appreciate the patience and clarification.
DaveAlbaugh
25 Points
2015-01-13 7:01 pm EST
Hi again JeffMa. I followed the instructions, connected to the server via ssh (my client PC is running Debian 7) and tried running psql but get a "psql: command not found" error.
DaveAlbaugh
25 Points
2015-01-13 7:43 pm EST
I was able to log in as your user an execute the command just fine. Be sure that you are actively within the server.
JeffMa
11,186 Points
Staff
2015-01-13 7:53 pm EST
Thanks again Jeff. Should I be using my domain name as the server name or the bizxxx.inmotionhosting.com domain name?
DaveAlbaugh
25 Points
2015-01-13 7:59 pm EST
Either will be perfectly fine as long as the domain resolves to the server.
JeffMa
11,186 Points
Staff
2015-01-13 8:01 pm EST
Ok. I have been able to connect with both, but now all I get are:
"-jailshell: fork: retry: Resource temporarily unavailable
-jailshell: fork: Resource temporarily unavailable"
errors. I can't even run ps -ux to see what is running.
DaveAlbaugh
25 Points
2015-01-13 8:04 pm EST
It looks like there was an issue within your account. I have corrected it and it should work normally now.
JeffMa
11,186 Points
Staff
2015-01-13 8:13 pm EST
Ok. I give up. I am connected to the server and cannot run psql. I keep getting:
"-jailshell: psql: command not found" when I run "psql -d DATABASE -U USERNAME". Yes, I am substituting the actual database and username in that command. But even if I just type in "psql" and hit enter I get the "-jailshell: psql: command not found" error.
DaveAlbaugh
25 Points
2015-01-13 8:23 pm EST
It looks like for some reason the permissions are being denied for your SSH session. You may want to submit a verified ticket to technical support in which our systems administrators can take a better look.
JeffMa
11,186 Points
Staff
2015-01-13 9:51 pm EST
Like this Question?

Support Center Login

Our Login page has moved, Click the button below to be taken to the login page.

Need more Help?

Search

Ask the Community!

Get help with your questions from our community of like-minded hosting users and InMotion Hosting Staff.

Current Customers

Chat: Click to Chat Now E-mail: support@InMotionHosting.com
Call: 888-321-HOST (4678) Ticket: Submit a Support Ticket

Not a Customer?

Get web hosting from a company that is here to help. Sign up today!