简介

GaussDB(for openGauss)是基于华为主导的openGauss生态推出的企业级分布式关系型数据库。该产品具备企业级复杂事务混合负载能力，同时支持分布式事务，同城跨AZ部署，数据0丢失，支持1000+的扩展能力，PB级海量存储。

目前官方收费版本：（2019年10月左右，命名再次调整）

• gaussdb 100，更名为 GaussDB T (以OLTP和集群为方向，在线事务处理）
• gaussdb 200 合并 300 的部分设计，更名为 GaussDB A (主打OLAP 在线分析处理）
• gaussdb 300 型号取消，涉及功能并入 100 或 200

目前免费开源版本：openGauss

关于数据迁移

GaussDB 最初是由 PostgreSQL 开源项目自研而来，虽几乎已经是全部自研，但还保留了驱动包以及驱动类。

官方提供了3个驱动包：（任意一个都行，但需注意冲突问题）

1. gsjdbc4.jar：驱动类为 org.postgresql.Driver

与 PostgreSQL 驱动类名一样，在无 PostgreSQL 时使用

2. gsjdbc200.jar：驱动类为 com.huawei.gauss200.jdbc.Driver

解决类名冲突问题，项目中同时有 PostgreSQL 和 GaussDB 时使用

3. openguassjdbc.jar：驱动类为 com.huawei.opengauss.jdbc.Driver

任意一个驱动类都行，可以通过使用 gsjdbc4.jar 替换 Datax、Kettle 等工具中 PostgreSQL 驱动包，来使其支持 GaussDB，从而进行数据迁移。

元数据使用

元数据表或视图

系统表

GS_	PG_	PG_	-
GS_AUDITING_POLICY	PG_AGGREGATE	PG_NAMESPACE	PGXC_CLASS
GS_AUDITING_POLICY_ACCESS	PG_AM	PG_OBJECT	PGXC_GROUP
GS_AUDITING_POLICY_FILTERS	PG_AMOP	PG_OPCLASS	PGXC_NODE
GS_AUDITING_POLICY_PRIVILEGES	PG_AMPROC	PG_OPERATOR	PGXC_REDISTB
GS_ASP	PG_APP_WORKLOADGROUP_MAPPING	PG_OPFAMILY	PGXC_SLICE
GS_CLIENT_GLOBAL_KEYS	PG_ATTRDEF	PG_PARTITION	PLAN_TABLE_DATA
GS_CLIENT_GLOBAL_KEYS_ARGS	PG_ATTRIBUTE	PG_PLTEMPLATE	STATEMENT_HISTORY
GS_COLUMN_KEYS	PG_AUTHID	PG_PROC	STREAMING_STREAM
GS_COLUMN_KEYS_ARGS	PG_AUTH_HISTORY	PG_PUBLICATION	STREAMING_CONT_QUERY
GS_DB_PRIVILEGE	PG_AUTH_MEMBERS	PG_PUBLICATION_REL	STREAMING_REAPER_STATUS
GS_ENCRYPTED_COLUMNS	PG_CAST	PG_RANGE
GS_ENCRYPTED_PROC	PG_CLASS	PG_REPLICATION_ORIGIN
GS_GLOBAL_CHAIN	PG_COLLATION	PG_RESOURCE_POOL
GS_GLOBAL_CONFIG	PG_CONSTRAINT	PG_REWRITE
GS_JOB_ATTRIBUTE	PG_CONVERSION	PG_RLSPOLICY
GS_JOB_ARGUMENT	PG_DATABASE	PG_SECLABEL
GS_MASKING_POLICY	PG_DB_ROLE_SETTING	PG_SHDEPEND
GS_MASKING_POLICY_ACTIONS	PG_DEFAULT_ACL	PG_SHDESCRIPTION
GS_MASKING_POLICY_FILTERS	PG_DEPEND	PG_SHSECLABEL
GS_MATVIEW	PG_DESCRIPTION	PG_STATISTIC
GS_MATVIEW_DEPENDENCY	PG_DIRECTORY	PG_SUBSCRIPTION
GS_MODEL_WAREHOUSE	PG_ENUM	PG_SYNONYM
GS_POLICY_LABEL	PG_EXTENSION_DATA_SOURCE	PG_TABLESPACE
GS_RECYCLEBIN	PG_FOREIGN_DATA_WRAPPER	PG_TRIGGER
GS_SQL_PATCH	PG_FOREIGN_SERVER	PG_TS_CONFIG
GS_TXN_SNAPSHOT	PG_FOREIGN_TABLE	PG_TS_CONFIG_MAP
GS_UID	PG_HASHBUCKET	PG_TS_DICT
GS_WLM_INSTANCE_HISTORY	PG_INDEX	PG_TS_PARSER
GS_WLM_OPERATOR_INFO	PG_INHERITS	PG_TS_TEMPLATE
GS_WLM_SESSION_QUERY_INFO_ALL	PG_JOB	PG_TYPE
GS_WLM_USER_RESOURCE_HISTORY	PG_JOB_PROC	PG_USER_MAPPING
	PG_LANGUAGE	PG_USER_STATUS
	PG_LARGEOBJECT	PG_WORKLOAD_GROUP
	PG_LARGEOBJECT_METADATA

系统视图

ADM/GLOBAL/PV	DB/MY	GS/PGXC	PG_
ADM_COL_COMMENTS	DB_ALL_TABLES	GS_ALL_CONTROL_GROUP_INFO	PG_AVAILABLE_EXTENSIONS
ADM_CONS_COLUMNS	DB_CONSTRAINTS	GS_AUDITING	PG_AVAILABLE_EXTENSION_VERSIONS
ADM_CONSTRAINTS	DB_CONS_COLUMNS	GS_AUDITING_ACCESS	PG_COMM_DELAY
ADM_DATA_FILES	DB_DEPENDENCIES	GS_AUDITING_PRIVILEGE	PG_COMM_RECV_STREAM
ADM_HIST_SNAPSHOT	DB_IND_COLUMNS	GS_CLUSTER_RESOURCE_INFO	PG_COMM_SEND_STREAM
ADM_HIST_SQL_PLAN	DB_IND_EXPRESSIONS	GS_DB_PRIVILEGES	PG_COMM_STATUS
ADM_HIST_SQLSTAT	DB_INDEXES	GS_GET_CONTROL_GROUP_INFO	PG_CONTROL_GROUP_CONFIG
ADM_HIST_SQLSTAT_FUNC	DB_OBJECTS	GS_GSC_MEMORY_DETAIL	PG_CURSORS
ADM_HIST_SQLSTAT_IDLAG_FUNC	DB_PROCEDURES	GS_LABELS	PG_GET_INVALID_BACKENDS
ADM_INDEXES	DB_SEQUENCES	GS_LSC_MEMORY_DETAIL	PG_GET_SENDERS_CATCHUP_TIME
ADM_IND_COLUMNS	DB_SOURCE	GS_MASKING	PG_GROUP
ADM_IND_EXPRESSIONS	DB_SYNONYMS	GS_MATVIEWS	PG_INDEXES
ADM_IND_PARTITIONS	DB_TAB_COLUMNS	GS_MATVIEWS	PG_LOCKS
ADM_OBJECTS	DB_TAB_COMMENTS	GS_SESSION_CPU_STATISTICS	PG_NODE_ENV
ADM_PART_INDEXES	DB_COL_COMMENTS	GS_SESSION_MEMORY_STATISTICS	PG_OS_THREADS
ADM_PART_TABLES	DB_TABLES	GS_SQL_COUNT	PG_POOLER_STATUS
ADM_PROCEDURES	DB_TRIGGERS	GS_STAT_DB_CU	PG_PREPARED_STATEMENTS
ADM_SEQUENCES	DB_USERS	GS_STAT_SESSION_CU	PG_PREPARED_XACTS
ADM_SCHEDULER_JOBS	DB_VIEWS	GS_WLM_CGROUP_INFO	PG_PUBLICATION_TABLES
ADM_SOURCE	MY_COL_COMMENTS	GS_WLM_OPERATOR_HISTORY	PG_REPLICATION_ORIGIN_STATUS
ADM_SYNONYMS	MY_CONS_COLUMNS	GS_WLM_OPERATOR_STATISTICS	PG_REPLICATION_SLOTS
ADM_TABLES	MY_CONSTRAINTS	GS_WLM_REBUILD_USER_RESOURCE_POOL	PG_RLSPOLICIES
ADM_TABLESPACES	MY_INDEXES	GS_WLM_RESOURCE_POOL	PG_ROLES
ADM_TAB_COLUMNS	MY_IND_COLUMNS	GS_WLM_SESSION_HISTORY	PG_RULES
ADM_TAB_COMMENTS	MY_IND_EXPRESSIONS	GS_WLM_SESSION_INFO	PG_RUNNING_XACTS
ADM_TAB_PARTITIONS	MY_IND_PARTITIONS	GS_WLM_SESSION_INFO_ALL	PG_SECLABELS
ADM_TRIGGERS	MY_JOBS	GS_WLM_USER_INFO	PG_SESSION_IOSTAT
ADM_TYPE_ATTRS	MY_OBJECTS	GS_WLM_USER_SESSION_INFO	PG_SESSION_WLMSTAT
ADM_USERS	MY_PART_INDEXES	GS_WLM_SESSION_STATISTICS	PG_SETTINGS
ADM_VIEWS	MY_PART_TABLES	GS_WLM_WORKLOAD_RECORDS	PG_SHADOW
COMM_CLIENT_INFO	MY_PROCEDURES	GS_WRITE_TERM_LOG	PG_SHARED_MEMORY_DETAIL
DV_SESSIONS	MY_SEQUENCES	PGXC_COMM_DELAY	PG_STATS
DV_SESSION_LONGOPS	MY_SOURCE	PGXC_COMM_RECV_STREAM	PG_STAT_ACTIVITY
GET_GLOBAL_PREPARED_XACTS	MY_SYNONYMS	PGXC_COMM_SEND_STREAM	PG_STAT_ALL_INDEXES
GV_SESSION	MY_TAB_COLUMNS	PGXC_COMM_STATUS	PG_STAT_ALL_TABLES
MPP_TABLES	MY_TAB_COMMENTS	PGXC_GET_STAT_ALL_TABLES	PG_STAT_BAD_BLOCK
PLAN_TABLE	MY_TAB_PARTITIONS	PGXC_GET_TABLE_SKEWNESS	PG_STAT_BGWRITER
SYS_DUMMY	MY_TABLES	PGXC_NODE_ENV	PG_STAT_DATABASE
GLOBAL_BAD_BLOCK_INFO	MY_TRIGGERS	PGXC_OS_THREADS	PG_STAT_DATABASE_CONFLICTS
GLOBAL_CLEAR_BAD_BLOCK_INFO	MY_VIEWS	PGXC_PREPARED_XACTS	PG_STAT_REPLICATION
GLOBAL_COMM_CLIENT_INFO		PGXC_RUNNING_XACTS	PG_STAT_SUBSCRIPTION
GLOBAL_STAT_HOTKEYS_INFO		PGXC_STAT_ACTIVITY	PG_STAT_SYS_INDEXES
GLOBAL_WAL_SENDER_STATUS		PGXC_STAT_BAD_BLOCK	PG_STAT_SYS_TABLES
PV_FILE_STAT		PGXC_SQL_COUNT	PG_STAT_USER_FUNCTIONS
PV_INSTANCE_TIME		PGXC_THREAD_WAIT_STATUS	PG_STAT_USER_INDEXES
PV_OS_RUN_INFO		PGXC_TOTAL_MEMORY_DETAIL	PG_STAT_USER_TABLES
PV_REDO_STAT		PGXC_VARIABLE_INFO	PG_STAT_XACT_ALL_TABLES
PV_SESSION_MEMORY		PGXC_WLM_REBUILD_USER_RESPOOL	PG_STAT_XACT_SYS_TABLES
PV_SESSION_MEMORY_CONTEXT		PGXC_WLM_SESSION_HISTORY	PG_STAT_XACT_USER_FUNCTIONS
PV_SESSION_MEMORY_DETAIL		PGXC_WLM_SESSION_INFO	PG_STAT_XACT_USER_TABLES
PV_SESSION_STAT		PGXC_WLM_SESSION_STATISTICS	PG_STATIO_ALL_INDEXES
PV_SESSION_TIME		PGXC_WLM_WORKLOAD_RECORDS	PG_STATIO_ALL_SEQUENCES
PV_THREAD_MEMORY_CONTEXT			PG_STATIO_ALL_TABLES
PV_TOTAL_MEMORY_DETAIL			PG_STATIO_SYS_INDEXES
			PG_STATIO_SYS_SEQUENCES
			PG_STATIO_SYS_TABLES
			PG_STATIO_USER_INDEXES
			PG_STATIO_USER_SEQUENCES
			PG_STATIO_USER_TABLES
			PG_THREAD_WAIT_STATUS
			PG_TABLES
			PG_TDE_INFO
			PG_TIMEZONE_ABBREVS
			PG_TIMEZONE_NAMES
			PG_TOTAL_MEMORY_DETAIL
			PG_TOTAL_USER_RESOURCE_INFO
			PG_TOTAL_USER_RESOURCE_INFO_OID
			PG_USER
			PG_USER_MAPPINGS
			PG_VARIABLE_INFO
			PG_VIEWS
			PG_WLM_STATISTICS

查询所有模式

SELECT
            pn.oid AS schema_oid
           ,iss.CATALOG_NAME
           ,iss.schema_owner
           ,iss.SCHEMA_NAME 
      FROM information_schema.schemata iss
INNER JOIN pg_namespace pn 
        ON pn.nspname = iss.SCHEMA_NAME;

查询当前模式下的所有表名以及中文注释

SELECT DISTINCT "table_name", obj_description ( oid, 'pg_class' ) AS table_alias 
  FROM information_schema.tables t1, pg_class t2 
 WHERE table_schema = 'gisdb' 
   AND t1."table_name" = t2.relname

表名查询字段属性

# 方式1：（支持查询指定模式schema下某张表的字段信息）
    SELECT u.relname, a.attname AS field, t.typname AS type
      FROM (select c.relname,c.oid,n.nspname from pg_class c left join pg_catalog.pg_namespace n on c.relnamespace = n.oid) u,
           pg_attribute a,
           pg_type t
     WHERE u.relname = '实际的表名'
       AND u.nspname = '实际的schema名称'
       AND a.attnum > 0
       AND a.attrelid = u.oid
       AND a.atttypid = t.oid
  ORDER BY a.attnum;
  
# 方式:2：
    SELECT a.attnum, a.attname AS field, t.typname AS type, a.attlen AS length, a.atttypmod AS lengthvar, a.attnotnull AS notnull, b.description AS comment
      FROM pg_class c, pg_attribute a
 LEFT JOIN pg_description b
        ON a.attrelid = b.objoid
       AND a.attnum = b.objsubid, pg_type t
     WHERE c.relname = '实际的表名'
       AND a.attnum > 0
       AND a.attrelid = c.oid
       AND a.atttypid = t.oid
  ORDER BY a.attnum;

查询视图定义语句

select 'create or repacle view ' || viewname || ' as ' || definition FROM pg_view WHERE schemaname = '模式' AND viewowner = '用户';
select 'create or repacle view ' || view_name || ' as ' || text from db_views  where owner = '用户';

查询表字段信息

SELECT table_name, column_name
  FROM information_schema.columns
 WHERE table_schema = '模式'
   AND table_name = '表名'
 ORDER BY table_name, column_name

踩坑日记

date 字段类型

高斯数据库兼容模式：

可能不同兼容模式行为不一致，可查询系统表 PG_DATABASE（datcompatibility字段） 查看当前数据库兼容模式（当前支持四种兼容模式：A、B、C、PG，分别表示兼容O、MY、TD和POSTGRES，默认为MySQL兼容模式）
20221122 补充：确实是因为创建模式时兼容模式导致，重新创建模式后问题解决

由于 date 字段类型不存储时分秒（Oracle模式下？），故需修改为 timestamp 类型，所有表及视图都需要重新创建。