PostgreSQL 安装和使用教程

1. PostgreSQL 简介

PostgreSQL 是一个功能强大且开源的对象-关系型数据库管理系统（ORDBMS），支持 SQL 标准、事务、并发控制、可扩展性等特性，广泛应用于 web 后端、大数据分析和企业级应用。

2. 安装环境准备

在安装之前，请确保：

• 拥有管理员 (Linux root/Sudo、Windows 管理员) 权限
• 机器具备至少 1 GB 内存，20 GB 磁盘空间
• 网络连接（用于下载安装包）

3. 在不同系统上的安装

3.1 Linux (Ubuntu/Debian)

# 更新软件包索引
ttysudo apt update
# 安装 PostgreSQL
sudo apt install -y postgresql postgresql-contrib
# 查看服务状态
sudo systemctl status postgresql

3.2 Windows

1. 访问 PostgreSQL 官方下载页面：https://www.postgresql.org/download/windows/
2. 下载适合的安装程序（通常为 EnterpriseDB 提供的 Windows Installer）
3. 双击运行，按向导选择安装目录、数据库端口（默认为 5432）、超级用户（postgres）密码等。
4. 安装完成后，使用 pgAdmin 或 psql 客户端连接测试。

3.3 macOS

推荐使用 Homebrew：

# 安装 Homebrew（如未安装）
/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"
# 安装 PostgreSQL
brew update
brew install postgresql
# 启动服务
brew services start postgresql

4. 初始化与启动

• 初始化集群 （大多数发行版安装时已自动完成）

  sudo -u postgres initdb -D /var/lib/postgresql/data

• 启动/停止服务

  sudo systemctl start|stop|restart postgresql

• 切换到超级用户

  sudo -i -u postgres

5. 基本使用

5.1 使用 psql 客户端

# 使用 postgres 用户进入 psql
sudo -u postgres psql
# 或指定数据库和用户
psql -h localhost -p 5432 -U 用户名 数据库名

在 psql 提示符下：

• \? 查看帮助命令
• \l 列出数据库
• \c 数据库名 切换数据库
• \dt 列出表
• \q 退出

5.2 创建数据库与用户

-- 创建用户
CREATE USER yys WITH PASSWORD 'secure_password';
-- 创建数据库
CREATE DATABASE mydb OWNER yys;
-- 授权
GRANT ALL PRIVILEGES ON DATABASE mydb TO yys;

5.3 数据库和表的基本操作（CRUD）

-- 切换到 mydb
\c mydb

-- 创建表
CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  department VARCHAR(50),
  salary NUMERIC(10,2)
);

-- 插入数据 (Create)
INSERT INTO employees (name, department, salary)
VALUES
  ('Alice', 'Engineering', 75000.00),
  ('Bob', 'Marketing', 65000.00);

-- 查询数据 (Read)
SELECT * FROM employees;

-- 更新数据 (Update)
UPDATE employees SET salary = salary * 1.05 WHERE department = 'Engineering';

-- 删除数据 (Delete)
DELETE FROM employees WHERE name = 'Bob';

6. 常用管理操作

6.1 备份与恢复

• 使用 pg_dump 备份单个数据库：

  pg_dump -U yys -F c -b -v -f mydb_backup.dump mydb

• 使用 pg_restore 恢复：

  pg_restore -U yys -d newdb -v mydb_backup.dump

• 使用 pg_dumpall 备份所有数据库：

  pg_dumpall -U postgres > all_databases.sql

6.2 查看和终止连接

-- 查看当前连接
SELECT pid, usename, datname, client_addr, state
FROM pg_stat_activity;
-- 终止连接
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'mydb' AND pid <> pg_backend_pid();

6.3 性能监控概览

• 利用 pg_stat_statements 扩展查看慢查询
• 使用 EXPLAIN ANALYZE 分析查询计划

7. GUI 工具推荐

• pgAdmin：官方开源管理工具，功能完善，支持可视化查询、备份恢复
• DBeaver：通用数据库管理客户端，支持多种数据库
• DataGrip：JetBrains 出品，商业收费，智能 SQL 编辑

8. 配置优化与安全

• 编辑 postgresql.conf 调整共享内存、工作内存、并发连接数
• 编辑 pg_hba.conf 配置访问控制，可按 IP、用户、数据库分组授权
• 启用 SSL 加密连接
• 使用角色权限细化安全控制

9. 常见问题与解决方案

• 端口占用：如果 5432 被占用，可在 postgresql.conf 中修改 port

• 密码验证失败：检查 pg_hba.conf 中的认证方法（md5 vs trust）

• 远程访问：

  • 在 postgresql.conf 中设置 listen_addresses='*'
  • 在 pg_hba.conf 中添加远程主机授权条目

10.报错总结

1.postgres=> SELECT * FROM yys_diary LIMIT 10; ERROR: relation "yys_diary" does not exist LINE 1: SELECT * FROM yys_diary LIMIT 10;

这个错误：

ERROR:  relation "yys_diary" does not exist

说明你当前连接的数据库虽然是 postgres，但查询时 PostgreSQL 找不到 yys_diary 这个表，很可能是由于 当前不在正确的 schema（public）搜索路径下。

先设置默认 schema（推荐）

在 psql 中执行一次：

SET search_path TO public;

然后就可以正常运行：

SELECT * FROM yys_diary LIMIT 10;

🔍 检查表是否真的存在

执行这个命令查看你现在所有表：

\dt *.*

你应该看到：

 Schema |    Name     | Type  | Owner
--------+-------------+-------+-----------------------------
 public | yys_diary   | table | postgres.vbvkjiywdbocjpcjmtjs

如果你没看到这个表，说明表不在当前连接的数据库，或者还没导入成功。

总结

2.ERROR: prepared statement "stmtcache_xxx" already exists (SQLSTATE 42P05)

原因：
使用 pgx（或其他 PostgreSQL 驱动）时，数据库连接复用中缓存的 prepared statement 与新建的重复，导致冲突。

✅ 快速解决办法：执行 DEALLOCATE ALL;

方法一：psql 命令行中执行

1. 进入 PostgreSQL：

   psql "postgres://用户名:密码@主机:端口/数据库名"

   示例：

   psql "postgres://postgres:123456@localhost:5432/postgres"

2. 输入：

   DEALLOCATE ALL;

3. 回车即可清除当前 session 中所有 prepared statement。

方法二：在代码中执行（Go 示例）

_, _ = db.Exec(`DEALLOCATE ALL`)

建议仅在调试阶段或连接初始化后执行，避免频繁运行。

⚠️ 注意事项

• DEALLOCATE ALL 只影响当前连接。
• 若连接池中有多个连接，建议重启服务或用 pgxpool 控制逻辑。
• 若长期遇到该问题，请关闭 statement cache（例如：使用 pgx 时配置 PreferSimpleProtocol: true）。