新手必看！PostgreSQL 入门超实用避坑秘籍，让你轻松少走弯路

作为一款功能强大的开源关系型数据库，PostgreSQL 深受开发者喜爱。但对于刚接触 PostgreSQL 的小白来说，往往会被各种”奇怪”的问题困扰。

本文根据搜索热度，整理了 PostgreSQL 小白最常问的 10 个问题，并给出详细的解决方案。无论你是刚安装完 PostgreSQL 遇到的连接问题，还是日常使用中遇到的性能问题，这篇文章都能帮你找到答案。

第一章：PostgreSQL 无法启动

1.1 问题描述

安装完 PostgreSQL 后，服务无法启动，客户端连接失败：

could not connect to server: Connection refused

或者在 Windows 服务管理器中启动失败，日志报错。

1.2 原因分析

根据排查频率，最常见的原因有四类：

原因一：端口被占用

PostgreSQL 默认使用 5432 端口，如果这个端口被其他程序占用，PostgreSQL 就无法启动。

原因二：残留文件未清理

异常关机或强制关闭 PostgreSQL 后，postmaster.pid 文件可能没有删除，系统会误认为数据库已经在运行。

原因三：权限问题

数据目录的文件权限配置不正确，导致 PostgreSQL 无法读写数据文件。

原因四：WAL 日志损坏

事务日志（WAL）损坏会导致数据库无法通过一致性检查。

1.3 解决方案

解决方案一：检查端口占用

# Linux
netstat -tulpn | grep 5432
# 或者
ss -tulpn | grep 5432

# Windows
netstat -ano | findstr "5432"

如果端口被占用，找到对应的进程：

# Windows 查看进程详情
tasklist | findstr "进程 PID"

常见占用 5432 端口的进程：

• 另一个 PostgreSQL 实例；
• IBM DB2；
• 某些旧版 Oracle。

解决方案：关闭占用端口的进程，或修改 PostgreSQL 配置文件中的端口号。

解决方案二：清理残留文件

# 进入数据目录
cd /var/lib/postgresql/data

# 删除 postmaster.pid 文件
rm -f postmaster.pid

# 如果有其他残留的锁文件也一并删除
rm -f postmaster.opts

解决方案三：修复文件权限

# Linux 下修复权限
chown -R postgres:postgres /var/lib/postgresql/data
chmod -R 700 /var/lib/postgresql/data

Windows 下需要：

1. 右键 data 文件夹 → 属性 → 安全；
2. 确保 postgres 用户有”完全控制”权限；
3. 添加 Users 组的”修改”和”写入”权限。

解决方案四：修复 WAL 日志

# 使用 pg_resetxlog 重置事务日志
pg_resetxlog -f /var/lib/postgresql/data

⚠️ 警告：这是最后的手段，可能导致少量数据丢失。慎用！！！

1.4 预防措施

1. 正常关闭数据库：pg_ctl stop或systemctl stop postgresql；
2. 定期备份数据；
3. 使用 UPS 防止异常断电。

第二章：远程连接被拒绝

2.1 问题描述

本地连接正常，但从其他服务器连接时报错：

FATAL: no pg_hba.conf entry for host "192.168.1.100", user "postgres", database "mydb"

或：

could not connect to server: Connection refused
    Is the server running on host "192.168.1.10" and accepting
    TCP/IP connections on port 5432?

2.2 原因分析

PostgreSQL 默认只接受本地连接，远程连接需要两个配置文件配合：

配置文件	作用
postgresql.conf	监听哪些地址
pg_hba.conf	允许哪些客户端连接

2.3 解决方案

步骤一：修改 postgresql.conf

找到并修改：

# 找到配置文件位置
ps aux | grep postgresql
# 或
SHOW config_file;

# 编辑配置文件
vi /etc/postgresql/16/main/postgresql.conf

修改以下参数：

# 监听地址，* 表示所有地址
listen_addresses = '*'

