PostgreSQL psql: could not connect to server: Connection refused-9786716

skypaladin

　　转自 http://www.cyberciti.biz/faq/postgresql-remote-access-or-connection/
　　Q. When I try to connect remote PostgreSQL, I am reciving an error which read as follows:
　　psql: could not connect to server: Connection refused
　　Is the server running on host host.domain.com and accepting
　　TCP/IP connections on port 5432?
　　How do I fix this problem? I am using CentOS 4.x version.
　　A. First make sure PostgreSQL server has been started to remote server.
　　# /etc/init.d/postgresql start
　　If it is running and you get above error, you need to add enable TCP/IP support. By default, the PostgreSQL server only allows connections to the database from the local machine or localhost. This is a security feature.
Step # 1: Allow remote IP address to access PostgreSQL
　　You need to open file called /var/lib/pgsql/data/pg_hba.conf. Login as postgres user using su command:
　　$ su - postgres
　　$ vi /var/lib/pgsql/data/pg_hba.conf
　　Now append following line. Let us say you would like to give access to 192.168.1.0/24 network:
　　host all all 192.168.1.0 255.255.255.0 trust
　　Please replace 192.168.1.0 and 255.255.255.0 to reflect the actual network IP address range of the clients system in your own network.
　　Save close the file.
Step # 2: Allow communication over TCP/IP
　　You need to open PostgreSQL configuration file /var/lib/pgsql/data/postgresql.conf
　　$ vi /var/lib/pgsql/data/postgresql.conf
　　Now bind and open TCP/IP port by setting tcpip_socket to true:
　　tcpip_socket = true
　　Save and close the file.
Step # 3: Restart PostgreSQL server
　　Restart the PostgreSQL server with the following command
　　# /etc/init.d/postgresql restart
　　This will open default port 5432.
Step # 4: Test your setup
　　Use psql command from client system as follows:
　　psql -h PostgreSQL-IP-ADDRESS -U USERNAME -d DATABASENAME
　　Connect to remote server by IP address 192.168.1.5 and login using vivek user to connect to sales database, use:
　　$ psql -h 192.168.1.5 -U vivek -d sales
　　Where,

• 　　-h 192.168.1.5 : Specifies the host name of the machine or IP address (192.168.1.5) on which the server is running.
  
• 　　-U vivek : Connect to the database as the vivek username instead of the default. You must have account and permission to connect as vivek user.
  
• 　　-d sales : Specifies the name of the database (sales) to connect to.
  

See also:
　　=> How do I add a user to PostgreSQL database server?

• 　　Kamchybek Jusupov March 23, 2007, 10:48 am
  　　$ su – postgres
  　　$ vi /var/lib/pgsql/data/pg_hba.conf
  　　You can also use 192.168.1.31/24 instead of 192.168.1.31 255.255.255.0 (CIDR-address), kinda config file will look “cleaner”, and I would use “md5” instead of “trust” for further peace-of-mind :)
  　　REPLY LINK
  
• 　　Rajeev January 9, 2008, 8:14 am
  　　Thanks for the fantastic FAQ. Just what I needed as a newbie with pgsql.
  　　Just wanted to mention here that was trying to connect a remote pgsql db on a Suse Linux 9. It did not have the tcpip_socket parameter in postgresql.conf
  　　To overcome this, I had to uncomment the following settings:
  　　#—————————————————————————
  　　# CONNECTIONS AND AUTHENTICATION
  　　#—————————————————————————
  　　# – Connection Settings –
  　　listen_addresses = ‘*’
  　　port = 5432
  　　Bounced the db and voila, I was able to connect and to run queries.
  　　Hope this helps.
  　　Thanks
  　　Rajeev
  　　REPLY LINK
  
• 　　Rajeev January 9, 2008, 8:16 am
  　　Forgot to mention the version of my postgresql db. It’s 8.1.4
  　　REPLY LINK
  
