setting ip address of database on different server

Question

I deployed a sample Rails app to a remote server with postgres hosted on the same server. The database.yml file was like this.

production:
  adapter: postgresql
  encoding: unicode
  database: remotepg_production
  pool: 5
  host: localhost
  username: mrmann
  password: secret

Everything worked fine. I then went into the database.yml file and replaced host: localhost with the ip address of a postgres database on another server host: 178.XXX.XXX like this

production:
  adapter: postgresql
  encoding: unicode
  database: remotepg_production
  pool: 5
  host: 178.XXX.XXX.XXX        #ip address of server with other postgres database
  username: mrmann
  password: secret

When I restarted postgres on the server with the Rails app, the sample application now gave me the 'something went wrong' page for Rails.

The username and the password for the username are the same on both dbs. Can you suggest what the problem might be? Thanks

Update

These are the settings in pg_hba.conf on the server with the database that I want to connect to.

local   all             postgres                                peer
local   all             all                                     peer
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

Fake Rails app IP: 192.241.XXX.X

Fake Database IP: 192.34.XX.XXX

bma · Accepted Answer · 2013-07-25 19:47:16Z

1

Do you have access to the database logs? If your connection got as far as attempting to connect to the database, there should be some messages there. My WAG is that you need to add that IP to server's pg_hba.conf file and reload Postgres. To be more clear, the IP to add to the server's pg_hba.conf is the IP that your application is connecting from, and you should also check the value of the database's listen_addresses setting. If the latter is not set to '*' or to the IP of your application, you'll need to change it in the postgresql.conf and restart the db cluster.

[edit, new information provided]

The line in your pg_hba.conf should be

host  all  all 192.241.XXX.X  md5

Reload (not restart) postgres (eg. pg_ctl reload) and then try connecting again. If it fails, find the db log and see what it shows. If there is no message, then it might be a firewall problem. If the latter is suspected, look for any rules relating to port 5432 in the output of iptables -nvL

edited Jul 25, 2013 at 19:47

answered Jul 25, 2013 at 16:19

bma

9,8562 gold badges35 silver badges22 bronze badges

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

8 Comments

Leahcim Over a year ago

I should have access to the database logs but I can't find where they are (can you help?). I'm assuming from your instructions that I need to add the IP of the database to the pg_hba.conf of the server hosting the rails app, or did you mean to add the IP of the server hosting the rails app to the pg_hba.conf of the server with the extra database?

bma Over a year ago

The log locations I set up are always at: select current_setting('data_directory')||'/'||current_setting('log_directory');, maybe that path will lead you to your logs.

Leahcim Over a year ago

In /etc/postgresql/9.1/main/postgresql.conf, I just set the ip address of the server with the rails app as the listen_address. That's done now. However, when I tried to restart postgres it said

2013-07-25 17:10:35 UTC LOG:  could not bind IPv4 socket: Cannot assign requested address 2013-07-25 17:10:35 UTC HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry. 2013-07-25 17:10:35 UTC WARNING:  could not create listen socket for "178.XXX.XXX.X" 2013-07-25 17:10:35 UTC FATAL:  could not create any TCP/IP sockets

Leahcim Over a year ago

In the pg_hba.conf file of the postgres database that I want to connect to, I also added host remotepg_production all 178.XXX.XXX.X ident to let the 'all' users from the server at that ip address connect to the remotepg_production but got the same error as last comment when trying to restart postgres

bma Over a year ago

@Leahcim Try with listen_addresses = '*' and restart the db. Do you get the same errors?

|

Collectives™ on Stack Overflow

setting ip address of database on different server

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related