迁移SQL Server、MySQL/MariaDB、Oracle等其他数据库到 PostgreSQL，强烈推荐这个工具！

当前位置：点晴教程→知识管理交流 →『技术文档交流』

admin

2024年10月12日 7:46 本文热度 2310

我们经常会要从其他数据库到 PostgreSQL，和 PG 系相关的数据库。选择一款合适的工具，往往决定了迁移的效率和结果。

介绍

db_migrator是一个 PostgreSQL 扩展，提供将数据库从其他数据源迁移到 PostgreSQL 的功能。这需要有一个要迁移的数据源的外部数据包装器。

您还需要有一个给db_migrator用的插件，其中包含特定于目标数据源的代码。目前，以下数据源都是有插件的：

• Oracle: ora_migrator
• MySQL/MariaDB: mysql_migrator
• Microsoft SQL Server: mssql_migrator

请参阅架构部分，以便了解正在发生的情况，请参阅用法，了解如何以最佳方式迁移数据库。

展示

这是使用ora_migrator插件，简单迁移一个 Oracle 数据库的完整示例。

使用超级用户设置阶段：

CREATE EXTENSION oracle_fdw;

CREATE SERVER oracle FOREIGN DATA WRAPPER oracle_fdw
   OPTIONS (dbserver '//dbserver.mydomain.com/ORADB');

GRANT USAGE ON FOREIGN SERVER oracle TO migrator;

CREATE USER MAPPING FOR migrator SERVER oracle
   OPTIONS (user 'orauser', password 'orapwd');

PostgreSQL 用户migrator具有创建 PostgreSQL 模式的权限，而 Oracle 用户orauser具有SELECT ANY DICTIONARY权限。

现在，我们以migrator连接并执行迁移，以让所有对象都属于此用户：

CREATE EXTENSION ora_migrator;

SELECT db_migrate(
   plugin => 'ora_migrator',
   server => 'oracle',
   only_schemas => '{TESTSCHEMA1,TESTSCHEMA2}'
);

NOTICE:  Creating staging schemas "fdw_stage" and "pgsql_stage" ...
NOTICE:  Creating foreign metadata views in schema "fdw_stage" ...
NOTICE:  Creating schemas ...
NOTICE:  Creating sequences ...
NOTICE:  Creating foreign tables ...
NOTICE:  Migrating table testschema1.baddata ...
WARNING:  Error loading table data for testschema1.baddata
DETAIL:  invalid byte sequence for encoding "UTF8": 0x00: 
NOTICE:  Migrating table testschema1.log ...
NOTICE:  Migrating table testschema1.tab1 ...
NOTICE:  Migrating table testschema1.tab2 ...
NOTICE:  Migrating table testschema2.tab3 ...
NOTICE:  Creating UNIQUE and PRIMARY KEY constraints ...
WARNING:  Error creating primary key or unique constraint on table testschema1.baddata
DETAIL:  relation "testschema1.baddata" does not exist: 
NOTICE:  Creating FOREIGN KEY constraints ...
NOTICE:  Creating CHECK constraints ...
NOTICE:  Creating indexes ...
NOTICE:  Setting column default values ...
NOTICE:  Dropping staging schemas ...
NOTICE:  Migration completed with 2 errors.
 db_migrate 
------------
          2
(1 row)

尽管由于 Oracle 数据库中有错误数据，其中一个表的迁移失败了，但其余数据都已成功迁移。

设置

前提条件

外部数据包装器

您需要为要迁移的数据源，安装外部数据包装器。请按照该软件的安装说明进行操作。PostgreSQL Wiki 中提供了一个可用的外部数据包装器列表。

您需要定义以下对象：

• 一个描述如何连接到远程数据源的外部服务器
• 一个服务器的用户映射，用于为执行迁移的用户提供凭据

权限

您需要一个拥有下面权限的数据库用户

• 当前数据库上面的CREATE权限
• 在安装了扩展的模式上面的USAGE权限
• 外部服务器上面的USAGE权限
• 所有必需的迁移函数上面的EXECUTE权限（通常默认会授予）

迁移完成后，可以减少用户权限。

db_migrator 插件

您还需要为要迁移的数据源，安装db_migrator插件。同样，请按照软件随附的安装说明进行操作。

架构

db_migrator使用到两个辅助模式，即 “FDW 暂存模式” 和 “Postgres 暂存模式”。默认情况下，名称为fdw_stage和pgsql_stage，但您也可以选择不同的名称。

在第一步中，db_migrator调用插件使用外部表填充fdw_stage，这些表以标准化方式提供有关远程数据源元数据的信息（有关详细信息，请参阅插件 API）。

在第二步中，数据被复制到 Postgres 暂存模式的表中，从而在 FDW 暂存模式生成一种数据快照。本章末尾将详细介绍这些表。在此快照期间，可以使用插件提供的一个函数转换表名和列名。该插件还提供了一个远程数据类型到 PostgreSQL 数据类型的默认映射。