# 或者分别设置
# listen_addresses = 'localhost,192.168.1.10'

# 端口（默认 5432，一般不用改）
port = 5432

步骤二：修改 pg_hba.conf

vi /etc/postgresql/16/main/pg_hba.conf

添加访问规则：

# 格式：TYPE  DATABASE  USER  ADDRESS  METHOD

# 允许所有 IP 用 md5 密码连接所有数据库
host all all 0.0.0.0/0 md5

# 只允许特定 IP 段
host all all 192.168.1.0/24 md5

# 允许特定 IP 无密码本地连接（测试用，不推荐生产环境）
host all all 192.168.1.100/32 trust

步骤三：重启服务

# Linux
systemctl restart postgresql

# 或
pg_ctl reload -D /var/lib/postgresql/data

# Windows
# 在服务管理器中重启 PostgreSQL 服务

2.4 连接方式说明

METHOD	说明	适用场景
trust	无需密码，任何人都能连	本地开发
md5	需要密码验证	生产环境推荐
peer	使用操作系统用户名验证	本地 Unix socket
cert	使用 SSL 证书验证	高安全要求

2.5 常见错误排查

错误信息	原因	解决方案
Connection refused	服务未启动或端口错误	检查服务状态和端口
No pg_hba.conf entry	未配置访问规则	添加对应规则
Authentication failed	密码错误	检查用户名密码
Server closed the connection	防火墙阻止	开放 5432 端口

第三章：忘记 postgres 用户密码

3.1 问题描述

安装 PostgreSQL 后，不知道 postgres 超级用户的密码，无法进行管理操作。

3.2 场景分析

操作系统	默认行为
Linux	安装时设置密码，或使用 peer 认证
Windows	安装过程中设置密码，可能忘记
macOS	安装时设置密码

3.3 解决方案

方法一：通过 pg_hba.conf 重置（推荐）

# 1. 编辑 pg_hba.conf
vi /etc/postgresql/16/main/pg_hba.conf

# 2. 将本地连接改为 trust 模式
# 找到类似这样的行：
local   all   all   peer
# 改为：
local   all   all   trust

# 或者添加一行允许本地无密码：
local   all   postgres   trust

# 3. 重载配置
systemctl reload postgresql

# 4. 连接到数据库（无需密码）
psql -U postgres

# 5. 修改密码
ALTER USER postgres WITH PASSWORD 'your_new_password';

# 6. 恢复 pg_hba.conf 设置
# 改回 md5 或 peer 认证

# 7. 再次重载
systemctl reload postgresql

方法二：单用户模式修复

# 1. 停止 PostgreSQL
systemctl stop postgresql

# 2. 以单用户模式启动
postgres --single -D /var/lib/postgresql/data

# 3. 在提示符下修改密码
ALTER USER postgres WITH PASSWORD 'new_password';

# 4. 按 Ctrl+D 退出

方法三：Windows 专用方法

1. 打开 PostgreSQL 安装目录下的 data 文件夹；
2. 编辑 pg_hba.conf；
3. 添加一行：host all all 127.0.0.1/32 trust；
4. 重启服务；
5. 连接后修改密码；
6. 恢复配置。

3.4 密码安全建议

1. 使用强密码：Postgres@2024!#abc；
2. 定期更换密码；
3. 生产环境禁用 trust 认证；
4. 使用 SSL 加密连接。

第四章：表名和列名大小写问题

4.1 问题描述

创建表时明明用大写字母命名的表，查询时却报错：

ERROR: relation "User" does not exist

或者反过来，查询时用了大写，数据库找不到表。

4.2 原因分析

PostgreSQL 对标识符（表名、列名）的处理规则：

创建方式	存储方式	查询方式
create table User	小写	select * from user ✓
create table “User”	保持原样	select * from “User” ✓
create table user	小写	select * from user ✓

核心规则：

• 不带引号 → 自动转为小写；
• 带引号 → 保持原样（包括大小写）。

