本页面介绍了如何将开源 PostgreSQL 数据库(从现在简称为 PostgreSQL)迁移到 Spanner PostgreSQL 方言数据库(今后统称 Spanner)。
如需了解如何迁移到 Spanner 和 GoogleSQL 方言,请参阅从 PostgreSQL 迁移到 Spanner(GoogleSQL 方言)。
迁移限制
Spanner 使用的某些概念与其他企业数据库管理工具有所不同,因此您可能需要对应用架构进行某些调整,以充分利用其功能。您可能还需要使用 Google Cloud 中的某些其他服务作为 Spanner 的补充,以满足您的需求。
存储过程和触发器
Spanner 不支持在数据库级层运行用户代码,因此在迁移过程中,必须将由数据库级层存储过程和触发器实现的业务逻辑迁移到应用中。
序列
Spanner 建议将 UUID 版本 4 作为生成主键值的默认方法。GENERATE_UUID()
函数(GoogleSQL、PostgreSQL)返回表示为 STRING
类型的 UUID 版本 4 值。
如果您需要生成整数值,Spanner 支持位倒序正序列(GoogleSQL、PostgreSQL),这些序列可生成在 64 位正数空间中均匀分布的值。您可以使用这些数字来避免热点问题。
如需了解详情,请参阅主键默认值策略。
访问权限控制
Spanner 支持在表级和列级实现精细的访问权限控制。不支持对视图进行精细的访问权限控制。如需了解详情,请参阅精细访问权限控制简介。
迁移过程
迁移涉及以下任务:
- 将 PostgreSQL 架构映射到 Spanner。
- 转换 SQL 查询。
- 创建 Spanner 实例、数据库和架构。
- 重构应用以使用 Spanner 数据库。
- 迁移数据。
- 验证新系统并将其切换到生产状态。
第 1 步:将 PostgreSQL 架构映射到 Spanner
如需将数据库从开源 PostgreSQL 迁移到 Spanner,首先要确定您必须进行哪些架构更改。
主键
在 Spanner 中,必须存储多个行的每个表都必须具有一个主键,该主键由表的一个或多个列组成。表的主键唯一标识表中的每一行,并且 Spanner 使用主键对表中的行进行排序。由于 Spanner 高度分布式,因此请务必选择可以随数据增长良好扩缩的主键生成技术。如需了解详情,请参阅我们推荐的主键迁移策略。
请注意,指定主键后,如果不删除并重新创建表,您以后就无法添加或移除主键列,也无法更改主键值。如需详细了解如何指定主键,请参阅架构和数据模型 - 主键。
索引
PostgreSQL b-tree 索引类似于 Spanner 中的二级索引。在 Spanner 数据库中,您可以使用二级索引将常用搜索列编入索引,以提高性能,并替换表中指定的任何 UNIQUE
限制条件。例如,如果您的 PostgreSQL DDL 具有以下语句:
CREATE TABLE customer (
id CHAR (5) PRIMARY KEY,
first_name VARCHAR (50),
last_name VARCHAR (50),
email VARCHAR (50) UNIQUE
);
您可以在 Spanner DDL 中使用以下语句:
CREATE TABLE customer (
id VARCHAR(5) PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(50)
);
CREATE UNIQUE INDEX customer_emails ON customer(email);
您可以通过在 psql
中运行 \di
元命令来查找任何 PostgreSQL 表的索引。
确定所需的索引后,添加 CREATE INDEX
语句进行创建。遵循二级索引中的指南。
Spanner 以表的形式实现索引,因此索引单调递增的列(例如包含 TIMESTAMP
数据的列)可能会导致热点。如需详细了解避免热点的方法,请参阅有关 Spanner 的 DBA 须知事项,第 1 部分:键和索引。
Spanner 实现二级索引的方式与表相同,因此要用作索引键的列值将受到与表主键相同的限制。这也意味着,索引具有与 Spanner 表相同的一致性保证。
使用二级索引查找值等效于使用表联接执行查询。您可以使用索引提高查询性能,方法是使用 INCLUDE
子句在二级索引中存储原始表的列值的副本,并将其设为覆盖索引。
只有在索引本身存储了所有查询列(覆盖查询)时,Spanner 的查询优化工具才会自动使用二级索引。如需在查询原始表中的列时强制使用索引,您必须在 SQL 语句中使用 FORCE INDEX 指令,例如:
SELECT *
FROM MyTable /*@ FORCE_INDEX=MyTableIndex */
WHERE IndexedColumn=$1;
以下示例 DDL 语句将为 Albums 表创建二级索引:
CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle);
如果在数据加载后创建其他索引,则填充索引可能需要一些时间。建议您将索引添加速率限制为平均每天三次。如需详细了解如何创建二级索引,请参阅二级索引。如需详细了解有关索引创建方面的限制,请参阅架构更新。
视图
Spanner 视图是只读的。但不能用于插入、更新或删除数据。如需了解详情,请参阅视图。
生成的列
Spanner 支持生成的列。请参阅创建和管理生成的列,了解语法差异和限制。
表交错
Spanner 提供了一项可将两个表定义为具有一对多父子关系的功能。借助此功能,您可以在存储空间内将子数据行交错到其对应的父行旁边,从而有效地预联接表,并提高父项和子项一起查询时的数据检索效率。
子表的主键必须以父表的主键列开头。从子行的角度来看,父行主键称为外键。您最多可以定义 6 个级别的父子关系。
您可以为子表定义 ON DELETE
操作,以确定父行被删除后会发生什么:要么所有子行都被删除,要么当子行存在时父行被删除。
以下示例演示了如何创建一个与之前定义的父级 Singers 表互相交错的 Albums 表:
CREATE TABLE Albums (
SingerID bigint,
AlbumID bigint,
AlbumTitle varchar,
PRIMARY KEY (SingerID, AlbumID)
)
INTERLEAVE IN PARENT Singers ON DELETE CASCADE;
如需了解详情,请参阅创建交错表。
数据类型
下表列出了 Spanner 的 PostgreSQL 页面不支持的开源 PostgreSQL 数据类型。
数据类型 | 改用 |
---|---|
bigserial,serial8 | bigint、int8 |
bit [ (n) ] | - |
位不同 [ (n) ]、varbit [ (n) ] | - |
盒子 | - |
字符 [ (n) ], 字符 [ (n) ] | 字符转换 |
cidr | PLAIN_TAXT; |
circle | - |
inet | PLAIN_TAXT; |
整数、int4 | bigint、int8 |
间隔 [字段] [ (p) ] | bigint |
json | jsonb |
line | - |
lseg | - |
macaddr | PLAIN_TAXT; |
money | 数字、小数 |
路径 | - |
pg_lsn | - |
point | - |
polygon | - |
realfloat4 | 双精度、float8 |
smallint、int2 | bigint、int8 |
smallserial、serial2 | bigint、int8 |
序列号、序列号 | bigint、int8 |
time [ (p) ] [无时区] | 文本(使用 HH:MM:SS.sss 表示法) |
time [ (p) ] 加上 time zonetimetz | 且使用 HH:MM:SS.sss+ZZZZ 符号表示。或使用两列。 |
timestamp [ (p) ] [无时区] | text 或 timestamptz |
tsquery | - |
tsvector | - |
txid_snapshot | - |
uuid | text 或 bytea |
xml | PLAIN_TAXT; |
第 2 步:转换任何 SQL 查询
Spanner 提供了许多开源 PostgreSQL 函数,可帮助减轻转换负担。
在执行 SQL 查询时,您可以使用 Google Cloud 控制台中的 Spanner Studio 页面来分析该查询。对大型表执行全表扫描时,查询费用通常很高,因此应谨慎使用。如需详细了解如何优化 SQL 查询,请参阅 SQL 最佳实践文档。
第 3 步:创建 Spanner 实例、数据库和架构
创建实例并使用 PostgreSQL 方言创建数据库。然后使用 PostgreSQL 数据定义语言 (DDL) 创建架构。
使用 pg_dump
创建用于定义 PostgreSQL 数据库中对象的 DDL 语句,然后按照前面部分中的说明修改语句。更新 DDL 语句后,请使用 DDL 语句在 Spanner 实例中创建数据库。
如需了解详情,请参阅以下主题:
第 4 步:重构应用
添加应用逻辑,以考虑修改后的架构和 SQL 查询,并替换过程和触发器等数据库驻留逻辑。
第 5 步:迁移数据
您可以通过以下两种方式迁移数据:
使用 Harbourbridge。
Harbourbridge 支持架构迁移和数据迁移。您可以导入 pg_dump 文件或 CSV 文件,也可以通过直接连接到开源 PostgreSQL 数据库来导入。
使用
COPY FROM STDIN
命令。如需了解详情,请参阅用于导入数据的 COPY 命令。