Babelfish

PostgreSQL 上的 MS SQL Server 线协议兼容性

Pigsty 允许用户使用 Babelfish 和 WiltonDB 创建与 Microsoft SQL Server 兼容的 PostgreSQL 集群！

Babelfish：由 AWS 开源的 MSSQL（Microsoft SQL Server）兼容性扩展
WiltonDB：专注于集成 Babelfish 的 PostgreSQL 内核发行版

Babelfish 是一个 PostgreSQL 扩展，但它运行在经过轻微修改的 PostgreSQL 内核分支上，WiltonDB 在 EL/Ubuntu 系统上提供编译后的内核二进制文件和扩展二进制包。

Pigsty 可以用 WiltonDB 替换原生 PostgreSQL 内核，提供开箱即用的 MSSQL 兼容集群，以及常见 PostgreSQL 集群支持的所有功能，如 HA、PITR、IaC、监控等。

WiltonDB 与 PostgreSQL 15 非常相似，但不能直接使用原版 PostgreSQL 扩展。WiltonDB 有几个重新编译的扩展，如 system_stats、pg_hint_plan 和 tds_fdw。

集群将监听默认的 PostgreSQL 端口和默认的 MSSQL 1433 端口，通过 TDS WireProtocol 在此端口上提供 MSSQL 服务。您可以使用任何 MSSQL 客户端连接到 Pigsty 提供的 MSSQL 服务，例如 SQL Server Management Studio，或使用 sqlcmd 命令行工具。

快速开始

使用 mssql 配置模板安装 Pigsty。

curl -fsSL https://repo.pigsty.io/get | bash -s v3.7.0; cd ~/pigsty;
./configure -c mssql     # 使用 mssql (babelfish) 模板
./install.yml            # 使用 pigsty 安装一切

对于生产部署，请确保在运行 install 剧本之前修改 pigsty.yml 配置中的密码参数。

注意事项

在安装和部署 MSSQL 模块时，请特别注意以下几点：

WiltonDB 在 EL（7/8/9）和 Ubuntu（20.04/22.04）上可用，但在 Debian 系统上不可用。
WiltonDB 目前基于 PostgreSQL 15 编译，因此您需要指定 pg_version: 15。
在 EL 系统上，wiltondb 二进制文件默认安装在 /usr/bin/ 目录中，而在 Ubuntu 系统上，它安装在 /usr/lib/postgresql/15/bin/ 目录中，这与官方 PostgreSQL 二进制文件位置不同。
在 WiltonDB 兼容模式下，HBA 密码认证规则需要使用 md5 而不是 scram-sha-256。因此，您需要覆盖 Pigsty 的默认 HBA 规则集，并在 dbrole_readonly 通配符认证规则之前插入 SQL Server 所需的 md5 认证规则。
WiltonDB 只能为主数据库启用，您应该指定一个用户作为 Babelfish 超级用户，允许 Babelfish 创建数据库和用户。默认是 mssql 和 dbuser_myssql。如果您更改了这个，您还应该修改 files/mssql.sql 中的用户。
WiltonDB TDS 线协议兼容性插件 babelfishpg_tds 需要在 shared_preload_libraries 中启用。
启用 WiltonDB 扩展后，它监听默认的 MSSQL 端口 1433。您可以覆盖 Pigsty 的默认服务定义，将 primary 和 replica 服务重定向到端口 1433 而不是 5432 / 6432 端口。

需要为 MSSQL 数据库集群配置以下参数：

