postgresql

Name: postgresql
Author: chaterm

66installs12forks

How to install postgresql?

chaterm/terminal-skills/postgresql

$npx -y skills add chaterm/terminal-skills --skill postgresql

Installs into the current project.

Summary

PostgreSQL 数据库管理

Files · 1

View on GitHub

SKILL.md

1	# PostgreSQL 数据库管理
2
3	## 概述
4	PostgreSQL 数据库管理、扩展使用、查询优化等技能。
5
6	## 连接管理
7
8	```bash
9	# 本地连接
10	psql -U postgres
11	psql -U username -d database
12
13	# 远程连接
14	psql -h hostname -p 5432 -U username -d database
15
16	# 执行 SQL 文件
17	psql -U username -d database -f script.sql
18
19	# 执行单条命令
20	psql -U username -d database -c "SELECT version();"
21	```
22
23	### psql 常用命令
24	```sql
25	\l -- 列出数据库
26	\c dbname -- 切换数据库
27	\dt -- 列出表
28	\d tablename -- 表结构
29	\du -- 列出用户
30	\dn -- 列出 schema
31	\df -- 列出函数
32	\di -- 列出索引
33	\q -- 退出
34	\? -- 帮助
35	\timing -- 显示执行时间
36	\x -- 扩展显示模式
37	```
38
39	## 用户与权限
40
41	```sql
42	-- 创建用户
43	CREATE USER username WITH PASSWORD 'password';
44	CREATE ROLE username WITH LOGIN PASSWORD 'password';
45
46	-- 创建超级用户
47	CREATE USER admin WITH SUPERUSER PASSWORD 'password';
48
49	-- 授权
50	GRANT ALL PRIVILEGES ON DATABASE dbname TO username;
51	GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO username;
52	GRANT USAGE ON SCHEMA schema_name TO username;
53
54	-- 设置默认权限
55	ALTER DEFAULT PRIVILEGES IN SCHEMA public
56	GRANT SELECT ON TABLES TO readonly_user;
57
58	-- 查看权限
59	\du username
60	SELECT * FROM information_schema.role_table_grants WHERE grantee = 'username';
61
62	-- 修改密码
63	ALTER USER username WITH PASSWORD 'newpassword';
64	```
65
66	## 数据库操作
67
68	```sql
69	-- 创建数据库
70	CREATE DATABASE dbname;
71	CREATE DATABASE dbname OWNER username ENCODING 'UTF8';
72
73	-- 删除数据库
74	DROP DATABASE dbname;
75
76	-- 查看数据库大小
77	SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname))
78	FROM pg_database ORDER BY pg_database_size(pg_database.datname) DESC;
79
80	-- 查看表大小
81	SELECT relname, pg_size_pretty(pg_total_relation_size(relid))
82	FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;
83	```
84
85	## 备份与恢复
86
87	### pg_dump
88	```bash
89	# 备份单个数据库
90	pg_dump -U username dbname > backup.sql
91	pg_dump -U username -Fc dbname > backup.dump # 自定义格式
92
93	# 备份所有数据库
94	pg_dumpall -U postgres > all_backup.sql
95
96	# 只备份结构
97	pg_dump -U username --schema-only dbname > schema.sql
98
99	# 只备份数据
100	pg_dump -U username --data-only dbname > data.sql
101
102	# 备份特定表
103	pg_dump -U username -t tablename dbname > table.sql
104
105	# 并行备份（大数据库）
106	pg_dump -U username -Fd -j 4 dbname -f backup_dir/
107	```
108
109	### 恢复
110	```bash
111	# 恢复 SQL 格式
112	psql -U username -d dbname < backup.sql
113
114	# 恢复自定义格式
115	pg_restore -U username -d dbname backup.dump
116
117	# 并行恢复
118	pg_restore -U username -d dbname -j 4 backup_dir/
119
120	# 恢复到新数据库
121	createdb -U postgres newdb
122	pg_restore -U postgres -d newdb backup.dump
123	```
124
125	## 性能监控
126
127	```sql
128	-- 当前连接
129	SELECT * FROM pg_stat_activity;
130	SELECT pid, usename, application_name, state, query
131	FROM pg_stat_activity WHERE state != 'idle';
132
133	-- 终止连接
134	SELECT pg_terminate_backend(pid);
135
136	-- 锁信息
137	SELECT * FROM pg_locks WHERE NOT granted;
138
139	-- 查看锁等待
140	SELECT blocked_locks.pid AS blocked_pid,
141	blocking_locks.pid AS blocking_pid,
142	blocked_activity.usename AS blocked_user,
143	blocking_activity.usename AS blocking_user,
144	blocked_activity.query AS blocked_statement
145	FROM pg_catalog.pg_locks blocked_locks
146	JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
147	JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
148	JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
149	WHERE NOT blocked_locks.granted;
150
151	-- 表统计
152	SELECT relname, seq_scan, idx_scan, n_tup_ins, n_tup_upd, n_tup_del
153	FROM pg_stat_user_tables;
154
155	-- 索引使用情况
156	SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
157	FROM pg_stat_user_indexes;
158	```
159
160	## 查询优化
161
162	```sql
163	-- 执行计划
164	EXPLAIN SELECT * FROM table WHERE condition;
165	EXPLAIN ANALYZE SELECT * FROM table WHERE condition;
166	EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT * FROM table;
167
168	-- 更新统计信息
169	ANALYZE tablename;
170	ANALYZE;
171
172	-- 重建索引
173	REINDEX TABLE tablename;
174	REINDEX DATABASE dbname;
175
176	-- VACUUM
177	VACUUM tablename;
178	VACUUM FULL tablename; -- 回收空间
179	VACUUM ANALYZE tablename; -- 同时更新统计
180	```
181
182	## 常见场景
183
184	### 场景 1：主从复制状态
185	```sql
186	-- 主库
187	SELECT * FROM pg_stat_replication;
188
189	-- 从库
190	SELECT * FROM pg_stat_wal_receiver;
191
192	-- 复制延迟
193	SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))::INT AS lag_seconds;
194	```
195
196	### 场景 2：慢查询分析
197	```sql
198	-- 启用 pg_stat_statements
199	CREATE EXTENSION pg_stat_statements;
200
201	-- 查看慢查询
202	SELECT query, calls, total_time, mean_time, rows
203	FROM pg_stat_statements
204	ORDER BY total_time DESC LIMIT 10;
205
206	-- 重置统计
207	SELECT pg_stat_statements_reset();
208	```
209
210	### 场景 3：表维护
211	```sql
212	-- 查看表膨胀
213	SELECT schemaname, relname, n_dead_tup, n_live_tup,
214	round(n_dead_tup * 100.0 / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio
215	FROM pg_stat_user_tables
216	WHERE n_dead_tup > 1000
217	ORDER BY n_dead_tup DESC;
218
219	-- 清理膨胀
220	VACUUM FULL tablename;
221	```
222
223	## 故障排查
224
225	\| 问题 \| 排查方法 \|
226	\|------\|----------\|
227	\| 连接数过多 \| `pg_stat_activity`, 检查 max_connections \|
228	\| 查询慢 \| `EXPLAIN ANALYZE`, 检查索引 \|
229	\| 锁等待 \| `pg_locks`, `pg_stat_activity` \|
230	\| 磁盘满 \| 检查 WAL、清理旧数据 \|
231	\| 复制延迟 \| `pg_stat_replication` \|

