# 场景：创建自定义系统目录存储扩展信息

# 步骤1：创建自定义系统目录表
CREATE TABLE fgedu_pg_extension_info (
extid oid NOT NULL,
extname name NOT NULL,
extversion text NOT NULL,
extdescription text,
extcreated timestamp with time zone NOT NULL,
CONSTRAINT pg_extension_info_pkey PRIMARY KEY (extid),
CONSTRAINT pg_extension_info_name_unique UNIQUE (extname)
);

# 步骤2：创建系统目录索引
CREATE INDEX pg_extension_info_name_idx ON pg_extension_info USING btree (extname);
CREATE INDEX pg_extension_info_created_idx ON pg_extension_info USING btree (extcreated);

# 步骤3：创建系统目录函数
CREATE OR REPLACE FUNCTION pg_extension_info(oid)
RETURNS record
AS $$
DECLARE
result pg_extension_info%ROWTYPE;
BEGIN
SELECT * INTO result FROM pg_extension_info WHERE extid = $1;
RETURN result;
END;
$$ LANGUAGE plpgsql;

# 步骤4：插入测试数据
INSERT INTO pg_extension_info (extid, extname, extversion, extdescription, extcreated)
VALUES (1, ‘pg_stat_statements’, ‘1.10’, ‘Track execution statistics of all SQL statements executed’, NOW());

# 步骤5：查询自定义系统目录
SELECT * FROM pg_extension_info;
SELECT pg_extension_info(1);

# 步骤6：集成到PostgreSQL
— 创建视图，使其在pg_catalog模式下可见
CREATE VIEW pg_catalog.pg_extension_info AS
SELECT * FROM public.pg_extension_info;

# 步骤1：备份系统目录
pg_dump -s -d fgedudb -f system_catalog_backup.sql

# 步骤2：查看备份文件
cat system_catalog_backup.sql

# 步骤3：恢复系统目录
psql -d fgedudb -f system_catalog_backup.sql

# 示例：备份系统目录
pg_dump -s -d fgedudb -f system_catalog_backup.sql

# 结果示例
— PostgreSQL fgedudb dump
—
— Dumped from fgedudb version 18.0
— Dumped by pg_dump version 18.0

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = ‘UTF8’;
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config(‘search_path’, ”, false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

—
— Name: pg_custom_catalog; Type: TABLE; Schema: public; Owner: fgedu
—

CREATE TABLE fgedu_public.pg_custom_catalog (
id oid NOT NULL,
name name NOT NULL,
description text
);

ALTER TABLE public.pg_custom_catalog OWNER TO fgedu;

—
— Name: pg_custom_catalog pg_custom_catalog_name_unique; Type: CONSTRAINT; Schema: public; Owner: fgedu
—

ALTER TABLE ONLY public.pg_custom_catalog
ADD CONSTRAINT pg_custom_catalog_name_unique UNIQUE (name);

—
— Name: pg_custom_catalog pg_custom_catalog_pkey; Type: CONSTRAINT; Schema: public; Owner: fgedu
—

ALTER TABLE ONLY public.pg_custom_catalog
ADD CONSTRAINT pg_custom_catalog_pkey PRIMARY KEY (id);

—
— Name: pg_custom_catalog_name_idx; Type: INDEX; Schema: public; Owner: fgedu
—

CREATE INDEX pg_custom_catalog_name_idx ON public.pg_custom_catalog USING btree (name);

—
— PostgreSQL fgedudb dump complete
—

# 症状：查询系统目录时出现错误

# 解决方法
– 运行VACUUM FULL修复系统目录表
– 从备份恢复系统目录
– 重建系统目录

# 常见问题2：系统目录查询性能慢

# 症状：查询系统目录时响应缓慢

# 解决方法
– 为系统目录表创建适当的索引
– 定期运行VACUUM和ANALYZE
– 优化系统目录查询

# 常见问题3：系统目录空间使用过大

# 症状：系统目录表占用过多磁盘空间

# 解决方法
– 运行VACUUM FULL回收空间
– 清理无用的系统目录条目
– 监控系统目录大小

# 常见问题4：系统目录一致性问题

# 症状：系统目录内容不一致

# 解决方法
– 运行pg_checksums检查数据一致性
– 从备份恢复系统目录
– 重建系统目录

# 常见问题5：自定义系统目录集成问题

# 症状：自定义系统目录无法在pg_catalog中访问

# 解决方法
– 创建视图在pg_catalog模式下
– 确保权限设置正确
– 测试自定义系统目录的访问