下一步，用户在 Postgres 暂存模式修改数据，以适应迁移要求（不同的数据类型、对函数和视图定义的编辑等）。这是通过更新 Postgres 暂存模式中的表来完成的。此外，大多数表都有一个boolean类型的migrate列，应该为要迁移的所有对象设置该列为TRUE。

下一步是在 PostgreSQL 数据库中创建模式，并使用指向远程数据源中对象的外部表，填充这些模式。然后，这些表会被“物化”，即创建本地表，并将来自外部表的数据插入到本地表中。

然后可以迁移其他对象，最后迁移索引和约束。

迁移完成后，不再需要 FDW 暂存模式和 PostgreSQL 暂存模式（以及外部数据包装器），可以将其删除。

Postgres 暂存模式中的表

仅编辑指示的列。例如，如果要更改模式或表名称，最好在完成迁移后重命名模式或表。

schemas

• schema（类型name）：模式的名称
• orig_schema（类型text）：远程数据源中使用的模式名称

tables

• schema（类型name）：表的模式
• table_name（类型name）：表的名称
• orig_table（类型text）：远程数据源中使用的表名
• migrate（类型boolean，默认值TRUE）：该表应该迁移时，为TRUE如果需要，请修改此列。

columns

• schema（类型name）：包含列的表的模式
• table_name（类型name）：包含列的表
• column_name（类型name）：列的名称
• column_options（类型jsonb）：特定于插件的列选项
• orig_column（类型text）：远程数据源中使用的列名
• position（类型integer）：定义列的顺序（第一列为 1）
• type_name（类型text）：PostgreSQL 数据类型（包括类型修饰符）如果需要，请修改此列。
• orig_type（类型text）：远程数据源中的数据类型
• nullable（类型boolean）：如果列是NOT NULL，则为FALSE如果需要，请修改此列。
• default_value（类型text）：如果需要，请修改此列。

checks（检查约束）

• schema（类型name）：具有该约束的表的模式
• table_name（类型name）：具有该约束的表
• constraint_name（类型name）：约束的名称
• orig_name（类型text）：远程数据源中约束的名称
• deferrable（类型boolean）：如果约束可以延迟，则为TRUE如果需要，请修改此列。
• deferred（类型boolean）：如果约束是INITIALLY DEFERRED，则为TRUE如果需要，请修改此列。
• condition（类型text）：待检查的条件如果需要，请修改此列。
• migrate（类型boolean，默认值TRUE）：约束应该迁移时，则为TRUE如果需要，请修改此列。

keys（主键和唯一键的列）

• schema（类型name）：具有该约束的表的模式
• table_name（类型name）：具有该约束的表
• constraint_name（类型name）：约束的名称
• orig_name（类型text）：远程数据源中约束的名称
• deferrable（类型boolean）：如果约束可以延迟，则为TRUE如果需要，请修改此列。
• deferred（类型boolean）：如果约束是INITIALLY DEFERRED，则为TRUE如果需要，请修改此列。
• column_name（类型name）：属于键的列的名称
• position（类型integer）：定义约束中列的顺序
• is_primary（类型boolean）：如果这是主键，则为TRUE
• migrate（类型boolean，默认值TRUE）：约束应该迁移时，则为TRUE如果需要，请修改此列。

indexes

• schema（类型name）：带有该索引的表的模式
• table_name（类型name）：带有该索引的表
• index_name（类型name）：索引的名称
• orig_name（类型text）：远程数据源中索引的名称
• uniqueness（类型boolean）：如果这是唯一索引，则为TRUE如果需要，请修改此列。
• where_clause（类型text）：关联到一个部分索引的表达式NULL表示没有应用过滤器。
• migrate（类型boolean，默认值TRUE）：约束应该迁移时，则为TRUE如果需要，请修改此列。

index_columns

• schema（类型name）：带有索引的表的模式
• table_name（类型name）：带有该索引的表
• index_name（类型name）：索引的名称
• position（类型integer）：确定索引列顺序
• descend（类型boolean）：如果索引列按DESC排序，则为TRUE如果需要，请修改此列。
• is_expression（类型boolean）：如果索引列是表达式而不是列名，则为TRUE
• column_name（类型text）：列的名称或者索引表达式（表达式通常必须用括号括起来）如果需要，请修改此列。

partitions

如果您在 PostgreSQL 中不需要一个分区表，请从此表中删除相关的行。或者，如果您想要将一个未分区的表迁移到 PostgreSQL 中的分区表，则可以在此表中添加相应的行。

