Lin Hong's TECH Blog! 刀不磨要生锈,人不学习要落后 - Thinking ahead

Postgresql 12 Connect Study 003 Tips

2020-09-01
Oracle
   

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域协议特点
  1. UNIX域套接字域TCP套接字相比，在同一台主机的传输速度前者是后者的两倍。UNIX域套接字仅仅复制数据，并不执行协议处理，不需要添加或删除网络报头，无需计算校验和，不产生顺序号，也不需要发送确认报文
  2. UNIX域套接字可以在同一台主机上各进程之间传递文件描述符
  3. 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)

参考

在端口 5432上，无法连接到 PostgreSQL

Have a good work&life! 2020/09 via LinHong

Similar Posts

上一篇 Postgresql 12 启动停止 Study 002 Tips

下一篇 Postgresql 12 Tablespace Study 004 Tips

Comments

Content