Polardb系列–初体验
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: Polardb系列–初体验
Polardb是阿里云研发的云原生数据库，有基于Oracle的版本，也有兼容MySQL和PostgreSQL的版本。对于兼容Oracle的版本，本质上来看也是基于PostgreSQL来做的，同时从测试来看，看上去也是对标Oracle 11g的兼容性。
由于Polardb支持集群版本，需要集群共享文件系统，我这里没环境，就先玩玩单机版。
+++初始化
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 |
[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 |
+++启动实例
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
[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
|
1 2 3 4 5 6 |
polardb=# create database enmotech; CREATE DATABASE polardb=# polardb=# create user roger createdb createrole login encrypted password 'enmotech'; CREATE ROLE polardb=# exit |
+++登录数据库
|
1 2 3 4 5 6 7 8 9 10 11 12 |
[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。
+++测试简单查询转换
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
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的执行计划还难看。
+++简单函数兼容性测试
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
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的文章；让更多的人参与到国产数据库的学习中。
Leave a Reply
You must be logged in to post a comment.