登录
1
2
3
4
5
| psql -h hostname -d dbname -U username
成功连接后，你将看到类似以下的提示符，表示你已经连接到数据库：
dbname=>
|
查看连接总数
1
| select count(*) from pg_stat_activity;
|
查看所有连接的用户
1
| select * from pg_stat_activity;
|
查询当前进程
| 查询目的 | 主要命令（可选条件） | 关键字段说明（部分） |
|———————–|—————————————————————————————————————–|—————————————————————————————|
| 🔍 查看所有活动连接/进程 | SELECT * FROM pg_stat_activity;157 | pid(进程ID), usename(用户名), datname(数据库名), query(执行语句), state(状态), backend_start(连接开始时间) |
| ⏳ 查看活跃查询（排除空闲） | SELECT * FROM pg_stat_activity WHERE state = ‘active’; | 同上，重点关注 state=’active’ 的记录 |
| 🐢 查看长时间运行的查询 | SELECT * FROM pg_stat_activity WHERE state = ‘active’ AND now() - query_start > interval ‘5 minutes’; (可调整时间间隔) | 同上，额外关注 query_start(查询开始时间)，计算运行时长 |
| 🔒 查看锁等待情况（关联查询） | 结合 pg_locks 和 pg_stat_activity 视图进行关联查询 | 需要关联字段如 pid，关注 wait_event_type 和 wait_event3 |
| ⚙️ 查看后台进程状态（如写进程） | SELECT * FROM pg_stat_bgwriter; | 提供后台写入进程的统计信息 |
| 📊 查看复制进程状态（如果配置了流复制） | SELECT * FROM pg_stat_replication; | 显示复制连接的详细信息（如进程ID、状态） |
查看后再终止（推荐做法）
1
2
3
4
5
6
7
8
9
10
11
12
| -- 先查看要终止的进程详情
SELECT pid, usename, datname, query, state, query_start
FROM pg_stat_activity
WHERE state = 'idle'
AND (now() - state_change) > interval '5 minutes';
--------- 一定要确认后再执行
-- 确认无误后再执行终止
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
AND (now() - state_change) > interval '5 minutes';
|
终止单个特定进程
1
2
3
4
5
| -- 终止单个特定进程
SELECT pg_terminate_backend(pid); -- 将 pid 替换为实际的进程ID
-- 示例：终止进程ID为 12345 的连接
SELECT pg_terminate_backend(12345);
|
列出所有库
切换数据库
列出所有表
1
2
3
4
5
| \dt
列出当前数据库中的所有表
\dt schema_name.*
|
列出表结构
查询所有数据库存储大小
1
2
3
4
5
| select datname,pg_size_pretty(pg_database_size(oid)) from pg_database;
k可以快速查看
\l+
|
查看表空间的占用情况, 列出所有数据库的大小
1
2
3
4
5
6
7
| SELECT
pg_database.datname AS database_name,
pg_size_pretty(pg_database_size(pg_database.datname)) AS size
FROM
pg_database
ORDER BY
pg_database_size(pg_database.datname) DESC;
|
列出当前所有表及占用空间
查询将列出当前数据库中所有用户表的模式、名称以及占用的空间大小，并按照表占用的空间大小降序排列
1
2
3
4
5
6
7
8
9
10
| SELECT
table_schema,
table_name,
pg_size_pretty(pg_total_relation_size(table_schema || '.' || table_name)) AS total_table_size
FROM
information_schema.tables
WHERE
table_type = 'BASE TABLE'
ORDER BY
pg_total_relation_size(table_schema || '.' || table_name) DESC;
|
查找最大的表
1
2
3
4
5
6
7
8
| SELECT
table_schema || '.' || table_name AS table_full_name,
pg_size_pretty(pg_total_relation_size(table_schema || '.' || table_name)) AS total_table_size
FROM
information_schema.tables
ORDER BY
pg_total_relation_size(table_schema || '.' || table_name) DESC
LIMIT 1;
|
删除表数据
1
2
3
4
5
6
| # 删除表
DELETE FROM table_name;
# 删除数据库中的数据
TRUNCATE TABLE your_table_name;
|
查看 dead tuples
1
2
3
| SELECT relname, n_dead_tup, n_live_tup
FROM pg_stat_user_tables
WHERE relname='nodestore_node';
|
| |
|
|
| relname |
n_dead_tup |
n_live_tup |
| nodestore_node |
12397323 |
300268042 |
1
2
| - n_dead_tup：需要回收的记录数
- n_live_tup：当前有效记录数
|
PgSQL事务日志比较大(WAL - Write-Ahead Log 文件)
PostgreSQL 的事务日志（通常指 WAL - Write-Ahead Log 文件）的清理是一个自动管理的过程，但理解其机制并正确配置对于维护数据库健康至关重要。
核心概念：WAL 和归档
WAL 文件（位于 pg_wal 目录中）是保证数据一致性和实现故障恢复的核心。它们的清理不是通过手动删除文件来完成的，而是由 PostgreSQL 的内部机制在满足特定条件后自动进行的。
清理的关键在于 WAL 文件的“归档” 和 检查点（Checkpoint）。
查看 WAL 使用情况
-- 查看当前WAL写入位置和归档状态
SELECT * FROM pg_stat_archiver;
-- 查看当前WAL文件信息
SELECT pg_current_wal_lsn(), pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0') AS bytes_since_start;
-- 估算WAL生成速率（需要在不同时间点查询并计算差值）
SELECT pg_current_wal_lsn();
-- 等待一段时间后再次查询
SELECT pg_current_wal_lsn();
-- 然后使用 pg_wal_lsn_diff 计算差值
SELECT pg_wal_lsn_diff('LSN2', 'LSN1') / 1024 / 1024 AS mb_generated;
查看归档状态
SELECT * FROM pg_stat_archiver;
| |
|
|
|
|
|
|
| archived_count |
last_archived_wal |
last_archived_time |
failed_count |
last_failed_wal |
last_failed_time |
stats_reset |
| 11930 |
0000000200000C2F000000F8 |
2025-09-05 09:57:04.646939+08 |
1 |
00000002.history |
2025-09-03 21:27:49.400382+08 |
2025-09-03 21:20:27.847903+08 |
last_failed_wal：最近一次归档失败的WAL文件。如果此字段不为空，说明归档有问题。
last_failed_time：最近一次失败的时间。
failed_count：失败计数。
| 字段 |
值 |
解读 |
| archived_count |
11930 |
已成功归档的WAL文件总数非常多，说明归档进程长期在运行且大部分时间是正常的。 |
| last_archived_wal |
0000000200000C2F000000F8 |
最后一个成功归档的WAL文件名。其命名包含时间线、逻辑ID和物理ID信息。 |
| last_archived_time |
2025-09-05 09:57:04.646939+08 |
最后一次成功归档的时间（北京时间）。这表明归档最近是正常的。 |
| failed_count |
1 |
归档失败的总次数。仅有1次失败，相对于近1.2万次成功，失败率极低，表明问题只是偶发。 |
| last_failed_wal |
00000002.history |
最后一次失败的文件是 .history 文件。这类文件包含时间线变化历史，通常在新时间线分支（如从备份恢复后）时创建。 |
| last_failed_time |
2025-09-03 21:27:49.400382+08 |
最后一次失败发生在9月3日晚上。失败后系统又成功归档了，说明问题已自动解决或已过去。 |
| stats_reset |
2025-09-03 21:20:27.847903+08 |
统计信息在9月3日晚刚被重置过。failed_count为1是重置后发生的失败。 |
查看PgSQL指定配置
1
| SELECT name, setting, unit FROM pg_settings WHERE name IN ('max_wal_size', 'min_wal_size', 'checkpoint_timeout', 'checkpoint_completion_target');
|
| | | |
| — | — | — |
| name | setting | unit
| checkpoint_completion_target | 0.9 |
| checkpoint_timeout | 300 | s
| max_wal_size | 1024 | MB
| min_wal_size | 80 | MB