[LightDB]一个实例同时兼容oracle,mysql,postgres
背景：
在适配各业务的过程中，不同的业务往往是从不同的数据库迁移过来的，有的是oracle，有的是mysql，我们需要根据不同的业务去创建不同的实例，然后针对实例按源数据库进行适配。这种方式虽然能满足业务的需求，但是架构极难伸缩，增加了运维成本，扩大了运营风险，部署不够灵活，同时也增加了适配难度。
解决方案：
LightDB为解决此问题，我们除了在实例中引入了工作模式的概念之外，还引入了数据库级别的工作模式（即创建数据库的时候指定工作模式）
通过合理设计， 我们能在同一个实例中同时支持分别运行于oracle，mysql，postgres工作模式的数据库。
为实现降本增效， 使得运维管理化繁为简，进一步提升系统行为一致性，本着开箱即用，按运行模式将相应的插件进行默认安装的目标。
在新的版本中（23.4）中，我们又向前走了一步，我们引入了如下三种新模板用于创建数据库使用：
- template_oracle
- template_mysql
- template_pg
此三个模板分别用于不同模式下创建数据库时的默认模板，为实现最大程度行为与相应模式的一致性，这三个模板均通过运行于对应的工作模式进行创建。
练习：
查看当前模板
lightdb@postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------------+---------+----------+-------------+-------------+---------------------
lt_test | lightdb | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | lightdb | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | lightdb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/lightdb +
| | | | | lightdb=CTc/lightdb
template1 | lightdb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/lightdb +
| | | | | lightdb=CTc/lightdb
template_mysql | lightdb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/lightdb +
| | | | | lightdb=CTc/lightdb
template_oracle | lightdb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/lightdb +
| | | | | lightdb=CTc/lightdb
template_pg | lightdb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/lightdb +
| | | | | lightdb=CTc/lightdb
(7 rows)
创建数据库
lightdb@postgres=# create database oradb with lightdb_syntax_compatible_type oracle; NOTICE: auto create user "oradb" success CREATE DATABASE
查看当前库的模式
lightdb@mydb=# \c oradb You are now connected to database "oradb" as user "lightdb". compatible type: oracle lightdb@oradb=# show lightdb_dblevel_syntax_compatible_type ; lightdb_dblevel_syntax_compatible_type ---------------------------------------- Oracle (1 row)
查看当前库插件列表
lightdb@postgres=# \c oradb
You are now connected to database "oradb" as user "lightdb".
compatible type: oracle
lightdb@oradb=# \dx
List of installed extensions
Name | Version | Schema | Description
--------------------+---------+------------+-----------------------------------------------------------------------------------------------
lt_buffercache | 1.3 | lt_catalog | examine the shared buffer cache
lt_bulkload | 1.0 | lt_catalog | lt_bulkload is a high speed data loading utility for LightDB
lt_hint_plan | 1.3.7 | hint_plan | Controls execution plan with hinting phrases in comment of special form
lt_log_long_xact | 1.1 | lt_catalog | pl/pgsql function to log long running transactions
lt_prewarm | 1.3 | lt_catalog | prewarm relation data
lt_show_plans | 1.0 | lt_catalog | show query plans of all currently running SQL statements
lt_sm | 1.1 | lt_catalog | sm encrypt/decrypt for LightDB
lt_stat_activity | 1.2 | lt_catalog | lt_stat_activity
lt_stat_statements | 1.8 | lt_catalog | Track planning and execution statistics of all SQL statements executed
lt_tempfile | 1.0 | lt_catalog | some functions for Managing temporary files
ltcrypto | 1.3 | lt_catalog | cryptographic functions
ltfce | 1.4 | lt_catalog | Functions and operators that emulate a subset of functions from the lt_catalog
ltfincore | 1.2 | lt_catalog | Examine and manage the os buffer cache
orafce | 3.25 | lt_catalog | Functions and operators that emulate a subset of functions and packages from the Oracle RDBMS
pageinspect | 1.8 | lt_catalog | inspect the contents of database pages at a low level
plorasql | 1.1 | pg_catalog | PL/oraSQL procedural language
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
rum | 1.3 | lt_catalog | RUM index access method
tablefunc | 1.0 | lt_catalog | Functions that manipulate whole tables, including crosstab
tsvector2 | 1.0 | lt_catalog | tsvector2 - extended format of tsvector
uuid-ossp | 1.1 | lt_catalog | Generate universally unique identifiers (UUIDs)
zhparser | 2.1 | lt_catalog | a parser for full-text search of Chinese
(22 rows)
指定模板创建数据库
lightdb@oradb=# create database mydb with template template_mysql lightdb_syntax_compatible_type mysql;
CREATE DATABASE
lightdb@oradb=# \c mydb
You are now connected to database "mydb" as user "lightdb".
compatible type: mysql
lightdb@mydb=# show lightdb_dblevel_syntax_compatible_type ;
lightdb_dblevel_syntax_compatible_type
----------------------------------------
MySql
lightdb@mydb=# \dx
List of installed extensions
Name | Version | Schema | Description
--------------------+---------+------------+---------------------------------------------------------------------------------
lt_buffercache | 1.3 | lt_catalog | examine the shared buffer cache
lt_bulkload | 1.0 | lt_catalog | lt_bulkload is a high speed data loading utility for LightDB
lt_hint_plan | 1.3.7 | hint_plan | Controls execution plan with hinting phrases in comment of special form
lt_log_long_xact | 1.1 | lt_catalog | pl/pgsql function to log long running transactions
lt_prewarm | 1.3 | lt_catalog | prewarm relation data
lt_show_plans | 1.0 | lt_catalog | show query plans of all currently running SQL statements
lt_sm | 1.1 | lt_catalog | sm encrypt/decrypt for LightDB
lt_stat_activity | 1.2 | lt_catalog | lt_stat_activity
lt_stat_statements | 1.8 | lt_catalog | Track planning and execution statistics of all SQL statements executed
lt_tempfile | 1.0 | lt_catalog | some functions for Managing temporary files
ltcrypto | 1.3 | lt_catalog | cryptographic functions
ltfce | 1.4 | lt_catalog | Functions and operators that emulate a subset of functions from the lt_catalog
ltfincore | 1.2 | lt_catalog | Examine and manage the os buffer cache
myfce | 1.7 | lt_catalog | Functions and operators that emulate a subset of functions from the Mysql RDBMS
pageinspect | 1.8 | lt_catalog | inspect the contents of database pages at a low level
plorasql | 1.1 | pg_catalog | PL/oraSQL procedural language
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
rum | 1.3 | lt_catalog | RUM index access method
tablefunc | 1.0 | lt_catalog | Functions that manipulate whole tables, including crosstab
tsvector2 | 1.0 | lt_catalog | tsvector2 - extended format of tsvector
uuid-ossp | 1.1 | lt_catalog | Generate universally unique identifiers (UUIDs)
zhparser | 2.1 | lt_catalog | a parser for full-text search of Chinese
(22 rows)
指定的内置模板与模式不匹配时，默认行为是报错，我们可以通过设置一个参数将报错降级为警告，并自动使用相应模式的默认模板
lightdb@mydb=# create database aodb template template_oracle lightdb_syntax_compatible_type mysql; ERROR: Template template_oracle does not match compatible mode mysql HINT: Available template should be template0 or template_mysql, or set lightdb_ignore_template_compatible_type_mismatch to true lightdb@mydb=# lightdb@mydb=# show lightdb_ignore_template_compatible_type_mismatch ; lightdb_ignore_template_compatible_type_mismatch -------------------------------------------------- off (1 row) lightdb@mydb=# set lightdb_ignore_template_compatible_type_mismatch to true; SET lightdb@mydb=# show lightdb_ignore_template_compatible_type_mismatch ; lightdb_ignore_template_compatible_type_mismatch -------------------------------------------------- on (1 row) lightdb@mydb=# create database aodb template template_oracle lightdb_syntax_compatible_type mysql; WARNING: Template template_oracle ignored, force to use template_mysql for compatible type mysql CREATE DATABASE
guc 参数 lightdb_ignore_template_compatible_type_mismatch 默认为off, 会话级。
注意：
1，用户可以创建自己的模板，或通过已有的模板创建一个新的模板，然后在创建数据库的时候使用自定义模板并指定对应的模式，当用户自己创建的模板与即将创建的数据库的模式不一致的时候，LightDB并未做相应的检查，建议创建的模板与数据库运行模式总是保持一致。
2，template1不再建议使用
