PostgreSQL基本操作

一、PostgreSQL 基础语法速览

1. 连接数据库

# 切换到 postgres 用户并进入 psql
sudo -u postgres psql

# 或指定数据库
psql -U username -d dbname -h localhost -p 5432

2. 数据库与用户管理

-- 创建用户（角色）
CREATE USER myuser WITH PASSWORD 'mypass';

-- 创建数据库，并指定所有者
CREATE DATABASE mydb OWNER myuser;

-- 删除数据库（需先断开连接）
DROP DATABASE mydb;

-- 删除用户（需先清理其拥有的对象）
DROP USER myuser;

3. 表操作

-- 创建表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email TEXT UNIQUE,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 查看表结构
\d users

-- 插入数据
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');

-- 查询数据
SELECT * FROM users WHERE name = 'Alice';

-- 更新数据
UPDATE users SET email = 'new@example.com' WHERE id = 1;

-- 删除数据
DELETE FROM users WHERE id = 1;

4. 常用元命令（psql 内部命令）

命令	作用
\l	列出所有数据库
\c dbname	切换数据库
\dt	列出当前数据库所有表
\d tablename	查看表结构
\du	列出所有用户/角色
\q	退出 psql

---

二、PostgreSQL 数据导出工具：pg_dump

pg_dump 是 PG 官方提供的逻辑备份工具，可导出结构、数据或两者，支持多种格式。

✅ 导出表结构（不含数据）

pg_dump -U postgres -d mydb --schema-only > schema.sql

✅ 导出单表数据（不含结构）

pg_dump -U postgres -d mydb -t users --data-only > users_data.sql

✅ 导出整个数据库（结构+数据）

# 文本格式（默认，人类可读）
pg_dump -U postgres -d mydb > full_backup.sql

# 自定义二进制格式（推荐用于大库，支持并行恢复）
pg_dump -U postgres -d mydb -Fc > full_backup.dump

🔧 常用选项

选项	说明
-t table	指定表
-n schema	指定 schema
--clean	在建表前加 DROP 语句
--no-owner	不输出 owner 信息（便于迁移）
-Z 9	压缩（仅 -Fc 格式支持）

---

三、PostgreSQL 数据导入工具：psql 与 pg_restore

✅ 导入 SQL 文件（文本格式）

# 确保目标数据库已存在
createdb -U postgres mydb_new

# 导入
psql -U postgres -d mydb_new -f backup.sql

⚠️ 如果 SQL 文件包含 CREATE DATABASE，需先手动创建库，因为 psql 不能跨库执行。

✅ 导入自定义格式（.dump）

# 使用 pg_restore（支持选择性恢复）
pg_restore -U postgres -d mydb_new full_backup.dump

# 并行恢复（加速大库导入）
pg_restore -j 4 -U postgres -d mydb_new full_backup.dump

🔒 导入时的安全建议

# 遇错停止，避免部分导入
psql -v ON_ERROR_STOP=1 -U postgres -d mydb -f backup.sql

---

四、高级场景：全实例备份与恢复

备份所有数据库 + 用户权限

# 备份全局对象（用户、角色等）
pg_dumpall -U postgres --globals-only > globals.sql

# 备份所有数据库
pg_dumpall -U postgres > all_databases.sql

恢复整个实例

psql -U postgres -f globals.sql    # 先恢复用户
psql -U postgres -f all_databases.sql

✅ 这是迁移整个 PostgreSQL 服务器的标准做法。

---

五、常见问题与最佳实践

❓ Q1：导出时提示“peer authentication failed”？

解决：使用 sudo -u postgres 执行，或修改 /etc/postgresql/*/main/pg_hba.conf 将 local 认证方式改为 trust 或 md5。

❓ Q2：如何导出 CSV？

-- 在 psql 中执行
COPY (SELECT * FROM users) TO '/tmp/users.csv' WITH CSV HEADER;

注意：路径必须是数据库服务器本地路径，且 postgres 用户有写权限。

✅ 最佳实践

1. 生产环境备份：使用 pg_dump -Fc + 定期 pg_dumpall --globals-only；
2. 迁移数据库：导出时加 --no-owner --no-privileges，避免权限冲突；
3. 大表导出：优先用 COPY 格式（默认），而非 --inserts；

---

六、工具对比总结

工具	用途	输出格式	适用场景
pg_dump	单库/单表备份	SQL / custom / tar	日常备份、迁移
pg_dumpall	全实例备份	SQL	服务器迁移
psql	执行 SQL 脚本	—	导入 .sql 文件
pg_restore	恢复自定义格式	—	快速恢复大库
COPY	表级数据导出	CSV / binary	ETL、数据分析