pg-meta:
  hosts:
    10.10.10.10: { pg_seq: 1, pg_role: primary }
  vars:
    pg_cluster: pg-meta
    pg_users:
      - {name: dbuser_mssql ,password: DBUser.MSSQL ,superuser: true, pgbouncer: true ,roles: [dbrole_admin], comment: superuser & owner for babelfish  }
    pg_databases:
      - name: mssql
        baseline: mssql.sql
        extensions: [uuid-ossp, babelfishpg_common, babelfishpg_tsql, babelfishpg_tds, babelfishpg_money, pg_hint_plan, system_stats, tds_fdw]
        owner: dbuser_mssql
        parameters: { 'babelfishpg_tsql.migration_mode' : 'multi-db' }
        comment: babelfish cluster, a MSSQL compatible pg cluster
    node_crontab: [ '00 01 * * * postgres /pg/bin/pg-backup full' ] # 每天凌晨 1 点进行全量备份

    # Babelfish / WiltonDB 临时设置
    pg_mode: mssql                     # Microsoft SQL Server 兼容模式
    pg_version: 15
    pg_packages: [ wiltondb, pgsql-common, sqlcmd ]
    pg_libs: 'babelfishpg_tds, pg_stat_statements, auto_explain' # 将 timescaledb 添加到 shared_preload_libraries
    pg_default_hba_rules: # 覆盖 babelfish 集群的默认 HBA 规则
      - { user: '${dbsu}'    ,db: all         ,addr: local     ,auth: ident ,title: 'dbsu access via local os user ident' }
      - { user: '${dbsu}'    ,db: replication ,addr: local     ,auth: ident ,title: 'dbsu replication from local os ident' }
      - { user: '${repl}'    ,db: replication ,addr: localhost ,auth: pwd   ,title: 'replicator replication from localhost' }
      - { user: '${repl}'    ,db: replication ,addr: intra     ,auth: pwd   ,title: 'replicator replication from intranet' }
      - { user: '${repl}'    ,db: postgres    ,addr: intra     ,auth: pwd   ,title: 'replicator postgres db from intranet' }
      - { user: '${monitor}' ,db: all         ,addr: localhost ,auth: pwd   ,title: 'monitor from localhost with password' }
      - { user: '${monitor}' ,db: all         ,addr: infra     ,auth: pwd   ,title: 'monitor from infra host with password' }
      - { user: '${admin}'   ,db: all         ,addr: infra     ,auth: ssl   ,title: 'admin @ infra nodes with pwd & ssl' }
      - { user: '${admin}'   ,db: all         ,addr: world     ,auth: ssl   ,title: 'admin @ everywhere with ssl & pwd' }
      - { user: dbuser_mssql ,db: mssql       ,addr: intra     ,auth: md5   ,title: 'allow mssql dbsu intranet access' } # <--- 为 mssql 用户使用 md5 认证方法
      - { user: '+dbrole_readonly',db: all    ,addr: localhost ,auth: pwd   ,title: 'pgbouncer read/write via local socket' }
      - { user: '+dbrole_readonly',db: all    ,addr: intra     ,auth: pwd   ,title: 'read/write biz user via password' }
      - { user: '+dbrole_offline' ,db: all    ,addr: intra     ,auth: pwd   ,title: 'allow etl offline tasks from intranet' }
    pg_default_services: # 将 primary 和 replica 服务路由到 mssql 端口 1433
      - { name: primary ,port: 5433 ,dest: 1433  ,check: /primary   ,selector: "[]" }
      - { name: replica ,port: 5434 ,dest: 1433  ,check: /read-only ,selector: "[]" , backup: "[? pg_role == `primary` || pg_role == `offline` ]" }
      - { name: default ,port: 5436 ,dest: postgres ,check: /primary   ,selector: "[]" }
      - { name: offline ,port: 5438 ,dest: postgres ,check: /replica   ,selector: "[? pg_role == `offline` || pg_offline_query ]" , backup: "[? pg_role == `replica` && !pg_offline_query]" }

您可以在 pg_databases 和 pg_users 部分定义业务数据库和用户：

#----------------------------------#
# pgsql (singleton on current node)
#----------------------------------#
# 这是一个在当前节点上安装了 postgis 和 timescaledb 的单节点 postgres 集群示例，包含一个业务数据库和两个业务用户
pg-meta:
  hosts:
    10.10.10.10: { pg_seq: 1, pg_role: primary } # <---- 具有读写能力的主实例
  vars:
    pg_cluster: pg-test
    pg_users:                           # 创建 MSSQL 超级用户
      - {name: dbuser_mssql ,password: DBUser.MSSQL ,superuser: true, pgbouncer: true ,roles: [dbrole_admin], comment: superuser & owner for babelfish  }
    pg_primary_db: mssql                # 使用 `mssql` 作为主 sql server 数据库
    pg_databases:
      - name: mssql
        baseline: mssql.sql             # 初始化 babelfish 数据库和用户
        extensions:
          - { name: uuid-ossp          }
          - { name: babelfishpg_common }
          - { name: babelfishpg_tsql   }
          - { name: babelfishpg_tds    }
          - { name: babelfishpg_money  }
          - { name: pg_hint_plan       }
          - { name: system_stats       }
          - { name: tds_fdw            }
        owner: dbuser_mssql
        parameters: { 'babelfishpg_tsql.migration_mode' : 'multi-db' }
        comment: babelfish cluster, a MSSQL compatible pg cluster

客户端访问

您可以使用任何兼容 SQL Server 的客户端工具来访问此数据库集群。

Microsoft 提供 sqlcmd 作为官方命令行工具。

此外，他们还有一个 go 版本的 cli 工具：go-sqlcmd

安装 go-sqlcmd：

curl -LO https://github.com/microsoft/go-sqlcmd/releases/download/v1.4.0/sqlcmd-v1.4.0-linux-amd64.tar.bz2
tar xjvf sqlcmd-v1.4.0-linux-amd64.tar.bz2
sudo mv sqlcmd* /usr/bin/

开始使用 go-sqlcmd

$ sqlcmd -S 10.10.10.10,1433 -U dbuser_mssql -P DBUser.MSSQL
1> select @@version
2> go
version
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Babelfish for PostgreSQL with SQL Server Compatibility - 12.0.2000.8
Oct 22 2023 17:48:32
Copyright (c) Amazon Web Services
PostgreSQL 15.4 (EL 1:15.4.wiltondb3.3_2-2.el8) on x86_64-redhat-linux-gnu (Babelfish 3.3.0)

(1 row affected)

您可以将服务流量路由到 MSSQL 1433 端口而不是 5433/5434：