4.3 解决方案

方案一：始终坚持小写（推荐）

-- 创建
CREATE TABLE userinfo (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 查询
SELECT * FROM userinfo;
SELECT username FROM userinfo WHERE id = 1;

方案二：使用双引号保持大小写

-- 创建时用双引号
CREATE TABLE "User" (
    "Id" SERIAL PRIMARY KEY,
    "UserName" VARCHAR(50),
    "CreateTime" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 查询时也必须用双引号
SELECT "Id", "UserName" FROM "User";

⚠️ 警告：一旦使用双引号创建，以后的每次查询都要加双引号，非常麻烦。

4.4 Java/Go 等开发语言中的坑

// Java 中使用 Hibernate
@Entity
@Table(name = "user_info")  // 建议用下划线
public class User { }

// 如果用驼峰命名，SQL 会变成：
// SELECT * FROM UserInfo -- 报错！
// 应该指定：
@Table(name = "\"UserInfo\"")  // 保持大小写

4.5 最佳实践

1. 表名、列名全部使用小写；
2. 使用下划线分隔：user_info, order_item；
3. 避免使用保留字：user, order, select等；
4. 保持命名风格一致。

第五章：索引不生效与查询慢

5.1 问题描述

明明创建了索引，查询还是很慢：

-- 创建了索引
CREATE INDEX idx_user_phone ON user(phone);

-- 但查询还是很慢
SELECT * FROM user WHERE phone = '13800138000';

或者：

-- count(*) 特别慢
SELECT COUNT(*) FROM big_table;

5.2 原因分析

原因一：表太小

PostgreSQL 优化器认为全表扫描比索引扫描更快。

原因二：统计信息过期

没有执行 ANALYZE，优化器不知道表有多少数据。

原因三：查询方式导致索引失效

-- 这些情况索引会失效
SELECT * FROM user WHERE phone LIKE '%138%';  -- 前导通配符
SELECT * FROM user WHERE LOWER(phone) = '138';  -- 使用函数
SELECT * FROM user WHERE phone = '138' OR name = 'Tom';  -- OR 条件

原因四：选择率过高

返回数据量超过表总行数的 5-10%，优化器会放弃索引。

5.3 解决方案

方案一：查看执行计划

EXPLAIN SELECT * FROM user WHERE phone = '13800138000';

-- 更详细的分析
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) 
SELECT * FROM user WHERE phone = '13800138000';

输出示例：

Index Scan using idx_user_phone on user  (cost=0.42..8.44 rows=1 width=45) (actual time=0.015..0.016 rows=1 loops=1)
   Index Cond: (phone = '13800138000'::character varying)
 Planning Time: 0.156 ms
 Execution Time: 0.052 ms

关键指标：

• cost：预估成本，越低越好；
• actual time：实际执行时间；
• rows：返回行数。

方案二：定期执行 ANALYZE

-- 分析单个表
ANALYZE user;

-- 分析整个数据库
ANALYZE;

-- 自动 vacuum analyze（推荐）
VACUUM ANALYZE;

方案三：创建合适的索引

-- 常见查询模式
-- 1. 等值查询
CREATE INDEX idx_user_phone ON user(phone);

-- 2. 范围查询
CREATE INDEX idx_order_date ON orders(order_date);
CREATE INDEX idx_order_status ON orders(status) WHERE status = 'active';

-- 3. 组合索引（注意顺序）
CREATE INDEX idx_order_status_date ON orders(status, order_date);

-- 4. 表达式索引（解决函数问题）
CREATE INDEX idx_user_phone_lower ON user(LOWER(phone));
SELECT * FROM user WHERE LOWER(phone) = '13800138000';  -- 会用到索引

方案四：解决 count(*) 慢的问题

-- 1. 使用索引扫描
SELECT COUNT(*) FROM big_table;

-- 2. 创建物化视图（数据允许轻微延迟）
CREATE MATERIALIZED VIEW order_count AS
SELECT COUNT(*) as cnt FROM orders;

-- 定时刷新
REFRESH MATERIALIZED VIEW CONCURRENTLY order_count;

5.4 性能优化 Checklist

• 用EXPLAIN分析慢查询；
• 确保索引列在WHERE条件中；
• 避免在索引列上使用函数；
• 定期VACUUM ANALYZE；
• 考虑组合索引的列顺序；
• 监控慢查询日志。

第六章：SERIAL 序列号有间隙

6.1 问题描述

插入数据后，序列号不连续：

1, 2, 3, 7, 8, 9, 15...

怀疑数据丢失或数据库有 bug。

6.2 原因分析

序列（Sequence）产生间隙是正常现象，不是 bug！

原因一：事务回滚

BEGIN;
INSERT INTO orders (product_id) VALUES (100);  -- 获取序列号 10
ROLLBACK;  -- 回滚了，但序列号 10 已经使用，不会再分配
INSERT INTO orders (product_id) VALUES (200);  -- 获取序列号 11

原因二：多条 INSERT

INSERT INTO orders (product_id) VALUES (1), (2), (3);  -- 一次获取 3 个序列号：13, 14, 15

原因三：服务器异常关闭

PostgreSQL 为了保证数据一致性，在异常恢复后可能跳过一些序列号。

原因四：使用了 CACHE

CREATE SEQUENCE order_id_seq CACHE 100;  -- 预生成 100 个序列号
-- 如果数据库重启，这 100 个就浪费了

6.3 解决方案

不要解决！ 这是设计如此，不是问题。

如果你必须需要连续无间隙的序列，可以自己实现：

-- 方法一：使用事务（性能差）
BEGIN;
-- 业务逻辑
INSERT INTO order_items (...) VALUES (...);
-- 记录使用的序列号
COMMIT;

-- 方法二：使用 UUID 代替自增主键
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    ...
);

