- 客户端连接到服务器端
- postgresql 中常用连接/安全认证参数涉及配置文件
- postgresql 客户端认证涉及的配置文件
- 连接后端口情况
- 常见连接错误问题
- VirtualBox虚拟机之间连接问题的解决
- UNIX域套接字 Tips
- 参考
Postgres 12 Connect Study 003 Tips
客户端连接到服务器端
成功示例:
从centos6客户端连接到centos7的postgres
[postgres@centos6pg ~]$ psql -h 192.168.50.96 -p 5432 -U lyn -d postgres
Password for user lyn:
psql (8.4.18, server 10.11)
WARNING: psql version 8.4, server version 10.11.
Some psql features might not work.
Type "help" for help.
postgres=> select inet_server_addr(),inet_server_port(),current_database(),current_user;
inet_server_addr | inet_server_port | current_database | current_user
------------------+------------------+------------------+--------------
192.168.50.96 | 5432 | postgres | lyn
(1 row)
postgres=> select version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 10.11 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
(1 row)
postgres=>
连接后的日志记录:
Connection matched pg_hba.conf line 96: "host all all 0.0.0.0/0 md5"
postgresql 中常用连接/安全认证参数涉及配置文件
postgresql中常用连接/安全认证参数涉及配置文件: $PGDATA/postgresql.conf
-
listen_addresses 数据库监听端口地址,默认是
localhost
(只容许本地登录),也就是默认禁止远程登陆 修改为本host的网络地址的话,可以容许其他节点远程访问 多个地址可以使用逗号隔开*
或者0.0.0.0
表示在本host上所有地址监听 -
port 数据库监听端口,默认是
5432
注意不要和其他服务或者多个实例使用同一个端口
修改这两个参数都需要重启实例
[postgres@centos7_pg ~]$ ls -tlr $PGDATA/postgresql.conf
-rw------- 1 postgres postgres 23173 Apr 6 11:09 /data/pgsql10/pgdata/postgresql.conf
[postgres@centos7_pg ~]$ cat /data/pgsql10/pgdata/postgresql.conf
~
listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
port = 5432 # (change requires restart)
~
centos7_pg 是本host的名字通过/etc/hosts
查看
[postgres@centos7_pg pgdata]$ cat /data/pgsql10/pgdata/postgresql.conf | grep listen | grep -v "^#"
listen_addresses = 'centos7_pg' # what IP address(es) to listen on;
[postgres@centos7_pg pgdata]$
[postgres@centos7_pg pgdata]$ cat /etc/hosts | grep centos7_pg
192.168.50.96 centos7_pg localhost localhost.localdomain localhost4 localhost4.localdomain4
[postgres@centos7_pg pgdata]$
- max_connections
最大并发连接数(可以支持同时连接的最大数目)
默认是
100
[postgres@centos7_pg pgdata]$ cat postgresql.conf | grep max_connection
max_connections = 100 # (change requires restart)
[postgres@centos7_pg pgdata]$
- superuser_reserved_connections 超级用户保留的连接数(不是超级用户的最大并发连接数) 默认是3 需要注意: 普通用户(不是超级用户)的最大并发连接数: max_connections - superuser_reserved_connections
[postgres@centos7_pg pgdata]$ cat postgresql.conf | grep superuser
#superuser_reserved_connections = 3 # (change requires restart)
[postgres@centos7_pg pgdata]$
虽然默认数是 3 ,但并不是说超级用户同时连接数目不能超过3(只是为超级用户保留了3个连接) 如下示例通过超级用户postgresql连接超过了3个。
[postgres@centos7_pg pgdata]$ netstat -natp | grep 5432
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 0 0 192.168.50.96:5432 0.0.0.0:* LISTEN 4187/postgres
tcp 0 0 192.168.50.96:5432 192.168.50.98:39247 ESTABLISHED 4733/postgres: post
tcp 0 0 192.168.50.96:5432 192.168.50.98:39235 ESTABLISHED 4550/postgres: post
tcp 0 0 192.168.50.96:5432 192.168.50.98:39245 ESTABLISHED 4730/postgres: post
tcp 0 0 192.168.50.96:5432 192.168.50.98:39241 ESTABLISHED 4560/postgres: post
tcp 0 0 192.168.50.96:5432 192.168.50.98:39243 ESTABLISHED 4727/postgres: post
[postgres@centos7_pg pgdata]$
postgres=# show max_connections;
max_connections
-----------------
100
(1 row)
postgres=# show superuser_reserved_connections ;
superuser_reserved_connections
--------------------------------
3
(1 row)
postgres=#
postgres=# select datname,pid,application_name,state from pg_stat_activity;
datname | pid | application_name | state
----------+------+------------------+--------
| 4194 | |
| 4192 | |
postgres | 4560 | | active
postgres | 4550 | | idle
postgres | 4727 | | idle
postgres | 4730 | | idle
postgres | 4733 | | idle
| 4190 | |
| 4189 | |
| 4191 | |
(10 rows)
postgres=#
postgres=# select usename, count(*) from pg_stat_activity group by usename order by count(*) desc;
usename | count
----------+-------
postgres | 6
| 4
(2 rows)
postgres=#
- authentication_timeout 客户端连接服务器端认证的timeout时间,默认是60秒(1分钟) 通过此参数可以阻止一些客户端进行长时间认证占用数据库的连接数目
[postgres@centos7_pg pgdata]$ cat postgresql.conf | grep authentication_timeout
#authentication_timeout = 1min # 1s-600s
[postgres@centos7_pg pgdata]$
postgres@postgres=> show authentication_timeout;
authentication_timeout
------------------------
1min
(1 row)
postgres@postgres=>
-
ssl 数据库是否接受SSL连接
-
ssl_ciphers 指定使用SSL加密算法
[postgres@centos7_pg pgdata]$ cat postgresql.conf | grep ssl
#ssl = off
#ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers
#ssl_prefer_server_ciphers = on
#ssl_ecdh_curve = 'prime256v1'
#ssl_dh_params_file = ''
#ssl_cert_file = 'server.crt'
#ssl_key_file = 'server.key'
#ssl_ca_file = ''
#ssl_crl_file = ''
[postgres@centos7_pg pgdata]$
查看操作系统的加密算法列表
[postgres@centos6pg ~]$ which openssl
/usr/bin/openssl
[postgres@centos6pg ~]$ openssl ciphers -v
ECDHE-RSA-AES256-GCM-SHA384 TLSv1.2 Kx=ECDH Au=RSA Enc=AESGCM(256) Mac=AEAD
ECDHE-ECDSA-AES256-GCM-SHA384 TLSv1.2 Kx=ECDH Au=ECDSA Enc=AESGCM(256) Mac=AEAD
ECDHE-RSA-AES256-SHA384 TLSv1.2 Kx=ECDH Au=RSA Enc=AES(256) Mac=SHA384
ECDHE-ECDSA-AES256-SHA384 TLSv1.2 Kx=ECDH Au=ECDSA Enc=AES(256) Mac=SHA384
ECDHE-RSA-AES256-SHA SSLv3 Kx=ECDH Au=RSA Enc=AES(256) Mac=SHA1
ECDHE-ECDSA-AES256-SHA SSLv3 Kx=ECDH Au=ECDSA Enc=AES(256) Mac=SHA1
DHE-DSS-AES256-GCM-SHA384 TLSv1.2 Kx=DH Au=DSS Enc=AESGCM(256) Mac=AEAD
DHE-RSA-AES256-GCM-SHA384 TLSv1.2 Kx=DH Au=RSA Enc=AESGCM(256) Mac=AEAD
DHE-RSA-AES256-SHA256 TLSv1.2 Kx=DH Au=RSA Enc=AES(256) Mac=SHA256
DHE-DSS-AES256-SHA256 TLSv1.2 Kx=DH Au=DSS Enc=AES(256) Mac=SHA256
DHE-RSA-AES256-SHA SSLv3 Kx=DH Au=RSA Enc=AES(256) Mac=SHA1
DHE-DSS-AES256-SHA SSLv3 Kx=DH Au=DSS Enc=AES(256) Mac=SHA1
DHE-RSA-CAMELLIA256-SHA SSLv3 Kx=DH Au=RSA Enc=Camellia(256) Mac=SHA1
DHE-DSS-CAMELLIA256-SHA SSLv3 Kx=DH Au=DSS Enc=Camellia(256) Mac=SHA1
ECDH-RSA-AES256-GCM-SHA384 TLSv1.2 Kx=ECDH/RSA Au=ECDH Enc=AESGCM(256) Mac=AEAD
ECDH-ECDSA-AES256-GCM-SHA384 TLSv1.2 Kx=ECDH/ECDSA Au=ECDH Enc=AESGCM(256) Mac=AEAD
ECDH-RSA-AES256-SHA384 TLSv1.2 Kx=ECDH/RSA Au=ECDH Enc=AES(256) Mac=SHA384
ECDH-ECDSA-AES256-SHA384 TLSv1.2 Kx=ECDH/ECDSA Au=ECDH Enc=AES(256) Mac=SHA384
ECDH-RSA-AES256-SHA SSLv3 Kx=ECDH/RSA Au=ECDH Enc=AES(256) Mac=SHA1
ECDH-ECDSA-AES256-SHA SSLv3 Kx=ECDH/ECDSA Au=ECDH Enc=AES(256) Mac=SHA1
AES256-GCM-SHA384 TLSv1.2 Kx=RSA Au=RSA Enc=AESGCM(256) Mac=AEAD
AES256-SHA256 TLSv1.2 Kx=RSA Au=RSA Enc=AES(256) Mac=SHA256
AES256-SHA SSLv3 Kx=RSA Au=RSA Enc=AES(256) Mac=SHA1
CAMELLIA256-SHA SSLv3 Kx=RSA Au=RSA Enc=Camellia(256) Mac=SHA1
PSK-AES256-CBC-SHA SSLv3 Kx=PSK Au=PSK Enc=AES(256) Mac=SHA1
ECDHE-RSA-AES128-GCM-SHA256 TLSv1.2 Kx=ECDH Au=RSA Enc=AESGCM(128) Mac=AEAD
ECDHE-ECDSA-AES128-GCM-SHA256 TLSv1.2 Kx=ECDH Au=ECDSA Enc=AESGCM(128) Mac=AEAD
ECDHE-RSA-AES128-SHA256 TLSv1.2 Kx=ECDH Au=RSA Enc=AES(128) Mac=SHA256
ECDHE-ECDSA-AES128-SHA256 TLSv1.2 Kx=ECDH Au=ECDSA Enc=AES(128) Mac=SHA256
ECDHE-RSA-AES128-SHA SSLv3 Kx=ECDH Au=RSA Enc=AES(128) Mac=SHA1
ECDHE-ECDSA-AES128-SHA SSLv3 Kx=ECDH Au=ECDSA Enc=AES(128) Mac=SHA1
DHE-DSS-AES128-GCM-SHA256 TLSv1.2 Kx=DH Au=DSS Enc=AESGCM(128) Mac=AEAD
DHE-RSA-AES128-GCM-SHA256 TLSv1.2 Kx=DH Au=RSA Enc=AESGCM(128) Mac=AEAD
DHE-RSA-AES128-SHA256 TLSv1.2 Kx=DH Au=RSA Enc=AES(128) Mac=SHA256
DHE-DSS-AES128-SHA256 TLSv1.2 Kx=DH Au=DSS Enc=AES(128) Mac=SHA256
DHE-RSA-AES128-SHA SSLv3 Kx=DH Au=RSA Enc=AES(128) Mac=SHA1
DHE-DSS-AES128-SHA SSLv3 Kx=DH Au=DSS Enc=AES(128) Mac=SHA1
DHE-RSA-SEED-SHA SSLv3 Kx=DH Au=RSA Enc=SEED(128) Mac=SHA1
DHE-DSS-SEED-SHA SSLv3 Kx=DH Au=DSS Enc=SEED(128) Mac=SHA1
DHE-RSA-CAMELLIA128-SHA SSLv3 Kx=DH Au=RSA Enc=Camellia(128) Mac=SHA1
DHE-DSS-CAMELLIA128-SHA SSLv3 Kx=DH Au=DSS Enc=Camellia(128) Mac=SHA1
ECDH-RSA-AES128-GCM-SHA256 TLSv1.2 Kx=ECDH/RSA Au=ECDH Enc=AESGCM(128) Mac=AEAD
ECDH-ECDSA-AES128-GCM-SHA256 TLSv1.2 Kx=ECDH/ECDSA Au=ECDH Enc=AESGCM(128) Mac=AEAD
ECDH-RSA-AES128-SHA256 TLSv1.2 Kx=ECDH/RSA Au=ECDH Enc=AES(128) Mac=SHA256
ECDH-ECDSA-AES128-SHA256 TLSv1.2 Kx=ECDH/ECDSA Au=ECDH Enc=AES(128) Mac=SHA256
ECDH-RSA-AES128-SHA SSLv3 Kx=ECDH/RSA Au=ECDH Enc=AES(128) Mac=SHA1
ECDH-ECDSA-AES128-SHA SSLv3 Kx=ECDH/ECDSA Au=ECDH Enc=AES(128) Mac=SHA1
AES128-GCM-SHA256 TLSv1.2 Kx=RSA Au=RSA Enc=AESGCM(128) Mac=AEAD
AES128-SHA256 TLSv1.2 Kx=RSA Au=RSA Enc=AES(128) Mac=SHA256
AES128-SHA SSLv3 Kx=RSA Au=RSA Enc=AES(128) Mac=SHA1
SEED-SHA SSLv3 Kx=RSA Au=RSA Enc=SEED(128) Mac=SHA1
CAMELLIA128-SHA SSLv3 Kx=RSA Au=RSA Enc=Camellia(128) Mac=SHA1
PSK-AES128-CBC-SHA SSLv3 Kx=PSK Au=PSK Enc=AES(128) Mac=SHA1
ECDHE-RSA-DES-CBC3-SHA SSLv3 Kx=ECDH Au=RSA Enc=3DES(168) Mac=SHA1
ECDHE-ECDSA-DES-CBC3-SHA SSLv3 Kx=ECDH Au=ECDSA Enc=3DES(168) Mac=SHA1
EDH-RSA-DES-CBC3-SHA SSLv3 Kx=DH Au=RSA Enc=3DES(168) Mac=SHA1
EDH-DSS-DES-CBC3-SHA SSLv3 Kx=DH Au=DSS Enc=3DES(168) Mac=SHA1
ECDH-RSA-DES-CBC3-SHA SSLv3 Kx=ECDH/RSA Au=ECDH Enc=3DES(168) Mac=SHA1
ECDH-ECDSA-DES-CBC3-SHA SSLv3 Kx=ECDH/ECDSA Au=ECDH Enc=3DES(168) Mac=SHA1
DES-CBC3-SHA SSLv3 Kx=RSA Au=RSA Enc=3DES(168) Mac=SHA1
IDEA-CBC-SHA SSLv3 Kx=RSA Au=RSA Enc=IDEA(128) Mac=SHA1
PSK-3DES-EDE-CBC-SHA SSLv3 Kx=PSK Au=PSK Enc=3DES(168) Mac=SHA1
KRB5-IDEA-CBC-SHA SSLv3 Kx=KRB5 Au=KRB5 Enc=IDEA(128) Mac=SHA1
KRB5-DES-CBC3-SHA SSLv3 Kx=KRB5 Au=KRB5 Enc=3DES(168) Mac=SHA1
KRB5-IDEA-CBC-MD5 SSLv3 Kx=KRB5 Au=KRB5 Enc=IDEA(128) Mac=MD5
KRB5-DES-CBC3-MD5 SSLv3 Kx=KRB5 Au=KRB5 Enc=3DES(168) Mac=MD5
ECDHE-RSA-RC4-SHA SSLv3 Kx=ECDH Au=RSA Enc=RC4(128) Mac=SHA1
ECDHE-ECDSA-RC4-SHA SSLv3 Kx=ECDH Au=ECDSA Enc=RC4(128) Mac=SHA1
ECDH-RSA-RC4-SHA SSLv3 Kx=ECDH/RSA Au=ECDH Enc=RC4(128) Mac=SHA1
ECDH-ECDSA-RC4-SHA SSLv3 Kx=ECDH/ECDSA Au=ECDH Enc=RC4(128) Mac=SHA1
RC4-SHA SSLv3 Kx=RSA Au=RSA Enc=RC4(128) Mac=SHA1
RC4-MD5 SSLv3 Kx=RSA Au=RSA Enc=RC4(128) Mac=MD5
PSK-RC4-SHA SSLv3 Kx=PSK Au=PSK Enc=RC4(128) Mac=SHA1
KRB5-RC4-SHA SSLv3 Kx=KRB5 Au=KRB5 Enc=RC4(128) Mac=SHA1
KRB5-RC4-MD5 SSLv3 Kx=KRB5 Au=KRB5 Enc=RC4(128) Mac=MD5
[postgres@centos6pg ~]$
-
tcp_keepalives_idle 一个TCP连接空闲多长时间会发送一个keepalive消息 这个参数只有支持tcp_keepidle或者tcp_keepalive功能的系统(windows)才能使用,其他系统设置为
0
-
tcp_keepalives_interval 一个空闲TCP连接中,发送第一个keepalive包后,如果在tcp_keepalives_interval没收到应答包,则会发送第二个keepalive消息…直到 tcp_keepalives_count 次数之后都没收到,就关闭该连接 这个参数只有支持tcp_keepalive功能的系统(windows)才能使用,其他系统设置为
0
-
tcp_keepalives_count
postgresql 客户端认证涉及的配置文件
postgresql 客户端认证涉及的配置文件: $PGDATA/pg_hba.conf
pg_hba 中的 hba 是 Host-Base-Authentication 缩写,中文意思:基于主机认证。
通过此配置文件可以控制容许那些ip地址访问数据库服务器。
从上到下匹配,如果选择了一条记录而且认证失败,则不考虑后面的记录。如果没有匹配的。则会拒绝访问。
认证格式主要有下面几种认证语法:
# TYPE DATABASE USER ADDRESS METHOD
local DATABASE USER METHOD [OPTIONS]
host DATABASE USER ADDRESS METHOD [OPTIONS]
hostssl DATABASE USER ADDRESS METHOD [OPTIONS]
hostnossl DATABASE USER ADDRESS METHOD [OPTIONS]
host DATABASE USER IP-address IP-mask METHOD [OPTIONS]
hostssl DATABASE USER IP-address IP-mask METHOD [OPTIONS]
hostnossl DATABASE USER IP-address IP-mask METHOD [OPTIONS]
local: 本地使用Unix域套接字的连接认证(即本地psql连接没有指定host和端口时候的连接认证),没有这记录的话,不容许Unix域套接字连接 host/hostssl: 通过TCP/IP连接,有ssl的话,指使用ssl加密连接(ssl功能需要在postgresql编译时候需要指定打开ssl编译参数) database: 数据库名称。指定为all的话,表示全部数据库,而 replication 不是数据库名称,而是指容许流复制连接 user: 连接的用户名。指定为all的话,表示全部用户 address: 客户端服务器地址。主机名或者ip地址范围 method: 认证方式。 trust/reject/md5/ident等
- trust 配置客户端认证
示例: 本地host上的任何操作系统用户,通过数据库用户连接时候不需要使用密码:
local all all trust
- md5 配置客户端认证
示例: 其他主机连接可以使用md5密码认证
host all all 0.0.0.0/0 md5
认证列表解释: |方式|说明 —|:–| |trust|允许不提供密码认证 |reject|连接将被拒绝 |md5|连接通过密码,md5表示口令密码通过加密 |password|连接通过密码,但是以明文形式,所以不建议在不安全网络连接中使用 |ident|通过确认客户端上ident服务器获得客户端的操作系统用户,检查是否匹配数据库用户名 |peer|限于本地连接,加入用户lyn登录unix,这种模式下,只能以lyn登录连接postgres,而且lyn不需要密码连接,lyn用户通过其他用户名连接将连接不了
还有其他参考文档: 20.1. The pg_hba.conf File
连接后端口情况
端口情况 / 服务器端 - centos7_pg
[postgres@centos7_pg ~]$ netstat -ntla
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address Foreign Address State
tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN
tcp 0 0 10.0.3.15:25 0.0.0.0:* LISTEN
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN
tcp 0 0 10.0.3.15:22 10.0.3.2:51916 ESTABLISHED
tcp 0 0 10.0.3.15:22 10.0.3.2:51912 ESTABLISHED
tcp 0 0 10.0.3.15:22 10.0.3.2:52345 ESTABLISHED
tcp 0 0 10.0.3.15:22 10.0.3.2:52273 ESTABLISHED
tcp6 0 0 :::5432 :::* LISTEN
tcp6 0 0 ::1:25 :::* LISTEN
tcp6 0 0 :::22 :::* LISTEN
[postgres@centos7_pg ~]$ netstat -ntla ##--> 连接之后端口变化情况
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address Foreign Address State
tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN
tcp 0 0 10.0.3.15:25 0.0.0.0:* LISTEN
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN
tcp 0 0 10.0.3.15:22 10.0.3.2:51916 ESTABLISHED
tcp 0 0 192.168.50.96:5432 192.168.50.98:39228 ESTABLISHED ##--> 增加了一行 从192.168.50.98:39228过来的连接
tcp 0 0 10.0.3.15:22 10.0.3.2:51912 ESTABLISHED
tcp 0 0 10.0.3.15:22 10.0.3.2:52345 ESTABLISHED
tcp 0 0 10.0.3.15:22 10.0.3.2:52273 ESTABLISHED
tcp6 0 0 :::5432 :::* LISTEN
tcp6 0 0 ::1:25 :::* LISTEN
tcp6 0 0 :::22 :::* LISTEN
[postgres@centos7_pg ~]$
常见连接错误问题
- No route to host 问题,防火墙问题比较大(host互联问题):
[postgres@centos6pg ~]$ psql -U lyn -d postgres -h 192.168.50.96 -p 5432
psql: could not connect to server: No route to host
Is the server running on host "192.168.50.96" and accepting
TCP/IP connections on port 5432?
[postgres@centos6pg ~]$
通过telnet命令查看(通过ip地址和端口)
[root@centos6pg ~]# telnet 192.168.50.96 5432
Trying 192.168.50.96...
telnet: connect to address 192.168.50.96: No route to host
[root@centos6pg ~]#
正确结果示例
[postgres@centos6pg ~]$ telnet 192.168.50.96 5432
Trying 192.168.50.96...
Connected to 192.168.50.96.
Escape character is '^]'.
Connection closed by foreign host.
[postgres@centos6pg ~]$
- no pg_hba.conf entry for host 配置文件问题:
[postgres@centos6pg ~]$ psql -h 192.168.50.96 -p 5432 -U lyn -d postgres
psql: FATAL: no pg_hba.conf entry for host "192.168.50.98", user "lyn", database "postgres", SSL off
[postgres@centos6pg ~]$
在 pg_hba.conf 添加规则后重启postgres
host all all 0.0.0.0/0 md5
VirtualBox虚拟机之间连接问题的解决
- ping 的通,但端口连接有问题:No route to host
[root@centos6pg ~]# telnet 192.168.50.96 5432
Trying 192.168.50.96...
telnet: connect to address 192.168.50.96: No route to host
[root@centos6pg ~]# ping 192.168.50.96
PING 192.168.50.96 (192.168.50.96) 56(84) bytes of data.
64 bytes from 192.168.50.96: icmp_seq=1 ttl=64 time=0.236 ms
64 bytes from 192.168.50.96: icmp_seq=2 ttl=64 time=0.323 ms
64 bytes from 192.168.50.96: icmp_seq=3 ttl=64 time=0.311 ms
64 bytes from 192.168.50.96: icmp_seq=4 ttl=64 time=0.240 ms
64 bytes from 192.168.50.96: icmp_seq=5 ttl=64 time=0.330 ms
64 bytes from 192.168.50.96: icmp_seq=6 ttl=64 time=0.250 ms
^C
--- 192.168.50.96 ping statistics ---
6 packets transmitted, 6 received, 0% packet loss, time 5310ms
rtt min/avg/max/mdev = 0.236/0.281/0.330/0.044 ms
[root@centos6pg ~]#
原因是防火墙问题,关闭即可
涉及Linux 6 (Centos 6.5 )和Linux7 (Centos 7.7)稍微不同
Linux 6
即时生效,重启失效
service iptables stop (关闭)
永久生效
chkconfig iptables off (关闭)
常用命令
service iptables start (开启)
service iptables stop (关闭)
service iptables status (查看)
service iptables restart (重启)
chkconfig iptables on (开启)
chkconfig iptables off (关闭)
chkconfig --list | grep iptables (查看启动的服务列表)
示例:
[root@centos6pg ~]# cat /etc/redhat-release
CentOS release 6.5 (Final)
[root@centos6pg ~]#
[root@centos6pg ~]# service iptables status
Table: filter
Chain INPUT (policy ACCEPT)
num target prot opt source destination
Chain FORWARD (policy ACCEPT)
num target prot opt source destination
Chain OUTPUT (policy ACCEPT)
num target prot opt source destination
[root@centos6pg ~]# service iptables stop
iptables: Setting chains to policy ACCEPT: filter [ OK ]
iptables: Flushing firewall rules: [ OK ]
iptables: Unloading modules: [ OK ]
[root@centos6pg ~]# service iptables status
iptables: Firewall is not running.
[root@centos6pg ~]#
[root@centos6pg ~]# chkconfig --help ##---> 命令帮助查看参数
chkconfig version 1.3.49.3 - Copyright (C) 1997-2000 Red Hat, Inc.
This may be freely redistributed under the terms of the GNU Public License.
usage: chkconfig [--list] [--type <type>] [name]
chkconfig --add <name>
chkconfig --del <name>
chkconfig --override <name>
chkconfig [--level <levels>] [--type <type>] <name> <on|off|reset|resetpriorities>
[root@centos6pg ~]#
[root@centos6pg ~]# chkconfig --list | grep iptables ##---> 确认
iptables 0:off 1:off 2:off 3:off 4:off 5:off 6:off
[root@centos6pg ~]#
Linux 7
关闭防火墙
即时生效,重启失效(停止firewall)
systemctl stop firewalld.service (停止)
永久生效(禁止firewall开机启动)
systemctl disable firewalld.service (禁止)
常用命令:
systemctl start firewalld.service (启动)
systemctl stop firewalld.service (关闭)
systemctl restart firewalld.service (重启)
systemctl status firewalld.service (查看)
systemctl enable firewalld.service (开机启用)
systemctl disable firewalld.service (开机禁用)
systemctl list-unit-files|grep enabled (查看启动的服务列表)
示例:
[root@centos7_pg ~]# cat /etc/redhat-release
CentOS Linux release 7.7.1908 (Core)
[root@centos7_pg ~]#
[root@centos7_pg ~]# systemctl status firewalld
● firewalld.service - firewalld - dynamic firewall daemon
Loaded: loaded (/usr/lib/systemd/system/firewalld.service; enabled; vendor preset: enabled)
Active: active (running) since Mon 2020-04-06 10:35:47 CST; 59min ago
Docs: man:firewalld(1)
Main PID: 724 (firewalld)
CGroup: /system.slice/firewalld.service
└─724 /usr/bin/python2 -Es /usr/sbin/firewalld --nofork --nopid
Apr 06 10:35:45 centos7_pg systemd[1]: Starting firewalld - dynamic firewall daemon...
Apr 06 10:35:47 centos7_pg systemd[1]: Started firewalld - dynamic firewall daemon.
[root@centos7_pg ~]# systemctl stop firewalld
[root@centos7_pg ~]# systemctl status firewalld
● firewalld.service - firewalld - dynamic firewall daemon
Loaded: loaded (/usr/lib/systemd/system/firewalld.service; enabled; vendor preset: enabled)
Active: inactive (dead) since Mon 2020-04-06 11:35:42 CST; 1s ago
Docs: man:firewalld(1)
Process: 724 ExecStart=/usr/sbin/firewalld --nofork --nopid $FIREWALLD_ARGS (code=exited, status=0/SUCCESS)
Main PID: 724 (code=exited, status=0/SUCCESS)
Apr 06 10:35:45 centos7_pg systemd[1]: Starting firewalld - dynamic firewall daemon...
Apr 06 10:35:47 centos7_pg systemd[1]: Started firewalld - dynamic firewall daemon.
Apr 06 11:35:42 centos7_pg systemd[1]: Stopping firewalld - dynamic firewall daemon...
Apr 06 11:35:42 centos7_pg systemd[1]: Stopped firewalld - dynamic firewall daemon.
[root@centos7_pg ~]#
[root@centos7_pg ~]# firewall-cmd --state
not running
[root@centos7_pg ~]#
UNIX域套接字 Tips
UNIX域套接字 - Unix Domain Socket
在Linux系统中,有很多进程间通信方式,套接字(Socket)就是其中的一种。但传统的套接字的用法都是基于TCP/IP协议栈的,需要指定IP地址。 如果不同主机上的两个进程进行通信,当然这样做没什么问题。但是,如果只需要在一台机器上的两个不同进程间通信,还要用到IP地址就有点多余。 对于套接字来说,还存在一种叫做Unix域套接字的类别,专门用来解决这个问题。其API的掉用方法基本上和普通TCP/IP的套接字一样,只是有些许差别。
- 什么是UNIX域套接字
Unix域协议并不是一个实际的协议族,而是在单个主机上执行客户/服务通信的一种方式。是进程间通信(IPC)的一种方式。 它提供了两类套接字:字节流套接字(有点像TCP)和数据报套接字(有点像UDP)。 UNIX域数据报服务是可靠的,不会丢失消息,也不会传递出错。
IP协议标识客户服务器是通过IP地址和端口号实现的,UNIX域协议中用于标识客户机和服务器的协议地址的是普通文件系统中的路径名。
- UNIX域协议特点
- UNIX域套接字域TCP套接字相比,在同一台主机的传输速度前者是后者的两倍。UNIX域套接字仅仅复制数据,并不执行协议处理,不需要添加或删除网络报头,无需计算校验和,不产生顺序号,也不需要发送确认报文
- UNIX域套接字可以在同一台主机上各进程之间传递文件描述符
- UNIX域套接字域传统套接字的区别是用路径名表示协议族的描述
postgresql的Unix域套接字
目录地址:
[postgres@centos7_pg pgdata]$ cat postgresql.conf | grep unix
#unix_socket_directories = '/tmp' # comma-separated list of directories
#unix_socket_group = '' # (change requires restart)
#unix_socket_permissions = 0777 # begin with 0 to use octal notation
[postgres@centos7_pg pgdata]$
[postgres@centos7_pg pgdata]$ psql
You are connected to database "postgres" as user "postgres" via socket in "/tmp" at port "5432".
psql (10.11)
Type "help" for help.
postgres@postgres=>
postgres@postgres=> show unix_socket_directories ;
unix_socket_directories
-------------------------
/tmp
(1 row)
postgres@postgres=> show unix_socket_permissions ;
unix_socket_permissions
-------------------------
0777
(1 row)
postgres@postgres=>
参数 unix_socket_directory 用来配置 socket 文件的目录,默认是 /tmp 目录。 参数 unix_socket_permissions 用来设置 socket 文件的权限。
5432 是端口名称,如果psql连接时候使用Unix域套接字,可以用PGPORT
来指定。(修改~/.bash_profile
或/etc/profile
文件中的端口PGPORT
指定)
具体文件名:
[postgres@centos7_pg ~]$ ls -tlr /tmp/.s.PGSQL.5432
srwxrwxrwx 1 postgres postgres 0 Apr 6 13:50 /tmp/.s.PGSQL.5432
[postgres@centos7_pg ~]$ fuser /tmp/.s.PGSQL.5432
/tmp/.s.PGSQL.5432: 4187 4972
[postgres@centos7_pg ~]$ ps -ef | grep 4972 | grep -v grep
postgres 4972 4187 0 13:41 ? 00:00:00 postgres: postgres postgres [local] idle
[postgres@centos7_pg ~]$
[postgres@centos7_pg ~]$ ls -tlr /tmp/.s.PGSQL.5432.lock
-rw------- 1 postgres postgres 47 Apr 6 13:50 /tmp/.s.PGSQL.5432.lock
[postgres@centos7_pg ~]$ fuser /tmp/.s.PGSQL.5432.lock
[postgres@centos7_pg ~]$
启动停止postgres之后的UNIX域套接字变化
[postgres@centos7_pg ~]$ ls -tlr /tmp/.s.PGSQL.5432.lock
ls: cannot access /tmp/.s.PGSQL.5432.lock: No such file or directory
[postgres@centos7_pg ~]$ ls -tlr /tmp/.s.PGSQL.5432
.ls: cannot access /tmp/.s.PGSQL.5432: No such file or directory
[postgres@centos7_pg ~]$ pg_ctl start -D /data/pgsql10/pgdata -l /tmp/pg10.log
waiting for server to start.... done
server started
[postgres@centos7_pg ~]$ ls -tlr /tmp/.s.PGSQL.5432
srwxrwxrwx 1 postgres postgres 0 Apr 6 14:29 /tmp/.s.PGSQL.5432
[postgres@centos7_pg ~]$ ls -tlr /tmp/.s.PGSQL.5432.lock
-rw------- 1 postgres postgres 47 Apr 6 14:29 /tmp/.s.PGSQL.5432.lock
[postgres@centos7_pg ~]$ fuser /tmp/.s.PGSQL.5432
/tmp/.s.PGSQL.5432: 6386
[postgres@centos7_pg ~]$ fuser /tmp/.s.PGSQL.5432.lock
[postgres@centos7_pg ~]$ ps -ef | grep 6386 | grep -v grep
postgres 6386 1 0 14:29 pts/1 00:00:00 /data/pgsql10/bin/postgres -D /data/pgsql10/pgdata
postgres 6388 6386 0 14:29 ? 00:00:00 postgres: checkpointer process
postgres 6389 6386 0 14:29 ? 00:00:00 postgres: writer process
postgres 6390 6386 0 14:29 ? 00:00:00 postgres: wal writer process
postgres 6391 6386 0 14:29 ? 00:00:00 postgres: autovacuum launcher process
postgres 6392 6386 0 14:29 ? 00:00:00 postgres: stats collector process
postgres 6393 6386 0 14:29 ? 00:00:00 postgres: bgworker: logical replication launcher
[postgres@centos7_pg ~]$
常见碰到错误
psql connections on Unix domain socket "/tmp/.s.PGSQL.5432"
FATAL: could not create lock file "/tmp/.s.PGSQL.5432.lock": Permission denied
可能解决办法:
- 查看
ls -la /tmp/.s.PGSQL*
是否被删除了[postgres@centos7_pg ~]$ ls -la /tmp/.s.PGSQL* srwxrwxrwx 1 postgres postgres 0 Apr 6 14:29 /tmp/.s.PGSQL.5432 -rw------- 1 postgres postgres 47 Apr 6 14:29 /tmp/.s.PGSQL.5432.lock [postgres@centos7_pg ~]$
-
Postgresql的端口地址是否更换了
-
/tmp/.s.PGSQL 文件和上层目录权限是否正确
-
尝试重启postgresql服务
- 查看 unix_socket_directory 和 unix_socket_permissions 的设置(postgresql.conf)
参考
Have a good work&life! 2020/09 via LinHong