透明加密 Greenplum 研发工程师 王湯舟 1. 我们所面临的问题 2. 基于pgcrypto的数据加密方案 3. GPDB数据透明加密方案设计 4. GPDB数据透明加解密流程 5. 总结 ## 我们所面临的问题 ## 什么是Greenplum数据库 GPDB 一款开源的HTAP数据库： • MPP架构 完整的事务+ACID+标准SQL支持 • 支持上千个节点的部署 • 支持PB级文件 支持PB级文件 • 丰富的ETL和外部组件 • 支持Python/R/Java直接访问处理数据库数据 https://github.com/greenplum-db/gpdb ## GPDB的数据安全运行模式 用户 连接数据库 • 运行业务 DBA 管理数据库 • 业务审计 System Admin 管理集群 数据备份恢复   ## GPDB的数据安全管理模式 用户 连接数据库 • 运行业务 DBA 管理数据库 业务审计 System Admin • 管理集群 数据备份恢复 ![Image](/uploads/do

https://github.com/greenplum-db/gpdb，内核代码基于PostgreSQL 9.4。目前（2019/04/23）主干分支的代码基于PostgreSQL 9.4。合并到PostgreSQL 9.5的工作也已经开始，有关最新工作进展请参见：https://github.com/greenplum-db/gpdb-postgres-merge。 ### 1. 从源代码编译 --enable-debug --enable-cassert --disable-orca --disable-gpcloud \ --disable-gpfdist --prefix=$HOME/gpdb.master $ make [-j4] $ make install 在苹果系统上初始化Greenplum单节点集群时，需要做些准备工作： - 添加export PGHOST=localhost至~/ kern.maxfiles=65535 kern.maxfilesperproc=65535 net.inet.tcp.msl=60 $ cd gpAux/gpdemo $ source $HOME/gpdb.master/greenplum_path.sh $ export PGHOST='hostname' $ make $ source gpdemo-env.sh $ psql

[Image](/uploads/documents/d/5/7/5/d575d5207f04162629e7107d02c9757d/p5_2.jpg) ## 内核参数 - 通常情况下，内核参数按照GPDB安装手册配置，如需要增加连接数支持，以下参数需要增大 kernel.shmmax = 1000000000 kernel.sem = 250 512000 100 2048 Redhat 6 per\_query|256GB|256GB| |superuser\_reserved\_connections|50|NA| ## I nstance实例数的配置建议 - Instance是GPDB的最小并行单元，每个Segment节点一般配置4~8个Instance，初始化完成后很难修改，需要提前规划； - 每个Instance都是一套独立的进程，当客户端发起一个请求时，每个Instance都将FORK子进程并行工作； 7f04162629e7107d02c9757d/p9_2.jpg) Set of Active Segment Instances ## 统计信息收集 对于系统表和用户表需要收集统计信息，GPDB的查询计划是cost base的，统计信息的准确性对查询计划的优劣有很大影响； 对于字段数较多的表，可关闭gp_autostate_mode (on_no_stats=>none)，仅对必

Base ## V ision Greenplum in the long run will be based on latest PostgreSQL ## Upcoming Roadmap GPDB 5.0 release upgrade from PG 8.2 to PG 8.3 (2017 time frame) - JSON/JSONB Full Text Search - Improved Optimization ## V ision Our new cost-based optimizer, Orca, will become the default optimizer in GPDB for all workloads, performing equal or better than legacy optimizer in all cases. ## Current Status Certification of GPHDFS with latest Cloudera, MapR, Hortonworks • Porting PostgreSQL Foreign Data Wrappers to GPDB (longer term) ## Storage & Backup ## V ision More ‘9s’, and increased support for mission critical

optimizer_print_optimization_stats 1922 optimizer_skew_factor 1923 optimizer_sort_factor 1923 optimizer_use_gpdb_allocators 1923 optimizer_xform_bind_threshold 1923 VMware Greenplum 6 Documentation VMware, Inc 108 causing a problem when client encoding is different from server encoding. The problem is fixed by using GPDB server encoding by default, whether it is QD or QE, making the default encoding of external tables This issue is resolved; Greenplum now returns the error “This query is not currently supported by GPDB.” when it encounters a function invocation that it does not support. 31011 - Server: Execution Resolved

optimizer_print_missing_stats 1850 optimizer_print_optimization_stats 1851 optimizer_sort_factor 1851 optimizer_use_gpdb_allocators 1851 optimizer_xform_bind_threshold 1851 VMware Tanzu Greenplum 6 Documentation VMware causing a problem when client encoding is different from server encoding. The problem is fixed by using GPDB server encoding by default, whether it is QD or QE, making the default encoding of external tables This issue is resolved; Greenplum now returns the error "This query is not currently supported by GPDB." when it encounters a function invocation that it does not support. 31011 - Server: Execution Resolved

database_name -f /gpdb/backups/gp_dump_1_1_20120714 3.$ psql database_name -f /gpdb/backups/gp_dump_0_2_20120714 $ psql database_name -f /gpdb/backups/gp_dump_0_3_20120714 $ psql database_name -f /gpdb/backup _20120714 $ psql database_name -f /gpdb/backups/gp_dump_0_5_20120714 4.psql database_name -f /gpdb/backups/gp_dump_0_5_20120714_post_data 5. gunzip -c /data/gpdb/master/gpseg-1/db_dumps/20150112/gp

optimizer_print_optimization_stats 1858 optimizer_skew_factor 1859 optimizer_sort_factor 1859 optimizer_use_gpdb_allocators 1859 optimizer_xform_bind_threshold 1859 password_encryption 1860 password_hash_algorithm causing a problem when client encoding is different from server encoding. The problem is fixed by using GPDB server encoding by default, whether it is QD or QE, making the default encoding of external tables This issue is resolved; Greenplum now returns the error “This query is not currently supported by GPDB.” when it encounters a function invocation that it does not support. 31011 - Server: Execution Resolved

Pivotal中国研发中心 ## 日程 • Greenplum 数据库（GPDB）简介 • Greenplum 数据库（GPDB）架构 • Greenplum 数据库（GPDB）组件 • Greenplum 数据库（GPDB）执行流程 • Greenplum 数据库（GPDB）5. x ## Greenplum 简介 ## GPDB：为大数据存储、计算、挖掘而设计 • 标准 SQL 数据库：ANSI Module Package • R Data Science Library Package COPY Command ON SEGMENT Clause ## PostgreSQL 合并 GPDB pre 5.0 8.4, 9.0, 9.1 ## CPU资源管理 ![Image](/uploads/documents/d/1/d/d/d1ddee402996d22fbea49d865d43b830/p39_2

optimizer_print_optimization_stats 1917 optimizer_skew_factor 1917 optimizer_sort_factor 1918 optimizer_use_gpdb_allocators 1918 optimizer_xform_bind_threshold 1918 password_encryption 1918 password_hash_algorithm variable was not found where expected. ## 15963 Introduces a new check for gpmmon process to make sure gpdb_get_master_data_dirsets themaster_data_dir` variable to a non-null pointer. ## 15856 Resolves an causing a problem when client encoding is different from server encoding. The problem is fixed by using GPDB server encoding by default, whether it is QD or QE, making the default encoding of external tables