7

I have a postgresql server inside a virtualbox created by vagrant.

I have also set up a portforwarding from 5432 inside the box to 15432 on the host system via the Vagrant file.

When connecting via psql

$ psql dbname username -h 127.0.0.1 -p 15432

psql: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.

Both, server and client are running Ubuntu 12.04 (postgresql-9.1, Version: 9.1+129ubuntu1)

Connecting inside the VM it self to port 5432 works fine.

The port forwarding it self does not seem to go completely wrong, because when I try to another port, I get "connection refused")

Improve this question
2
  • 1
    what does 'ifconfig -a' on the host return? you should have a second (virtual) network adapter for the VBox NAT like '192.168.56.x'. if you replace 127.0.0.1 in your command with the VBox NAT IP address, does it work correctly? when connecting to a vbox nat, its just like connecting to a publicly forwarded port on a gateway router. hosts outside the NAT network use the public IP to connect to it.
    – Frank Thomas
    Dec 6, 2012 at 15:37
  • 1
    @FrankThomas I have only the normal eth0, lo, wlan0 interfaces. Connecting to SSH and HTTP via forwarded ports and 127.0.0.1 works fine.
    – Alex
    Dec 6, 2012 at 16:46

2 Answers 2

Reset to default
7

Did you configure Postgres to listen on the public interface? By default, it listens only on the loopback adapter

If you're using the Postgres cookbook, you need to set the attribute:

set['postgresql']['config']['listen_addresses'] = '*'

This corresponds to the listen_addresses parameter in postgresql.conf

And likely open up pg_hba to the network that Postgres thinks your connection is coming in on:

Again, the Chef attribute:

set['postgresql']['pg_hba'] = [
    {:type => 'local', :db => 'all', :user => 'all', :addr => nil, :method => 'trust'},
    {:type => 'host', :db => 'all', :user => 'all', :addr => '127.0.0.1/32', :method => 'trust'},
    {:type => 'host', :db => 'all', :user => 'all', :addr => '10.0.0.0/16', :method => 'trust'}
]
Improve this answer
5
  • great help, especialy the chef bits.
    – JL Peyret
    Apr 5, 2016 at 5:27
  • You can tighten this up a bit by being more specific, on the basis of your vm's host ip (in vagrant terms) which you get by node[:network][:default_gateway] in my case, it's 10.0.2.15, which I also see in ifconfig. then postgres.conf gets listen_addresses = 'localhost,10.0.2.15'.
    – JL Peyret
    Apr 5, 2016 at 5:36
  • and pg_hba.conf gets host all myuser 10.0.2.15/0 trust
    – JL Peyret
    Apr 5, 2016 at 5:37
  • Tried both, the problem still persists.
    – art-solopov
    Aug 2, 2017 at 9:43
  • Did you disable your firewall?
    – Bobby Durrett
    Feb 26, 2018 at 23:56
2

Use SSH tunneling:

Run this command on the host to map port 5454 on the host to port 5432 (PostgreSQL's default port) on the VirtualBox guest machine. This example assumes you've already set up port forwarding to map the host's port 2222 to the guest's SSH default port (port 22).

ssh -p 2222 -f -L 5454:127.0.0.1:5432 dev@localhost -N
  • Host port: 5454
  • Host address:127.0.0.1
  • Guest port:5432
  • Guest account: dev
  • Guest server: localhost

After you’ve run this, you should be able to set up a connection to the guest’s postgresql server using ‘localhost’ and port 5454. For example,

psql -h localhost -p 5454 -U postgres
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .