如何在Ubuntu上排查PostgreSQL故障

1. 检查PostgreSQL服务状态
首先确认PostgreSQL服务是否正在运行，使用以下命令查看服务状态：

sudo systemctl status postgresql

如果服务未运行，启动服务：

sudo systemctl start postgresql

若需开机自启，可执行：

sudo systemctl enable postgresql

2. 验证配置文件设置
PostgreSQL的主配置文件postgresql.conf和客户端认证文件pg_hba.conf是故障排查的关键：

postgresql.conf：检查listen_addresses（是否允许远程连接，如listen_addresses = '*'）、port（默认5432，确保未被占用）等参数，修改后需重启服务：
```
sudo systemctl restart postgresql
```
pg_hba.conf：配置客户端认证规则，如允许某网段通过MD5密码认证连接：
```
host all all 192.168.1.0/24 md5
```
修改后需执行sudo systemctl reload postgresql使配置生效。

3. 检查防火墙与端口连通性
Ubuntu的防火墙（UFW）可能阻止PostgreSQL的默认端口（5432），需放行该端口：

sudo ufw allow 5432/tcp
sudo ufw enable  # 若未启用防火墙

使用telnet或nc命令测试端口连通性（从客户端机器执行）：

telnet <服务器IP> 5432
# 或
nc -zv <服务器IP> 5432

4. 查看PostgreSQL日志文件
日志是定位故障的核心依据，默认路径为/var/log/postgresql/<版本>/main/（如/var/log/postgresql/15/main/）。使用以下命令查看实时日志：

sudo tail -f /var/log/postgresql/15/main/postgresql.log

重点关注错误信息（如连接拒绝、权限不足、磁盘空间满等），日志中的时间戳可帮助定位故障发生时间。

5. 测试数据库连接
使用psql命令行工具测试本地连接（默认用户postgres）：

sudo -u postgres psql

若本地连接正常，再测试远程连接（从客户端机器执行）：

psql -h <服务器IP> -p 5432 -U <用户名> -d <数据库名>

输入密码后若仍无法连接，需返回检查配置文件和防火墙设置。

6. 分析数据库性能与活动

查看当前活动进程：使用pg_stat_activity视图查看当前连接的进程，识别长时间运行的查询或阻塞进程：
```
SELECT * FROM pg_stat_activity WHERE state = 'active';
```
检查锁等待：通过pg_locks视图查看锁等待情况，定位阻塞事务：
```
SELECT * FROM pg_locks WHERE NOT granted;
```
使用pgBadger生成性能报告：安装pgBadger（sudo apt install pgbadger），配置postgresql.conf开启详细日志（log_destination = 'csvlog'、logging_collector = on等），然后生成HTML报告：
```
pgbadger /var/log/postgresql/15/main/postgresql-*.csv -o report.html
```
报告可直观展示慢查询、高频查询、锁等待等性能问题。

7. 检查表空间与磁盘空间

查看表空间使用情况：使用以下SQL查询表空间大小及剩余空间：

SELECT spcname, pg_size_pretty(pg_tablespace_size(spcname)) AS size, 
       pg_size_pretty(pg_tablespace_size(spcname) - pg_tablespace_size(spcname) * 0.8) AS free_space
FROM pg_tablespace;

若表空间剩余空间不足（如小于20%），需清理无用数据或扩展表空间。

检查磁盘空间：使用df -h命令查看系统磁盘使用情况，确保/var/lib/postgresql/<版本>/main（数据目录）所在分区有足够空间。

8. 排查用户权限问题
若出现“permission denied”错误，需检查用户权限：

查看用户权限：

\du  # psql内查看用户列表及角色

授予权限：为用户授予数据库访问权限（如GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;）或表操作权限（如GRANT SELECT, INSERT ON TABLE mytable TO myuser;）。

最新问答

相关标签