3

Here is my problem, I want to access our database through ruby script, and I can connect to database through PSequal using ssh tunnel. When I tried to connect to database from ruby, it always return me time out error.

The Error MESSAGE IS LIKE THIS :"could not connect to server: Operation timed out Is the server running on host "HOSTNAME" (IP ADDRESS) and accepting TCP/IP connections on port 5432"

I have tried to use "psql -h -d -U" in terminal to login, but I got the same answer. Thanks

def connectDb
  begin
    file = File.open("pem file")
    gateway = Net::SSH::Gateway.new('hostname', 'username', keys_only: true, port: 22, key_data: file)
    port_pg = gateway.open('hostname', 5432)
    puts port_pg
    con = PG::Connection.open('hostname', portpg, "", "", 'dbname', 'username', 'password')
  rescue PG::Error => e
    puts e.message
  end
end
Shark
  • 43
  • 5
  • 1
    Please don't put code in images. It makes it harder to answer your question. Instead, put the code inside your post in text form so that it can easily be seen by answerers. – Cache Staheli Jun 21 '16 at 17:09
  • 1
    Is there a reason you want to connect via SSH instead of just using a database driver to connect to the database directly? – Justin Wood Jun 21 '16 at 17:33
  • Why not just create an appropriate database.yml entry and use that connection in your model? – jamesc Jun 21 '16 at 18:56
  • @CacheStaheli Thanks for reminder. I will do that in future posting. – Shark Jun 22 '16 at 16:25
  • @JustinWood I am using pg driver to connect to the database, but it always return me time out error. I tried pg client version like Postico, it required ssh information before connect, which makes me think that I might need ssh to connect to database – Shark Jun 22 '16 at 16:29
  • If you are getting a time out error then you should have mentioned that in your question. You will have to ensure Postgres server is configured to listen on the correct port and to punch a hole in your ufw firewall for the correct port – jamesc Jun 22 '16 at 16:51
  • See my updated answer. – jamesc Jun 22 '16 at 17:01

1 Answers1

2

Make sure your Postgres server is listening on the correct port In postgresql.conf (Probably located somewhere like /etc/postgresql/9.3/main/postgresql.conf comment out

#listen_addresses = 'localhost'

and add a listen on all ports

listen_addresses = '*'

The Postgres config files will likely be owned by Postgres which can make them more difficult to find.

Log into your postgres database and run

SHOW config_file;

This should give you the location for you to then be able to edit the file

Have a look at this StackOverflow thread for further info on this After editing the file ( you will probably need sudo privileges to do this i.e. sudo vim path_to/config_file ) you will need to restart postgres for the changes to take effect. How you restart will depend on how pg is set up but most likely it is runing as a service so tyhe following should do the trick.

sudo service postgres restart

If not then ask your host how to restart

Create an entry in your database.yml file for the remote server e.g.

remote:
  adapter: postgresql
  database: remote_db_name
  username: xxx
  password: xxx
  pool: 5
  timeout: 5000
  host: ip_address_for_remote_server
  port: prob_3306_but_whatever_port_you_have_configured_on_remote_server
  strict: false

Then create a specific set of models to deal with the remote database and establish a connection with the database.yml entry

e.g.

class SomeTableNameOnRemoteServer < ActiveRecord::Base
    establish_connection :remote
    #etc...

Don't be tempted to try to use models that already connect to your local database. If you want to share logic then create a module and just include the module in both model classes

Some further reading might help. This is a guide on how to setup and configure pg on a DigitalOcean droplet

https://www.digitalocean.com/community/tutorials/scaling-ruby-on-rails-setting-up-a-dedicated-postgresql-server-part-3

some way down the page there is a section on remote access but you may find the whole document useful so not pasting content in here.

Community
  • 1
  • 1
jamesc
  • 12,423
  • 15
  • 74
  • 113
  • Thank you for your answer and patient. I have modified my question. I only find psql in /usr/local/bin/ path, and I did not find anything related to psql under /etc path. Thanks! – Shark Jun 22 '16 at 18:23
  • @Shark also be sure to check your firewall for port access – jamesc Jun 22 '16 at 19:43
  • @jameswThanks for your updating and time. I really appreciate your help. When I tried to log in to the database server from terminal, I got the same error message :"could not connect to server: Operation timed out Is the server running on host "..." (IP address) and accepting TCP/IP connections on port 5432". I have updated my question. – Shark Jun 22 '16 at 19:55
  • @Shark, that'll most likely be because your pg server is not running. start it up and check the output, does it fail? does it start? If running, check the port it's running on – jamesc Jun 22 '16 at 21:29
  • The pg server that I am trying to connect to is running since I could connect to it through client version either Postico or PSequal by using same hostname and user and password. But in client version, there is one extra area I need to fill is SSH Tunneling, I filled the jumpbox as ssh host, and upload the pem file in identity file, so I could connect to pg server successfully through client version. However,when I tried to connect to pg server through terminal, I got this error. Thanks again for your patience and time. @jamesw – Shark Jun 22 '16 at 23:15
  • @Shark If you ssh in to the server that is running the pg server and run the following command `ps -ef | grep postgres` what output do you get? – jamesc Jun 23 '16 at 00:32
  • Thanks. I figured out why. Because the server problem, I can not login . – Shark Jun 23 '16 at 20:35
  • @Shark Have you sorted the problem? – jamesc Jun 24 '16 at 00:30
  • No...I don't get it, what's the meaning of sorted the problem? Do you mean vote for solution? I have tried too, but the score did not change due to my low reputation. – Shark Jun 24 '16 at 18:53
  • @Shark Have you resolved your issue? Is everything working now? do you still need help? – jamesc Jun 24 '16 at 23:16
  • Thank you for your patient and help. I have resolved my issue, actually it's server's issue, not mine. Thanks again for your help. – Shark Jun 28 '16 at 17:59