• schema（类型name）：分区表的模式
• table_name（类型name）：分区表的名称
• partition_name（类型name）：分区的名称
• orig_name（类型name）：远程数据源中的分区名称
• type（类型text）：可支持的一种分区方式，LIST、RANGE或HASH
• key（类型text）：用作分区键的列名或表达式
• values（类型text[]）：分区绑定规范
• values是分区绑定规范非数字值（如时间戳）必须作为字符串常量用引号引用（例如ARRAY['''2022-01-01''','''2023-01-01''']）。

• 对于列表分区，values包含列表
• 对于范围分区，values包含下限和上限（其中下限是包含的，但上限是不包括的）
• 对于哈希分区，values中唯一的条目，是此分区的余数部分

• is_default（类型boolean，默认值FALSE）；如果是默认分区，则为TRUE

subpartitions

如果您在 PostgreSQL 中不需要一个子分区表，请从此表中删除相关的行。或者，如果要将没有子分区的表迁移到 PostgreSQL 中有子分区的表，则可以在此表中添加相应的行。

• schema（类型name）：分区表的模式
• table_name（类型name）：分区表的名称
• partition_name（类型name）：父分区的名称
• subpartition_name（类型name）：子分区的名称
• orig_name（类型name）：远程数据源中子分区的名称
• type（类型text）：可支持的一种分区方式，LIST、RANGE或HASH
• key（类型text）：用作分区键的列名或表达式
• values（类型text[]）：分区绑定规范请参阅上面partitions表的文档中values部分的说明。
• is_default（类型boolean，默认值FALSE）；如果是默认子分区，则为TRUE

views

• schema（类型name）：带有视图的表的模式
• view_name（类型name）：视图的名称
• definition（类型text）：定义视图的 SQL 语句如果需要，请修改此列。
• orig_def（类型text）：远程数据源上的视图定义
• migrate（类型boolean，默认值TRUE）：约束应该迁移时，为TRUE如果需要，请修改此列。
• verified（类型boolean）：可以按你所需地使用如果视图已成功转换，这可能对存储很有用。

sequences

• schema（类型name）：序列的模式
• sequence_name（类型name）：序列的名称
• min_value（类型bigint）：生成值的最小值如果需要，请修改此列。
• max_value（类型bigint）：生成值的最大值如果需要，请修改此列。
• increment_by（类型bigint）：生成值之间的差值如果需要，请修改此列。
• cyclical（类型boolean）：如果序列“翻转”了，则为TRUE如果需要，请修改此列。
• cache_size（类型integer）：客户端缓存的序列值数目如果需要，请修改此列。
• last_value（类型bigint）：序列的当前位置如果需要，请修改此列。
• orig_value（类型bigint）：远程数据源上的当前位置

functions（函数和存储过程）

• schema（类型name）：函数或存储过程的模式
• function_name（类型name）：函数或存储过程的名称
• is_procedure（类型boolean）：如果是存储过程，则为TRUE如果需要，请修改此列。
• source（类型text）：函数或存储过程的源代码如果需要，请修改此列。
• orig_source（类型text）：远程数据源上的源代码
• migrate（类型boolean，默认值FALSE）：对象应该迁移时，则为TRUE如果需要，请修改此列。请注意，由于默认值为FALSE，默认情况下不会迁移函数和存储过程。
• verified（类型boolean）：可以按你所需地使用如果源代码已成功转换，这可能对存储很有用。

triggers

• schema（类型name）：带有触发器的表的模式
• table_name（类型name）：触发器关联的表的名称
• trigger_name（类型name）：触发器的名称
• trigger_type（类型text）：BEFORE、AFTER或INSTEAD OF如果需要，请修改此列。
• triggering_event（类型text）：INSERT、UPDATE、DELETE或TRUNCATE（如果需要多个，则使用OR结合）如果需要，请修改此列。
• for_each_row（类型boolean）：如果触发器是针对每个修改的行执行一次，而不是每个触发的语句执行一次，则为TRUE如果需要，请修改此列。
• when_clause（类型text）：触发器执行的条件如果需要，请修改此列。
• trigger_body（类型text）：触发器的函数体如果需要，请修改此列。
• orig_source（类型text）：远程数据源上的触发器源代码
• migrate（类型boolean，默认值FALSE）：触发器应该迁移时，则为TRUE如果需要，请修改此列。请注意，由于默认值为FALSE，默认情况下不会迁移触发器。
• verified（类型boolean）：可以按你所需地使用如果触发器已成功转换，这可能对存储很有用。

table_privs（表上的权限）

这些权限不会由db_migrator迁移，但迁移脚本可以使用这些权限信息来迁移权限。

• schema（类型name）：具有该权限的表的模式
• table_name（类型name）：具有该权限的表的名称
• privilege（类型text）：权限的名称
• grantor（类型name）：授予权限的用户
• grantee（类型name）：获得权限的用户
• grantable（类型boolean）：如果被授权者可以将权限授予其他人，则为TRUE

