$ psql -c “SHOW log_directory;”
log_directory
—————-
log
(1 row)

$ psql -c “SHOW log_filename;”
log_filename
—————-
postgresql-%a.log
(1 row)

# 查看日志文件位置
$ ls -la /postgresql/data18/log/
总用量 24
drwx——. 2 pgsql pgsql 61 4月 2 10:00 .
drwx——. 20 pgsql pgsql 4096 4月 2 09:50 ..
-rw——-. 1 pgsql pgsql 12345 4月 2 10:00 postgresql-Thu.log

# 查找连接错误
$ grep -i “error” /postgresql/data18/log/postgresql-Thu.log | tail -n 10
2026-04-02 10:15:30.123 CST [12345] ERROR: connection to fgedudb “nonexistentdb” failed: FATAL: fgedudb “nonexistentdb” does not exist
2026-04-02 10:16:45.678 CST [12346] ERROR: password authentication failed for fgedu “testfgedu”
2026-04-02 10:17:20.987 CST [12347] ERROR: permission denied for table fgedu_fgedus
2026-04-02 10:18:55.432 CST [12348] ERROR: syntax error at or near “FROM”

# 分析慢查询
$ grep -i “duration” /postgresql/data18/log/postgresql-Thu.log | sort -k2 -n -r | head -n 5
2026-04-02 10:20:15.678 CST [12349] LOG: duration: 1234.567 ms statement: SELECT * FROM fgfgapp.fgedu_orders WHERE created_at > ‘2025-01-01’;
2026-04-02 10:21:30.987 CST [12350] LOG: duration: 890.123 ms statement: SELECT COUNT(*) FROM fgfgapp.fgedu_products WHERE stock_quantity < 10; 2026-04-02 10:22:45.345 CST [12351] LOG: duration: 567.890 ms statement: SELECT * FROM fgfgapp.fgedu_fgedus WHERE email LIKE '%fgedu.net.cn';

# 复现BUG并收集信息
# 示例：发现一个查询性能问题
$ psql -d fgedudb
fgedudb=# EXPLAIN ANALYZE SELECT * FROM fgfgapp.fgedu_orders WHERE created_at > ‘2025-01-01’;
QUERY PLAN
———————————————————————————————————————————————
Seq Scan on fgedu_orders (cost=0.00..1234.56 rows=7890 width=123) (actual time=0.012..1.234 rows=5678 loops=1)
Filter: (created_at > ‘2025-01-01 00:00:00’::timestamp without time zone)
Rows Removed by Filter: 1234
Planning Time: 0.056 ms
Execution Time: 1234.567 ms
(5 rows)

# 发现问题：没有使用索引，导致全表扫描
# 查看表结构和索引
fgedudb=# \d fgfgapp.fgedu_orders
Table “fgfgapp.fgedu_orders”
Column | Type | Collation | Nullable | Default
—————-+—————————–+———–+———-+——————————————-
order_id | integer | | not null | nextval(‘fgfgapp.fgedu_orders_order_id_seq’::regclass)
fgedu_id | integer | | not null |
order_date | timestamp without time zone | | | now()
total_amount | numeric(10,2) | | not null |
status | character varying(20) | | not null | ‘pending’
shipping_address | text | | not null |
created_at | timestamp without time zone | | | now()
updated_at | timestamp without time zone | | | now()
Indexes:
“fgedu_orders_pkey” PRIMARY KEY, btree (order_id)
“idx_fgedu_orders_status” btree (status)
“idx_fgedu_orders_fgedu_id” btree (fgedu_id)

# 发现问题：created_at列没有索引，需要创建索引
fgedudb=# CREATE INDEX idx_fgedu_orders_created_at ON fgfgapp.fgedu_orders(created_at);
CREATE INDEX

# 再次执行查询，验证性能提升
fgedudb=# EXPLAIN ANALYZE SELECT * FROM fgfgapp.fgedu_orders WHERE created_at > ‘2025-01-01’;
QUERY PLAN
———————————————————————————————————————————————
Bitmap Heap Scan on fgedu_orders (cost=0.00..456.78 rows=7890 width=123) (actual time=0.012..0.123 rows=5678 loops=1)
Filter: (created_at > ‘2025-01-01 00:00:00’::timestamp without time zone)
Rows Removed by Filter: 0
Heap Blocks: exact=123
-> Bitmap Index Scan on idx_fgedu_orders_created_at (cost=0.00..123.45 rows=7890 width=0) (actual time=0.008..0.008 rows=5678 loops=1)
Index Cond: (created_at > ‘2025-01-01 00:00:00’::timestamp without time zone)
Planning Time: 0.056 ms
Execution Time: 123.456 ms
(8 rows)

# 性能提升：从1234.567 ms降低到123.456 ms