印刻数据库

外观

PostgreSQL 监听地址和端口配置 ​

基础配置 ​

1. 监听地址配置 ​

PostgreSQL通过listen_addresses参数控制监听哪些网络地址，默认只监听本地地址（localhost）。

bash
# 查看当前监听地址
SHOW listen_addresses;

# 修改监听地址为所有地址
ALTER SYSTEM SET listen_addresses = '*';

# 修改监听地址为指定IP
ALTER SYSTEM SET listen_addresses = 'localhost,192.168.1.100';

# 修改监听地址为多个IP
ALTER SYSTEM SET listen_addresses = '192.168.1.100,10.0.0.100';

2. 端口配置 ​

PostgreSQL默认监听5432端口，可以通过port参数修改。

bash
# 查看当前端口
SHOW port;

# 修改端口为5433
ALTER SYSTEM SET port = 5433;

3. Unix套接字配置 ​

PostgreSQL还支持Unix套接字连接，可以通过以下参数配置：

bash
# 查看Unix套接字目录
SHOW unix_socket_directories;

# 修改Unix套接字目录
ALTER SYSTEM SET unix_socket_directories = '/var/run/postgresql,/tmp';

# 查看Unix套接字权限
SHOW unix_socket_permissions;

# 修改Unix套接字权限
ALTER SYSTEM SET unix_socket_permissions = 0777;

高级配置 ​

1. 多端口配置 ​

PostgreSQL支持通过配置多个实例或使用代理来实现多端口监听。

使用多个PostgreSQL实例 ​

bash
# 初始化第二个实例
initdb -D /var/lib/postgresql/14/second_instance

# 配置第二个实例的端口和监听地址
echo "port = 5433" >> /var/lib/postgresql/14/second_instance/postgresql.conf
echo "listen_addresses = '*'" >> /var/lib/postgresql/14/second_instance/postgresql.conf

# 启动第二个实例
pg_ctl -D /var/lib/postgresql/14/second_instance start

使用pgBouncer实现多端口 ​

ini
# pgBouncer配置文件示例（/etc/pgbouncer/pgbouncer.ini）
[databases]
* = host=127.0.0.1 port=5432

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
listen_port2 = 6433
listen_port3 = 6434

auth_type = trust
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

2. 连接限制配置 ​

bash
# 查看最大连接数
SHOW max_connections;

# 修改最大连接数
ALTER SYSTEM SET max_connections = 200;

# 查看每个用户的连接限制
SHOW superuser_reserved_connections;

# 修改超级用户预留连接数
ALTER SYSTEM SET superuser_reserved_connections = 10;

安全配置 ​

1. 限制监听地址 ​

生产环境中应避免使用'*'监听所有地址，建议只监听必要的IP地址：

bash
# 只监听本地地址和内网IP
ALTER SYSTEM SET listen_addresses = 'localhost,192.168.1.100';

2. 使用防火墙限制访问 ​

bash
# 使用iptables限制访问
iptables -A INPUT -p tcp --dport 5432 -s 192.168.1.0/24 -j ACCEPT
iptables -A INPUT -p tcp --dport 5432 -j DROP

# 使用ufw限制访问
ufw allow from 192.168.1.0/24 to any port 5432
ufw deny 5432

# 使用firewalld限制访问
firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="192.168.1.0/24" port protocol="tcp" port="5432" accept'
firewall-cmd --reload

3. 使用SSL加密连接 ​

bash
# 启用SSL
ALTER SYSTEM SET ssl = on;
ALTER SYSTEM SET ssl_cert_file = 'server.crt';
ALTER SYSTEM SET ssl_key_file = 'server.key';
ALTER SYSTEM SET ssl_ca_file = 'root.crt';

4. pg_hba.conf配置 ​

pg_hba.conf文件控制客户端访问认证，应严格配置：

bash
# 本地连接
local   all             all                                     trust

# IPv4本地连接
host    all             all             127.0.0.1/32            trust

# IPv4内网连接
host    all             all             192.168.1.0/24          md5

# IPv6本地连接
host    all             all             ::1/128                 trust

# 复制连接
host    replication     replicator      192.168.1.0/24          md5

配置验证 ​

1. 验证监听配置 ​

bash
# 使用netstat查看监听端口
netstat -tulpn | grep postgres

# 使用ss查看监听端口
ss -tulpn | grep postgres

# 使用lsof查看监听端口
lsof -i :5432

# 从远程主机测试连接
telnet postgres_host 5432
psql -h postgres_host -U postgres

2. 验证连接权限 ​

sql
-- 查看当前连接
SELECT 
  pid, 
  usename, 
  application_name, 
  client_addr, 
  client_port, 
  backend_type
FROM pg_stat_activity;

-- 查看连接数统计
SELECT 
  usename, 
  count(*) 
FROM pg_stat_activity 
GROUP BY usename;

最佳实践 ​

