PostgreSQL数据库管理实战指南 -

piliqiu 收录于 PostgreSQL

2024-08-26 约 1500 字预计阅读 7 分钟

警告

本文最后更新于 2024-08-26，文中内容可能已过时。

数据库基础信息查询

查看表字段类型和长度

用于获取表中字段的详细信息，包括数据类型、长度、精度等。

参数说明：

column_name: 字段名称
data_type: 字段的数据类型
character_maximum_length: 字符串类型的最大长度
numeric_precision: 数值类型的精度（总位数）
numeric_scale: 数值类型的小数位数

注意事项：

非字符型数据类型的 character_maximum_length 为 NULL
非数值型数据类型的 numeric_precision 和 numeric_scale 为 NULL

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


SELECT 
    column_name,
    data_type,
    character_maximum_length,
    numeric_precision,
    numeric_scale
FROM 
    information_schema.columns
WHERE 
    table_schema = 'public'
    AND table_name = '表名';

查看数据库列表

1

SELECT datname FROM pg_database;

查看当前数据库的所有表

1

SELECT tablename FROM pg_tables WHERE schemaname = 'public';

数据库锁管理

查看当前所有进程

1
2
3


SELECT pid, usename, application_name, client_addr, state, query 
FROM pg_stat_activity 
WHERE state = 'active';

查询特定表的锁信息

1
2
3
4
5
6
7


-- 1. 查询表的OID
SELECT oid FROM pg_class WHERE relname = '表名';

-- 2. 使用上面查询到的OID查询锁信息
SELECT pid, locktype, mode, granted 
FROM pg_locks 
WHERE relation = '上面查询到的OID';

释放进程锁定

1
2


-- 优雅地取消查询
SELECT pg_cancel_backend('进程ID');

强制终止进程

1
2


-- 强制终止进程
SELECT pg_terminate_backend('进程ID');

数据库用户和权限管理

创建用户

1
2


-- 创建新用户
CREATE USER 用户名 WITH PASSWORD '密码';

修改用户密码

1
2


-- 修改用户密码
ALTER USER 用户名 WITH PASSWORD '新密码';

赋予用户权限

1
2
3
4
5
6
7
8


-- 赋予数据库访问权限
GRANT ALL PRIVILEGES ON DATABASE 数据库名 TO 用户名;

-- 赋予表操作权限
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO 用户名;

-- 允许用户创建表
GRANT CREATE ON SCHEMA public TO 用户名;

撤销用户权限

1
2
3
4
5


-- 撤销数据库权限
REVOKE ALL PRIVILEGES ON DATABASE 数据库名 FROM 用户名;

-- 撤销表权限
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM 用户名;

数据备份和恢复

备份数据库

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14


# 备份整个数据库
pg_dump -U 用户名 -h 主机名 数据库名 > backup.sql

# 备份特定表
pg_dump -U 用户名 -h 主机名 -t 表名 数据库名 > backup_table.sql

# 压缩备份
pg_dump -U 用户名 -h 主机名 数据库名 | gzip > backup.sql.gz

# 备份仅结构（不包含数据）
pg_dump -U 用户名 -h 主机名 --schema-only 数据库名 > schema_only.sql

# 备份仅数据
pg_dump -U 用户名 -h 主机名 --data-only 数据库名 > data_only.sql

恢复数据库

1
2
3
4
5
6
7
8


# 从SQL文件恢复
psql -U 用户名 -h 主机名 -d 数据库名 < backup.sql

# 从压缩文件恢复
gunzip -c backup.sql.gz | psql -U 用户名 -h 主机名 -d 数据库名

# 恢复特定表
psql -U 用户名 -h 主机名 -d 数据库名 < backup_table.sql

导出数据为CSV格式

1
2
3
4
5
6
7


-- 导出整个表到CSV
COPY 表名 TO '/绝对路径/到/文件.csv' WITH CSV HEADER;

-- 导出查询结果到CSV
COPY (SELECT * FROM 表名 WHERE 条件) 
TO '/绝对路径/到/过滤数据.csv' 
WITH CSV HEADER;

Docker部署PostgreSQL

拉取并启动PostgreSQL容器

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


# 拉取镜像
docker pull pgvector/pgvector:0.8.0-pg17

# 启动容器
docker run --name 容器名 \
  -p 5432:5432 \
  -e POSTGRES_PASSWORD=强密码 \
  -v /本地数据路径:/var/lib/postgresql/data \
  --restart=always \
  -d pgvector/pgvector:0.8.0-pg17

进入容器操作

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17


# 进入容器执行psql
docker exec -it 容器名 psql -U postgres

# 创建新用户
CREATE USER 用户名 WITH PASSWORD '密码';

# 赋予管理员权限（谨慎使用）
ALTER USER 容器名 WITH SUPERUSER;

# 创建数据库
CREATE DATABASE 数据库名;

# 切换到新数据库
\c 数据库名

# 安装pgvector扩展
CREATE EXTENSION vector;

数据导入导出

方式一：执行SQL文件

适用于将所有建表语句整合到一个SQL文件中。

1
2
3
4
5
6
7
8


