How to connect postgres databases using ssh tunnel in ruby

Question

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

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
– Cache Staheli, Commented Jun 21, 2016 at 17:09
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
– Justin Wood, Commented Jun 21, 2016 at 17:33
Why not just create an appropriate database.yml entry and use that connection in your model? — jamesc
– jamesc, Commented Jun 21, 2016 at 18:56
@CacheStaheli Thanks for reminder. I will do that in future posting. — Shark
– Shark, Commented Jun 22, 2016 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
– Shark, Commented Jun 22, 2016 at 16:29

Community · Accepted Answer · 2017-05-23 12:22:40Z

1

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.

edited May 23, 2017 at 12:22

CommunityBot

11 silver badge

answered Jun 21, 2016 at 19:04

jamesc

12.9k15 gold badges80 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Shark Over a year ago

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!

jamesc Over a year ago

@Shark also be sure to check your firewall for port access

Shark Over a year ago

@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.

jamesc Over a year ago

@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

Shark Over a year ago

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

|

Collectives™ on Stack Overflow

How to connect postgres databases using ssh tunnel in ruby

1 Answer 1

11 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related