1. 生产环境配置建议 ​

  • 监听地址：只监听必要的IP地址，避免使用'*'
  • 端口配置：考虑使用非默认端口（5432）以提高安全性
  • 防火墙配置：使用防火墙限制只允许特定IP访问PostgreSQL端口
  • SSL配置：生产环境建议启用SSL加密连接
  • pg_hba.conf：严格配置客户端认证，遵循最小权限原则
  • 连接数限制：根据服务器资源合理设置max_connections

2. 性能优化建议 ​

  • 连接池：使用pgBouncer或pgpool-II等连接池管理连接
  • 减少连接开销：使用长连接或连接池减少连接建立开销
  • 优化共享内存：根据连接数调整shared_buffers等内存参数
  • 监控连接：定期监控连接数和连接状态

3. 监控与告警 ​

  • 监控监听状态：使用Prometheus+Grafana监控PostgreSQL监听状态
  • 监控连接数：设置连接数告警，避免连接数过高
  • 监控连接来源：监控异常连接来源，及时发现安全问题
  • 日志监控：启用连接日志，监控连接尝试和失败情况

常见问题处理 ​

1. 配置不生效 ​

问题：修改了listen_addresses或port参数后，配置不生效。

解决方法：

  • 这些参数需要重启PostgreSQL服务才能生效
  • 验证配置文件路径是否正确
  • 检查配置文件语法是否正确
bash
# 重启PostgreSQL服务
systemctl restart postgresql

# 或使用pg_ctl重启
pg_ctl restart -D /var/lib/postgresql/14/main

2. 无法远程连接 ​

问题：无法从远程主机连接到PostgreSQL。

解决方法：

  • 检查listen_addresses是否包含远程主机的IP或'*'
  • 检查防火墙是否允许PostgreSQL端口访问
  • 检查pg_hba.conf是否允许远程主机连接
  • 检查PostgreSQL服务是否正在运行
bash
# 检查PostgreSQL服务状态
systemctl status postgresql

# 检查防火墙规则
iptables -L -n | grep 5432

3. 端口被占用 ​

问题：启动PostgreSQL时提示端口被占用。

解决方法：

  • 检查哪个进程占用了端口
  • 修改PostgreSQL端口或停止占用端口的进程
bash
# 检查端口占用情况
lsof -i :5432
netstat -tulpn | grep 5432

# 修改PostgreSQL端口
ALTER SYSTEM SET port = 5433;

4. 连接数过高 ​

问题：PostgreSQL连接数过高，导致性能下降。

解决方法：

  • 调整max_connections参数
  • 使用连接池管理连接
  • 终止空闲连接
  • 优化应用程序，减少连接数
sql
-- 查看空闲连接
SELECT pid, usename, application_name, client_addr, state, query_start 
FROM pg_stat_activity 
WHERE state = 'idle' 
  AND now() - query_start > interval '10 minutes';

-- 终止空闲连接
SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE state = 'idle' 
  AND now() - query_start > interval '10 minutes';

常见问题（FAQ） ​

Q1：如何查看PostgreSQL正在监听哪些端口？ ​

A1：可以使用以下命令查看：

bash
netstat -tulpn | grep postgres
ss -tulpn | grep postgres
lsof -i :5432

Q2：修改listen_addresses参数后需要重启吗？ ​

A2：是的，listen_addresses和port参数属于Postmaster级别的参数，需要重启PostgreSQL服务才能生效。

Q3：如何配置PostgreSQL监听多个端口？ ​

A3：PostgreSQL本身不支持单个实例监听多个端口，但可以通过以下方式实现：

  • 运行多个PostgreSQL实例，每个实例监听不同端口
  • 使用pgBouncer或pgpool-II等代理工具
  • 使用Nginx或HAProxy等反向代理

Q4：如何限制特定IP访问PostgreSQL？ ​

A4：可以通过以下方式限制：

  • 在listen_addresses中只指定允许的IP
  • 使用防火墙（iptables、ufw、firewalld）限制访问
  • 在pg_hba.conf中配置允许的IP地址范围

Q5：如何提高PostgreSQL的连接安全性？ ​

A5：可以通过以下方式提高安全性：

  • 只监听必要的IP地址
  • 使用非默认端口
  • 启用SSL加密连接
  • 严格配置pg_hba.conf
  • 使用防火墙限制访问
  • 定期更新PostgreSQL版本
  • 使用强密码策略

Q6：如何监控PostgreSQL的连接情况？ ​

A6：可以通过以下方式监控：

  • 查询pg_stat_activity视图
  • 使用Prometheus+Grafana设置连接数告警
  • 启用连接日志
  • 使用pgBadger分析连接日志
  • 使用pgAdmin等图形化工具

Q7：如何优化PostgreSQL的连接性能？ ​

A7：优化方法：

  • 使用连接池管理连接
  • 调整max_connections参数
  • 优化共享内存配置
  • 减少连接建立开销
  • 终止长时间空闲连接

Q8：无法连接到PostgreSQL，提示"connection refused"怎么办？ ​

A8：处理步骤：

  1. 检查PostgreSQL服务是否正在运行
  2. 检查监听地址和端口配置
  3. 检查防火墙是否允许访问
  4. 检查pg_hba.conf配置
  5. 检查客户端IP是否在允许列表中