Establish remote connection to PostGreSQL database

Avatar
  • Answered
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: psql DBNAME USERNAME" 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: psql -h -p -U -d 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?
Avatar
JeffMa
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