-- PostgreSQL 13+ 还可以用：
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    ...
);

6.4 总结

误区	真相
序列必须连续	允许间隙，是正常现象
间隙说明数据丢失	不一定，只是序列号没被使用
回滚会释放序列号	不会，序列号是一次性消耗的

第七章：字符类型选择

7.1 问题描述

PostgreSQL 有多种存储字符串的类型，应该怎么选？

• VARCHAR(n) – 可变长度；
• CHAR(n) – 固定长度；
• TEXT – 无限长度。

7.2 类型的区别

类型	存储方式	长度	适用场景
TEXT	可变	最多 1GB	推荐使用
VARCHAR(n)	可变	最多 n 个字符	需要长度限制时
CHAR(n)	固定	不足补空格	很少使用

7.3 性能对比

很多人误以为 CHAR(n) 会比 TEXT 快，实际上：

-- 创建测试表
CREATE TABLE test_char (c CHAR(10));
CREATE TABLE test_text (c TEXT);
CREATE TABLE test_varchar (c VARCHAR(10));

-- 插入测试数据
INSERT INTO test_char SELECT generate_series(1,10000)::text || 'abc';
INSERT INTO test_text SELECT generate_series(1,10000)::text || 'abc';
INSERT INTO test_varchar SELECT generate_series(1,10000)::text || 'abc';

-- 查询性能几乎一样！
EXPLAIN ANALYZE SELECT * FROM test_char WHERE c = '1abc';
EXPLAIN ANALYZE SELECT * FROM test_text WHERE c = '1abc';
EXPLAIN ANALYZE SELECT * FROM test_varchar WHERE c = '1abc';

7.4 最佳实践

-- 推荐用法
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,  -- 需要限制长度
    email TEXT NOT NULL,                    -- 正常用 TEXT
    phone CHAR(11),                         -- 中国手机号固定 11 位
    bio TEXT,                               -- 简介可能很长
    status VARCHAR(20) DEFAULT 'active'     -- 状态字段
);

7.5 BYTEA vs TEXT