People who liked this also viewed

6 picks

Type

azure-storageAzure Storage Services including Blob Storage, File Shares, Queue Storage, Table Storage, and Data Lake. Answers questions about storage access tiers (hot, cool, cold, archive), when to use each tier,SkillsJul 2026471k1.3k
supabase-postgres-best-practicesPostgres performance optimization and best practices from Supabase. Use this skill when writing, reviewing, or optimizing Postgres queries, schema designs, or database configurations.SkillsJul 2026302k2.4k
supabaseUse when doing ANY task involving Supabase. Triggers: Supabase products (Database, Auth, Edge Functions, Realtime, Storage, Vectors, Cron, Queues); client libraries and SSR integrations (supabase-js, SkillsJul 2026179k2.4k
convex-quickstartCreates or adds Convex to an app. Use for new Convex projects, npm create convex@latest, frontend setup, env vars, or the first npx convex dev run.SkillsJul 202690k39
convex-performance-auditAudits Convex performance for reads, subscriptions, write contention, and function limits. Use for slow features, insights findings, OCC conflicts, or read amplification.SkillsJul 202689k39
convex-setup-authSets up Convex auth, identity mapping, and access control. Use for login, auth providers, users tables, protected functions, or roles in a Convex app.SkillsJul 202689k39

postgresql

66installs12forks

View on GitHub

How to install postgresql?

chaterm/terminal-skills/postgresql

$npx -y skills add chaterm/terminal-skills --skill postgresql

Installs into the current project.

Summary

PostgreSQL 数据库管理

Files · 1

View on GitHub

SKILL.md

1	# PostgreSQL 数据库管理
2
3	## 概述
4	PostgreSQL 数据库管理、扩展使用、查询优化等技能。
5
6	## 连接管理
7
8	```bash
9	# 本地连接
10	psql -U postgres
11	psql -U username -d database
12
13	# 远程连接
14	psql -h hostname -p 5432 -U username -d database
15
16	# 执行 SQL 文件
17	psql -U username -d database -f script.sql
18
19	# 执行单条命令
20	psql -U username -d database -c "SELECT version();"
21	```
22
23	### psql 常用命令
24	```sql
25	\l -- 列出数据库
26	\c dbname -- 切换数据库
27	\dt -- 列出表
28	\d tablename -- 表结构
29	\du -- 列出用户
30	\dn -- 列出 schema
31	\df -- 列出函数
32	\di -- 列出索引
33	\q -- 退出
34	\? -- 帮助
35	\timing -- 显示执行时间
36	\x -- 扩展显示模式
37	```
38
39	## 用户与权限
40
41	```sql
42	-- 创建用户
43	CREATE USER username WITH PASSWORD 'password';
44	CREATE ROLE username WITH LOGIN PASSWORD 'password';
45
46	-- 创建超级用户
47	CREATE USER admin WITH SUPERUSER PASSWORD 'password';
48
49	-- 授权
50	GRANT ALL PRIVILEGES ON DATABASE dbname TO username;
51	GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO username;
52	GRANT USAGE ON SCHEMA schema_name TO username;
53
54	-- 设置默认权限
55	ALTER DEFAULT PRIVILEGES IN SCHEMA public
56	GRANT SELECT ON TABLES TO readonly_user;
57
58	-- 查看权限
59	\du username
60	SELECT * FROM information_schema.role_table_grants WHERE grantee = 'username';
61
62	-- 修改密码
63	ALTER USER username WITH PASSWORD 'newpassword';
64	```
65
66	## 数据库操作
67
68	```sql
69	-- 创建数据库
70	CREATE DATABASE dbname;
71	CREATE DATABASE dbname OWNER username ENCODING 'UTF8';
72
73	-- 删除数据库
74	DROP DATABASE dbname;
75
76	-- 查看数据库大小
77	SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname))
78	FROM pg_database ORDER BY pg_database_size(pg_database.datname) DESC;
79
80	-- 查看表大小
81	SELECT relname, pg_size_pretty(pg_total_relation_size(relid))
82	FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;
83	```
84
85	## 备份与恢复
86
87	### pg_dump
88	```bash
89	# 备份单个数据库
90	pg_dump -U username dbname > backup.sql
91	pg_dump -U username -Fc dbname > backup.dump # 自定义格式
92
93	# 备份所有数据库
94	pg_dumpall -U postgres > all_backup.sql
95
96	# 只备份结构
97	pg_dump -U username --schema-only dbname > schema.sql
98
99	# 只备份数据
100	pg_dump -U username --data-only dbname > data.sql
101
102	# 备份特定表
103	pg_dump -U username -t tablename dbname > table.sql
104
105	# 并行备份（大数据库）
106	pg_dump -U username -Fd -j 4 dbname -f backup_dir/
107	```
108
109	### 恢复
110	```bash
111	# 恢复 SQL 格式
112	psql -U username -d dbname < backup.sql
113
114	# 恢复自定义格式
115	pg_restore -U username -d dbname backup.dump
116
117	# 并行恢复
118	pg_restore -U username -d dbname -j 4 backup_dir/
119
120	# 恢复到新数据库
121	createdb -U postgres newdb
122	pg_restore -U postgres -d newdb backup.dump
123	```
124
125	## 性能监控
126
127	```sql
128	-- 当前连接
129	SELECT * FROM pg_stat_activity;
130	SELECT pid, usename, application_name, state, query
131	FROM pg_stat_activity WHERE state != 'idle';
132
133	-- 终止连接
134	SELECT pg_terminate_backend(pid);
135
136	-- 锁信息
137	SELECT * FROM pg_locks WHERE NOT granted;
138
139	-- 查看锁等待
140	SELECT blocked_locks.pid AS blocked_pid,
141	blocking_locks.pid AS blocking_pid,
142	blocked_activity.usename AS blocked_user,
143	blocking_activity.usename AS blocking_user,
144	blocked_activity.query AS blocked_statement
145	FROM pg_catalog.pg_locks blocked_locks
146	JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
147	JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
148	JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
149	WHERE NOT blocked_locks.granted;
150
151	-- 表统计
152	SELECT relname, seq_scan, idx_scan, n_tup_ins, n_tup_upd, n_tup_del
153	FROM pg_stat_user_tables;
154
155	-- 索引使用情况
156	SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
157	FROM pg_stat_user_indexes;
158	```
159
160	## 查询优化
161
162	```sql
163	-- 执行计划
164	EXPLAIN SELECT * FROM table WHERE condition;
165	EXPLAIN ANALYZE SELECT * FROM table WHERE condition;
166	EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT * FROM table;
167
168	-- 更新统计信息
169	ANALYZE tablename;
170	ANALYZE;
171
172	-- 重建索引
173	REINDEX TABLE tablename;
174	REINDEX DATABASE dbname;
175
176	-- VACUUM
177	VACUUM tablename;
178	VACUUM FULL tablename; -- 回收空间
179	VACUUM ANALYZE tablename; -- 同时更新统计
180	```
181
182	## 常见场景
183
184	### 场景 1：主从复制状态
185	```sql
186	-- 主库
187	SELECT * FROM pg_stat_replication;
188
189	-- 从库
190	SELECT * FROM pg_stat_wal_receiver;
191
192	-- 复制延迟
193	SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))::INT AS lag_seconds;
194	```
195
196	### 场景 2：慢查询分析
197	```sql
198	-- 启用 pg_stat_statements
199	CREATE EXTENSION pg_stat_statements;
200
201	-- 查看慢查询
202	SELECT query, calls, total_time, mean_time, rows
203	FROM pg_stat_statements
204	ORDER BY total_time DESC LIMIT 10;
205
206	-- 重置统计
207	SELECT pg_stat_statements_reset();
208	```
209
210	### 场景 3：表维护
211	```sql
212	-- 查看表膨胀
213	SELECT schemaname, relname, n_dead_tup, n_live_tup,
214	round(n_dead_tup * 100.0 / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio
215	FROM pg_stat_user_tables
216	WHERE n_dead_tup > 1000
217	ORDER BY n_dead_tup DESC;
218
219	-- 清理膨胀
220	VACUUM FULL tablename;
221	```
222
223	## 故障排查
224
225	\| 问题 \| 排查方法 \|
226	\|------\|----------\|
227	\| 连接数过多 \| `pg_stat_activity`, 检查 max_connections \|
228	\| 查询慢 \| `EXPLAIN ANALYZE`, 检查索引 \|
229	\| 锁等待 \| `pg_locks`, `pg_stat_activity` \|
230	\| 磁盘满 \| 检查 WAL、清理旧数据 \|
231	\| 复制延迟 \| `pg_stat_replication` \|

People who liked this also viewed

6 picks

Type