PostgreSQL实战指南：多领域数据库应用与实践

第六部分：PostgreSQL高级管理与优化

本部分聚焦于 PostgreSQL 的高级运维和管理，内容涵盖高可用架构、备份恢复、安全加固、性能诊断与调优，以及通过扩展开发来增强数据库功能。旨在帮助数据库管理员（DBA）和开发人员精通 PostgreSQL 的管理与维护，确保数据库系统的稳定、安全与高效。

第28章：数据库扩展与插件开发

PostgreSQL 最强大的特性之一就是其无与伦比的可扩展性。它不仅仅是一个数据库，更是一个数据库开发的平台。从 PostGIS 到 TimescaleDB 再到 Citus，本书中介绍的许多强大功能都是通过扩展（Extension）机制实现的。本章将揭开 PostgreSQL 扩展的神秘面纱，引导你了解如何利用现有的扩展，并尝试使用 C 语言开发一个属于你自己的简单扩展。

28.1 PostgreSQL 扩展生态

PostgreSQL 拥有一个极其丰富的开源扩展生态系统，几乎可以满足任何可以想象到的需求。

PGXN (PostgreSQL Extension Network): https://pgxn.org/ 是一个集中的 PostgreSQL 扩展仓库，你可以在这里发现、下载和安装各种扩展。
管理扩展:
- CREATE EXTENSION extension_name;: 在当前数据库中启用一个已安装的扩展。
- \dx: 在 psql 中列出所有已安装的扩展。
- ALTER EXTENSION extension_name UPDATE;: 将扩展更新到新版本。
- DROP EXTENSION extension_name;: 卸载扩展。

28.2 扩展的组成部分

一个标准的 PostgreSQL 扩展通常由以下文件组成：

extension_name.control 文件: 这是扩展的元数据文件，包含了扩展的版本、依赖关系、默认 schema 等信息。
SQL 脚本文件 (--*.sql): 定义了扩展所包含的 SQL 对象，如自定义函数、数据类型、操作符等。当执行 CREATE EXTENSION 时，这个脚本会被执行。
共享库文件 (.so 或 .dll): 如果扩展包含 C 语言编写的函数，这些函数会被编译成一个共享库文件，PostgreSQL 在需要时会动态加载它。

28.3 使用 C 语言开发自定义函数

虽然可以使用 PL/pgSQL、PL/Python 等过程语言在数据库内部编写函数，但对于性能要求极高或需要与底层系统交互的场景，使用 C 语言是最佳选择。

PostgreSQL C 语言 API 版本1:

PostgreSQL 提供了一套稳定的 C 语言层面的 API，用于开发自定义函数。你需要包含 postgres.h 头文件，并使用特定的宏（如 PG_FUNCTION_INFO_V1, PG_GETARG_*, PG_RETURN_*）来定义和实现函数。

开发流程:

编写 C 源代码。
使用 Makefile 和 PGXS (PostgreSQL’s extension building infrastructure) 进行编译。
将编译好的共享库和 SQL 文件安装到 PostgreSQL 的目录中。
在数据库中通过 CREATE FUNCTION 将 SQL 函数名与 C 函数关联起来。

28.4 场景实战：开发一个计算阶乘的 C 语言扩展

业务场景描述:

我们需要一个高性能的函数来计算一个整数的阶乘。虽然可以用 SQL 的递归 CTE 实现，但使用 C 语言版本会快得多。我们将把这个函数打包成一个名为 mymath 的扩展。

步骤1：创建项目目录和文件

1
2
3
mkdir mymath
cd mymath
touch mymath.c mymath.control mymath--1.0.sql Makefile

步骤2：编写 C 源代码 (mymath.c)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
#include "postgres.h"
#include "fmgr.h"

PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(c_factorial);

Datum
c_factorial(PG_FUNCTION_ARGS)
{
    int32 arg = PG_GETARG_INT32(0);
    int64 result = 1;
    int i;

    if (arg < 0)
        ereport(ERROR,
                (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
                 errmsg("Factorial input must be non-negative")));

    for (i = 2; i <= arg; i++)
        result *= i;

    PG_RETURN_INT64(result);
}

步骤3：编写 Control 文件 (mymath.control)

1
2
3
4
5
# mymath extension
comment = 'My custom math functions'
default_version = '1.0'
module_pathname = '$libdir/mymath'
relocatable = true

步骤4：编写 SQL 脚本 (mymath--1.0.sql)

1
2
3
4
5
6
7
-- complain if script is sourced in psql, rather than via CREATE EXTENSION
\echo Use "CREATE EXTENSION mymath" to load this file. \quit

CREATE FUNCTION factorial(integer)
RETURNS bigint
AS '$libdir/mymath', 'c_factorial'
LANGUAGE C STRICT IMMUTABLE;

步骤5：编写 Makefile

1
2
3
4
5
6
7
8
9
# Makefile for mymath extension
MODULES = mymath
EXTENSION = mymath
DATA = mymath--1.0.sql

# PostgreSQL annd PGXS
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

步骤6：编译和安装

1
2
3
# 确保 pg_config 在你的 PATH 中
make
sudo make install

步骤7：在数据库中使用扩展

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
-- 启用扩展
CREATE EXTENSION mymath;

-- 调用新函数
SELECT factorial(5);
--  factorial
-- -----------
--        120
--(1 row)

SELECT factorial(20);
--      factorial
-- --------------------
-- 2432902008176640000
--(1 row)

代码解释与思考:

PG_MODULE_MAGIC: 这是一个必须包含的宏，用于检查编译的模块是否与当前 PostgreSQL 版本兼容。
PG_FUNCTION_INFO_V1: 宏，用于声明一个符合版本1调用约定的 C 函数。
PG_GETARG_* / PG_RETURN_*: 用于在 SQL 和 C 之间传递参数和返回值的宏。例如 PG_GETARG_INT32(0) 获取第一个 integer 类型的参数。
$libdir/mymath: 在 CREATE FUNCTION 中，这告诉 PostgreSQL 在其标准库目录中查找名为 mymath.so 的共享库文件。
LANGUAGE C: 指定这是一个 C 语言函数。
STRICT: 如果任何参数为 NULL，则不执行函数，直接返回 NULL。
IMMUTABLE: 向优化器声明这是一个纯函数，即对于相同的输入，总是返回相同的结果，且不修改数据库。这允许优化器对函数调用进行预计算和索引。
PGXS: 极大地简化了扩展的编译过程，它会自动处理所有必要的编译器和链接器标志。

28.5 总结

本章我们揭开了 PostgreSQL 扩展开发的神秘面纱。我们了解了 PostgreSQL 强大的扩展生态系统，学习了构成一个扩展的基本文件，并亲手实践了如何使用 C 语言和 PGXS 构建、打包和安装一个自定义函数扩展。虽然我们只实现了一个简单的阶乘函数，但这个过程为你打开了一扇通往无限可能的大门。通过扩展机制，你可以为 PostgreSQL 添加任何你需要的功能，从新的数据类型、索引方法到复杂的分析算法，真正地将 PostgreSQL 定制成满足你特定需求的完美平台。

PostgreSQL实战指南：多领域数据库应用与实践 - 第28章：数据库扩展与插件开发

文章目录