-- 存储二进制数据用 BYTEA
CREATE TABLE files (
    id SERIAL PRIMARY KEY,
    name TEXT,
    content BYTEA
);

-- 存储图片、文件等

第八章：NULL 值处理

8.1 问题描述

• 查询不到 NULL 值的数据；
• NULL 与其他值比较后还是 NULL；
• 排序时 NULL 不知道放在前面还是后面。

8.2 NULL 的特性

-- NULL 表示"未知"或"不存在"
-- 任何与 NULL 的运算结果都是 NULL

SELECT 1 + NULL;        -- 结果: NULL
SELECT 'hello' || NULL; -- 结果: NULL
SELECT NULL = NULL;     -- 结果: NULL（不是 true！）
SELECT NULL > 5;        -- 结果: NULL

8.3 判断 NULL

-- 错误写法（永远返回 0 行）
SELECT * FROM users WHERE phone = NULL;
SELECT * FROM users WHERE phone <> NULL;

-- 正确写法
SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;

8.4 替换 NULL 值

-- 使用 COALESCE（返回第一个非 NULL 值）
SELECT COALESCE(phone, '未填写') FROM users;
SELECT COALESCE(email, phone, '无联系方式') FROM users;

-- 使用 NULLIF（相等时返回 NULL）
SELECT NULLIF(price, 0) FROM products;  -- 如果 price 是 0，返回 NULL

-- 使用 NVL（Oracle 风格，PostgreSQL 也支持）
SELECT NVL(phone, '未填写') FROM users;

8.5 NULL 排序

-- NULL 放在最后（默认）
SELECT * FROM users ORDER BY created_at;

-- NULL 放在最前
SELECT * FROM users ORDER BY created_at NULLS FIRST;

-- NULL 放在最后
SELECT * FROM users ORDER BY created_at DESC NULLS LAST;

8.6 约束中的 NULL

-- 允许 NULL（默认）
CREATE TABLE orders (
    id SERIAL,
    shipped_at TIMESTAMP  -- 可以为 NULL
);

-- 不允许 NULL
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL
);

-- 复合唯一约束允许部分 NULL
CREATE TABLE user_emails (
    user_id INT,
    email VARCHAR(255),
    UNIQUE (user_id, email)  -- 允许多个 email 为 NULL 的行
);

第九章：数据库备份与恢复

9.1 常用备份工具

工具	用途	特点
pg_dump	备份单个数据库	可跨版本恢复
pg_dumpall	备份整个集群	包含角色、表空间等
pg_basebackup	物理备份	速度快，适合大数据库

9.2 逻辑备份（pg_dump）

# 备份单个数据库
pg_dump -U postgres mydb > backup.sql

# 压缩备份
pg_dump -U postgres mydb | gzip > backup.sql.gz

# 备份指定表
pg_dump -U postgres -t orders -t order_items mydb > tables.sql

# 自定义格式备份（推荐）
pg_dump -U postgres -Fc mydb > backup.dump

# 只备份结构（不包括数据）
pg_dump -U postgres -s mydb > structure.sql

9.3 恢复数据库

# 恢复 SQL 文件
psql -U postgres mydb < backup.sql

# 解压并恢复
gunzip -c backup.sql.gz | psql -U postgres mydb

# 恢复自定义格式
pg_restore -U postgres -d mydb backup.dump

# 恢复到一个新数据库
createdb -U postgres newdb
pg_restore -U postgres -d newdb backup.dump

9.4 全量备份（pg_dumpall）

# 备份所有数据库（包含角色、表空间等）
pg_dumpall -U postgres > all_databases.sql

# 只备份角色和表空间
pg_dumpall -U postgres -r > roles.sql

9.5 物理备份（pg_basebackup）

# 基础备份（需要配置 replication）
pg_basebackup -U replication -D /backup/base -P -Xs -R

# 参数说明：
# -D: 备份目录
# -P: 显示进度
# -Xs: 使用流复制
# -R: 生成恢复配置

