Polardb是阿里云研发的云原生数据库，有基于Oracle的版本，也有兼容MySQL和PostgreSQL的版本。对于兼容Oracle的版本，本质上来看也是基于PostgreSQL来做的，同时从测试来看，看上去也是对标Oracle 11g的兼容性。
由于Polardb支持集群版本，需要集群共享文件系统，我这里没环境，就先玩玩单机版。
+++初始化
[polardb@td1 ~]$ initdb -D /data/polardb/pgdata
The files belonging to this database system will be owned by user "polardb".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /data/polardb/pgdata ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... 2020-06-10 15:51:15 CST LOG: polar_csnlog remove /data/polardb/pgdata/pg_csnlog/0000
ok
performing post-bootstrap initialization ... ok
creating edb sys ... ok
loading edb contrib modules ...
edb_redwood_bytea.sql ok
edb_redwood_date.sql ok
dbms_alert_public.sql ok
dbms_alert.plb ok
dbms_job_public.sql ok
dbms_job.plb ok
dbms_lob_public.sql ok
dbms_lob.plb ok
dbms_output_public.sql ok
dbms_output.plb ok
dbms_pipe_public.sql ok
dbms_pipe.plb ok
dbms_rls_public.sql ok
dbms_rls.plb ok
dbms_sql_public.sql ok
dbms_sql.plb ok
dbms_utility_public.sql ok
dbms_utility.plb ok
dbms_aqadm_public.sql ok
dbms_aqadm.plb ok
dbms_aq_public.sql ok
dbms_aq.plb ok
dbms_profiler_public.sql ok
dbms_profiler.plb ok
dbms_random_public.sql ok
dbms_random.plb ok
dbms_redact_public.sql ok
dbms_redact.plb ok
dbms_lock_public.sql ok
dbms_lock.plb ok
dbms_scheduler_public.sql ok
dbms_scheduler.plb ok
dbms_crypto_public.sql ok
dbms_crypto.plb ok
dbms_mview_public.sql ok
dbms_mview.plb ok
dbms_session_public.sql ok
dbms_session.plb ok
edb_gen.sql ok
edb_objects.sql ok
edb_redwood_casts.sql ok
edb_redwood_strings.sql ok
edb_redwood_views.sql ok
utl_encode_public.sql ok
utl_encode.plb ok
utl_http_public.sql ok
utl_http.plb ok
utl_file.plb ok
utl_tcp_public.sql ok
utl_tcp.plb ok
utl_smtp_public.sql ok
utl_smtp.plb ok
utl_mail_public.sql ok
utl_mail.plb ok
utl_url_public.sql ok
utl_url.plb ok
commoncriteria.sql ok
waitstates.sql ok
installing extension edb_dblink_libpq ... ok
installing extension edb_dblink_oci ... ok
installing extension pldbgapi ... ok
snap_tables.sql ok
snap_functions.sql ok
sys_stats.sql ok
loading edb contrib modules ...
installing extension polar_utl_raw ... ok
installing extension polar_dbms_obfuscation_toolkit ... ok
installing extension appendchildxml ... ok
installing extension polar_asciistr ... ok
installing extension polar_cyclechecker_noticer ... ok
installing extension polar_datetime_double ... ok
finalizing initial databases ... ok
syncing data to disk ... ok
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
pg_ctl -D /data/polardb/pgdata -l logfile start
+++启动实例
[polardb@td1 pgdata]$ pg_ctl -D /data/polardb/pgdata -l /data/polardb/pgdata/polardb.log start
waiting for server to start.... done
server started
[polardb@td1 pgdata]$
[polardb@td1 pgdata]$ cat polardb.log
2020-06-10 15:53:57 CST LOG: listening on IPv4 address "127.0.0.1", port 5444
2020-06-10 15:53:57 CST LOG: listening on Unix socket "/tmp/.s.PGSQL.5444"
2020-06-10 15:53:58 CST LOG:
** EnterpriseDB Dynamic Tuning Agent ********************************************
* System Utilization: 66 % *
* Database Version: 11.2.9 *
* RAM: 39.3 GB *
* Shared Memory: 40239 MB *
* Max DB Connections: 112 *
* Autovacuum: on *
* Autovacuum Naptime: 60 Seconds *
*********************************************************************************
2020-06-10 15:53:58 CST LOG: database system was shut down at 2020-06-10 15:51:19 CST
2020-06-10 15:53:58 CST LOG: database system is ready to accept connections
2020-06-10 15:53:58 CST LOG: dbms_aq launcher started
从上述启动日志来看,Polardb也支持对于资源的自动调节。
+++创建db
polardb=# create database enmotech;
CREATE DATABASE
polardb=#
polardb=# create user roger createdb createrole login encrypted password 'enmotech';
CREATE ROLE
polardb=# exit
+++登录数据库
[polardb@td1 pgdata]$ psql -U roger -W enmotech
Password:
psql (11.2.9)
Type "help" for help.
enmotech=> select version();
version
------------------------------------------------------------------
PostgreSQL 11.2 (POLARDB Database Compatible with Oracle 11.2.9)
(1 row)
enmotech=>
从上面查询来看,PolarDB for Oracle版本，本质上是基于PostgreSQL 11.2来进行二次开发的。至于兼容性，猜测应该是对标Oracle 11g。
+++测试简单查询转换
enmotech=> explain select distinct t2.a from t2 where t2.a not in (select t1.a from t1 where t1.a=t2.a and t2.a=3) ;
QUERY PLAN
------------------------------------------------------------------------------
Unique (cost=9286.92..9289.12 rows=191 width=32)
-> Sort (cost=9286.92..9288.02 rows=440 width=32)
Sort Key: t2.a
-> Seq Scan on t2 (cost=0.00..9267.60 rows=440 width=32)
Filter: (NOT (SubPlan 1))
SubPlan 1
-> Result (cost=0.00..21.00 rows=4 width=32)
One-Time Filter: (t2.a = '3'::numeric)
-> Seq Scan on t1 (cost=0.00..21.00 rows=4 width=32)
Filter: (a = t2.a)
(10 rows)
enmotech=> explain select distinct t2.a from t2 where exists (select t1.a from t1 where t1.a=t2.a and t2.a=3) ;
QUERY PLAN
----------------------------------------------------------------------
Unique (cost=0.00..42.10 rows=4 width=32)
-> Nested Loop Semi Join (cost=0.00..42.10 rows=4 width=32)
-> Seq Scan on t2 (cost=0.00..21.00 rows=4 width=32)
Filter: (a = '3'::numeric)
-> Materialize (cost=0.00..21.02 rows=4 width=32)
-> Seq Scan on t1 (cost=0.00..21.00 rows=4 width=32)
Filter: (a = '3'::numeric)
(7 rows)
enmotech=>
enmotech=> explain select distinct t2.a from t2 where not exists (select t1.a from t1 where t1.a=t2.a and t2.a=3) ;
QUERY PLAN
------------------------------------------------------------------------
HashAggregate (cost=64.12..66.12 rows=200 width=32)
Group Key: t2.a
-> Hash Anti Join (cost=29.80..61.93 rows=878 width=32)
Hash Cond: (t2.a = t1.a)
Join Filter: (t2.a = '3'::numeric)
-> Seq Scan on t2 (cost=0.00..18.80 rows=880 width=32)
-> Hash (cost=18.80..18.80 rows=880 width=32)
-> Seq Scan on t1 (cost=0.00..18.80 rows=880 width=32)
(8 rows)
从上述执行计划来看,not exsits 类似Oracle和Oceanbase的处理；对于not in、exists的处理似乎有点不同。 同时我不得不说,基于PostgreSQL的这个执行计划很难看呀；比Oceanbase的执行计划还难看。
+++简单函数兼容性测试
enmotech=> select sysdate from dual;
sysdate
--------------------
13-JUN-20 16:59:03
(1 row)
enmotech=> select regexp_substr('400-660-8775', '[0-9][0-9][0-9]', 1, 1) from dual;
regexp_substr
---------------
400
(1 row)
enmotech=> select substr('400-660-8775',1,5) from dual;
substr
--------
400-6
(1 row)
enmotech=> select regexp_instr('400-660-8775', '[0-9][0-9][0-9]', 1, 1) from dual;
regexp_instr
--------------
1
(1 row)
enmotech=> select instr('400-660-8775', '[0-9][0-9][0-9]', 1, 1) from dual;
instr
-------
0
(1 row)
enmotech=> select round(to_date('16-DEC-09','DD-MON-YY'),'MONTH') from dual;
round
--------------------
01-JAN-10 00:00:00
(1 row)
enmotech=> select trunc(sysdate) from dual;
trunc
--------------------
13-JUN-20 00:00:00
(1 row)
从测试来看，很多常用函数都支持，基本兼容Oracle。后面我将抽空进行更加全面的测试，争取写一个系列的Polardb的文章；让更多的人参与到国产数据库的学习中。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
