ubuntu postgresql常见问题

Ubuntu 上 PostgreSQL 常见问题与排查清单

一 安装与启动

• 安装与基础检查
  • 更新并安装：sudo apt update && sudo apt install postgresql postgresql-contrib
  • 查看状态：sudo systemctl status postgresql（应为 active (running)）
  • 设置开机自启：sudo systemctl enable postgresql
• 常见症状与处理
  • 报错 Unit postgresql.service could not be found：未安装，执行上面的安装命令即可。
  • 服务启动失败：查看日志定位配置错误或端口冲突
    • sudo journalctl -xe
    • sudo tail -n 50 /var/log/postgresql/postgresql-<版本>-main.log
  • WSL 环境无法用 systemctl 启动或 socket 不存在
    • 优先使用：sudo service postgresql start（WSL 对 systemd 支持有限）
    • 若仍报 “No such file or directory” 检查实例是否真的在监听：ss -lntp | grep 5432
    • 必要时重新初始化数据目录（见文末“彻底重装”）并确认以正确版本启动。

二 登录认证与会话

• 本地登录与修改密码
  • 切换到系统用户并进入 psql：sudo -u postgres psql
  • 修改管理员密码：ALTER USER postgres WITH PASSWORD ‘your_password’;
• 忘记 postgres 密码
  • 临时将本地认证改为 trust：编辑 /etc/postgresql/<版本>/main/pg_hba.conf，在本地行将 METHOD 改为 trust，保存后重启服务
  • 登录 psql 后执行：ALTER USER postgres WITH PASSWORD ‘new_password’;
  • 将 pg_hba.conf 改回 scram-sha-256/md5 并重启
• 常见报错与处理
  • FATAL: password authentication failed for user “postgres”：密码错误或认证方式不匹配，核对密码与 pg_hba.conf 规则
  • CREATE DATABASE 未找到命令：SQL 必须在 psql 中执行，而非 Bash
  • psql: error: connection to server on socket “…/.s.PGSQL.5432” failed: No such file or directory：服务未运行或实例未监听，检查服务与端口。

三 远程连接与防火墙

• 配置要点
  • postgresql.conf：设置 listen_addresses = ‘*’
  • pg_hba.conf：添加规则（示例）host all all 0.0.0.0/0 scram-sha-256
  • 重启服务：sudo systemctl restart postgresql
  • 防火墙放行：sudo ufw allow 5432/tcp && sudo ufw reload
• 客户端工具连接失败排查
  • 确认连接的是 PostgreSQL（而非 SQL Server）扩展/驱动
  • 核对主机、端口、数据库名、用户名、密码；避免数据库名拼写错误或多余空格
  • 若使用云服务器，确保安全组/NACL 也放行 5432。

四 创建数据库与排序规则

• 排序规则不兼容
  • 错误示例：new collation (en_US.UTF-8) is incompatible with the collation of the template database (zh_CN.UTF-8)
  • 解决：使用干净的模板 template0 创建
    • CREATE DATABASE yourdb WITH ENCODING = ‘UTF8’ LC_COLLATE = ‘en_US.UTF-8’ LC_CTYPE = ‘en_US.UTF-8’ TEMPLATE = template0;
• 新建用户与授权
  • 交互式建用户：sudo -u postgres createuser --interactive
  • 建库：sudo -u postgres createdb mydb
  • 赋权：在 psql 中 \c mydb 后执行 GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

五 性能优化与维护

• 关键参数建议（示例为中等内存环境，按实际调整）
  • shared_buffers：建议为内存的 1/4（不超过 1/2）
  • effective_cache_size：建议为内存的 1/2（供成本估算，非实际占用）
  • work_mem：根据并发与排序/哈希操作适当增大
  • maintenance_work_mem：VACUUM/创建索引等维护操作的内存
  • fsync：保持 on（关闭虽能提速，但有断电丢数据风险）
  • 查看生效：在 psql 中 SHOW 参数名
• 日常维护与工具
  • 定期执行 VACUUM / ANALYZE，保持统计信息与空间回收
  • 使用连接池降低连接开销；为高频查询字段建立合适索引；大表考虑分区
  • 客户端工具（如 PgAdmin）侧可减少并发查询、优化 SQL、启用缓存等。

附 常用命令速查

• 服务：sudo systemctl start|stop|restart|reload|status postgresql
• 登录：sudo -u postgres psql
• 日志：sudo tail -n 50 /var/log/postgresql/postgresql-<版本>-main.log
• 备份/恢复：
  • 备份：sudo -u postgres pg_dump mydb > mydb.sql
  • 恢复：sudo -u postgres psql -d mydb -f mydb.sql
• 彻底重装（谨慎）
  • sudo apt purge postgresql*
  • sudo rm -rf /etc/postgresql/ /var/lib/postgresql/
  • sudo apt autoremove
  • 重新安装后再初始化与建库