9.6 定时自动备份脚本

#!/bin/bash
# backup.sh

DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/backup/postgresql"
DB_NAME="mydb"

# 创建备份目录
mkdir -p $BACKUP_DIR

# 执行备份
pg_dump -U postgres -Fc $DB_NAME > $BACKUP_DIR/${DB_NAME}_${DATE}.dump

# 删除 7 天前的备份
find $BACKUP_DIR -name "*.dump" -mtime +7 -delete

# 记录日志
echo "[$(date)] Backup completed: ${DB_NAME}_${DATE}.dump" >> /var/log/pg_backup.log

添加到 crontab：

# 每天凌晨 2 点执行
0 2 * * * /path/to/backup.sh

第十章：连接数过多

10.1 问题描述

连接 PostgreSQL 时报错：

FATAL: sorry, too many clients

或

could not connect to server: Too many clients

10.2 原因分析

PostgreSQL 默认最大连接数为 100。当连接数达到这个上限时，新连接会被拒绝。

-- 查看当前最大连接数
SHOW max_connections;

-- 查看当前连接数
SELECT count(*) FROM pg_stat_activity;

-- 按用户统计
SELECT usename, count(*) FROM pg_stat_activity GROUP BY usename;

-- 按数据库统计
SELECT datname, count(*) FROM pg_stat_activity GROUP BY datname;

10.3 解决方案

方案一：增加最大连接数

# 编辑 postgresql.conf
max_connections = 200

# 重启服务
systemctl restart postgresql

⚠️ 注意：每个连接都会占用一定内存，增加太多会影响性能。

方案二：使用连接池（推荐）

PgBouncer 配置示例：

# 安装
apt-get install pgbouncer

# 配置 /etc/pgbouncer/pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction  # 推荐使用事务级连接池

# 配置 /etc/pgbouncer/pgbouncer.ini 的用户
"postgres" "md5xxx..."

# 重启
systemctl restart pgbouncer

应用连接改为连接到 6432 端口（PgBouncer），而不是 5432（PostgreSQL）。

方案三：优化连接

-- 查看长时间运行的查询
SELECT 
    pid,
    now() - pg_stat_activity.query_start AS duration,
    query,
    state
FROM pg_stat_activity
WHERE state = 'active' 
  AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY duration DESC;

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

-- 杀掉指定用户的连接
SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE usename = 'some_user';

方案四：设置连接超时

# postgresql.conf
tcp_keepalives_idle = 60
tcp_keepalives_interval = 10
tcp_keepalives_count = 10

# 空闲连接超时
idle_session_timeout = 600000  -- 10 分钟

10.4 连接数规划建议

场景	推荐连接数
小型应用	50-100
中型应用	100-200 + 连接池
大型应用	200-500 + PgBouncer

总结

本文详细讲解了 PostgreSQL 小白最常遇到的 10 个问题：

1. 服务启动失败 – 端口、权限、日志问题；
2. 远程连接被拒绝 – pg_hba.conf 和listen_addresses配置；
3. 密码忘记 – 通过 trust 模式重置；
4. 大小写问题 – 始终坚持小写命名；
5. 查询慢/索引不生效 – EXPLAIN分析 + 正确的索引设计；
6. 序列号间隙 – 正常现象，无需处理；
7. 字符类型选择 – 推荐直接用TEXT；
8. NULL 值处理 – IS NULL / COALESCE / NULLS FIRST；
9. 备份恢复 – pg_dump / pg_dumpall / pg_basebackup；
10. 连接数过多 – 连接池是终极解决方案。

希望这份详细指南能帮助你更好地使用 PostgreSQL！

以上关于新手必看！PostgreSQL 入门超实用避坑秘籍，让你轻松少走弯路的文章就介绍到这了，更多相关内容请搜索码云笔记以前的文章或继续浏览下面的相关文章，希望大家以后多多支持码云笔记。