digoal
2023-07-06
PostgreSQL , PolarDB , Oracle兼容 , IvorySQL
目前在PostgreSQL生态体系中主要有以下几种方式获得Oracle兼容性.
- 安装orafce插件, 兼容性较弱, 包含类型、操作符等. 但是不兼容PL/SQL. https://github.com/orafce/orafce
- 使用EnterpriseDB产品, 兼容性较好, 包括PL/SQL的兼容. https://www.enterprisedb.com/
- 使用PolarDB Oracle兼容版本. 兼容性比较好(可能比EDB还要强一些), 同时有兼容性评估和迁移工具ADAM的加持, 可以较为顺畅的进行迁移. https://www.aliyun.com/product/apsaradb/polardbpg
最近又多了一个Oracle兼容性产品:IvorySQL (由翰高开源), 基础的兼容性继承自orafce插件, 增强部分开源了PL/SQL的兼容性(算是比较有诚意的Oracle兼容性开源).
https://www.ivorysql.org/zh-CN/
https://github.com/IvorySQL/IvorySQL
在macOS中编译安装
git clone --depth 1 -b IVORY_REL_2_STABLE https://github.com/IvorySQL/IvorySQL
cd IvorySQL/
CXX=clang++ CC=clang ./configure --prefix=/Users/digoal/isql
make world -j 8
make install-world
I/O error : Attempt to load network entity http://www.oasis-open.org/docbook/xml/4.5/docbookx.dtd
postgres.sgml:21: warning: failed to load external entity "http://www.oasis-open.org/docbook/xml/4.5/docbookx.dtd"
]>
^
postgres.sgml:23: element book: validity error : No declaration for attribute id of element book
<book id="postgres">
^
make install
cd contrib/
make install
oracle兼容模式通过参数来进行配置, 或者在数据库集群初始化时指定.
cat /Users/digoal/isql/share/postgresql/postgresql.conf.sample
# default configuration for compatibility mode
#compatible_mode = postgres # postgres, oracle
cd /Users/digoal/isql
IT-C02YW2EFLVDL:isql digoal$ ./bin/initdb --help|grep oracle
-c, --compatible-mode compatibility mode (postgres (default), oracle)
初始化集群, 设置为oracle兼容模式
./bin/initdb -D /Users/digoal/isql_data -U postgres -E UTF-8 --lc-collate=C --lc-collate=en_US.UTF-8 -c oracle
参数配置
postgresql.conf
listen_addresses = 'localhost'
port = 5333
max_connections = 100
unix_socket_directories = '., /tmp'
shared_buffers = 128MB
dynamic_shared_memory_type = posix
bgwriter_delay = 10ms
bgwriter_lru_maxpages = 500
max_parallel_workers_per_gather = 0
max_wal_size = 1GB
min_wal_size = 80MB
log_destination = 'csvlog'
logging_collector = on
log_truncate_on_rotation = on
log_timezone = 'Asia/Shanghai'
datestyle = 'iso, mdy'
timezone = 'Asia/Shanghai'
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
default_text_search_config = 'pg_catalog.english'
compatible_mode = oracle
启动实例
/Users/digoal/isql/bin/pg_ctl start -D /Users/digoal/isql_data
连接实例
cd /Users/digoal/isql
IT-C02YW2EFLVDL:bin digoal$ ./psql -h 127.0.0.1 -p 5333 -U postgres postgres
psql (15.3)
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
ivorysql | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
(4 rows)
默认已安装plisql存储过程语言
postgres=# \c ivorysql
You are now connected to database "ivorysql" as user "postgres".
ivorysql=# \dx
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+------------------------------
plisql | 1.0 | pg_catalog | PL/iSQL procedural language
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
Oracle相关兼容性测试目前还没有放到stable分支:
未来可持续关注IvorySQL几个方面的发展:
- Oracle 兼容性能力
- 类似 ADAM这样的Oracle兼容性评估产品, https://www.aliyun.com/product/adam
- 更多的 Oracle test case
- 迁移指导手册, 对应不兼容的部分, 改写指导.
- 迁移案例
