PostgreSQL安装后Navicat客户端连接报错：Could not connect to server:Connection refused(0x00002740/10061
PostgreSQL安装后Navicat客户端连接报错：Could not connect to server:Connection refused(0x00002740/10061)
经常使用PostgreSQL数据库的用户，在用Navicat客户端连接服务器端时，报错：
Could not connect to server：Connection refused(0x00002740/10061)
Is the server running on host “xxx.xxx.xx.xx” and accepting
TCP/IP connetions on port 5432?
此问题产生的原因有：
1.host“xxx.xxx.xxx.xxx”不存在，此时客户端ping此IP应不是不通的
2.Host“xxx.xxx.xxx.xxx”存在，客户端ping此Ip地址能ping通，但是此主机并未安装PostgreSQL数据库
以上两个原因好理解，下面还有几个原因，后台查看ps -ef|grep postmaster如下：
|
1
2
3
|
[root@CM-126 pgsql_9.2.2]# ps -ef|grep postmaster root 793 32595 0 14:30 pts/0 00:00:00 grep postmaster postgres 32762 1 0 13:50 ? 00:00:00 /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data |
3.host“xxx.xxx.xxx.xxx”存在，客户端ping此Ip地址能ping通，此主机上也安装了PostgreSQL数据库，监听端口是5432，但客户端填写时而不是5432，如果5432也不正确，就确认下数据库监听的端口，后台用“netstat -anp|grep postmaster”命令查看下
|
1
2
3
4
5
|
[root@localhost pgsql]# netstat -anp|grep postmaster tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 14430/postmaster tcp 0 0 :::5432 :::* LISTEN 14430/postmaster udp 0 0 127.0.0.1:1316 127.0.0.1:1316 ESTABLISHED 14430/postmaster unix 2 [ ACC ] STREAM LISTENING 14603833 14430/postmaster /tmp/.s.PGSQL.5432 |
4.host“xxx.xxx.xxx.xxx”存在，能被客户端ping通，PostgreSQL数据库在主机上已安装，但$PGDATA/data/目录下的配置文件postgresql.conf中关于监听地址配置为：
|
1
|
listen_addresses = 'localhost' |
或者
|
1
|
#listen_addresses = '*' |
如果是此种情况，有两种方法可以解决此问题。
第一种：修改配置文件postgresql.conf
此时只需将监听地址改为：
|
1
|
listen_addresses = '*' |
然后，重启(/etc/rc.d/init.d/postgresql restart)PostgreSQL即可
第二种：配置文件postgresql.conf不做修改
保持postgresql.conf不变，转而修改PostgreSQL的启动脚本postgresql:
原来启动时命令行为
|
1
|
su - $PGUSER -c "$DAEMON -D '$PGDATA' &" >>$PGLOG 2>&1 |
现改为
|
1
|
su - $PGUSER -c "$DAEMON -i -D '$PGDATA' &" >>$PGLOG 2>&1 |
启动后一般为
|
1
|
/usr/local/pgsql/bin/postmaster -i -D /usr/local/pgsql/data |
启动时加上-i选项，意思就是允许TCP/IP连接
|
1
2
|
[root@localhost pgsql]# /usr/local/pgsql/bin/postmaster --help|grep TCP/IP -i enable TCP/IP connections |
5、防火墙开启也会造成此种现象
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
[root@cloudera135 ~]# iptables -LChain INPUT (policy ACCEPT)target prot opt source destination ACCEPT udp -- anywhere anywhere udp dpt:domainACCEPT tcp -- anywhere anywhere tcp dpt:domainACCEPT udp -- anywhere anywhere udp dpt:bootpsACCEPT tcp -- anywhere anywhere tcp dpt:bootpsACCEPT all -- anywhere anywhere state RELATED,ESTABLISHEDACCEPT icmp -- anywhere anywhere ACCEPT all -- anywhere anywhere ACCEPT tcp -- anywhere anywhere state NEW tcp dpt:sshREJECT all -- anywhere anywhere reject-with icmp-host-prohibitedChain FORWARD (policy ACCEPT)target prot opt source destination ACCEPT all -- anywhere 192.168.122.0/24 state RELATED,ESTABLISHEDACCEPT all -- 192.168.122.0/24 anywhere ACCEPT all -- anywhere anywhere REJECT all -- anywhere anywhere reject-with icmp-port-unreachableREJECT all -- anywhere anywhere reject-with icmp-port-unreachableREJECT all -- anywhere anywhere reject-with icmp-host-prohibitedChain OUTPUT (policy ACCEPT)target prot opt source destination |
此种情况若解决，只需关闭防火墙即可
|
1
2
3
4
|
[root@cloudera135 ~]# service iptables stopiptables：清除防火墙规则： [确定]iptables：将链设置为政策 ACCEPT：nat mangle filter [确定]iptables：正在卸载模块： [确定] |
若不想关闭防火墙，也可以改变防火墙规则以解决此问题。
注意：3、4两种情况下，$PGDATA/pg_hba.conf文件中的配置为
|
1
2
|
# TYPE DATABASE USER ADDRESS METHOD host all all 0.0.0.0/0 trust |