column_privs（表列上的权限）

这些权限不会由db_migrator迁移，但迁移脚本可以使用这些权限信息来迁移权限。

• schema（类型name）：具有该权限的表的模式
• table_name（类型name）：具有该权限的表的名称
• column_name（类型name）：具有该权限的表列的名称
• privilege（类型text）：权限的名称
• grantor（类型name）：授予权限的用户
• grantee（类型name）：获得权限的用户
• grantable（类型boolean）：如果被授权者可以将权限授予其他人，则为TRUE

用法

执行迁移的数据库用户，将会是所有迁移的模式和对象的所有者。在迁移完成后，所有权可以转移。数据库对象上面的权限是不会迁移的（但插件可能会提供数据源上面权限的信息）。

对转换过程代码（函数、存储过程和触发器）并没有特别的支持，您必须自己完成。

对于非常简单的情况（无需迁移存储过程或触发器，所有视图均采用标准 SQL，无需数据类型适配），只需调用db_migrate函数，即可迁移所需的数据库模式。

对于更复杂的迁移，您要编写一个执行以下操作（或部分执行）的 SQL 脚本：

• 调用db_migrate_prepare以创建和填充 FDW 和 Postgres 暂存模式（有关详细信息，请参阅架构）。
• 现在，您可以更新 Postgres 暂存模式中的表，以更改数据类型、存储过程代码、视图等。这也是在 Postgres 暂存模式的表中设置migrate标志的时候，以指示哪些对象应该迁移，哪些对象不应该迁移。
• 在调用db_migrate_mkforeign之前的任何给定时间点，都可以调用db_migrate_refresh，以使用当前元数据更新 Postgres 暂存模式中的快照。
• 接下来，调用db_migrate_mkforeign以迁移模式和创建的外部表，这些表指向包含了应迁移数据的远程对象。
• 现在，如果需要对这些外部表进行调整，则可以使用ALTER FOREIGN TABLE。
• 接下来，调用db_migrate_tables，以将外部表替换为实际的 PostgreSQL 表，并迁移数据。此步骤通常需要花费最多时间。请注意，可以选择执行“仅模式”迁移来测试对象定义，而无需迁移所有数据。
• 如果要迁移此类对象，现在可以调用db_migrate_functions、db_migrate_triggers和db_migrate_views函数，来迁移这些对象。如果视图依赖于函数，则最后调用db_migrate_views。
• 然后，调用db_migrate_constraints，以迁移那些已迁移表的索引和约束。将此操作放到最后来执行，通常是个好主意，因为索引和约束可能会依赖于函数。
• 最后，调用db_migrate_finish，以删除由db_migrate_prepare创建的 FDW 和 Postgres 暂存模式。

在数据库迁移过程中发生错误（连接问题除外），处理不会终止。相反，它们将会作为警告输出。此外，此类错误会记录在 PostgreSQL 暂存模式中的migrate_log表里。

后面错误可能是前面错误的结果：例如，迁移 Oracle 表的任何失败，也会导致依赖于该表的所有视图和约束失败。

完成后，请删除迁移用的扩展，以删除迁移的所有跟踪信息。

迁移函数的详细说明

db_migrate_prepare

参数：

• plugin（类型name，必填）：要使用的db_migrator插件的名称
• server（类型name，必填）：描述要迁移的数据源的外部服务器的名称
• staging_schema（类型name，默认值fdw_stage）：远程暂存模式的名称
• pgstage_schema（类型name，默认值pgsql_stage）：Postgres 暂存模式的名称
• only_schemas（类型name[]，默认包含所有模式）：要迁移的模式列表输入的内容必须与远程数据源上的模式名称完全相同。
• options（类型jsonb，可选）：传递给插件的选项有关可用选项，请参阅插件的文档。

必须先调用此函数。它会创建暂存模式。插件会填充远程暂存模式。它会调用db_migrate_refresh，以在 Postgres 暂存模式创建远程暂存模式的快照。

db_migrate_refresh

参数：

• plugin（类型name，必填）：要使用的db_migrator插件的名称
• staging_schema（类型name，默认值fdw_stage）：远程暂存模式的名称
• pgstage_schema（类型name，默认值pgsql_stage）：Postgres 暂存模式的名称
• only_schemas（类型name[]，默认包含所有模式）：要迁移的模式列表

您可以调用此函数，以使用远程暂存模式的新快照刷新 Postgres 暂存模式。只要远程数据源上没有对象被重命名或删除，这就可以工作（添加表和列也可以正常工作）。对 Postgres 暂存模式所做的编辑将被保留。

db_migrate_mkforeign

参数：