• 　　deven January 31, 2008, 5:51 am
  　　Hi,
  　　this guide was a good help for me.
  　　only thing as rajeev said their is no parameter with the name tcpip_socket in Ubuntu also.
  　　so used following things in the postgresql.conf config file
  　　listen_addresses = ‘*’
  　　port = 5432
  　　REPLY LINK
  
• 　　quocvietit August 22, 2008, 3:34 pm
  　　please help me.i try to connect to Postgresql sever by LAN
  　　i already configured following step but not success
  　　here my config on flatform Window
  　　pg_hba.conf:
  　　host all all 192.168.1.37/32 md5
  　　postgresql.conf:
  　　i try all
  　　#listen_addresses = ‘*’
  　　port = 5432
  　　or
  　　tcpip_socket = true
  　　it’s still not working
  　　thanks anyway!
  　　REPLY LINK
  
• 　　GILBERT October 23, 2009, 1:53 pm
  　　I Remote PostgreSQL Access On the web
  　　how can i Connect to my postgresql database on my server remotely through the web/internet and manage it directly from your PC?and even connect some application so i run transaction?
  　　REPLY LINK
  
• 　　Scotos February 4, 2010, 8:24 am
  　　I have the same problem as quocvietit. I’m also in Windows.
  　　REPLY LINK
  
• 　　Raveendar March 1, 2010, 5:10 am
  　　Can anybody help me with this…
  　　I’ve postgre db on redhat linux. It’s been configured to a single network earlier viz., 192.168.0.1 but now I want to access the same db from different vlans(192.168.0.1 , 192.168.1.1……to 192.168.20.1). there are about 20 machines on each network. I’m able to access from the client machines which are on 192.168.0.1… network but where as when I try to access from others it says “I don’t permissions to access the db”. Pls can anybody help in this regard…
  　　REPLY LINK
  
• 　　Raveendar March 1, 2010, 5:15 am
  　　I opened this file but
  　　$ vi /var/lib/pgsql/data/pg_hba.conf but couldnot find
  　　host all… instead if find
  　　host = ” ”
  　　how can I solve this
  　　REPLY LINK
  
• 　　martin February 3, 2011, 6:35 am
  　　user account postgres does exist but i can’t login to it hence postgres service does not start.how do i go about this?
  　　REPLY LINK
  
• 　　koko100mohaed April 19, 2011, 10:10 am
  　　hiii mohad mekhelfi ana algerie b/amer (algerie )
  　　REPLY LINK
  
• 　　Black Teppi July 19, 2011, 4:06 am
  　　I have same problem.
  　　I use Windows and PostgreSql 9.0.4
  　　anybody help?
  　　So thanks!
  　　REPLY LINK
  
  
  
  • 　　ErpBeginner August 1, 2011, 12:43 am
    　　Hi everyone , i’m havin’ same problem , Postgres 8.4 server does start , but when i try to run OpenErp Client , an error appear in the db field : Server couldnt start !! Could Anybody help me ppl ….Configuration : win7 32bits ,
    　　REPLY LINK
    
  
  
• 　　Pomodoro August 3, 2011, 10:17 am
  　　And how about this error in postgres 9.0?
  　　FATAL: unrecognized configuration parameter “tcpip_socket”
  　　REPLY LINK
  
• 　　biju August 23, 2012, 6:25 am
  　　while installing postgres sql 8.0 in windows an error occurs ‘port number already in use please specify another ‘ how can i solve this
  　　REPLY LINK
  
• 　　paulo roberto October 8, 2012, 6:58 pm
  　　meu banco de dados postgresql no esta abrindo ele manda msg dizendo que no consegue se comunicar com o servidor ,estou usando o servidor virtual apache ,o que fao para no perder os meus dados ?, já até reinstalei o apache mais no resolve,o sistema é windows 7
  　　REPLY LINK
  
• 　　Puneet Pandey August 14, 2013, 9:11 am
  　　What a wonderful post and the tremendous response from Rajeev. It took me hardly 3 mins to configure this and access PG remotely.