You have to edit postgresql.conf file and change the listen_addresses line.
This file you can find in the /etc/postgresql/9.3/main directory.
Default Ubuntu config have allowed only localhost (or 127.0.0.1) interface, which is sufficient for using, when every PostgreSQL client work on the same computer, as PostgreSQL server. If you want connect PostgreSQL server from other computers, you have change this config line in this way:
listen_addresses = '*'
Then you have edit pg_hba.conf file, too. In this file you have set, from which computers you can connect to this server and what method of authentication you can use. Usually you will need similar line:
host all all 192.168.1.0/24 md5
Please, read comments in this file...
EDIT:
After the editing postgresql.conf and pg_hba.conf you have to restart postgresql server: sudo service postgresql restart
EDIT2: Highlited configuration files.
Answer from Jan Marek on Stack OverflowYou have to edit postgresql.conf file and change the listen_addresses line.
This file you can find in the /etc/postgresql/9.3/main directory.
Default Ubuntu config have allowed only localhost (or 127.0.0.1) interface, which is sufficient for using, when every PostgreSQL client work on the same computer, as PostgreSQL server. If you want connect PostgreSQL server from other computers, you have change this config line in this way:
listen_addresses = '*'
Then you have edit pg_hba.conf file, too. In this file you have set, from which computers you can connect to this server and what method of authentication you can use. Usually you will need similar line:
host all all 192.168.1.0/24 md5
Please, read comments in this file...
EDIT:
After the editing postgresql.conf and pg_hba.conf you have to restart postgresql server: sudo service postgresql restart
EDIT2: Highlited configuration files.
Uncomment the listen_addresses = '*' in the postgresql.conf
This has bitten me a second time so I thought might be worth mentioning. The line listen_addresses = '*' in the postgresql.conf is by default commented. Be sure to uncomment (remove the pound sign, # at the beginning) it after updating otherwise, remote connections will continue to be blocked.
PS: psql -U postgres -c 'SHOW config_file' - to locate the postgresql.conf file path
This issue comes from installing the postgres package without a version number. Although postgres will be installed and it will be the correct version, the script to setup the cluster will not run correctly; it's a packaging issue.
If you're comfortable with postgres there is a script you can run to create this cluster and get postgres running. However, there's an easier way.
First purge the old postgres install, which will remove everything of the old installation, including databases, so back up your databases first.. The issue currently lies with 9.1 so I will assume that's what you have installed
sudo apt-get remove --purge postgresql-9.1
Now simply reinstall
sudo apt-get install postgresql-9.1
Note the package name with the version number. HTH.
The error message refers to a Unix-domain socket, so you need to tweak your netstat invocation to not exclude them. So try it without the option -t:
netstat -nlp | grep 5432
I would guess that the server is actually listening on the socket /tmp/.s.PGSQL.5432 rather than the /var/run/postgresql/.s.PGSQL.5432 that your client is attempting to connect to. This is a typical problem when using hand-compiled or third-party PostgreSQL packages on Debian or Ubuntu, because the source default for the Unix-domain socket directory is /tmp but the Debian packaging changes it to /var/run/postgresql.
Possible workarounds:
- Use the clients supplied by your third-party package (call
/opt/djangostack-1.3-0/postgresql/bin/psql). Possibly uninstall the Ubuntu-supplied packages altogether (might be difficult because of other reverse dependencies). - Fix the socket directory of the third-party package to be compatible with Debian/Ubuntu.
- Use
-H localhostto connect via TCP/IP instead. - Use
-h /tmpor equivalentPGHOSTsetting to point to the right directory. - Don't use third-party packages.
As your netstat output indicates, it's listening at 127.0.0.1:5432 which is localhost. That is only connectable from localhost ;)
Set listen_addresses='*' in your config and it will work.
[edit] Other things to check:
- is the amazon firewall blocking anything?
- is iptables blocking anything?
But first make sure the listening address is correct, your netstat output shows that it won't work like this.
listen_addresses='localhost, private_ip' fixed the issue. I was not able to start postmaster server on elastic IPs. Once postgres server started o localhost and private IPs, I was able to connect.