# 1. 查询所有表
$ psql -U fgedu -d fgedudb -c ”
SELECT table_schema, table_name, table_type
FROM information_schema.tables
WHERE table_schema NOT IN (‘pg_catalog’, ‘information_schema’)
ORDER BY table_schema, table_name;
”

table_schema | table_name | table_type
————–+——————-+————
public | fgedu_orders | BASE TABLE
public | fgedu_customers | BASE TABLE
public | fgedu_products | BASE TABLE
public | fgedu_categories | BASE TABLE
(4 rows)

# 2. 查询表的列信息
$ psql -U fgedu -d fgedudb -c ”
SELECT column_name, data_type, character_maximum_length, is_nullable
FROM information_schema.columns
WHERE table_schema = ‘public’ AND table_name = ‘fgedu_orders’
ORDER BY ordinal_position;
”

column_name | data_type | character_maximum_length | is_nullable
————-+——————-+————————–+————-
order_id | integer | | NO
customer_id | integer | | NO
product_id | integer | | NO
amount | numeric | | NO
status | character varying | 20 | YES
created_at | timestamp | | NO
(6 rows)

# 3. 查询表的约束信息
$ psql -U fgedu -d fgedudb -c ”
SELECT constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_schema = ‘public’ AND table_name = ‘fgedu_orders’;
”

constraint_name | constraint_type
—————–+—————–
fgedu_orders_pkey | PRIMARY KEY
(1 row)

# 4. 查询主键列
$ psql -U fgedu -d fgedudb -c ”
SELECT column_name
FROM information_schema.key_column_usage
WHERE table_schema = ‘public’ AND table_name = ‘fgedu_orders’
AND constraint_name = ‘fgedu_orders_pkey’;
”

column_name
————-
order_id
(1 row)

# 1. 查询表的索引
$ psql -U fgedu -d fgedudb -c ”
SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname = ‘public’ AND tablename = ‘fgedu_orders’;
”

indexname | indexdef
——————–+————————————————————-
fgedu_orders_pkey | CREATE UNIQUE INDEX fgedu_orders_pkey ON public.fgedu_orders USING btree (order_id)
idx_orders_customer | CREATE INDEX idx_orders_customer ON public.fgedu_orders USING btree (customer_id)
idx_orders_created | CREATE INDEX idx_orders_created ON public.fgedu_orders USING btree (created_at)
(3 rows)

# 2. 查询表的统计信息
$ psql -U fgedu -d fgedudb -c ”
SELECT relname, n_live_tup, n_dead_tup, last_vacuum, last_analyze
FROM pg_stat_user_tables
WHERE schemaname = ‘public’ AND relname = ‘fgedu_orders’;
”

relname | n_live_tup | n_dead_tup | last_vacuum | last_analyze
—————+————+————+——————————-+——————————-
fgedu_orders | 100000 | 0 | 2026-04-07 10:00:00.123456+08 | 2026-04-07 10:00:00.123456+08
(1 row)

# 1. 查询表权限
$ psql -U fgedu -d fgedudb -c ”
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_schema = ‘public’ AND table_name = ‘fgedu_orders’;
”

grantee | privilege_type
———-+—————-
fgedu | ALL PRIVILEGES
fgedu_fgapp | SELECT
fgedu_fgapp | INSERT
fgedu_fgapp | UPDATE
fgedu_read | SELECT
(4 rows)

# 2. 查询角色权限
$ psql -U fgedu -d fgedudb -c ”
SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb
FROM pg_roles
WHERE rolname LIKE ‘fgedu%’;
”

rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb
————+———-+————+—————+————-
fgedu | t | t | t | t
fgedu_fgapp | f | t | f | f
fgedu_read | f | t | f | f
fgedu_repl | f | t | f | f
(4 rows)

# 生成数据字典

echo “PostgreSQL数据字典”
echo “生成时间: $(date)”
echo “数据库: fgedudb”
echo “==========================================”

echo “\n1. 表信息”
echo “==========================================”
psql -U fgedu -d fgedudb -c ”
SELECT
table_schema,
table_name,
table_type,
CASE
WHEN table_type = ‘BASE TABLE’ THEN ‘表’
WHEN table_type = ‘VIEW’ THEN ‘视图’
ELSE table_type
END AS 类型
FROM information_schema.tables
WHERE table_schema NOT IN (‘pg_catalog’, ‘information_schema’)
ORDER BY table_schema, table_name;
”