# 将所有成员上的 5433 路由到主节点上的 1433
sqlcmd -S 10.10.10.11,5433 -U dbuser_mssql -P DBUser.MSSQL

# 将所有成员上的 5434 路由到副本上的 1433
sqlcmd -S 10.10.10.11,5434 -U dbuser_mssql -P DBUser.MSSQL

安装

如果您有互联网访问权限，可以将 WiltonDB 仓库添加到节点并直接作为节点包安装：

node_repo_modules: local,node,pgsql,mssql
node_packages: [ wiltondb ]

使用以下命令安装 wiltondb：

./node.yml -t node_repo,node_pkg

在同一个节点上安装原版 PostgreSQL 和 WiltonDB 是可以的，但一次只能运行其中一个，不建议在生产环境中这样做。

扩展

PGSQL 模块的大多数扩展（非 SQL 类）不能直接在 MSSQL 模块的 WiltonDB 内核上使用，需要重新编译。

WiltonDB 目前附带以下扩展插件：

名称	版本	注释
dblink	1.2	从数据库内连接到其他 PostgreSQL 数据库
adminpack	2.1	PostgreSQL 的管理功能
dict_int	1.0	整数的文本搜索字典模板
intagg	1.1	整数聚合器和枚举器（已过时）
dict_xsyn	1.0	扩展同义词处理的文本搜索字典模板
amcheck	1.3	验证关系完整性的函数
autoinc	1.0	自动递增字段的函数
bloom	1.0	bloom 访问方法 - 基于签名文件的索引
fuzzystrmatch	1.1	确定字符串之间的相似性和距离
intarray	1.5	1-D 整数数组的函数、操作符和索引支持
btree_gin	1.3	在 GIN 中索引常见数据类型的支持
btree_gist	1.7	在 GiST 中索引常见数据类型的支持
hstore	1.8	存储（键，值）对集合的数据类型
hstore_plperl	1.0	hstore 和 plperl 之间的转换
isn	1.2	国际产品编号标准的数据类型
hstore_plperlu	1.0	hstore 和 plperlu 之间的转换
jsonb_plperl	1.0	jsonb 和 plperl 之间的转换
citext	1.6	不区分大小写字符串的数据类型
jsonb_plperlu	1.0	jsonb 和 plperlu 之间的转换
jsonb_plpython3u	1.0	jsonb 和 plpython3u 之间的转换
cube	1.5	多维立方体的数据类型
hstore_plpython3u	1.0	hstore 和 plpython3u 之间的转换
earthdistance	1.1	计算地球表面的大圆距离
lo	1.1	大对象维护
file_fdw	1.0	平面文件访问的外部数据包装器
insert_username	1.0	跟踪谁更改了表的函数
ltree	1.2	分层树状结构的数据类型
ltree_plpython3u	1.0	ltree 和 plpython3u 之间的转换
pg_walinspect	1.0	检查 PostgreSQL 写前日志内容的函数
moddatetime	1.0	跟踪最后修改时间的函数
old_snapshot	1.0	支持 old_snapshot_threshold 的实用程序
pgcrypto	1.3	加密函数
pgrowlocks	1.2	显示行级锁定信息
pageinspect	1.11	在低级别检查数据库页面的内容
pg_surgery	1.0	对损坏关系进行手术的扩展
seg	1.4	表示线段或浮点区间的数据类型
pgstattuple	1.5	显示元组级统计信息
pg_buffercache	1.3	检查共享缓冲区缓存
pg_freespacemap	1.2	检查空闲空间映射（FSM）
postgres_fdw	1.1	远程 PostgreSQL 服务器的外部数据包装器
pg_prewarm	1.2	预热关系数据
tcn	1.0	触发的更改通知
pg_trgm	1.6	基于三元组的文本相似度测量和索引搜索
xml2	1.1	XPath 查询和 XSLT
refint	1.0	实现引用完整性的函数（已过时）
pg_visibility	1.2	检查可见性映射（VM）和页面级可见性信息
pg_stat_statements	1.10	跟踪所有已执行 SQL 语句的规划和执行统计信息
sslinfo	1.2	SSL 证书信息
tablefunc	1.0	操作整个表的函数，包括交叉表
tsm_system_rows	1.0	接受行数作为限制的 TABLESAMPLE 方法
tsm_system_time	1.0	接受毫秒时间作为限制的 TABLESAMPLE 方法
unaccent	1.1	去除重音符号的文本搜索字典
uuid-ossp	1.1	生成通用唯一标识符（UUID）
plpgsql	1.0	PL/pgSQL 过程语言
babelfishpg_money	1.1.0	babelfishpg_money
system_stats	2.0	PostgreSQL 的 EnterpriseDB 系统统计信息
tds_fdw	2.0.3	查询 TDS 数据库（Sybase 或 Microsoft SQL Server）的外部数据包装器
babelfishpg_common	3.3.3	Transact SQL 数据类型支持
babelfishpg_tds	1.0.0	TDS 协议扩展
pg_hint_plan	1.5.1
babelfishpg_tsql	3.3.1	Transact SQL 兼容性