# 进入容器
docker exec -it 容器名 psql -U postgres

# 切换到目标数据库
\c 数据库名

# 执行外部SQL文件
\i /绝对路径/到/文件.sql

方式二：批量执行SQL文件

适用于有多个SQL文件需要按顺序执行的情况。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


# 创建临时容器执行批量SQL
docker run --rm \
  -v /SQL文件目录:/docker-entrypoint-initdb.d \
  -e POSTGRES_PASSWORD=密码 \
  pgvector/pgvector:0.8.0-pg17

# 或者启动包含SQL文件的持久容器
docker run --name 容器名 \
  -v /SQL目录:/docker-entrypoint-initdb.d \
  -p 5432:5432 \
  -e POSTGRES_PASSWORD=密码 \
  -d pgvector/pgvector:0.8.0-pg17

导入CSV数据

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


-- 创建表结构
CREATE TABLE 导入表 (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 导入CSV数据
\copy 导入表 FROM '/绝对路径/到/数据.csv' WITH CSV HEADER;

性能监控和优化

查看慢查询

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15


-- 启用慢查询日志（设置500ms以上为慢查询）
ALTER SYSTEM SET log_min_duration_statement = 500;
SELECT pg_reload_conf();

-- 查看当前慢查询统计
SELECT query, mean_time, calls, total_time,
       (mean_time * calls) as total_execution_time
FROM pg_stat_statements 
ORDER BY mean_time DESC 
LIMIT 10;

-- 查看当前活跃查询
SELECT pid, now() - pg_stat_activity.query_start AS duration, query
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';

查看数据库连接数

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15


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

-- 按用户统计连接数
SELECT usename, count(*) as connection_count
FROM pg_stat_activity
GROUP BY usename;

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

-- 查看当前连接配置
SELECT name, setting, unit, context 
FROM pg_settings 
WHERE name LIKE '%connection%';

查看数据库大小

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20


-- 查看所有数据库大小
SELECT datname, 
       pg_size_pretty(pg_database_size(datname)) as size,
       pg_database_size(datname) as size_bytes
FROM pg_database
ORDER BY size_bytes DESC;

-- 查看特定数据库大小
SELECT pg_size_pretty(pg_database_size('数据库名')) as database_size;

-- 查看当前数据库所有表的大小
SELECT schemaname, tablename, 
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size,
       pg_total_relation_size(schemaname||'.'||tablename) as size_bytes
FROM pg_tables 
WHERE schemaname = 'public'
ORDER BY size_bytes DESC;

-- 查看单个表大小
SELECT pg_size_pretty(pg_total_relation_size('表名')) as table_size;

查看索引使用情况

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


-- 查看索引使用统计
SELECT schemaname, tablename, indexname, 
       idx_scan, idx_tup_read, idx_tup_fetch,
       pg_size_pretty(pg_relation_size(indexrelid)) as index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- 查看未使用的索引（可能需要删除）
SELECT schemaname, tablename, indexname,
       pg_size_pretty(pg_relation_size(indexrelid)) as index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY index_size DESC;

常用维护命令

清理和重建索引

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17


-- 查看索引使用情况
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE tablename = '表名'
ORDER BY idx_scan DESC;

-- 分析表统计信息
ANALYZE 表名;

-- 重建索引（整库）
REINDEX DATABASE 数据库名;

-- 重建特定表索引
REINDEX TABLE 表名;

-- 重建特定索引
REINDEX INDEX 索引名;

清理数据库统计信息

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21


-- 更新统计信息
ANALYZE;

-- 清理数据库
VACUUM;

-- 清理特定表
VACUUM 表名;

-- 完全清理
VACUUM FULL 表名;

-- 查看表统计信息
SELECT schemaname, tablename, 
       n_tup_ins as inserts, 
       n_tup_upd as updates, 
       n_tup_del as deletes,
       n_live_tup, 
       n_dead_tup
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

检查数据完整性

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21


-- 检查表完整性
SELECT schemaname, tablename, n_dead_tup, n_live_tup,
       ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) as dead_tuple_percent
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

-- 查看表大小和膨胀情况
SELECT schemaname, tablename,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as total_size,
       pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) as table_size,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename)) as index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- 查看数据库连接详情
SELECT pid, usename, application_name, client_addr, state, 
       now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start;

数据库健康检查

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22


-- 检查数据库总体健康状况
SELECT 
    'Total Databases' as metric,
    count(*) as value
FROM pg_database
UNION ALL
SELECT 
    'Total Tables',
    count(*)
FROM pg_tables
UNION ALL
SELECT 
    'Active Connections',
    count(*)
FROM pg_stat_activity
WHERE state = 'active';

-- 检查数据库配置
SELECT name, setting, unit, context 
FROM pg_settings 
WHERE context IN ('superuser', 'postmaster')
ORDER BY name;

PostgreSQL 18 安装 PostGIS 与 pgvector 扩展

一、安装 PostGIS 3.6.2

PostGIS 是一个独立的扩展，我们需要下载 Windows 安装程序，它会自动将文件复制到 PostgreSQL 目录并创建扩展。