• plugin（类型name，必填）：要使用的db_migrator插件的名称
• server（类型name，必填）：描述要迁移的数据源的外部服务器的名称
• staging_schema（类型name，默认值fdw_stage）：远程暂存模式的名称
• pgstage_schema（类型name，默认值pgsql_stage）：Postgres 暂存模式的名称
• options（类型jsonb，可选）：传递给插件的选项有关可用选项，请参阅插件的文档。

在对 Postgres 暂存模式编辑完成后，调用此函数。它将创建所有应迁移的模式，并为要迁移的所有远程表创建外部表。

db_migrate_tables

参数：

• plugin（类型name，必填）：要使用的db_migrator插件的名称
• pgstage_schema（类型name，默认值pgsql_stage）：Postgres 暂存模式的名称
• with_data（类型boolean，默认值TRUE）：如果为FALSE，将迁移除表数据之外的所有内容这对于测试元数据的迁移非常有用。

此函数调用materialize_foreign_table，以将db_migrate_mkforeign创建的所有外部表替换为实际表。除非with_data是FALSE，否则将迁移表数据。

db_migrate_functions

参数：

• plugin（类型name，必填）：要使用的db_migrator插件的名称
• pgstage_schema（类型name，默认值pgsql_stage）：Postgres 暂存模式的名称

调用此函数以迁移函数和存储过程。请注意，函数和存储过程默认会设置migrate为FALSE，因此如果要迁移函数，则必须更改该标志。

db_migrate_views

参数：

• plugin（类型name，必填）：要使用的db_migrator插件的名称
• pgstage_schema（类型name，默认值pgsql_stage）：Postgres 暂存模式的名称

调用此函数以迁移视图。

db_migrate_triggers

参数：

• plugin（类型name，必填）：要使用的db_migrator插件的名称
• pgstage_schema（类型name，默认值pgsql_stage）：Postgres 暂存模式的名称

调用此函数以迁移触发器。请注意，触发器默认会设置migrate为FALSE，因此如果要迁移触发器，则必须更改该标志。

db_migrate_indexes

参数：

• plugin（类型name，必填）：要使用的db_migrator插件的名称
• pgstage_schema（类型name，默认值pgsql_stage）：Postgres 暂存模式的名称

调用此函数可迁移那些已迁移表的用户定义索引。

此函数应在迁移函数后运行，以便索引所需的所有函数都已存在。

db_migrate_constraints

参数：

• plugin（类型name，必填）：要使用的db_migrator插件的名称
• pgstage_schema（类型name，默认值pgsql_stage）：Postgres 暂存模式的名称

调用此函数可迁移已迁移表的约束和列默认值。

此函数必须在迁移其他所有内容后运行，以便列默认值可能需要的所有函数都已存在，并且外键约束可以使用到用户定义的索引来加快数据验证。

db_migrate_finish

参数：

• staging_schema（类型name，默认值fdw_stage）：远程暂存模式的名称
• pgstage_schema（类型name，默认值pgsql_stage）：Postgres 暂存模式的名称

迁移所需的所有内容后调用此函数。它将会删除暂存模式及其所有内容。

db_migrate

参数：

• plugin（类型name，必填）：要使用的db_migrator插件的名称
• server（类型name，必填）：描述要迁移的数据源的外部服务器的名称
• staging_schema（类型name，默认值fdw_stage）：远程暂存模式的名称
• pgstage_schema（类型name，默认值pgsql_stage）：Postgres 暂存模式的名称
• only_schemas（类型name[]，默认包含所有模式）：要迁移的模式列表输入的内容必须与远程数据源上的模式名称完全相同。
• options（类型jsonb，可选）：传递给插件的选项有关可用选项，请参阅插件的文档。

此函数通过按以下顺序调用这些函数，来提供“一键式”迁移：

• db_migrate_prepare
• db_migrate_mkforeign
• db_migrate_tables
• db_migrate_functions
• db_migrate_views
• db_migrate_triggers
• db_migrate_indexes
• db_migrate_constraints
• db_migrate_finish

这提供了一种迁移简单数据库的简单方法（没有用户定义的函数和触发器，视图定义采用符合标准的 SQL，无需修改数据类型）。

请注意，它不会迁移函数和触发器，因为默认情况下这些对象的migrate是FALSE。

底层迁移函数

这些函数由上面详述的迁移函数调用。

它们作为底层实现方法提供，如果您想并行迁移多个关系，以提高使用自己的外部工具的处理速度，则它们特别有用。

materialize_foreign_table

参数：

• schema（类型name，必填）：要迁移的表的模式
• table_name（类型name，必填）：要迁移的表的名称
• with_data（类型boolean，默认值TRUE）：如果为FALSE，将迁移除表数据之外的所有内容这对于测试元数据的迁移非常有用。
• pgstage_schema（类型name，默认值pgsql_stage）：Postgres 暂存模式的名称