echo “\n2. 列信息”
echo “==========================================”
psql -U fgedu -d fgedudb -c ”
SELECT
table_schema,
table_name,
column_name,
data_type,
CASE
WHEN is_nullable = ‘YES’ THEN ‘是’
ELSE ‘否’
END AS 可空,
column_default AS 默认值
FROM information_schema.columns
WHERE table_schema NOT IN (‘pg_catalog’, ‘information_schema’)
ORDER BY table_schema, table_name, ordinal_position;
”

echo “\n3. 约束信息”
echo “==========================================”
psql -U fgedu -d fgedudb -c ”
SELECT
table_schema,
table_name,
constraint_name,
constraint_type
FROM information_schema.table_constraints
WHERE table_schema NOT IN (‘pg_catalog’, ‘information_schema’)
ORDER BY table_schema, table_name, constraint_name;
”

echo “\n数据字典生成完成!”

# 检查权限配置

echo “PostgreSQL权限检查”
echo “检查时间: $(date)”
echo “数据库: fgedudb”
echo “==========================================”

echo “\n1. 角色权限”
echo “==========================================”
psql -U fgedu -d fgedudb -c ”
SELECT
rolname AS 角色名,
CASE WHEN rolsuper THEN ‘是’ ELSE ‘否’ END AS 超级用户,
CASE WHEN rolcreaterole THEN ‘是’ ELSE ‘否’ END AS 创建角色,
CASE WHEN rolcreatedb THEN ‘是’ ELSE ‘否’ END AS 创建数据库,
CASE WHEN rolcanlogin THEN ‘是’ ELSE ‘否’ END AS 可登录
FROM pg_roles
WHERE rolname NOT LIKE ‘pg_%’ AND rolname != ‘postgres’
ORDER BY rolname;
”

echo “\n2. 表权限”
echo “==========================================”
psql -U fgedu -d fgedudb -c ”
SELECT
table_schema AS 模式,
table_name AS 表名,
grantee AS 授权用户,
privilege_type AS 权限类型
FROM information_schema.role_table_grants
WHERE table_schema NOT IN (‘pg_catalog’, ‘information_schema’)
ORDER BY table_schema, table_name, grantee, privilege_type;
”

echo “\n3. 列权限”
echo “==========================================”
psql -U fgedu -d fgedudb -c ”
SELECT
table_schema AS 模式,
table_name AS 表名,
column_name AS 列名,
grantee AS 授权用户,
privilege_type AS 权限类型
FROM information_schema.role_column_grants
WHERE table_schema NOT IN (‘pg_catalog’, ‘information_schema’)
ORDER BY table_schema, table_name, column_name, grantee, privilege_type;
”

echo “\n权限检查完成!”

# 1. 应用程序元数据获取

# Python示例
import psycopg2

conn = psycopg2.connect(
host=”192.168.1.100″,
port=”5432″,
database=”fgedudb”,
user=”fgedu”,
password=”Fgedu@2026″
)

cursor = conn.cursor()

# 获取表信息
cursor.execute(“””
SELECT table_name
FROM information_schema.tables
WHERE table_schema = ‘public’
ORDER BY table_name
“””)
tables = cursor.fetchall()

print(“数据库表:”)
for table in tables:
print(f”- {table[0]}”)

# 获取列信息
for table in tables:
table_name = table[0]
print(f”\n{table_name} 列:”)
cursor.execute(“””
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = ‘public’ AND table_name = %s
ORDER BY ordinal_position
“””, (table_name,))
columns = cursor.fetchall()
for column in columns:
print(f”- {column[0]} ({column[1]}, 可空: {column[2]})”)

cursor.close()
conn.close()

# 2. 监控系统集成

# 查询表大小
$ psql -U fgedu -d fgedudb -c ”
SELECT
table_schema,
table_name,
pg_size_pretty(pg_total_relation_size(table_schema || ‘.’ || table_name)) AS size
FROM information_schema.tables
WHERE table_schema NOT IN (‘pg_catalog’, ‘information_schema’)
ORDER BY pg_total_relation_size(table_schema || ‘.’ || table_name) DESC;
”

table_schema | table_name | size
————–+——————-+——–
public | fgedu_orders | 15 MB
public | fgedu_customers | 10 MB
public | fgedu_products | 5 MB
public | fgedu_categories | 1 MB
(4 rows)

# 3. 自动化运维工具

# 检查未使用的索引
$ psql -U fgedu -d fgedudb -c ”
SELECT
schemaname,
relname AS tablename,
indexrelname AS indexname,
idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND schemaname NOT IN (‘pg_catalog’, ‘information_schema’)
ORDER BY schemaname, relname, indexrelname;
”

schemaname | tablename | indexname | idx_scan
————+———–+———–+———-
public | fgedu_test | idx_test_id | 0
(1 row)