PostgreSQL数据库管理实战指南

从基础命令到高级管理的完整教程

piliqiu piliqiu 收录于 PostgreSQL
 约 1200 字 预计阅读 6 分钟 
警告
本文最后更新于 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;
更新于 2024-08-26 
在编辑器中打开
PostgreSQL
返回 | 主页
0%