此函数将db_migrate_mkforeign创建的单个外表替换为实际表。如果partitions表中有任何此表的条目，则该表将创建为分区表。如果subpartitions表中存在相应的条目，则创建子分区。除非with_data参数值是FALSE，否则将迁移表数据。

construct_schemas_statements

参数：

• pgstage_schema（类型name，默认值pgsql_stage）：Postgres 暂存模式的名称

将返回一个由以下列组成的表：

• schema_name（类型name）：模式的名称
• statement（类型text）：模式相关的 CREATE SCHEMA 语句

construct_sequences_statements

参数：

• pgstage_schema（类型name，默认值pgsql_stage）：Postgres 暂存模式的名称

将返回一个由以下列组成的表：

• schema_name（类型name）：序列的模式
• sequence_name（类型name）：序列的名称
• statement（类型text）：序列相关的 CREATE SEQUENCE 语句

construct_foreign_tables_statements

参数：

• plugin（类型name，必填）：要使用的db_migrator插件的名称
• server（类型name，必填）：描述要迁移的数据源的外部服务器的名称
• pgstage_schema（类型name，默认值pgsql_stage）：Postgres 暂存模式的名称
• options（类型jsonb，可选）：传递给插件的选项

将返回一个由以下列组成的表：

• schema_name（类型name）：带有该外部表的表的模式
• table_name（类型name）：外表的名称
• statement（类型text）：外表相关的 CREATE FOREIGN TABLE 语句

construct_functions_statements

参数：

• plugin（类型name，必填）：要使用的db_migrator插件的名称
• pgstage_schema（类型name，默认值pgsql_stage）：Postgres 暂存模式的名称

将返回一个由以下列组成的表：

• schema_name（类型name）：带有该函数的表的模式
• function_name（类型name）：函数或存储过程的名称
• statement（类型text）：函数相关的 CREATE FUNCTION 或 CREATE PROCEDURE 语句

construct_views_statements

参数：

• plugin（类型name，必填）：要使用的db_migrator插件的名称
• pgstage_schema（类型name，默认值pgsql_stage）：Postgres 暂存模式的名称

将返回一个由以下列组成的表：

• schema_name（类型name）：带有该函数的表的模式
• view_name（类型name）：视图的名称
• statements（类型text[]）：视图相关的 SET LOCAL search_path 和 CREATE VIEW 语句

construct_triggers_statements

参数：

• plugin（类型name，必填）：要使用的db_migrator插件的名称
• pgstage_schema（类型name，默认值pgsql_stage）：Postgres 暂存模式的名称

将返回一个由以下列组成的表：

• schema_name（类型name）：带有触发器的表的模式
• trigger_name（类型name）：触发器的名称
• statements（类型text[]）：触发器相关的 CREATE FUNCTION 和 CREATE TRIGGER 语句

construct_indexes_statements

参数：

• plugin（类型name，必填）：要使用的db_migrator插件的名称
• pgstage_schema（类型name，默认值pgsql_stage）：Postgres 暂存模式的名称

将返回一个由以下列组成的表：

• schema_name（类型name）：带有索引的表的模式
• index_name（类型name）：索引的名称
• statement（类型text）：索引相关的 CREATE INDEX 语句

construct_key_constraints_statements

参数：

• plugin（类型name，必填）：要使用的db_migrator插件的名称
• pgstage_schema（类型name，默认值pgsql_stage）：Postgres 暂存模式的名称

将返回一个由以下列组成的表：

• schema_name（类型name）：表的模式
• table_name（类型name）：带有该键约束的表的名称
• statement（类型text）：表相关的 ADD CONSTRAINT 语句

construct_fkey_constraints_statements

参数：

• plugin（类型name，必填）：要使用的db_migrator插件的名称
• pgstage_schema（类型name，默认值pgsql_stage）：Postgres 暂存模式的名称

将返回一个由以下列组成的表：

• schema_name（类型name）：表的模式
• table_name（类型name）：带有该外键约束的表的名称
• statement（类型text）：表相关的 ADD CONSTRAINT 语句

construct_check_constraints_statements

参数：

• plugin（类型name，必填）：要使用的db_migrator插件的名称
• pgstage_schema（类型name，默认值pgsql_stage）：Postgres 暂存模式的名称

将返回一个由以下列组成的表：

• schema_name（类型name）：表的模式
• table_name（类型name）：带有该检查约束的表的名称
• statement（类型text）：表相关的 ADD CONSTRAINT 语句

construct_defaults_statements

参数：

• plugin（类型name，必填）：要使用的db_migrator插件的名称
• pgstage_schema（类型name，默认值pgsql_stage）：Postgres 暂存模式的名称

将返回一个由以下列组成的表：

• schema_name（类型name）：表的模式
• table_name（类型name）：列带有默认值的表的名称
• statement（类型text）：表相关的 ALTER SET DEFAULT 语句

execute_statements

参数：