1. 下载安装包

访问 PostGIS 官方下载站：

https://download.osgeo.org/postgis/windows/pg18/

找到文件名中包含 postgis-bundle-pg18x64-setup-3.6.2-1.exe 的安装程序（约 100 MB），点击下载。

2. 运行安装程序

双击 .exe 文件，欢迎界面点击 Next。
阅读许可协议，勾选 I accept the agreement，然后点击 Next。
选择组件（重要）：
保持默认勾选 PostGIS Bundle，同时务必勾选 Create spatial database。
其他选项保持默认不勾选即可。
勾选 Create spatial database 可以让安装程序自动在您指定的数据库中启用 PostGIS，省去手动执行 CREATE EXTENSION 的麻烦。
选择安装位置：
安装程序会自动检测 PostgreSQL 18 的安装目录（默认为 C:\Program Files\PostgreSQL\18）。
请确认路径正确（即能看到 bin、data、lib 等子文件夹），然后点击 Next。
数据库连接信息：
安装程序会弹出对话框，要求输入 PostgreSQL 的连接信息：
- Host：localhost（或 127.0.0.1）
- Port：5432（默认）
- Superuser：postgres（默认）
- Password：安装 PostgreSQL 时设置的密码
- Database：选择你想要启用 PostGIS 的数据库（例如 postgres 或你新建的 gisdb）
点击 Next 开始安装。完成后点击 Finish。

3. 验证 PostGIS 是否安装成功

打开 SQL Shell (psql) 或使用 pgAdmin，连接到目标数据库，执行：

1

SELECT PostGIS_full_version();

如果返回类似以下信息（包含 POSTGIS="3.6.2"），即表示成功：

POSTGIS="3.6.2" [GEOS] ... (等)

也可以直接测试 box2d 类型是否存在：

1

SELECT 'BOX(0 0, 1 1)'::box2d;

不报错即为正常。

💡 常见问题：如果之前手动运行过旧版 PostGIS 的 SQL 脚本，可能会遇到 spatial_ref_sys_pkey 重复键错误。
解决方法：删除冲突的 SRID 记录后重试 CREATE EXTENSION postgis;。
1
2
DELETE FROM spatial_ref_sys WHERE srid = 2000;
CREATE EXTENSION postgis;
更彻底的方式是先用 DROP EXTENSION postgis CASCADE; 清理后再重新创建。

二、安装 pgvector 0.8.2（支持 halfvec 类型）

pgvector 官方并未提供 Windows 预编译安装程序，但社区开发者已经为我们打包好了。我们只需要下载并解压到 PostgreSQL 目录即可。

1. 下载预编译文件

访问 GitHub 上的发布页面：

https://github.com/andreiramani/pgvector_pgsql_windows/releases

找到适配 PostgreSQL 18 的压缩包，文件名通常为：

vector.v0.8.2-pg18.zip

（版本号可能会更新，建议选择最新的 v0.8.x 且明确标注 pg18 的版本）

2. 解压到 PostgreSQL 安装目录

将下载的 .zip 文件解压。
解压后你会看到两个文件夹：lib 和 share。
将这两个文件夹 复制并覆盖 到你的 PostgreSQL 18 根目录（例如 C:\Program Files\PostgreSQL\18）。
如果提示覆盖文件，请选择 “是”。

⚠️ 注意：如果你的 PostgreSQL 安装在不同路径，请相应调整。

3. 在数据库中创建扩展

打开 psql 或 pgAdmin，连接到目标数据库（与启用 PostGIS 的数据库可以是同一个），执行：

1

CREATE EXTENSION vector;

如果没有任何错误，扩展即安装成功。

4. 验证 halfvec 类型及向量功能

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16


-- 检查 halfvec 类型是否存在
SELECT NULL::halfvec;
-- 应该返回 "(null)" 而不报错

-- 创建一个测试表并插入半精度向量数据
CREATE TABLE test_vectors (
    id serial,
    embedding halfvec(3)
);

INSERT INTO test_vectors (embedding) VALUES ('[1,2,3]'), ('[4,5,6]');

-- 执行向量距离查询（欧氏距离）
SELECT * FROM test_vectors
ORDER BY embedding <-> '[3,1,2]'
LIMIT 5;

如果能正常返回结果，说明 pgvector 和 halfvec 均已完全可用。

💡 提示：halfvec 是 pgvector 提供的一种优化数据类型，使用 16 位浮点数存储向量，适合内存敏感或维度非常高的场景。普通的 vector 类型（使用 32 位浮点数）同样可用。

三、同时使用 PostGIS 和 pgvector

PostGIS 和 pgvector 是完全独立的扩展，互不干扰。你可以在同一个数据库中同时启用它们：

1
2


CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS vector;

之后你就可以在同一个表里既存储地理几何类型（geometry、geography），也存储嵌入向量（vector 或 halfvec），实现空间数据与向量数据的联合查询。

例如：

1
2
3
4
5


CREATE TABLE hybrid_data (
    id serial primary key,
    location geometry(Point, 4326),
    embedding halfvec(128)
);