db_migrator: 迁移其他数据库到 PostgreSQL

一月 2, 2024

摘要db_migrator是一个 PostgreSQL 扩展,它提供了将数据库从其他数据源迁移到 PostgreSQL 的功能。

本文包含以下部分:

  1. 介绍
  2. 展示
  3. 设置
  4. 架构
  5. 用法
  6. 插件 API

介绍

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

您还需要有一个给db_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_stagepgsql_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):可支持的一种分区方式,LISTRANGEHASH

  • key(类型text):用作分区键的列名或表达式

  • values(类型text[]):分区绑定规范

  • values是分区绑定规范

    • 对于列表分区,values包含列表

    • 对于范围分区,values包含下限和上限(其中下限是包含的,但上限是不包括的)

    • 对于哈希分区,values中唯一的条目,是此分区的余数部分

    非数字值(如时间戳)必须作为字符串常量用引号引用(例如ARRAY['''2022-01-01''','''2023-01-01'''])。

  • is_default(类型boolean,默认值FALSE);如果是默认分区,则为TRUE

subpartitions

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

  • schema(类型name):分区表的模式

  • table_name(类型name):分区表的名称

  • partition_name(类型name):父分区的名称

  • subpartition_name(类型name):子分区的名称

  • orig_name(类型name):远程数据源中子分区的名称

  • type(类型text):可支持的一种分区方式,LISTRANGEHASH

  • 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):BEFOREAFTERINSTEAD OF

    如果需要,请修改此列。

  • triggering_event(类型text):INSERTUPDATEDELETETRUNCATE(如果需要多个,则使用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_functionsdb_migrate_triggersdb_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_dataFALSE,否则将迁移表数据。

db_migrate_functions

参数:

  • plugin(类型name,必填):要使用的db_migrator插件的名称

  • pgstage_schema(类型name,默认值pgsql_stage):Postgres 暂存模式的名称

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

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 暂存模式的名称

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

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,无需修改数据类型)。

请注意,它不会迁移函数和触发器,因为默认情况下这些对象的migrateFALSE

底层迁移函数

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

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

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_valuemax_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
)

请注意,此表必须同时包含tablesviews表的列。

  • position定义表列的顺序

  • length表示变长数据类型的长度限制,例如character varying

    对于具有固定长度,或者带有precisionscale的数据类型,请将它设置为 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 是可支持的一种分区方式,LISTRANGEHASH

  • key 用作分区键的列名或表达式

  • values 是分区绑定规范

    • 对于列表分区,values包含列表

    • 对于范围分区,values包含下限和上限(其中下限是包含的,但上限是不包括的)

    • 对于哈希分区,values中唯一的条目,是此分区的余数部分

    非数字值(如时间戳)必须作为字符串常量用引号引用(例如ARRAY['''2022-01-01''','''2023-01-01'''])。

  • 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 应为BEFOREAFTERINSTEAD OF

  • triggering_event 描述了导致触发器执行的 DML 事件,例如DELETEINSERT 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
)

数据类型转换函数

参数:

  • 类型名称(类型text):远程数据源上的数据类型的名称

  • 长度(类型integer):变长非数值类型的最大长度

  • 精度(类型integer):变长数值类型的最大有效位数

  • 小数位数(类型integer):变长数值类型的小数点后位数

结果类型:text

此函数将远程数据源中的数据类型转换为 PostgreSQL 数据类型。如果适用,结果应该包括类型修饰符,例如character varying(20)

标识符转换函数

参数:

  • 标识符名称(类型text):远程数据源上的标识符的名称

结果类型:name

此函数应生成一个 PostgreSQL 对象或列的名称。如果不需要转换,该函数应仅返回其参数,该参数将会自动截断为 63 个字节。

表达式转换函数

参数:

  • SQL 表达式(类型text):远程数据源上的 SQL 表达式,用于列默认值、检查约束或索引定义

结果类型:text

此函数应该尽最大努力在 SQL 方言之间自动转换表达式。在迁移过程中,此函数无法转换的任何内容都必须手动转换。

外表创建函数

参数:

  • 外部服务器(类型name):要迁移的 PostgreSQL 外部服务器

  • 模式(类型name):外表的 PostgreSQL 模式名称

  • 表名(类型name):外表的 PostgreSQL 名称

  • 原始模式(类型text):远程数据源上表的模式

  • 原始表名(类型text):远程数据源上的表名

  • 列名(类型name[]):外表列的名称

  • 列选项(类型jsonb[]):特定于插件的 FDW 列选项

  • 原始列名(类型text[]):远程数据源上的列名

  • 数据类型(类型text[]):外表列的数据类型

  • 可否为空(类型boolean[]):如果外表列是NOT NULL,则为FALSE

  • 额外选项(类型jsonb):特定于插件的选项;这是通过db_migrate_mkforeignoptions参数传递的

结果类型:text

此函数生成一个CREATE FOREIGN TABLE语句,该语句使用这些定义创建一个外表。它是必需的,因为语法会因外部数据包装器而异。