• operation（类型text，必填）：任意操作说明
• schema（类型name，必填）：语句相关对象的模式
• object_name（类型name，必填）：语句所涉及的关系的名称
• statements（类型text[]，必填）：在同一子事务中执行的语句
• pgstage_schema（类型name，默认值pgsql_stage）：migrate_log表创建时所在的 Postgres 暂存模式的名称

此函数会遍历一个 SQL 语句数组，并在子事务中执行它们。如果一个语句失败，它会引发一条详细的警告，并将失败的语句及其上下文插入到migrate_log表中，并且子事务中所有先前成功的语句都将被回滚。失败时会返回false。

插件 API

给db_migrator用的插件必须是一个 PostgreSQL 扩展，并提供许多函数：

db_migrator_callback

没有输入参数。输出参数为：

• create_metadata_views_fun（类型regprocedure）：填充 FDW 暂存模式的“元数据视图创建函数”
• translate_datatype_fun（类型regprocedure）： “数据类型转换函数”，用于将远程数据源中的数据类型转换为 PostgreSQL 数据类型
• translate_identifier_fun（类型regprocedure）： “标识符转换函数”，用于将远程数据源中的标识符名称转换为 PostgreSQL 标识符
• translate_expression_fun（类型regprocedure）： “表达式转换函数”，用于将 SQL 表达式从远程数据源转换到 PostgreSQL
• create_foreign_table_fun（类型regprocedure）： “外表创建函数”，用于生成 SQL 字符串以定义外表

这些函数可以具有任意名称，如下所述。

元数据视图创建函数

参数：

• server（类型name，必填）：我们要访问其元数据的外部服务器的名称
• schema（类型name）：FDW 暂存模式的名称
• options（类型jsonb，可选）：插件特定的参数

在创建 FDW 暂存模式后，db_migrate_prepare将调用此函数。它必须创建一些外部表（或外部表上的视图），以提供对远程数据源元数据的访问。

如果远端数据源没有提供某项功能（比如，该数据源不支持触发器的特性），则可以创建一个空表，而不是相应的外表。

如果插件提供了其他功能，则允许在 FDW 暂存模式中创建其他对象。同样，除了 API 规范要求的列之外，还可以提供其他列。

以下外部表或视图是必须创建的：

模式表

schemas (
   schema text NOT NULL
)

序列表

sequences (
   schema        text    NOT NULL,
   sequence_name text    NOT NULL,
   min_value     numeric,
   max_value     numeric,
   increment_by  numeric NOT NULL,
   cyclical      boolean NOT NULL,
   cache_size    integer NOT NULL,
   last_value    numeric NOT NULL
)

• min_value和max_value是序列值可能的最小值和最大值。
• last_value是序列值的当前位置
• increment_by是生成值之间的差值
• cyclical标记为TRUE，用于在序列值超出max_value后可以min_value继续
• cache_size是客户端缓存的序列值数目

表信息的表

tables (
   schema     text NOT NULL,
   table_name text NOT NULL
)

表列和视图列的表

columns (
   schema        text    NOT NULL,
   table_name    text    NOT NULL,
   column_name   text    NOT NULL,
   position      integer NOT NULL,
   type_name     text    NOT NULL,
   length        integer NOT NULL,
   precision     integer,
   scale         integer,
   nullable      boolean NOT NULL,
   default_value text
)

请注意，此表必须同时包含tables和views表的列。

• position定义表列的顺序
• length表示变长数据类型的长度限制，例如character varying对于具有固定长度，或者带有precision和scale的数据类型，请将它设置为 0。
• precision表示变长数值类型的有效位数
• scale表示变长数值类型的小数点后的最大有效位数
• default_value是列定义DEFAULT子句中的 SQL 表达式

检查约束表

checks (
   schema          text    NOT NULL,
   table_name      text    NOT NULL,
   constraint_name text    NOT NULL,
   deferrable      boolean NOT NULL,
   deferred        boolean NOT NULL,
   condition       text    NOT NULL
)

• constraint_name标识约束，但名称不会迁移
• deferrable如果约束可以推迟到事务结束执行，则应为TRUE
• deferred如果约束是自动延迟的，则应为TRUE
• condition是定义检查约束的 SQL 表达式db_migrator不会迁移col IS NOT NULL形式的检查约束。应确保此类列columns.nullable为 FALSE。

主键列和唯一约束列的表

keys (
   schema          text    NOT NULL,
   table_name      text    NOT NULL,
   constraint_name text    NOT NULL,
   deferrable      boolean NOT NULL,
   deferred        boolean NOT NULL,
   column_name     text    NOT NULL,
   position        integer NOT NULL,
   is_primary      boolean NOT NULL
)

