做网站公司郑州郑州的网站建设公司排名,单页网站seo如何优化,网页版微信二维码扫描,优秀网站要素数仓架构与物理结构分析 一、部署架构二、物理结构三、测试验证 一、部署架构 华为数据仓库服务DWS#xff0c;集群版本8.1.3.x 集群拓扑结构#xff1a; 上述拓扑结构为DWS单AZ高可靠部署架构#xff0c;为减少硬件故障对系统可用性的影响#xff0c;建议集群部署方案遵… 数仓架构与物理结构分析 一、部署架构二、物理结构三、测试验证 一、部署架构 华为数据仓库服务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 --detailommhost-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 Normal第二种方式cm_ctl query -v -commhost-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
ommhost-192-168-5-204:~ 查看dws数据仓库安装目录的结构ommhost-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
ommhost-192-168-5-203:/opt/huawei/Bigdata/mppdb/core
ommhost-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
ommhost-192-168-5-203:/opt/huawei/Bigdata/mppdb/core/bin
ommhost-192-168-5-203:/opt/huawei/Bigdata/mppdb/core/include/postgresql cd server/
ommhost-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
ommhost-192-168-5-203:/opt/huawei/Bigdata/mppdb/core/include/postgresql/serverommhost-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 sslcertommhost-192-168-5-203:/opt/huawei/Bigdata/mppdb/core/share cd postgis/
ommhost-192-168-5-203:/opt/huawei/Bigdata/mppdb/core/share/postgis ll
total 4
-rw------- 1 omm wheel 3469 Aug 17 2023 PostGIS_install.shommhost-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
ommhost-192-168-5-203:/opt/huawei/Bigdata/mppdb/core/share/postgresqlommhost-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
ommhost-192-168-5-203:/opt/huawei/Bigdata/mppdb/core/share/postgresql/tsearch_dataommhost-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
ommhost-192-168-5-203:/opt/huawei/Bigdata/mppdb/cmommhost-192-168-5-203:/opt/huawei/Bigdata/mppdb/cm cd cm_server/
ommhost-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
ommhost-192-168-5-203:/opt/huawei/Bigdata/mppdb/cm/cm_server cd ../cm_agent/
ommhost-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了解下两者的内容大概是什么ommhost-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查看cm.confommhost-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数据目录结构ommhost-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从
ommhost-192-168-5-204:/srv/BigData/mppdb/data1查看CN节点数据目录如下ommhost-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
ommhost-192-168-5-204:/srv/BigData/mppdb/data1/coordinator pwd
/srv/BigData/mppdb/data1/coordinator查看DN节点数据目录如下ommhost-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
ommhost-192-168-5-205:/srv/BigData/mppdb/data1/master1CN和DN数据目录结构不同点如下图所示 进入CN某个表空间目录下查看下数据库下的数据文件ommhost-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_tbs5ommhost-192-168-5-203:/srv/BigData/mppdb/data1/master1/pg_tblspcommhost-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
ommhost-192-168-5-204:/srv/BigData/mppdb/data1/coordinator/pg_tblspc/135737/PG_9.2_201611171_cn_5002/135744 进入DN某个表空间目录下查看下数据库下的数据文件ommhost-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_tbs5ommhost-192-168-5-205:/srv/BigData/mppdb/data1/master1/pg_tblspcommhost-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
ommhost-192-168-5-205:/srv/BigData/mppdb/data1/master1/pg_tblspc/135423/PG_9.2_201611171_dn_6005_6006/135430通过对比CN和DN表空间下数据库数据文件对比并未发现有和区别说明两者数据组织方式是一样的。
三、测试验证 利用gsql登录到postgres数据库下例如执行如下命令 ommhost-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 spcnamets01; oid | spcname | spcowner | spcacl | spcoptions | spcmaxsize | relative
--------------------------------------------------------------------- 135738 | ts01 | 10 | | | | t
(1 row) db01# select oid,datname,dattablespace from pg_database where datnamedb01; oid | datname | dattablespace
-------------------------------- 135745 | db01 | 135738
(1 row) db01# select oid,* from pg_class where relnamet1; 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 | | {orientationrow,compressionno} | d | 1654215
(1 row) 上述查看到表空间和数据库的关系可以通过oid进行关联但是所查的表oid只看到了表oid和schemarelnamespace字段的oid进行关联relnamespace等于2200的是名为public的schema。 根据前一个步骤所查到的oid查看物理目录及文件 查看当前gsql客户端所连接的CN节点dws03192.168.5.203上的数据目录及数据文件经查看和上述所查到表空间、数据库及表的oid保持一一对应如下 ommhost-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
ommhost-192-168-5-203:/srv/BigData/mppdb/data1/coordinator/pg_location/ts/ts01/PG_9.2_201611171_cn_5001/135745 其中135745文件db01的oid136277为表t1的oid 查看 dws04(192.168.5.204 ) 节点上CN的数据目录及其数据文件,其中数据库目录的名称oid不一样目录名称为135744而db01的oid是135745但是表文件的名称是和表的oid一样的 ommhost-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也不一样的 ommhost-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_typeD; 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 relnamepart01; 140801 | part01 db01# select relname,parttype,parentid,relfilenode,boundaries from pg_partition where parentid140801; 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节点数据目录的变化 ommhost-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 ommhost-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 ommhost-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字节 ommhost-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 140808ommhost-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 ommhost-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节点并不会记录真实的数据到对应的表文件中 ommhost-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 140808ommhost-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 ommhost-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 datnametestdb; 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 relnamet1; 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 | | {orientationrow,compressionno} | d | 1703460 查看DN节点对应的数据目录及其文件大小的变化 dws03 DN节点 ommhost-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
ommhost-192-168-5-203:/srv/BigData/mppdb/data1/master1/base/140755 ls -l 141025
-rw------- 1 omm wheel 0 Aug 8 15:06 141025 dws04 DN节点 ommhost-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
ommhost-192-168-5-204:/srv/BigData/mppdb/data1/master1/base/140763 ls -l 141035
-rw------- 1 omm wheel 0 Aug 8 15:06 141035 dws05 DN节点 ommhost-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
ommhost-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 ommhost-192-168-5-203:/srv/BigData/mppdb/data1/master1/base/140755 ls -l 141025
-rw------- 1 omm wheel 8192 Aug 8 15:13 141025 ommhost-192-168-5-204:/srv/BigData/mppdb/data1/master1/base/140763 ls -l 141035
-rw------- 1 omm wheel 8192 Aug 8 15:13 141035 ommhost-192-168-5-205:/srv/BigData/mppdb/data1/master1/base/140753 ls -l 141021
-rw------- 1 omm wheel 8192 Aug 8 15:13 141021 查看这三个文件的内容发现确实是我们刚刚插入的内容 ommhost-192-168-5-203:/srv/BigData/mppdb/data1/master1/base/140755 cat 141025
▒\G▒],▒ [▒؟F WangWuommhost-192-168-5-203:/srv/BigData/mppdb/data1/master1/base/140755 ommhost-192-168-5-204:/srv/BigData/mppdb/data1/master1/base/140763 cat 141035
8▒;E\{8 [▒؟J▒▒B▒▒J▒F GuanYu zhangfei LiSi zhangsanommhost-192-168-5-204:/srv/BigData/mppdb/data1/master1/base/140763 ommhost-192-168-5-205:/srv/BigData/mppdb/data1/master1/base/140753 cat 141021
▒▒▒F:▒,▒ [▒؟H Zhaoyunommhost-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节点下的数据目录及其文件 ommhost-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的数据文件
ommhost-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
ommhost-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从备
ommhost-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字节没有存储真实的数据内容数据节点DNDN主节点和DN备节点都存储数据库表的数据但DN从备不占用实际存储空间仅在主、备DN故障时才起作用且只存储数据日志不存储数据页面。
