数仓架构与物理结构分析
- 一、部署架构
- 二、物理结构
- 三、测试验证
一、部署架构
-
华为数据仓库服务DWS，集群版本8.1.3.x
-
集群拓扑结构：
上述拓扑结构为DWS单AZ高可靠部署架构，为减少硬件故障对系统可用性的影响，建议集群部署方案遵循如下原则：- 对于每组实例，其主、备部署在不同的节点上。例如：GTM的主、备分别部署在不同的节点上。DN的主、备、从备部署在不同的节点上。
- 建议节点内存大于等于512G，每个节点部署4个DN。
- 低并发场景下，整个集群部署2~4个CN即可以满足使用要求。
- 建议GTM、CM部署在没有CN的节点上。如此部署，既减少某节点故障带来的损失，还可以避免集群运行压力集中在个别节点上。
- 安全环是集群组网的基本单元，普通安全环内包含至少3个服务器，各服务器的DN形成完备的主备关系。系统默认会根据Datanode进程数据目录的个数加1确定环节点数，也可以配置环节点列表和环节点数参数指定成环规则，建议使用小环，环节点数不宜过大。
为保证负载均衡和资源的有效利用，在遵循上述原则的基础上，建议部署模式如下：
- 主GTM和备CMServer部署在同一个节点上，备GTM和主CMServer部署在同一个节点上。
- 根据需要在部分节点上部署CN。
- 对于DN的部署模式：
- 一个服务器上的主DN对应的备节点和从备节点会按照上图所示自动依照安全环中节点的顺序依次分散部署在其他节点上，DN分布均衡。
- 各个节点上的DN数要求相同。
- DN的主、备、从备部署在不同的节点上。
- 特别说明：
从备DN不占用实际存储空间，仅在主、备DN故障时才起作用，且只存储数据日志，不存储数据页面。
二、物理结构
本小节内容主要查看DWS数仓按照上述部署架构完成部署后，其服务器上数仓的物理结构是什么样子的，以及当发生DDL、DML等操作后，CN和DN各自发生了什么变化等，并查看表的数据文件存储情况等。以下是通过实操来演示整个过程：
- 以root用户，利用SSH工具登录到dws数仓后台服务器。
- 切换到omm用户，然后source一下环境变量，例如执行如下命令：
source /opt/huawei/Bigdata/mppdb/.mppdbgs_profile - 查看集群状态信息，两种方式
第一种方式：gs_om -t status --detail
第二种方式：cm_ctl query -v -comm@host-192-168-5-204:~> gs_om -t status --detail [ CMServer State ]node node_ip instance state ---------------------------------------------------------------------------- 1 dws03 192.168.5.203 1 /opt/huawei/Bigdata/mppdb/cm/cm_server Standby 3 dws05 192.168.5.205 2 /opt/huawei/Bigdata/mppdb/cm/cm_server Primary[ Cluster State ]cluster_state : Normal redistributing : No balanced : Yes[ Coordinator State ]node node_ip instance state --------------------------------------------------------------------------- 1 dws03 192.168.5.203 5001 /srv/BigData/mppdb/data1/coordinator Normal 2 dws04 192.168.5.204 5002 /srv/BigData/mppdb/data1/coordinator Normal 3 dws05 192.168.5.205 5003 /srv/BigData/mppdb/data1/coordinator Normal[ Central Coordinator State ]node node_ip instance state -------------------------------------------------------------------------- 2 dws04 192.168.5.204 5002 /srv/BigData/mppdb/data1/coordinator Normal[ GTM State ]node node_ip instance state sync_state ---------------------------------------------------------------- 3 dws05 192.168.5.205 1001 /opt/huawei/Bigdata/mppdb/gtm P Primary Connection ok Sync 1 dws03 192.168.5.203 1002 /opt/huawei/Bigdata/mppdb/gtm S Standby Connection ok Sync[ Datanode State ] 主备从架构node node_ip instance state | node node_ip instance state | node node_ip instance state ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 dws03 192.168.5.203 6001 /srv/BigData/mppdb/data1/master1 P Primary Normal | 2 dws04 192.168.5.204 6002 /srv/BigData/mppdb/data1/slave1 S Standby Normal | 3 dws05 192.168.5.205 3002 /srv/BigData/mppdb/data1/dummyslave1 R Secondary Normal 2 dws04 192.168.5.204 6003 /srv/BigData/mppdb/data1/master1 P Primary Normal | 3 dws05 192.168.5.205 6004 /srv/BigData/mppdb/data1/slave1 S Standby Normal | 1 dws03 192.168.5.203 3003 /srv/BigData/mppdb/data1/dummyslave1 R Secondary Normal 3 dws05 192.168.5.205 6005 /srv/BigData/mppdb/data1/master1 P Primary Normal | 1 dws03 192.168.5.203 6006 /srv/BigData/mppdb/data1/slave1 S Standby Normal | 2 dws04 192.168.5.204 3004 /srv/BigData/mppdb/data1/dummyslave1 R Secondary Normalomm@host-192-168-5-204:~> cm_ctl query -v -C [ CMServer State ]node instance state ------------------------- 1 dws03 1 Standby 3 dws05 2 Primary[ Cluster State ]cluster_state : Normal redistributing : No balanced : Yes[ Coordinator State ]node instance state -------------------------- 1 dws03 5001 Normal 2 dws04 5002 Normal 3 dws05 5003 Normal[ Central Coordinator State ]node instance state ------------------------- 2 dws04 5002 Normal[ GTM State ]node instance state sync_state ------------------------------------------------ 3 dws05 1001 P Primary Connection ok Sync 1 dws03 1002 S Standby Connection ok Sync[ Datanode State ]node instance state | node instance state | node instance state -------------------------------------------------------------------------------------------------------------- 1 dws03 6001 P Primary Normal | 2 dws04 6002 S Standby Normal | 3 dws05 3002 R Secondary Normal 2 dws04 6003 P Primary Normal | 3 dws05 6004 S Standby Normal | 1 dws03 3003 R Secondary Normal 3 dws05 6005 P Primary Normal | 1 dws03 6006 S Standby Normal | 2 dws04 3004 R Secondary Normal omm@host-192-168-5-204:~> - 查看dws数据仓库安装目录的结构
omm@host-192-168-5-203:/opt/huawei/Bigdata/mppdb/core> ll total 16 drwx------ 4 omm wheel 4096 Oct 31 2023 bin drwx------ 2 omm wheel 58 Oct 26 2023 etc drwx------ 3 omm wheel 24 Aug 17 2023 include drwx------ 4 omm wheel 95 Mar 1 2022 jre drwx------ 6 omm wheel 8192 Oct 26 2023 lib drwx------ 6 omm wheel 68 Oct 26 2023 share drwx------ 2 omm wheel 20 Oct 26 2023 utilslib omm@host-192-168-5-203:/opt/huawei/Bigdata/mppdb/core> omm@host-192-168-5-203:/opt/huawei/Bigdata/mppdb/core/bin> ls alarmItem.conf diagcollect.sh gds gs_guc gs_running_xacts pg_config runsessionstat.sh cluster_dynamic_config drop_caches.sh getDEK.jar gs_initcm gs_upgrade pg_controldata script cluster_guc.conf etcd gs_cgroup gs_initdb gtm_ctl pg_format_cu seq_query cluster_static_config etcdctl gs_clean gs_initgtm initdb_param pg_recvlogical server.key.cipher cm_agent gaussdb gs_ctl gs_log jeprof pg_resetxlog server.key.rand cm_agent.lock GaussDB-8.1.3-SUSE11-x86_64bit-symbol.tar.gz gs_dump gsql om_monitor pg_xlogdump total_database_size cm_ctl gaussdb.license gs_dumpall gs_redis om_monitor.lock result transfer.py cm_server gaussdb.version gs_encrypt gs_restore openssl retry_errcodes.conf upgrade_version dfx_tool gaussmaster gs_gtm gs_roach pagehack run_drop_cache.sh version.cfg omm@host-192-168-5-203:/opt/huawei/Bigdata/mppdb/core/bin> omm@host-192-168-5-203:/opt/huawei/Bigdata/mppdb/core/include/postgresql> cd server/ omm@host-192-168-5-203:/opt/huawei/Bigdata/mppdb/core/include/postgresql/server> ll total 324 drwx------ 3 omm wheel 128 Aug 17 2023 access drwx------ 2 omm wheel 61 Aug 17 2023 catalog drwx------ 8 omm wheel 180 Oct 26 2023 cfunction -rw------- 1 omm wheel 37130 Aug 17 2023 c.h drwx------ 2 omm wheel 22 Aug 17 2023 common drwx------ 2 omm wheel 25 Aug 17 2023 datatype drwx------ 2 omm wheel 24 Aug 17 2023 executor -rw------- 1 omm wheel 38382 Aug 17 2023 extension_dependency.h -rw------- 1 omm wheel 24767 Aug 17 2023 fmgr.h -rw------- 1 omm wheel 2376 Aug 17 2023 gs_thread.h -rw------- 1 omm wheel 632 Aug 17 2023 gs_threadlocal.h drwx------ 2 omm wheel 42 Aug 17 2023 lib -rw------- 1 omm wheel 40469 Aug 17 2023 libpq-fe.h drwx------ 2 omm wheel 24 Aug 17 2023 mb drwx------ 2 omm wheel 145 Aug 17 2023 nodes -rw------- 1 omm wheel 28705 Aug 17 2023 pg_config.h -rw------- 1 omm wheel 10722 Aug 17 2023 pg_config_manual.h -rw------- 1 omm wheel 1051 Aug 17 2023 pg_config_os.h -rw------- 1 omm wheel 1841 Aug 17 2023 pgtime.h drwx------ 2 omm wheel 23 Aug 17 2023 pgxc drwx------ 2 omm wheel 43 Aug 17 2023 port -rw------- 1 omm wheel 14190 Aug 17 2023 port.h -rw------- 1 omm wheel 2054 Aug 17 2023 postgres_ext.h -rw------- 1 omm wheel 26454 Aug 17 2023 postgres.h -rw------- 1 omm wheel 8483 Aug 17 2023 securec_check.h -rw------- 1 omm wheel 28973 Apr 21 2023 securec.h -rw------- 1 omm wheel 17751 Apr 21 2023 securectype.h drwx------ 2 omm wheel 198 Aug 17 2023 storage drwx------ 2 omm wheel 20 Aug 17 2023 tcop drwx------ 3 omm wheel 4096 Aug 17 2023 utils omm@host-192-168-5-203:/opt/huawei/Bigdata/mppdb/core/include/postgresql/server>omm@host-192-168-5-203:/opt/huawei/Bigdata/mppdb/core/share> ll total 4 drwx------ 2 omm wheel 29 Aug 17 2023 llvmir drwx------ 2 omm wheel 32 Aug 17 2023 postgis drwx------ 7 omm wheel 4096 Oct 26 2023 postgresql drwx------ 6 omm wheel 55 Oct 26 2023 sslcertomm@host-192-168-5-203:/opt/huawei/Bigdata/mppdb/core/share> cd postgis/ omm@host-192-168-5-203:/opt/huawei/Bigdata/mppdb/core/share/postgis> ll total 4 -rw------- 1 omm wheel 3469 Aug 17 2023 PostGIS_install.shomm@host-192-168-5-203:/opt/huawei/Bigdata/mppdb/core/share/postgresql> ll total 1300 -rw------- 1 omm wheel 5440 Aug 17 2023 cm.conf.sample -rw------- 1 omm wheel 76384 Aug 17 2023 conversion_create.sql drwx------ 2 omm wheel 4096 Aug 17 2023 extension -rw------- 1 omm wheel 3093 Aug 17 2023 gtm.conf.sample -rw------- 1 omm wheel 107038 Aug 17 2023 information_schema.sql -rw------- 1 omm wheel 72 Aug 17 2023 pg_cast_oid.txt -rw------- 1 omm wheel 4446 Aug 17 2023 pg_hba.conf.sample -rw------- 1 omm wheel 1636 Aug 17 2023 pg_ident.conf.sample -rw------- 1 omm wheel 604 Aug 17 2023 pg_service.conf.sample -rw------- 1 omm wheel 122640 Oct 26 2023 pmk_schema_bak.sql -rw------- 1 omm wheel 122586 Aug 17 2023 pmk_schema_single_inst.sql -rw------- 1 omm wheel 122592 Aug 17 2023 pmk_schema.sql -rw------- 1 omm wheel 236646 Aug 17 2023 postgres.bki -rw------- 1 omm wheel 32948 Aug 17 2023 postgres.description -rw------- 1 omm wheel 35156 Aug 17 2023 postgresql.conf.sample -rw------- 1 omm wheel 49 Aug 17 2023 postgres.shdescription -rw------- 1 omm wheel 220 Aug 17 2023 psqlrc.sample -rw------- 1 omm wheel 4814 Aug 17 2023 recovery.conf.sample -rw------- 1 omm wheel 13359 Aug 17 2023 snowball_create.sql -rw------- 1 omm wheel 33329 Aug 17 2023 sql_features.txt -rw------- 1 omm wheel 347311 Aug 17 2023 system_views.sql drwx------ 18 omm wheel 4096 Aug 17 2023 timezone drwx------ 2 omm wheel 237 Aug 17 2023 timezonesets drwx------ 2 omm wheel 25 Aug 17 2023 tmp drwx------ 2 omm wheel 4096 Aug 17 2023 tsearch_data omm@host-192-168-5-203:/opt/huawei/Bigdata/mppdb/core/share/postgresql>omm@host-192-168-5-203:/opt/huawei/Bigdata/mppdb/core/share/postgresql/tsearch_data> ll total 27004 -rw------- 1 omm wheel 424 Aug 17 2023 danish.stop -rw------- 1 omm wheel 13245765 Aug 17 2023 dict.gbk.xdb -rw------- 1 omm wheel 14315393 Aug 17 2023 dict.utf8.xdb -rw------- 1 omm wheel 453 Aug 17 2023 dutch.stop -rw------- 1 omm wheel 622 Aug 17 2023 english.stop -rw------- 1 omm wheel 1579 Aug 17 2023 finnish.stop -rw------- 1 omm wheel 805 Aug 17 2023 french.stop -rw------- 1 omm wheel 1349 Aug 17 2023 german.stop -rw------- 1 omm wheel 1227 Aug 17 2023 hungarian.stop -rw------- 1 omm wheel 242 Aug 17 2023 hunspell_sample.affix -rw------- 1 omm wheel 465 Aug 17 2023 ispell_sample.affix -rw------- 1 omm wheel 81 Aug 17 2023 ispell_sample.dict -rw------- 1 omm wheel 1654 Aug 17 2023 italian.stop -rw------- 1 omm wheel 851 Aug 17 2023 norwegian.stop -rw------- 1 omm wheel 1267 Aug 17 2023 portuguese.stop -rw------- 1 omm wheel 3714 Aug 17 2023 rules.gbk.ini -rw------- 1 omm wheel 4396 Aug 17 2023 rules.utf8.ini -rw------- 1 omm wheel 1235 Aug 17 2023 russian.stop -rw------- 1 omm wheel 2178 Aug 17 2023 spanish.stop -rw------- 1 omm wheel 559 Aug 17 2023 swedish.stop -rw------- 1 omm wheel 73 Aug 17 2023 synonym_sample.syn -rw------- 1 omm wheel 473 Aug 17 2023 thesaurus_sample.ths -rw------- 1 omm wheel 260 Aug 17 2023 turkish.stop omm@host-192-168-5-203:/opt/huawei/Bigdata/mppdb/core/share/postgresql/tsearch_data>omm@host-192-168-5-203:/opt/huawei/Bigdata/mppdb/cm> ll total 0 drwx------ 2 omm wheel 60 Aug 7 15:15 cm_agent drwx------ 2 omm wheel 27 Aug 7 15:17 cm_server omm@host-192-168-5-203:/opt/huawei/Bigdata/mppdb/cm>omm@host-192-168-5-203:/opt/huawei/Bigdata/mppdb/cm> cd cm_server/ omm@host-192-168-5-203:/opt/huawei/Bigdata/mppdb/cm/cm_server> ll total 4 -rw------- 1 omm wheel 46 Aug 7 15:17 cm_server.pid omm@host-192-168-5-203:/opt/huawei/Bigdata/mppdb/cm/cm_server> cd ../cm_agent/ omm@host-192-168-5-203:/opt/huawei/Bigdata/mppdb/cm/cm_agent> ll total 20 -rw------- 1 omm wheel 45 Aug 7 15:15 cm_agent.pid -rw------- 1 omm wheel 5580 Oct 26 2023 cm.conf -rw------- 1 omm wheel 5580 Oct 26 2023 cm.conf.bak - 在dws安装路径下，查看gtm.conf和cm.conf，了解下两者的内容大概是什么
查看cm.confomm@host-192-168-5-203:/opt/huawei/Bigdata/mppdb/gtm> cat gtm.conf # ---------------------- # GTM configuration file # ---------------------- # # This file must be placed on gtm working directory # specified by -D command line option of gtm or gtm_ctl. The # configuration file name must be "gtm.conf" # # # This file consists of lines of the form # # name = value # # (The "=" is optional.) Whitespace may be used. Comments are # introduced with "#" anywhere on a line. The complete list of # parameter names and allowed values can be found in the # Postgres-XC documentation. # # The commented-out settings shown in this file represent the default # values. # # Re-commenting a setting is NOT sufficient to revert it to the default # value. # # You need to restart the server.#------------------------------------------------------------------------------ # GENERAL PARAMETERS #------------------------------------------------------------------------------nodename = 'gtm_1002' # Specifies the node name.# (changes requires restart) listen_addresses = 'localhost,192.168.5.203' # Listen addresses of this GTM.# (changes requires restart) port = 25306 # Port number of this GTM.# (changes requires restart)#------------------------------------------------------------------------------ # ERROR REPORTING AND LOGGING #------------------------------------------------------------------------------log_directory = '/var/log/Bigdata/mpp/omm/pg_log/gtm' # directory where log files are written,# can be absolute or relative. #log_file = 'gtm-%Y-%m-%d_%H%M%S.log' # Log file name #log_min_messages = WARNING # log_min_messages. Default WARNING.# Valid value: DEBUG, DEBUG5, DEBUG4, DEBUG3,# DEBUG2, DEBUG1, INFO, NOTICE, WARNING,# ERROR, LOG, FATAL, PANIC.#------------------------------------------------------------------------------ # GTM STANDBY PARAMETERS #------------------------------------------------------------------------------#Those parameters are effective when GTM is activated as a standby server active_host = '192.168.5.205' # Listen address of active GTM.# (changes requires restart) active_port = 25305# (changes requires restart)local_host = '192.168.5.203' # Listen address of HA local host.# (changes requires restart) local_port = 25307# (changes requires restart)#--------------------------------------- # OTHER OPTIONS #---------------------------------------enable_alarm = on enable_connect_control = true # check ip. #standby_connection_timeout = 7 # standby connect timeout. #keepalives_idle = 0 # Keepalives_idle parameter. #keepalives_interval = 0 # Keepalives_interval parameter. #keepalives_count = 0 # Keepalives_count internal parameter. #synchronous_backup = auto # If backup to standby is synchronous# off, on or auto. #wlm_max_mem = 2048 # Maximum memory an instance can use for its executions, unit: MB.# (changes requires restart) #query_memory_limit = 0.25 # Sets the percentage limit of memory a query can use.# (changes requires restart)alarm_component = '/opt/huawei/Bigdata/mppdb/snas_cm_cmd'omm@host-192-168-5-203:/opt/huawei/Bigdata/mppdb/cm/cm_agent> cat cm.conf #-------------------------------------------------------------------------------------------------- # LOG #--------------------------------------------------------------------------------------------------# Default: cm_agent data dir. cm_agent_log_dir = '/var/log/Bigdata/mpp/omm/cm/cm_agent' # # Default: cm_server data dir. cm_server_log_dir = '/var/log/Bigdata/mpp/omm/cm/cm_server'# Valid values: DEBUG5, DEBUG1, WARNING, ERROR, LOG, FATAL. # Default: WARNING log_min_messages = WARNING# Only support MB. # Default: 16MB. log_file_size = 16MB#-------------------------------------------------------------------------------------------------- # ALARM #--------------------------------------------------------------------------------------------------alarm_component = '/opt/huawei/Bigdata/mppdb/snas_cm_cmd'# Default: 3 alarm_report_interval = 3#-------------------------------------------------------------------------------------------------- # TIMEOUT #--------------------------------------------------------------------------------------------------# Default: 30 # Minimum: 8 instance_heartbeat_timeout = 30# Default: 600 coordinator_heartbeat_timeout = 600#-------------------------------------------------------------------------------------------------- # THREAD POOL #--------------------------------------------------------------------------------------------------# Default: 10 # Range : [2, 255] thread_count = 10#-------------------------------------------------------------------------------------------------- # ABNORMAL CHECK #--------------------------------------------------------------------------------------------------# Default: on enable_abnormal_check = onabnormal_check_memory_usage = '{ "_name" : "libac_memory_usage.so", "check_interval" : "60", "usage_threshold" : "70", "check_count" : "10" }' abnormal_check_general_task = '{ "_name" : "libac_general_task.so", "check_interval" : "3600" }' abnormal_check_create_table = '{ "_name" : "libac_create_table.so", "check_interval" : "150", "check_count" : "6" }' abnormal_check_phony_dead = '{ "_name" : "libac_phony_dead.so", "check_interval" : "180", "phony_dead_effective_time" : "5", "cmserver_phony_dead_restart_interval" : "21600" }'#-------------------------------------------------------------------------------------------------- # STORAGE #--------------------------------------------------------------------------------------------------# Default: on enable_transaction_read_only = on# Default: 600 datastorage_threshold_check_interval = 600# Default: 90 datastorage_threshold_value_check = 90# Default: 43200 max_datastorage_threshold_check = 43200#-------------------------------------------------------------------------------------------------- # AUTH #--------------------------------------------------------------------------------------------------# Default: trust cm_auth_method = trustcm_krb_server_keyfile = /opt/huawei/Bigdata/mppdb/auth_config/mppdb.keytab#-------------------------------------------------------------------------------------------------- # LOG COMPRESS #--------------------------------------------------------------------------------------------------# Default: on enable_log_compress = on# Default: 1800 log_threshold_check_interval = 1800# Default: 1024 log_max_size = 1024# Default: 10000 log_max_count = 10000# Default: 90 log_saved_days = 90log_pattern_cm_agent = cm_agent- log_pattern_cm_ctl = cm_ctl- log_pattern_cm_server = cm_server- log_pattern_gs_backup = gs_backup- log_pattern_gs_check = gs_check- log_pattern_gs_checkos = gs_checkos- log_pattern_gs_checkperf = gs_checkperf- log_pattern_gs_clean = gs_clean- log_pattern_gs_collector = gs_collector- log_pattern_gs_ctl = gs_ctl- log_pattern_gs_dump = gs_dump- log_pattern_gs_dumpall = gs_dumpall- log_pattern_gs_expand = gs_expand- log_pattern_gs_guc = gs_guc- log_pattern_gs_initcm = gs_initcm- log_pattern_gs_initdb = gs_initdb- log_pattern_gs_initgtm = gs_initgtm- log_pattern_gs_install = gs_install- log_pattern_gs_local = gs_local- log_pattern_gs_om = gs_om- - 查看dws数据目录结构
查看CN节点数据目录，如下：omm@host-192-168-5-204:/srv/BigData/mppdb/data1> ll total 16 drwx------ 20 omm wheel 4096 Aug 7 17:45 coordinator # 协调节点CN数据目录 drwx------ 20 omm wheel 4096 Aug 7 17:45 dummyslave1 # 数据节点DN的从备 drwx------ 20 omm wheel 4096 Aug 7 17:45 master1 # 数据节点DN主 drwx------ 20 omm wheel 4096 Aug 7 17:45 slave1 # 数据节点DN从 omm@host-192-168-5-204:/srv/BigData/mppdb/data1>
查看DN节点数据目录，如下：omm@host-192-168-5-204:/srv/BigData/mppdb/data1/coordinator> ll total 160 drwx------ 7 omm wheel 71 Oct 31 2023 base -rw------- 1 omm wheel 4403 Aug 17 2023 cacert.pem -rw------- 1 omm wheel 72 Aug 7 15:16 gaussdb.state drwx------ 2 omm wheel 4096 Aug 7 16:37 global drwx------ 2 omm wheel 26 Oct 26 2023 pg_clog drwx------ 2 omm wheel 6 Oct 26 2023 pg_clog_restore drwx------ 2 omm wheel 26 Oct 26 2023 pg_csnlog -rw------- 1 omm wheel 0 Oct 26 2023 pg_ctl.lock drwx------ 2 omm wheel 6 Nov 9 2023 pg_errorinfo -rw------- 1 omm wheel 4627 Oct 26 2023 pg_hba.conf -rw------- 1 omm wheel 4627 Oct 26 2023 pg_hba.conf.bak -rw------- 1 omm wheel 1024 Oct 26 2023 pg_hba.conf.lock -rw------- 1 omm wheel 1636 Oct 26 2023 pg_ident.conf drwx------ 4 omm wheel 39 Oct 26 2023 pg_llog drwx------ 4 omm wheel 37 Aug 7 16:36 pg_location drwx------ 4 omm wheel 36 Oct 26 2023 pg_multixact drwx------ 2 omm wheel 6 Oct 26 2023 pg_notify drwx------ 2 omm wheel 6 Oct 26 2023 pg_replslot drwx------ 2 omm wheel 32 Aug 7 15:16 pg_residualfiles drwx------ 2 omm wheel 6 Oct 26 2023 pg_serial drwx------ 2 omm wheel 6 Oct 26 2023 pg_snapshots drwx------ 2 omm wheel 25 Aug 7 15:16 pg_stat_tmp drwx------ 2 omm wheel 85 Aug 7 16:36 pg_tblspc drwx------ 2 omm wheel 6 Oct 26 2023 pg_twophase -rw------- 1 omm wheel 4 Oct 26 2023 PG_VERSION drwx------ 3 omm wheel 4096 Aug 7 16:31 pg_xlog -rw------- 1 omm wheel 35362 Aug 7 15:26 postgresql.conf -rw------- 1 omm wheel 35362 Aug 7 15:26 postgresql.conf.bak -rw------- 1 omm wheel 1024 Oct 26 2023 postgresql.conf.lock -rw------- 1 omm wheel 103 Aug 7 15:16 postmaster.opts -rw------- 1 omm wheel 125 Aug 7 15:16 postmaster.pid -rw------- 1 omm wheel 4405 Aug 17 2023 server.crt -rw------- 1 omm wheel 1766 Aug 17 2023 server.key -rw------- 1 omm wheel 40 Aug 17 2023 server.key.cipher -rw------- 1 omm wheel 24 Aug 17 2023 server.key.rand omm@host-192-168-5-204:/srv/BigData/mppdb/data1/coordinator> pwd /srv/BigData/mppdb/data1/coordinator
CN和DN数据目录结构不同点如下图所示：omm@host-192-168-5-205:/srv/BigData/mppdb/data1/master1> ll total 132 drwx------ 7 omm wheel 71 Oct 31 2023 base -rw------- 1 omm wheel 72 Aug 7 15:17 gaussdb.state drwx------ 2 omm wheel 4096 Aug 7 16:37 global drwx------ 2 omm wheel 26 Oct 26 2023 pg_clog drwx------ 2 omm wheel 6 Oct 26 2023 pg_clog_restore drwx------ 2 omm wheel 26 Oct 26 2023 pg_csnlog -rw------- 1 omm wheel 0 Nov 15 2023 pg_ctl.lock drwx------ 2 omm wheel 6 Nov 9 2023 pg_errorinfo -rw------- 1 omm wheel 4627 Oct 26 2023 pg_hba.conf -rw------- 1 omm wheel 4627 Oct 26 2023 pg_hba.conf.bak -rw------- 1 omm wheel 1024 Oct 26 2023 pg_hba.conf.lock -rw------- 1 omm wheel 1636 Oct 26 2023 pg_ident.conf drwx------ 4 omm wheel 39 Oct 26 2023 pg_llog drwx------ 4 omm wheel 37 Aug 7 16:36 pg_location drwx------ 4 omm wheel 36 Oct 26 2023 pg_multixact drwx------ 2 omm wheel 6 Oct 26 2023 pg_notify drwx------ 4 omm wheel 46 Oct 26 2023 pg_replslot drwx------ 2 omm wheel 32 Aug 7 15:17 pg_residualfiles drwx------ 2 omm wheel 6 Oct 26 2023 pg_serial drwx------ 2 omm wheel 6 Oct 26 2023 pg_snapshots drwx------ 2 omm wheel 25 Aug 7 15:16 pg_stat_tmp drwx------ 2 omm wheel 85 Aug 7 16:36 pg_tblspc drwx------ 2 omm wheel 6 Oct 26 2023 pg_twophase -rw------- 1 omm wheel 4 Oct 26 2023 PG_VERSION drwx------ 3 omm wheel 4096 Aug 7 15:23 pg_xlog -rw------- 1 omm wheel 35575 Oct 26 2023 postgresql.conf -rw------- 1 omm wheel 35575 Oct 26 2023 postgresql.conf.bak -rw------- 1 omm wheel 1024 Oct 26 2023 postgresql.conf.lock -rw------- 1 omm wheel 111 Aug 7 15:17 postmaster.opts -rw------- 1 omm wheel 124 Aug 7 15:16 postmaster.pid omm@host-192-168-5-205:/srv/BigData/mppdb/data1/master1>
进入CN某个表空间目录下查看下数据库下的数据文件：
进入DN某个表空间目录下查看下数据库下的数据文件：omm@host-192-168-5-203:/srv/BigData/mppdb/data1/master1/pg_tblspc> ll total 0 lrwxrwxrwx 1 omm wheel 52 Aug 7 16:36 135423 -> /srv/BigData/mppdb/data1/master1/pg_location/ts/ts01 lrwxrwxrwx 1 omm wheel 77 Nov 2 2023 55632 -> /srv/BigData/mppdb/data1/master1/pg_location/tb_tablespace/tb_tablespace_tbs1 lrwxrwxrwx 1 omm wheel 77 Nov 7 2023 83846 -> /srv/BigData/mppdb/data1/master1/pg_location/tb_tablespace/tb_tablespace_tbs2 lrwxrwxrwx 1 omm wheel 77 Nov 7 2023 83847 -> /srv/BigData/mppdb/data1/master1/pg_location/tb_tablespace/tb_tablespace_tbs3 lrwxrwxrwx 1 omm wheel 77 Nov 7 2023 83848 -> /srv/BigData/mppdb/data1/master1/pg_location/tb_tablespace/tb_tablespace_tbs4 lrwxrwxrwx 1 omm wheel 77 Nov 7 2023 83849 -> /srv/BigData/mppdb/data1/master1/pg_location/tb_tablespace/tb_tablespace_tbs5omm@host-192-168-5-203:/srv/BigData/mppdb/data1/master1/pg_tblspc>omm@host-192-168-5-204:/srv/BigData/mppdb/data1/coordinator/pg_tblspc/135737/PG_9.2_201611171_cn_5002/135744> ls 15112 15130_vm 15181 15206_vm 15234 15255_fsm 15283 15307_fsm 15361 15420_vm 15477_fsm 15507 15112_fsm 15132 15183 15208 15234_fsm 15255_vm 15283_fsm 15307_vm 15362 15422 15477_vm 15509 15112_vm 15133 15184 15210 15234_vm 15257 15283_vm 15309 15363 15423 15479 15511 15114 15138 15184_fsm 15211 15236 15258 15285 15310 15363_fsm 15424 15481 15512 15115 15140 15184_vm 15212 15237 15259 15287 15342 15363_vm 15424_fsm 15482 15514 15116 15142 15186 15213 15238 15261 15288 15342_fsm 15365 15424_vm 15482_fsm 15516 15116_fsm 15143 15186_fsm 15213_fsm 15238_fsm 15262 15289 15342_vm 15366 15426 15482_vm 15517 15116_vm 15148 15186_vm 15213_vm 15238_vm 15262_fsm 15289_fsm 15344 15367 15435 15484 15519 15118 15150 15188 15215 15240 15262_vm 15289_vm 15345 15369 15437 15486 15521 15118_fsm 15152 15189 15217 15241 15264 15291 15346 15371 15438 15487 15522 15118_vm 15153 15190 15218 15242 15265 15292 15346_fsm 15372 15439 15487_fsm 15524 15120 15155 15191 15219 15242_fsm 15265_fsm 15293 15346_vm 15397 15441 15487_vm 15526 15121 15157 15191_fsm 15220 15242_vm 15265_vm 15295 15348 15399 15442 15489 15527 15122 15158 15191_vm 15221 15244 15267 15296 15349 15400 15447 15491 pg_filenode.map 15122_fsm 15160 15193 15222 15245 15267_fsm 15297 15349_fsm 15402 15449 15492 pg_filenode.map.backup 15122_vm 15162 15194 15224 15246 15267_vm 15298 15349_vm 15404 15450 15492_fsm pg_internal.init.91635 15124 15163 15195 15225 15246_fsm 15269 15298_fsm 15351 15405 15452 15492_vm PG_VERSION 15124_fsm 15165 15196 15226 15246_vm 15270 15298_vm 15352 15406 15453 15494 15125 15167 15198 15226_fsm 15248 15271 15300 15353 15407 15456 15496 15125_fsm 15168 15200 15226_vm 15249 15272 15301 15353_fsm 15409 15458 15497 15126 15169 15201 15228 15250 15274 15302 15353_vm 15410 15459 15497_fsm 15126_fsm 15171 15202 15229 15251 15276 15302_fsm 15355 15411 15472 15497_vm 15126_vm 15173 15203 15230 15251_fsm 15277 15302_vm 15356 15413 15472_fsm 15499 15128 15174 15204 15230_fsm 15251_vm 15278 15304 15357 15415 15472_vm 15501 15129 15176 15205 15230_vm 15253 15279 15305 15359 15416 15474 15502 15130 15178 15206 15232 15254 15280 15306 15359_fsm 15420 15476 15504 15130_fsm 15179 15206_fsm 15233 15255 15282 15307 15359_vm 15420_fsm 15477 15506 omm@host-192-168-5-204:/srv/BigData/mppdb/data1/coordinator/pg_tblspc/135737/PG_9.2_201611171_cn_5002/135744>
通过对比CN和DN表空间下数据库数据文件对比，并未发现有和区别，说明两者数据组织方式是一样的。omm@host-192-168-5-205:/srv/BigData/mppdb/data1/master1/pg_tblspc> ll total 0 lrwxrwxrwx 1 omm wheel 52 Aug 7 16:36 135423 -> /srv/BigData/mppdb/data1/master1/pg_location/ts/ts01 lrwxrwxrwx 1 omm wheel 77 Nov 2 2023 55653 -> /srv/BigData/mppdb/data1/master1/pg_location/tb_tablespace/tb_tablespace_tbs1 lrwxrwxrwx 1 omm wheel 77 Nov 7 2023 83868 -> /srv/BigData/mppdb/data1/master1/pg_location/tb_tablespace/tb_tablespace_tbs2 lrwxrwxrwx 1 omm wheel 77 Nov 7 2023 83869 -> /srv/BigData/mppdb/data1/master1/pg_location/tb_tablespace/tb_tablespace_tbs3 lrwxrwxrwx 1 omm wheel 77 Nov 7 2023 83870 -> /srv/BigData/mppdb/data1/master1/pg_location/tb_tablespace/tb_tablespace_tbs4 lrwxrwxrwx 1 omm wheel 77 Nov 7 2023 83871 -> /srv/BigData/mppdb/data1/master1/pg_location/tb_tablespace/tb_tablespace_tbs5omm@host-192-168-5-205:/srv/BigData/mppdb/data1/master1/pg_tblspc>omm@host-192-168-5-205:/srv/BigData/mppdb/data1/master1/pg_tblspc/135423/PG_9.2_201611171_dn_6005_6006/135430> ls 15112 15130_vm 15181 15206_vm 15234 15255_fsm 15283 15307_fsm 15361 15420_vm 15477_fsm 15507 15112_fsm 15132 15183 15208 15234_fsm 15255_vm 15283_fsm 15307_vm 15362 15422 15477_vm 15509 15112_vm 15133 15184 15210 15234_vm 15257 15283_vm 15309 15363 15423 15479 15511 15114 15138 15184_fsm 15211 15236 15258 15285 15310 15363_fsm 15424 15481 15512 15115 15140 15184_vm 15212 15237 15259 15287 15342 15363_vm 15424_fsm 15482 15514 15116 15142 15186 15213 15238 15261 15288 15342_fsm 15365 15424_vm 15482_fsm 15516 15116_fsm 15143 15186_fsm 15213_fsm 15238_fsm 15262 15289 15342_vm 15366 15426 15482_vm 15517 15116_vm 15148 15186_vm 15213_vm 15238_vm 15262_fsm 15289_fsm 15344 15367 15435 15484 15519 15118 15150 15188 15215 15240 15262_vm 15289_vm 15345 15369 15437 15486 15521 15118_fsm 15152 15189 15217 15241 15264 15291 15346 15371 15438 15487 15522 15118_vm 15153 15190 15218 15242 15265 15292 15346_fsm 15372 15439 15487_fsm 15524 15120 15155 15191 15219 15242_fsm 15265_fsm 15293 15346_vm 15397 15441 15487_vm 15526 15121 15157 15191_fsm 15220 15242_vm 15265_vm 15295 15348 15399 15442 15489 15527 15122 15158 15191_vm 15221 15244 15267 15296 15349 15400 15447 15491 pg_filenode.map 15122_fsm 15160 15193 15222 15245 15267_fsm 15297 15349_fsm 15402 15449 15492 pg_filenode.map.backup 15122_vm 15162 15194 15224 15246 15267_vm 15298 15349_vm 15404 15450 15492_fsm pg_internal.init.91635 15124 15163 15195 15225 15246_fsm 15269 15298_fsm 15351 15405 15452 15492_vm PG_VERSION 15124_fsm 15165 15196 15226 15246_vm 15270 15298_vm 15352 15406 15453 15494 15125 15167 15198 15226_fsm 15248 15271 15300 15353 15407 15456 15496 15125_fsm 15168 15200 15226_vm 15249 15272 15301 15353_fsm 15409 15458 15497 15126 15169 15201 15228 15250 15274 15302 15353_vm 15410 15459 15497_fsm 15126_fsm 15171 15202 15229 15251 15276 15302_fsm 15355 15411 15472 15497_vm 15126_vm 15173 15203 15230 15251_fsm 15277 15302_vm 15356 15413 15472_fsm 15499 15128 15174 15204 15230_fsm 15251_vm 15278 15304 15357 15415 15472_vm 15501 15129 15176 15205 15230_vm 15253 15279 15305 15359 15416 15474 15502 15130 15178 15206 15232 15254 15280 15306 15359_fsm 15420 15476 15504 15130_fsm 15179 15206_fsm 15233 15255 15282 15307 15359_vm 15420_fsm 15477 15506 omm@host-192-168-5-205:/srv/BigData/mppdb/data1/master1/pg_tblspc/135423/PG_9.2_201611171_dn_6005_6006/135430>
三、测试验证
-
利用gsql登录到postgres数据库下，例如执行如下命令：
omm@host-192-168-5-203: ~> gsql -d postgres -p 25308 -
查看dws集群节点信息，可查两张系统视图
postgres=# select * from pgxc_node_env order by node_name; node_name | host | process | port | installpath | datapath | log_directory --------------+--------------+---------+-------+--------------------------------+--------------------------------------+----------------------------------------- cn_5001 | localhost | 6951 | 25308 | /opt/huawei/Bigdata/mppdb/core | /srv/BigData/mppdb/data1/coordinator | /var/log/Bigdata/mpp/omm/pg_log/cn_5001 cn_5002 | 192.168.5.204 | 6071 | 25308 | /opt/huawei/Bigdata/mppdb/core | /srv/BigData/mppdb/data1/coordinator | /var/log/Bigdata/mpp/omm/pg_log/cn_5002 cn_5003 | 192.168.5.205 | 6863 | 25308 | /opt/huawei/Bigdata/mppdb/core | /srv/BigData/mppdb/data1/coordinator | /var/log/Bigdata/mpp/omm/pg_log/cn_5003 dn_6001_6002 | 192.168.5.203 | 6959 | 25330 | /opt/huawei/Bigdata/mppdb/core | /srv/BigData/mppdb/data1/master1 | /var/log/Bigdata/mpp/omm/pg_log/dn_6001 dn_6003_6004 | 192.168.5.204 | 6214 | 25330 | /opt/huawei/Bigdata/mppdb/core | /srv/BigData/mppdb/data1/master1 | /var/log/Bigdata/mpp/omm/pg_log/dn_6003 dn_6005_6006 | 192.168.5.205 | 6879 | 25330 | /opt/huawei/Bigdata/mppdb/core | /srv/BigData/mppdb/data1/master1 | /var/log/Bigdata/mpp/omm/pg_log/dn_6005 (6 rows)postgres=# select * from pgxc_node; node_name | node_type | node_port | node_host | node_port1 | node_host1 | hostis_primary | nodeis_primary | nodeis_preferred | node_id | sctp_port | control_port | sctp_port1 | control_port1 | nodeis_central --------------+-----------+-----------+--------------+------------+--------------+----------------+----------------+------------------+-------------+-----------+--------------+------------+---------------+---------------- cn_5003 | C | 25308 | 192.168.5.205 | 25308 | 192.168.5.205 | t | f | f | -125853378 | 25310 | 25311 | 0 | 0 | f cn_5001 | C | 25308 | localhost | 25308 | localhost | t | f | f | 1120683504 | 25310 | 25311 | 0 | 0 | f cn_5002 | C | 25308 | 192.168.5.204 | 25308 | 192.168.5.204 | t | f | f | -1736975100 | 25310 | 25311 | 0 | 0 | t dn_6001_6002 | D | 25330 | 192.168.5.203 | 25490 | 192.168.5.204 | t | f | f | 1644780306 | 25332 | 25333 | 25492 | 25493 | f dn_6003_6004 | D | 25330 | 192.168.5.204 | 25490 | 192.168.5.205 | t | f | f | -966646068 | 25332 | 25333 | 25492 | 25493 | f dn_6005_6006 | D | 25330 | 192.168.5.205 | 25490 | 192.168.5.203 | t | f | f | 868850011 | 25332 | 25333 | 25492 | 25493 | f (6 rows) -
创建用户自定义表空间、database和用户表
create tablespace ts01 relative location 'ts/ts01'; create database db01 tablespace ts01; create table t1(id int) tablespace ts01; -
切换到db01数据库下，查看表空间、数据库、表的oid
db01=# select oid, * from pg_tablespace where spcname='ts01'; oid | spcname | spcowner | spcacl | spcoptions | spcmaxsize | relative --------+---------+----------+--------+------------+------------+---------- 135738 | ts01 | 10 | | | | t (1 row) db01=# select oid,datname,dattablespace from pg_database where datname='db01'; oid | datname | dattablespace --------+---------+--------------- 135745 | db01 | 135738 (1 row) db01=# select oid,* from pg_class where relname='t1'; oid | relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | reltoastidxid | reldeltarelid | reldeltaidx | relcudescrelid | relcudescidx | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks | relhasoids | relhaspkey | relhasrules | relhastriggers | relhassubclass | relcmprs | relhasclusterkey | relrowmovement | parttype | relfrozenxid | relacl | reloptions | relreplident | relfrozenxid64 --------+---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+---------------+---------------+-------------+----------------+--------------+-------------+-------------+----------------+---------+----------+-----------+------------+------------+-------------+----------------+----------------+----------+------------------+----------------+----------+--------------+--------+----------------------------------+--------------+---------------- 136277 | t1 | 2200 | 136279 | 0 | 10 | 0 | 136277 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | f | f | p | r | 1 | 0 | f | f | f | f | f | 1 | f | f | n | 1654215 | | {orientation=row,compression=no} | d | 1654215 (1 row)上述查看到表空间和数据库的关系可以通过oid进行关联，但是所查的表oid只看到了表oid和schema（relnamespace字段）的oid进行关联，relnamespace等于2200的是名为public的schema。
-
根据前一个步骤所查到的oid，查看物理目录及文件
查看当前gsql客户端所连接的CN节点（dws03（192.168.5.203））上的数据目录及数据文件，经查看和上述所查到表空间、数据库及表的oid保持一一对应，如下：omm@host-192-168-5-203:/srv/BigData/mppdb/data1/coordinator/pg_location/ts/ts01/PG_9.2_201611171_cn_5001/135745> ls 136277 15130_fsm 15179 15206_fsm 15233 15255 15282 15307 15359_vm 15420_fsm 15477 15506 15112 15130_vm 15181 15206_vm 15234 15255_fsm 15283 15307_fsm 15361 15420_vm 15477_fsm 15507 15112_fsm 15132 15183 15208 15234_fsm 15255_vm 15283_fsm 15307_vm 15362 15422 15477_vm 15509 15112_vm 15133 15184 15210 15234_vm 15257 15283_vm 15309 15363 15423 15479 15511 15114 15138 15184_fsm 15211 15236 15258 15285 15310 15363_fsm 15424 15481 15512 15115 15140 15184_vm 15212 15237 15259 15287 15342 15363_vm 15424_fsm 15482 15514 15116 15142 15186 15213 15238 15261 15288 15342_fsm 15365 15424_vm 15482_fsm 15516 15116_fsm 15143 15186_fsm 15213_fsm 15238_fsm 15262 15289 15342_vm 15366 15426 15482_vm 15517 15116_vm 15148 15186_vm 15213_vm 15238_vm 15262_fsm 15289_fsm 15344 15367 15435 15484 15519 15118 15150 15188 15215 15240 15262_vm 15289_vm 15345 15369 15437 15486 15521 15118_fsm 15152 15189 15217 15241 15264 15291 15346 15371 15438 15487 15522 15118_vm 15153 15190 15218 15242 15265 15292 15346_fsm 15372 15439 15487_fsm 15524 15120 15155 15191 15219 15242_fsm 15265_fsm 15293 15346_vm 15397 15441 15487_vm 15526 15121 15157 15191_fsm 15220 15242_vm 15265_vm 15295 15348 15399 15442 15489 15527 15122 15158 15191_vm 15221 15244 15267 15296 15349 15400 15447 15491 pg_filenode.map 15122_fsm 15160 15193 15222 15245 15267_fsm 15297 15349_fsm 15402 15449 15492 pg_filenode.map.backup 15122_vm 15162 15194 15224 15246 15267_vm 15298 15349_vm 15404 15450 15492_fsm pg_internal.init.91635 15124 15163 15195 15225 15246_fsm 15269 15298_fsm 15351 15405 15452 15492_vm PG_VERSION 15124_fsm 15165 15196 15226 15246_vm 15270 15298_vm 15352 15406 15453 15494 15125 15167 15198 15226_fsm 15248 15271 15300 15353 15407 15456 15496 15125_fsm 15168 15200 15226_vm 15249 15272 15301 15353_fsm 15409 15458 15497 15126 15169 15201 15228 15250 15274 15302 15353_vm 15410 15459 15497_fsm 15126_fsm 15171 15202 15229 15251 15276 15302_fsm 15355 15411 15472 15497_vm 15126_vm 15173 15203 15230 15251_fsm 15277 15302_vm 15356 15413 15472_fsm 15499 15128 15174 15204 15230_fsm 15251_vm 15278 15304 15357 15415 15472_vm 15501 15129 15176 15205 15230_vm 15253 15279 15305 15359 15416 15474 15502 15130 15178 15206 15232 15254 15280 15306 15359_fsm 15420 15476 15504 omm@host-192-168-5-203:/srv/BigData/mppdb/data1/coordinator/pg_location/ts/ts01/PG_9.2_201611171_cn_5001/135745>其中：
135745文件db01的oid，136277为表t1的oid
查看 dws04(192.168.5.204 ) 节点上CN的数据目录及其数据文件,其中数据库目录的名称（oid）不一样（目录名称为：135744，而db01的oid是135745），但是表文件的名称是和表的oid一样的：omm@host-192-168-5-204:/srv/BigData/mppdb/data1/coordinator/pg_location/ts/ts01/PG_9.2_201611171_cn_5002/135744> ls 136277 15130_fsm 15179 15206_fsm 15233 15255 15282 15307 15359_vm 15420_fsm 15477 15506 15112 15130_vm 15181 15206_vm 15234 15255_fsm 15283 15307_fsm 15361 15420_vm 15477_fsm 15507 15112_fsm 15132 15183 15208 15234_fsm 15255_vm 15283_fsm 15307_vm 15362 15422 15477_vm 15509 15112_vm 15133 15184 15210 15234_vm 15257 15283_vm 15309 15363 15423 15479 15511 15114 15138 15184_fsm 15211 15236 15258 15285 15310 15363_fsm 15424 15481 15512 15115 15140 15184_vm 15212 15237 15259 15287 15342 15363_vm 15424_fsm 15482 15514 15116 15142 15186 15213 15238 15261 15288 15342_fsm 15365 15424_vm 15482_fsm 15516 15116_fsm 15143 15186_fsm 15213_fsm 15238_fsm 15262 15289 15342_vm 15366 15426 15482_vm 15517 15116_vm 15148 15186_vm 15213_vm 15238_vm 15262_fsm 15289_fsm 15344 15367 15435 15484 15519 15118 15150 15188 15215 15240 15262_vm 15289_vm 15345 15369 15437 15486 15521 15118_fsm 15152 15189 15217 15241 15264 15291 15346 15371 15438 15487 15522 15118_vm 15153 15190 15218 15242 15265 15292 15346_fsm 15372 15439 15487_fsm 15524 15120 15155 15191 15219 15242_fsm 15265_fsm 15293 15346_vm 15397 15441 15487_vm 15526 15121 15157 15191_fsm 15220 15242_vm 15265_vm 15295 15348 15399 15442 15489 15527 15122 15158 15191_vm 15221 15244 15267 15296 15349 15400 15447 15491 pg_filenode.map 15122_fsm 15160 15193 15222 15245 15267_fsm 15297 15349_fsm 15402 15449 15492 pg_filenode.map.backup 15122_vm 15162 15194 15224 15246 15267_vm 15298 15349_vm 15404 15450 15492_fsm pg_internal.init.91635 15124 15163 15195 15225 15246_fsm 15269 15298_fsm 15351 15405 15452 15492_vm PG_VERSION 15124_fsm 15165 15196 15226 15246_vm 15270 15298_vm 15352 15406 15453 15494 15125 15167 15198 15226_fsm 15248 15271 15300 15353 15407 15456 15496 15125_fsm 15168 15200 15226_vm 15249 15272 15301 15353_fsm 15409 15458 15497 15126 15169 15201 15228 15250 15274 15302 15353_vm 15410 15459 15497_fsm 15126_fsm 15171 15202 15229 15251 15276 15302_fsm 15355 15411 15472 15497_vm 15126_vm 15173 15203 15230 15251_fsm 15277 15302_vm 15356 15413 15472_fsm 15499 15128 15174 15204 15230_fsm 15251_vm 15278 15304 15357 15415 15472_vm 15501 15129 15176 15205 15230_vm 15253 15279 15305 15359 15416 15474 15502 15130 15178 15206 15232 15254 15280 15306 15359_fsm 15420 15476 15504查看dws05(192.168.5.205) 节点上CN的数据目录及其数据文件,其中
数据库目录的名称（oid）不一样（目录名称为：135741，而db01的oid是135745），表文件的名称是和表的oid也不一样的：omm@host-192-168-5-205:/srv/BigData/mppdb/data1/coordinator/pg_location/ts/ts01/PG_9.2_201611171_cn_5003/135741> ls 136273 15129 15176 15205 15230_vm 15253 15279 15305 15359 15416 15474 15502 15130 15178 15206 15232 15254 15280 15306 15359_fsm 15420 15476 15504 15130_fsm 15179 15206_fsm 15233 15255 15282 15307 15359_vm 15420_fsm 15477 15506 15112 15130_vm 15181 15206_vm 15234 15255_fsm 15283 15307_fsm 15361 15420_vm 15477_fsm 15507 15112_fsm 15132 15183 15208 15234_fsm 15255_vm 15283_fsm 15307_vm 15362 15422 15477_vm 15509 15112_vm 15133 15184 15210 15234_vm 15257 15283_vm 15309 15363 15423 15479 15511 15114 15138 15184_fsm 15211 15236 15258 15285 15310 15363_fsm 15424 15481 15512 15115 15140 15184_vm 15212 15237 15259 15287 15342 15363_vm 15424_fsm 15482 15514 15116 15142 15186 15213 15238 15261 15288 15342_fsm 15365 15424_vm 15482_fsm 15516 15116_fsm 15143 15186_fsm 15213_fsm 15238_fsm 15262 15289 15342_vm 15366 15426 15482_vm 15517 15116_vm 15148 15186_vm 15213_vm 15238_vm 15262_fsm 15289_fsm 15344 15367 15435 15484 15519 15118 15150 15188 15215 15240 15262_vm 15289_vm 15345 15369 15437 15486 15521 15118_fsm 15152 15189 15217 15241 15264 15291 15346 15371 15438 15487 15522 15118_vm 15153 15190 15218 15242 15265 15292 15346_fsm 15372 15439 15487_fsm 15524 15120 15155 15191 15219 15242_fsm 15265_fsm 15293 15346_vm 15397 15441 15487_vm 15526 15121 15157 15191_fsm 15220 15242_vm 15265_vm 15295 15348 15399 15442 15489 15527 15122 15158 15191_vm 15221 15244 15267 15296 15349 15400 15447 15491 pg_filenode.map 15122_fsm 15160 15193 15222 15245 15267_fsm 15297 15349_fsm 15402 15449 15492 pg_filenode.map.backup 15122_vm 15162 15194 15224 15246 15267_vm 15298 15349_vm 15404 15450 15492_fsm pg_internal.init.91635 15124 15163 15195 15225 15246_fsm 15269 15298_fsm 15351 15405 15452 15492_vm PG_VERSION 15124_fsm 15165 15196 15226 15246_vm 15270 15298_vm 15352 15406 15453 15494 15125 15167 15198 15226_fsm 15248 15271 15300 15353 15407 15456 15496 15125_fsm 15168 15200 15226_vm 15249 15272 15301 15353_fsm 15409 15458 15497 15126 15169 15201 15228 15250 15274 15302 15353_vm 15410 15459 15497_fsm 15126_fsm 15171 15202 15229 15251 15276 15302_fsm 15355 15411 15472 15497_vm 15126_vm 15173 15203 15230 15251_fsm 15277 15302_vm 15356 15413 15472_fsm 15499 15128 15174 15204 15230_fsm 15251_vm 15278 15304 15357 15415 15472_vm 15501 -
查看数据节点DN的变化（todo）
-
创建表继续测试
db01=# SELECT node_name,node_type,node_host FROM pgxc_node where node_type='D'; dn_6001_6002 | D | 192.168.5.203 dn_6003_6004 | D | 192.168.5.204 dn_6005_6006 | D | 192.168.5.205 db01=# insert into t1 values(1),(2),(3),(4),(5),(6); db01=# execute direct on(dn_6005_6006) 'select * from t1'; 3 6 db01=# execute direct on(dn_6003_6004) 'select * from t1'; 2 5 db01=# execute direct on(dn_6001_6002) 'select * from t1'; 1 4 db01=# create table t2(id int,name varchar(50)) distribute by replication; db01=# insert into t2 values(1,'suben'),(2,'zhangsan'),(3,'lisi'); db01=# execute direct on(dn_6001_6002) 'select * from t2'; 1 | suben 2 | zhangsan 3 | lisi db01=# execute direct on(dn_6003_6004) 'select * from t2'; 1 | suben 2 | zhangsan 3 | lisi db01=# execute direct on(dn_6005_6006) 'select * from t2'; 1 | suben 2 | zhangsan 3 | lisi db01=# create table t3(id int,name varchar(50)) distribute by hash(id); db01=# insert into t3 values(1,'suben'),(2,'zhangsan'),(3,'lisi'),(4,'wuhang'),(5,'wangwu'),(6,'zhangfei'); db01=# execute direct on(dn_6001_6002) 'select * from t3'; 3 | lisi db01=# execute direct on(dn_6003_6004) 'select * from t3'; 1 | suben 2 | zhangsan 4 | wuhang 5 | wangwu db01=# execute direct on(dn_6005_6006) 'select * from t3'; 6 | zhangfei db01=# select oid,relname from pg_class where relname='part01'; 140801 | part01 db01=# select relname,parttype,parentid,relfilenode,boundaries from pg_partition where parentid=140801; part01 | r | 140801 | 0 | p1 | p | 140801 | 140805 | {3} p2 | p | 140801 | 140806 | {5} p3 | p | 140801 | 140807 | {7} p4 | p | 140801 | 140808 | {NULL}查看CN节点数据目录的变化：
omm@host-192-168-5-203:/srv/BigData/mppdb/data1/coordinator/pg_tblspc/135738/PG_9.2_201611171_cn_5001/135745> ls -l 1408* -rw------- 1 omm wheel 0 Aug 8 13:58 140801 -rw------- 1 omm wheel 0 Aug 8 13:58 140805 -rw------- 1 omm wheel 0 Aug 8 13:58 140806 -rw------- 1 omm wheel 0 Aug 8 13:58 140807 -rw------- 1 omm wheel 0 Aug 8 13:58 140808 omm@host-192-168-5-204:/srv/BigData/mppdb/data1/coordinator/pg_location/ts/ts01/PG_9.2_201611171_cn_5002/135744> ls -l 14080* -rw------- 1 omm wheel 0 Aug 8 13:58 140801 -rw------- 1 omm wheel 0 Aug 8 13:58 140805 -rw------- 1 omm wheel 0 Aug 8 13:58 140806 -rw------- 1 omm wheel 0 Aug 8 13:58 140807 -rw------- 1 omm wheel 0 Aug 8 13:58 140808 omm@host-192-168-5-205:/srv/BigData/mppdb/data1/coordinator/pg_location/ts/ts01/PG_9.2_201611171_cn_5003/135741> ls -l 1408* -rw------- 1 omm wheel 0 Aug 8 13:58 140801 -rw------- 1 omm wheel 0 Aug 8 13:58 140802 -rw------- 1 omm wheel 0 Aug 8 13:58 140803 -rw------- 1 omm wheel 0 Aug 8 13:58 140804继续插入数据：
db01=# insert into part01 values(1,'test1'),(2,'test2'),(3,'test3'),(4,'test4'),(5,'test5'),(6,'test6'); db01=# execute direct on(dn_6001_6002) 'select * from part01 partition (p1)'; db01=# execute direct on(dn_6001_6002) 'select * from part01 partition (p2)'; 3 | test3 db01=# execute direct on(dn_6001_6002) 'select * from part01 partition (p3)'; db01=# execute direct on(dn_6001_6002) 'select * from part01 partition (p4)'; db01=# execute direct on(dn_6003_6004) 'select * from part01 partition (p1)'; 1 | test1 2 | test2 db01=# execute direct on(dn_6003_6004) 'select * from part01 partition (p2)'; 4 | test4 db01=# execute direct on(dn_6003_6004) 'select * from part01 partition (p3)'; 5 | test5 db01=# execute direct on(dn_6003_6004) 'select * from part01 partition (p4)'; db01=# execute direct on(dn_6005_6006) 'select * from part01 partition (p1)'; db01=# execute direct on(dn_6005_6006) 'select * from part01 partition (p2)'; db01=# execute direct on(dn_6005_6006) 'select * from part01 partition (p3)'; 6 | test6 db01=# execute direct on(dn_6005_6006) 'select * from part01 partition (p4)'; db01=# select * from part01 partition (p1); 1 | test1 2 | test2 db01=# select * from part01 partition (p2); 3 | test3 4 | test4 db01=# select * from part01 partition (p3); 5 | test5 6 | test6可看到：
查询分区表时，并行从各个分区所在的DN节点中检索数据，并汇总到CN，由CN返回给客户端。
查看CN节点数据目录的文件变化，发现并没有将上述内容写入对下的文件，文件的大小依然是0字节：omm@host-192-168-5-203:/srv/BigData/mppdb/data1/coordinator/pg_tblspc/135738/PG_9.2_201611171_cn_5001/135745> ls -l 1408* -rw------- 1 omm wheel 0 Aug 8 13:58 140801 -rw------- 1 omm wheel 0 Aug 8 13:58 140805 -rw------- 1 omm wheel 0 Aug 8 13:58 140806 -rw------- 1 omm wheel 0 Aug 8 13:58 140807 -rw------- 1 omm wheel 0 Aug 8 13:58 140808omm@host-192-168-5-204:/srv/BigData/mppdb/data1/coordinator/pg_location/ts/ts01/PG_9.2_201611171_cn_5002/135744> ls -l 14080* -rw------- 1 omm wheel 0 Aug 8 13:58 140801 -rw------- 1 omm wheel 0 Aug 8 13:58 140805 -rw------- 1 omm wheel 0 Aug 8 13:58 140806 -rw------- 1 omm wheel 0 Aug 8 13:58 140807 -rw------- 1 omm wheel 0 Aug 8 13:58 140808 omm@host-192-168-5-205:/srv/BigData/mppdb/data1/coordinator/pg_location/ts/ts01/PG_9.2_201611171_cn_5003/135741> ls -l 1408* -rw------- 1 omm wheel 0 Aug 8 13:58 140801 -rw------- 1 omm wheel 0 Aug 8 13:58 140802 -rw------- 1 omm wheel 0 Aug 8 13:58 140803 -rw------- 1 omm wheel 0 Aug 8 13:58 140804查看默认的
checkpoint_time时间是15分钟：db01=# show checkpoint_timeout; 15min手动执行checkpoint，让数据落盘：
db01=# checkpoint;继续查看CN节点对应的数据目录下的文件变化，发现相关文件的大小依然是0，
说明CN节点并不会记录真实的数据到对应的表文件中：omm@host-192-168-5-203:/srv/BigData/mppdb/data1/coordinator/pg_tblspc/135738/PG_9.2_201611171_cn_5001/135745> ls -l 1408* -rw------- 1 omm wheel 0 Aug 8 13:58 140801 -rw------- 1 omm wheel 0 Aug 8 13:58 140805 -rw------- 1 omm wheel 0 Aug 8 13:58 140806 -rw------- 1 omm wheel 0 Aug 8 13:58 140807 -rw------- 1 omm wheel 0 Aug 8 13:58 140808omm@host-192-168-5-204:/srv/BigData/mppdb/data1/coordinator/pg_location/ts/ts01/PG_9.2_201611171_cn_5002/135744> ls -l 14080* -rw------- 1 omm wheel 0 Aug 8 13:58 140801 -rw------- 1 omm wheel 0 Aug 8 13:58 140805 -rw------- 1 omm wheel 0 Aug 8 13:58 140806 -rw------- 1 omm wheel 0 Aug 8 13:58 140807 -rw------- 1 omm wheel 0 Aug 8 13:58 140808 omm@host-192-168-5-205:/srv/BigData/mppdb/data1/coordinator/pg_location/ts/ts01/PG_9.2_201611171_cn_5003/135741> ls -l 1408* -rw------- 1 omm wheel 0 Aug 8 13:58 140801 -rw------- 1 omm wheel 0 Aug 8 13:58 140802 -rw------- 1 omm wheel 0 Aug 8 13:58 140803 -rw------- 1 omm wheel 0 Aug 8 13:58 140804到此的问题：到底数据是写入到哪个文件去了呢？
该表的数据已经被写入到/srv/BigData/mppdb/data1/master1/ -
继续测试，创建的新的数据库和表，这次只查看DN节点上数据目录的变化
postgres=# create database testdb encoding 'utf8' template template0; postgres=# select oid,datname from pg_database where datname='testdb'; oid | datname --------+--------- 141047 | testdb postgres=# \c testdb testdb=# create table t1(id int,name varchar(50)) distribute by hash(id); testdb=# select oid,* from pg_class where relname='t1'; oid | relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | reltoastidxid | reldeltarelid | reldeltaidx | relcudescrelid | relcudescidx | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks | relhasoids | relhaspkey | relhasrules | relhastriggers | relhassubclass | relcmprs | relhasclusterkey | relrowmovement | parttype | relfrozenxid | relacl | reloptions | relreplident | relfrozenxid64 --------+---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+---------------+---------------+-------------+----------------+--------------+-------------+-------------+----------------+---------+----------+-----------+------------+------------+-------------+----------------+----------------+----------+------------------+----------------+----------+--------------+--------+----------------------------------+--------------+---------------- 141315 | t1 | 2200 | 141317 | 0 | 10 | 0 | 141315 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | f | f | p | r | 2 | 0 | f | f | f | f | f | 1 | f | f | n | 1703460 | | {orientation=row,compression=no} | d | 1703460查看DN节点对应的数据目录及其文件大小的变化：
dws03 DN节点：omm@host-192-168-5-203:/srv/BigData/mppdb/data1/master1/base/140755> ls 141025 15126 15160 15188 15210 15230_fsm 15246_vm 15265_fsm 15288 15307 15353_vm 15402 15437 15479 15501 15112 15126_fsm 15162 15189 15211 15230_vm 15248 15265_vm 15289 15307_fsm 15355 15404 15438 15481 15502 15112_fsm 15126_vm 15163 15190 15212 15232 15249 15267 15289_fsm 15307_vm 15356 15405 15439 15482 15504 15112_vm 15128 15165 15191 15213 15233 15250 15267_fsm 15289_vm 15309 15357 15406 15441 15482_fsm 15506 15114 15129 15167 15191_fsm 15213_fsm 15234 15251 15267_vm 15291 15310 15359 15407 15442 15482_vm 15507 15115 15130 15168 15191_vm 15213_vm 15234_fsm 15251_fsm 15269 15292 15342 15359_fsm 15409 15447 15484 15509 15116 15130_fsm 15169 15193 15215 15234_vm 15251_vm 15270 15293 15342_fsm 15359_vm 15410 15449 15486 15511 15116_fsm 15130_vm 15171 15194 15217 15236 15253 15271 15295 15342_vm 15361 15411 15450 15487 15512 15116_vm 15132 15173 15195 15218 15237 15254 15272 15296 15344 15362 15413 15452 15487_fsm 15514 15118 15133 15174 15196 15219 15238 15255 15274 15297 15345 15363 15415 15453 15487_vm 15516 15118_fsm 15138 15176 15198 15220 15238_fsm 15255_fsm 15276 15298 15346 15363_fsm 15416 15456 15489 15517 15118_vm 15140 15178 15200 15221 15238_vm 15255_vm 15277 15298_fsm 15346_fsm 15363_vm 15420 15458 15491 15519 15120 15142 15179 15201 15222 15240 15257 15278 15298_vm 15346_vm 15365 15420_fsm 15459 15492 15521 15121 15143 15181 15202 15224 15241 15258 15279 15300 15348 15366 15420_vm 15472 15492_fsm 15522 15122 15148 15183 15203 15225 15242 15259 15280 15301 15349 15367 15422 15472_fsm 15492_vm 15524 15122_fsm 15150 15184 15204 15226 15242_fsm 15261 15282 15302 15349_fsm 15369 15423 15472_vm 15494 15526 15122_vm 15152 15184_fsm 15205 15226_fsm 15242_vm 15262 15283 15302_fsm 15349_vm 15371 15424 15474 15496 15527 15124 15153 15184_vm 15206 15226_vm 15244 15262_fsm 15283_fsm 15302_vm 15351 15372 15424_fsm 15476 15497 pg_filenode.map 15124_fsm 15155 15186 15206_fsm 15228 15245 15262_vm 15283_vm 15304 15352 15397 15424_vm 15477 15497_fsm pg_filenode.map.backup 15125 15157 15186_fsm 15206_vm 15229 15246 15264 15285 15305 15353 15399 15426 15477_fsm 15497_vm pg_internal.init.91635 15125_fsm 15158 15186_vm 15208 15230 15246_fsm 15265 15287 15306 15353_fsm 15400 15435 15477_vm 15499 PG_VERSION omm@host-192-168-5-203:/srv/BigData/mppdb/data1/master1/base/140755> ls -l 141025 -rw------- 1 omm wheel 0 Aug 8 15:06 141025dws04 DN节点：
omm@host-192-168-5-204:/srv/BigData/mppdb/data1/master1/base/140763> ls 141035 15129 15173 15201 15226_fsm 15246_fsm 15267_fsm 15295 15346_fsm 15369 15426 15482 15511 15112 15130 15174 15202 15226_vm 15246_vm 15267_vm 15296 15346_vm 15371 15435 15482_fsm 15512 15112_fsm 15130_fsm 15176 15203 15228 15248 15269 15297 15348 15372 15437 15482_vm 15514 15112_vm 15130_vm 15178 15204 15229 15249 15270 15298 15349 15397 15438 15484 15516 15114 15132 15179 15205 15230 15250 15271 15298_fsm 15349_fsm 15399 15439 15486 15517 15115 15133 15181 15206 15230_fsm 15251 15272 15298_vm 15349_vm 15400 15441 15487 15519 15116 15138 15183 15206_fsm 15230_vm 15251_fsm 15274 15300 15351 15402 15442 15487_fsm 15521 15116_fsm 15140 15184 15206_vm 15232 15251_vm 15276 15301 15352 15404 15447 15487_vm 15522 15116_vm 15142 15184_fsm 15208 15233 15253 15277 15302 15353 15405 15449 15489 15524 15118 15143 15184_vm 15210 15234 15254 15278 15302_fsm 15353_fsm 15406 15450 15491 15526 15118_fsm 15148 15186 15211 15234_fsm 15255 15279 15302_vm 15353_vm 15407 15452 15492 15527 15118_vm 15150 15186_fsm 15212 15234_vm 15255_fsm 15280 15304 15355 15409 15453 15492_fsm pg_filenode.map 15120 15152 15186_vm 15213 15236 15255_vm 15282 15305 15356 15410 15456 15492_vm pg_filenode.map.backup 15121 15153 15188 15213_fsm 15237 15257 15283 15306 15357 15411 15458 15494 pg_internal.init.91635 15122 15155 15189 15213_vm 15238 15258 15283_fsm 15307 15359 15413 15459 15496 PG_VERSION 15122_fsm 15157 15190 15215 15238_fsm 15259 15283_vm 15307_fsm 15359_fsm 15415 15472 15497 15122_vm 15158 15191 15217 15238_vm 15261 15285 15307_vm 15359_vm 15416 15472_fsm 15497_fsm 15124 15160 15191_fsm 15218 15240 15262 15287 15309 15361 15420 15472_vm 15497_vm 15124_fsm 15162 15191_vm 15219 15241 15262_fsm 15288 15310 15362 15420_fsm 15474 15499 15125 15163 15193 15220 15242 15262_vm 15289 15342 15363 15420_vm 15476 15501 15125_fsm 15165 15194 15221 15242_fsm 15264 15289_fsm 15342_fsm 15363_fsm 15422 15477 15502 15126 15167 15195 15222 15242_vm 15265 15289_vm 15342_vm 15363_vm 15423 15477_fsm 15504 15126_fsm 15168 15196 15224 15244 15265_fsm 15291 15344 15365 15424 15477_vm 15506 15126_vm 15169 15198 15225 15245 15265_vm 15292 15345 15366 15424_fsm 15479 15507 15128 15171 15200 15226 15246 15267 15293 15346 15367 15424_vm 15481 15509 omm@host-192-168-5-204:/srv/BigData/mppdb/data1/master1/base/140763> ls -l 141035 -rw------- 1 omm wheel 0 Aug 8 15:06 141035dws05 DN节点：
omm@host-192-168-5-205:/srv/BigData/mppdb/data1/master1/base/140753> ls 141021 15129 15173 15201 15226_fsm 15246_fsm 15267_fsm 15295 15346_fsm 15369 15426 15482 15511 15112 15130 15174 15202 15226_vm 15246_vm 15267_vm 15296 15346_vm 15371 15435 15482_fsm 15512 15112_fsm 15130_fsm 15176 15203 15228 15248 15269 15297 15348 15372 15437 15482_vm 15514 15112_vm 15130_vm 15178 15204 15229 15249 15270 15298 15349 15397 15438 15484 15516 15114 15132 15179 15205 15230 15250 15271 15298_fsm 15349_fsm 15399 15439 15486 15517 15115 15133 15181 15206 15230_fsm 15251 15272 15298_vm 15349_vm 15400 15441 15487 15519 15116 15138 15183 15206_fsm 15230_vm 15251_fsm 15274 15300 15351 15402 15442 15487_fsm 15521 15116_fsm 15140 15184 15206_vm 15232 15251_vm 15276 15301 15352 15404 15447 15487_vm 15522 15116_vm 15142 15184_fsm 15208 15233 15253 15277 15302 15353 15405 15449 15489 15524 15118 15143 15184_vm 15210 15234 15254 15278 15302_fsm 15353_fsm 15406 15450 15491 15526 15118_fsm 15148 15186 15211 15234_fsm 15255 15279 15302_vm 15353_vm 15407 15452 15492 15527 15118_vm 15150 15186_fsm 15212 15234_vm 15255_fsm 15280 15304 15355 15409 15453 15492_fsm pg_filenode.map 15120 15152 15186_vm 15213 15236 15255_vm 15282 15305 15356 15410 15456 15492_vm pg_filenode.map.backup 15121 15153 15188 15213_fsm 15237 15257 15283 15306 15357 15411 15458 15494 pg_internal.init.91635 15122 15155 15189 15213_vm 15238 15258 15283_fsm 15307 15359 15413 15459 15496 PG_VERSION 15122_fsm 15157 15190 15215 15238_fsm 15259 15283_vm 15307_fsm 15359_fsm 15415 15472 15497 15122_vm 15158 15191 15217 15238_vm 15261 15285 15307_vm 15359_vm 15416 15472_fsm 15497_fsm 15124 15160 15191_fsm 15218 15240 15262 15287 15309 15361 15420 15472_vm 15497_vm 15124_fsm 15162 15191_vm 15219 15241 15262_fsm 15288 15310 15362 15420_fsm 15474 15499 15125 15163 15193 15220 15242 15262_vm 15289 15342 15363 15420_vm 15476 15501 15125_fsm 15165 15194 15221 15242_fsm 15264 15289_fsm 15342_fsm 15363_fsm 15422 15477 15502 15126 15167 15195 15222 15242_vm 15265 15289_vm 15342_vm 15363_vm 15423 15477_fsm 15504 15126_fsm 15168 15196 15224 15244 15265_fsm 15291 15344 15365 15424 15477_vm 15506 15126_vm 15169 15198 15225 15245 15265_vm 15292 15345 15366 15424_fsm 15479 15507 15128 15171 15200 15226 15246 15267 15293 15346 15367 15424_vm 15481 15509 omm@host-192-168-5-205:/srv/BigData/mppdb/data1/master1/base/140753> ls -l 141021 -rw------- 1 omm wheel 0 Aug 8 15:06 141021注意：
DN节点上的数据库oid、表的oid与物理目录名称和物理数据文件名称不同，特别需要注意。只有gsql客户端所连接的CN节点上的数据目录下才相同。执行插入数据操作:
insert into t1 values(1,'zhangsan'),(2,'LiSi'),(3,'WangWu'),(4,'zhangfei'),(5,'GuanYu'),(6,'Zhaoyun');继续查看上述刚刚查看的数据文件，
发现这些文件大小已经发生了变化，每个数据文件大小为8192字节（8KB）：omm@host-192-168-5-203:/srv/BigData/mppdb/data1/master1/base/140755> ls -l 141025 -rw------- 1 omm wheel 8192 Aug 8 15:13 141025 omm@host-192-168-5-204:/srv/BigData/mppdb/data1/master1/base/140763> ls -l 141035 -rw------- 1 omm wheel 8192 Aug 8 15:13 141035 omm@host-192-168-5-205:/srv/BigData/mppdb/data1/master1/base/140753> ls -l 141021 -rw------- 1 omm wheel 8192 Aug 8 15:13 141021查看这三个文件的内容，发现确实是我们刚刚插入的内容：
omm@host-192-168-5-203:/srv/BigData/mppdb/data1/master1/base/140755> cat 141025 ▒\"G▒]@,▒ [▒؟F WangWuomm@host-192-168-5-203:/srv/BigData/mppdb/data1/master1/base/140755> omm@host-192-168-5-204:/srv/BigData/mppdb/data1/master1/base/140763> cat 141035 8▒;E\{@8` [▒؟J▒▒B▒▒J`▒F GuanYu zhangfei LiSi zhangsanomm@host-192-168-5-204:/srv/BigData/mppdb/data1/master1/base/140763> omm@host-192-168-5-205:/srv/BigData/mppdb/data1/master1/base/140753> cat 141021 ▒▒▒F:▒@,▒ [▒؟H Zhaoyunomm@host-192-168-5-205:/srv/BigData/mppdb/data1/master1/base/140753>由此说明：
从上述文件内容变化来看，141025文件大小由0变成了8192字节，说明刚刚的insert操作已经写入数据文件中。从上述查看结果来看，大概可以看出来了，三个DN Master节点个有部分数据，因其是hash分布的表。通过系统函数查看t1文件路径：
testdb=# select pg_relation_filepath(141315); pg_relation_filepath ---------------------- base/141047/141315通过查看系统表查看t1文件路径：
SELECT n.nspname AS schema_name, c.relname AS table_name, pg_relation_filepath(c.oid) AS file_path FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid WHERE c.relkind = 'r' AND n.nspname NOT IN ('pg_catalog', 'information_schema') AND c.relname = 't1';查询结果如下：
schema_name | table_name | file_path -------------+------------+-------------------- public | t1 | base/141047/141315 (1 row)注意：
base/141047/141315 和系统函数查询到的结果是一样的。这个路径其实就是数据文件所被保存再CN节点上的元数据了。继续查看CN节点下的数据目录及其文件：
omm@host-192-168-5-203:/srv/BigData/mppdb/data1/coordinator/base> ll total 88 drwx------ 2 omm wheel 12288 Oct 26 2023 1 drwx------ 2 omm wheel 8192 Aug 8 15:06 141047 # 就是这个数据库oid drwx------ 2 omm wheel 8192 Oct 26 2023 15529 drwx------ 2 omm wheel 20480 Aug 8 15:50 15534 drwx------ 2 omm wheel 8192 Aug 7 15:17 47164 drwx------ 2 omm wheel 6 Nov 9 2023 pgsql_tmp# 进入141047目录下查看表t1的数据文件 omm@host-192-168-5-203:/srv/BigData/mppdb/data1/coordinator/base/141047> ls -l 141315 -rw------- 1 omm wheel 0 Aug 8 15:06 141315 # DN主 # 192-168-5-204 omm@host-192-168-5-204:/srv/BigData/mppdb/data1/coordinator/base/141047> ls -l 14131* -rw------- 1 omm wheel 0 Aug 8 15:06 141316 # DN备 # DN从备 omm@host-192-168-5-205:/srv/BigData/mppdb/data1/coordinator/base/141043> ls -l 1413* -rw------- 1 omm wheel 0 Aug 8 15:06 141312测试结论：
- 客户端所连接的CN节点：系统函数和系统表查询出来t1表的文件路径是在当前客户端所连接的CN节点下的数据目录下的coordinator/base目录下，也就是客户端连接的是哪个CN节点，那系统函数查询出来的该t1表的filepath就哪个路径下；
- 客户端未连接的CN节点：如dws04服务器上的coordinator/base/141047/目录下存在一个141316文件，该文件是t1表的oid加1的结果。dws05服务器上的coordinator/base/141043/目录下存在一个141312文件，该文件是t1表的oid减少3的结果，但是这些文件都是0字节，没有存储真实的数据内容；
- 数据节点DN：DN主节点和DN备节点都存储数据库表的数据，但DN从备不占用实际存储空间，仅在主、备DN故障时才起作用，且只存储数据日志，不存储数据页面。
![PostgreSQL的学习心得和知识总结（一百五十）|[performance]更好地处理冗余 IS [NOT] NULL 限定符](https://i-blog.csdnimg.cn/direct/0429c52cc29e4daf8b54d936a535c2fe.png)
