转自 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/postgresqlstart

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:

hostallall192.168.1.0255.255.255.0trust

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/postgresqlrestart

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-h192.168.1.5-Uvivek-dsales

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 haveaccount and permissionto connect as vivek user.

• -d sales: Specifies the name of the database (sales) to connect to.

Kamchybek JusupovMarch 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 :)

  REPLYLINK

• RajeevJanuary 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:

#―――――――――――――――――――――――――
#CONNECTIONSANDAUTHENTICATION
#―――――――――――――――――――――――――
#�ConnectionSettings�
listen_addresses=‘*’
port=5432
Bouncedthedbandvoila,Iwasabletoconnectandtorunqueries.
Hopethishelps.
Thanks
Rajeev

• RajeevForgot to mention the version of my postgresql db. It’s 8.1.4

  REPLYLINK

• devenJanuary 31,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