• constraint_name标识约束，但名称不会迁移
• deferrable如果约束可以推迟到事务结束执行，则应为TRUE
• deferred如果约束是自动延迟的，则应为TRUE
• position定义多列约束中列的顺序
• is_primary对于唯一约束是FALSE，对于主键是TRUE

对于一个多列约束，每个列在表中都会有一行。

外键约束列的表

foreign_keys (
   schema          text    NOT NULL,
   table_name      text    NOT NULL,
   constraint_name text    NOT NULL,
   deferrable      boolean NOT NULL,
   deferred        boolean NOT NULL,
   delete_rule     text    NOT NULL,
   column_name     text    NOT NULL,
   position        integer NOT NULL,
   remote_schema   text    NOT NULL,
   remote_table    text    NOT NULL,
   remote_column   text    NOT NULL
)

• constraint_name标识约束，但名称不会迁移
• deferrable如果约束可以推迟到事务结束执行，则应为TRUE
• deferred如果约束是自动延迟的，则应为TRUE
• position定义多列约束中列的顺序

对于一个多列约束，每个列在表中都会有一行。

分区的表

partitions (
    schema         name    NOT NULL,
    table_name     name    NOT NULL,
    partition_name name    NOT NULL,
    type           text    NOT NULL,
    key            text    NOT NULL,
    is_default     boolean NOT NULL,
    values         text[]
)

• type 是可支持的一种分区方式，LIST、RANGE或HASH
• key 用作分区键的列名或表达式
• values 是分区绑定规范非数字值（如时间戳）必须作为字符串常量用引号引用（例如ARRAY['''2022-01-01''','''2023-01-01''']）。

• 对于列表分区，values包含列表
• 对于范围分区，values包含下限和上限（其中下限是包含的，但上限是不包括的）
• 对于哈希分区，values中唯一的条目，是此分区的余数部分

• is_default 如果是默认分区，则为TRUE

子分区的表

subpartitions (
    schema            name    NOT NULL,
    table_name        name    NOT NULL,
    partition_name    name    NOT NULL,
    subpartition_name name    NOT NULL,
    type              text    NOT NULL,
    key               text    NOT NULL,
    is_default        boolean NOT NULL,
    values            text[]
)

有关说明，请参见上面的partitions表。

视图表

views (
   schema     text NOT NULL,
   view_name  text NOT NULL,
   definition text NOT NULL
)

• definition 是定义视图的SELECT语句

视图的列在columns表中定义。

函数和存储过程的表

functions (
   schema        text    NOT NULL,
   function_name text    NOT NULL,
   is_procedure  boolean NOT NULL,
   source        text    NOT NULL
)

• is_procedure 对于函数是FALSE，对于存储过程是TRUE
• source 是函数的源代码，包括参数列表和返回类型

索引表

indexes (
   schema        text    NOT NULL,
   table_name    text    NOT NULL,
   index_name    text    NOT NULL,
   uniqueness    boolean NOT NULL,
   where_clause  text
)

• index_name 标识索引，但名称不会迁移
• uniqueness 对于唯一索引是TRUE
• where_clause 是一个部分索引表达式，或 NULL

索引列的表

index_columns (
   schema        text    NOT NULL,
   table_name    text    NOT NULL,
   index_name    text    NOT NULL,
   position      integer NOT NULL,
   descend       boolean NOT NULL,
   is_expression boolean NOT NULL,
   column_name   text    NOT NULL
)

• position 定义多列索引中列的顺序
• descend 如果索引列按降序排序，则为TRUE；如果索引列按升序排序，则为FALSE
• is_expression 如果column_name是常规列名而不是表达式，则为FALSE
• column_name 是索引列名或表达式

触发器表

triggers (
   schema            text    NOT NULL,
   table_name        text    NOT NULL,
   trigger_name      text    NOT NULL,
   trigger_type      text    NOT NULL,
   triggering_event  text    NOT NULL,
   for_each_row      boolean NOT NULL,
   when_clause       text,
   trigger_body      text    NOT NULL
)

• trigger_type 应为BEFORE、AFTER或INSTEAD OF
• triggering_event 描述了导致触发器执行的 DML 事件，例如DELETE或INSERT OR UPDATE
• for_each_row 对于语句级触发器为FALSE，对于行级触发器为TRUE
• when_clause 是用于按条件执行的触发器的一个 SQL 表达式
• trigger_body 是触发器的源代码

表权限的表

table_privs (
   schema     text    NOT NULL,
   table_name text    NOT NULL,
   privilege  text    NOT NULL,
   grantor    text    NOT NULL,
   grantee    text    NOT NULL,
   grantable  boolean NOT NULL
)

列权限的表

column_privs (
   schema      text    NOT NULL,
   table_name  text    NOT NULL,
   column_name text    NOT NULL,
   privilege   text    NOT NULL,
   grantor     text    NOT NULL,
   grantee     text    NOT NULL,
   grantable   boolean NOT NULL
)