运维管理22 分钟阅读
GaussDB（集中式） 运维命令 100 条
GaussDB 同时提供集中式和分布式部署形态，两者在系统视图、节点管理、执行计划和运维方式上存在明显差异。如果把两套架构的命令混在一起，不仅查询可能报错，还容易对巡检结果产生错误判断。
2026年7月29日阅读—点赞—收藏—
dba100gaussdb墨力计划
DBA100
100 条命令系列文章专栏
100 条命令系列文章专栏
GaussDB 同时提供集中式和分布式部署形态，两者在系统视图、节点管理、执行计划和运维方式上存在明显差异。如果把两套架构的命令混在一起，不仅查询可能报错，还容易对巡检结果产生错误判断。
GaussDB 同时提供集中式和分布式部署形态，两者在系统视图、节点管理、执行计划和运维方式上存在明显差异。如果把两套架构的命令混在一起，不仅查询可能报错，还容易对巡检结果产生错误判断。
本文只整理 GaussDB 集中式 V2.0-8.x 常用命令，主要面向通过 gsql 连接数据库后可以执行的 SQL、元命令和客户端工具操作，覆盖基础信息、数据库对象、参数、会话、事务锁、SQL 性能、空间、表索引、用户权限、导入导出和高可用检查等场景。
GaussDB 公有云属于托管数据库。实例启停、扩容、备份恢复、主备切换、参数修改和账号权限中的部分操作，需要通过华为云控制台、API 或运维管理面完成，普通数据库账号不能直接执行。因此，本文不会把 openGauss 的 gs_om、gs_ctl 等服务器管理命令直接套用到 GaussDB 云服务。
不同内核小版本、部署形态和账号权限下，系统视图字段可能存在差异。文中的数据库、用户、模式、IP、端口和对象名称均为示例，执行前应先在测试环境验证。涉及终止会话、删除对象、修改参数、回收空间和恢复数据时，应先确认影响范围。
1gsql -d appdb -h 192.168.1.10 -p 8000 -U appuser -W-W 表示交互式输入密码，避免密码明文保存在 Shell 历史记录中。
1SELECT version();排查问题前应先确认内核版本，不能只根据控制台中的产品名称判断。
1SELECT2 current_database(),3 current_user,4 session_user;1SELECT2 inet_server_addr() AS server_ip,3 inet_server_port() AS server_port,4 inet_client_addr() AS client_ip,5 inet_client_port() AS client_port;1SELECT2 pg_postmaster_start_time(),3 now() - pg_postmaster_start_time() AS uptime;1SELECT2 now(),3 current_setting('TimeZone') AS timezone;1SELECT datname, datcompatibility2FROM pg_database3WHERE datname = current_database();兼容模式会影响 SQL 语法、数据类型、函数和对象行为。
1SHOW search_path;临时修改当前会话搜索路径：
1SET search_path TO app, public;1SELECT 1;这条命令常用于连接池、监控探针和故障切换后的可用性验证。
1gsql --version2gsql --help1SELECT2 datname,3 datdba::regrole AS owner,4 encoding,5 datallowconn,6 datconnlimit7FROM pg_database8ORDER BY datname;1SELECT2 datname,3 pg_size_pretty(pg_database_size(datname)) AS database_size4FROM pg_database5WHERE datallowconn6ORDER BY pg_database_size(datname) DESC;1CREATE DATABASE appdb2 WITH OWNER = appuser3 ENCODING = 'UTF8';公有云实例是否允许创建数据库，以及允许创建的数量，应以实例规格和账号权限为准。
1ALTER DATABASE appdb CONNECTION LIMIT 200;该值只是数据库级限制，实际连接数还受到实例参数和用户连接数限制。
1SELECT2 nspname AS schema_name,3 nspowner::regrole AS owner4FROM pg_namespace5ORDER BY nspname;1CREATE SCHEMA app AUTHORIZATION appuser;1SELECT2 schemaname,3 tablename,4 tableowner,5 tablespace6FROM pg_tables7WHERE schemaname NOT IN ('pg_catalog', 'information_schema')8ORDER BY schemaname, tablename;1SELECT2 schemaname,3 viewname,4 viewowner5FROM pg_views6WHERE schemaname NOT IN ('pg_catalog', 'information_schema')7ORDER BY schemaname, viewname;1SELECT2 sequence_schema,3 sequence_name,4 data_type,5 start_value,6 minimum_value,7 maximum_value,8 increment9FROM information_schema.sequences10ORDER BY sequence_schema, sequence_name;1\d+ app.orders列出表：
1\dt+ app.*1SHOW max_connections;1SELECT2 name,3 setting,4 unit,5 context,6 vartype,7 pending_restart8FROM pg_settings9ORDER BY name;1SELECT2 name,3 setting,4 boot_val,5 reset_val,6 context7FROM pg_settings8WHERE setting IS DISTINCT FROM boot_val9ORDER BY name;1SELECT2 name,3 setting,4 pending_restart5FROM pg_settings6WHERE pending_restart7ORDER BY name;1SELECT name, setting, unit2FROM pg_settings3WHERE name IN (4 'max_connections',5 'session_timeout',6 'statement_timeout',7 'idle_in_transaction_session_timeout'8)9ORDER BY name;部分参数是否存在与当前版本有关。
1SELECT name, setting, unit2FROM pg_settings3WHERE name IN (4 'shared_buffers',5 'work_mem',6 'maintenance_work_mem',7 'temp_buffers'8)9ORDER BY name;1SELECT name, setting, unit2FROM pg_settings3WHERE name LIKE 'log_%'4ORDER BY name;1SET statement_timeout = '30min';恢复默认值：
1RESET statement_timeout;1ALTER DATABASE appdb2SET statement_timeout = '30min';新连接建立后生效。恢复设置：
1ALTER DATABASE appdb RESET statement_timeout;1SELECT pg_reload_conf();GaussDB 公有云参数通常应通过控制台修改。即使 SQL 可以执行，也不代表普通账号能够修改实例级配置。
1SELECT count(*) AS connection_count2FROM pg_stat_activity;1SELECT2 usename,3 state,4 count(*) AS sessions5FROM pg_stat_activity6GROUP BY usename, state7ORDER BY sessions DESC;1SELECT2 client_addr,3 usename,4 count(*) AS sessions5FROM pg_stat_activity6GROUP BY client_addr, usename7ORDER BY sessions DESC;1SELECT2 pid,3 usename,4 datname,5 client_addr,6 application_name,7 state,8 query_start,9 query10FROM pg_stat_activity11WHERE state <> 'idle'12ORDER BY query_start;1SELECT2 pid,3 usename,4 client_addr,5 now() - query_start AS running_time,6 state,7 query8FROM pg_stat_activity9WHERE query_start IS NOT NULL10 AND state = 'active'11ORDER BY running_time DESC;1SELECT2 pid,3 usename,4 client_addr,5 now() - xact_start AS transaction_time,6 state,7 query8FROM pg_stat_activity9WHERE xact_start IS NOT NULL10ORDER BY transaction_time DESC;1SELECT2 pid,3 usename,4 client_addr,5 now() - xact_start AS transaction_time,6 query7FROM pg_stat_activity8WHERE state = 'idle in transaction'9ORDER BY xact_start;1SELECT txid_current();1SELECT pg_cancel_backend(12345);取消 SQL 后连接仍然保留。
1SELECT pg_terminate_backend(12345);终止会话会回滚该会话未提交的事务。必须先核对 PID、用户、客户端地址和业务影响。
1SELECT2 pid,3 locktype,4 mode,5 database,6 relation::regclass AS relation_name,7 transactionid,8 granted9FROM pg_locks10ORDER BY granted, pid;1SELECT2 pid,3 locktype,4 mode,5 relation::regclass AS relation_name,6 transactionid7FROM pg_locks8WHERE NOT granted9ORDER BY pid;1SELECT2 node_name,3 db_name,4 tid,5 sessionid,6 wait_status,7 wait_event,8 locktag,9 lockmode,10 block_sessionid11FROM pg_thread_wait_status12WHERE wait_status <> 'none'13ORDER BY tid;pg_stat_activity 主要通过 waiting 判断是否等待锁，更详细的锁、轻量锁和 I/O 等待信息应查询 pg_thread_wait_status。
1SELECT2 blocked.pid AS blocked_pid,3 blocked.usename AS blocked_user,4 blocker.pid AS blocker_pid,5 blocker.usename AS blocker_user,6 blocked.query AS blocked_query,7 blocker.query AS blocker_query8FROM pg_stat_activity blocked9JOIN pg_locks blocked_lock10 ON blocked.pid = blocked_lock.pid11 AND NOT blocked_lock.granted12JOIN pg_locks blocker_lock13 ON blocker_lock.granted14 AND blocker_lock.locktype = blocked_lock.locktype15 AND blocker_lock.database IS NOT DISTINCT FROM blocked_lock.database16 AND blocker_lock.relation IS NOT DISTINCT FROM blocked_lock.relation17 AND blocker_lock.page IS NOT DISTINCT FROM blocked_lock.page18 AND blocker_lock.tuple IS NOT DISTINCT FROM blocked_lock.tuple19 AND blocker_lock.transactionid IS NOT DISTINCT FROM blocked_lock.transactionid20JOIN pg_stat_activity blocker21 ON blocker.pid = blocker_lock.pid22WHERE blocker.pid <> blocked.pid;1SELECT2 l.pid,3 a.usename,4 l.mode,5 l.granted,6 a.state,7 a.query8FROM pg_locks l9JOIN pg_stat_activity a ON a.pid = l.pid10WHERE l.relation = 'app.orders'::regclass11ORDER BY l.granted, l.pid;1SELECT2 l.pid,3 l.relation::regclass AS relation_name,4 a.usename,5 a.query_start,6 a.query7FROM pg_locks l8JOIN pg_stat_activity a ON a.pid = l.pid9WHERE l.mode = 'AccessExclusiveLock'10 AND l.granted;1SELECT2 pid,3 usename,4 now() - query_start AS elapsed,5 waiting,6 state,7 query8FROM pg_stat_activity9WHERE state <> 'idle'10ORDER BY elapsed DESC;1SET lockwait_timeout = '30s';参数名称和可设置范围应以当前版本的 pg_settings 为准。
1SHOW transaction_isolation;1BEGIN;2SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;事务完成后执行：
1COMMIT;1EXPLAIN2SELECT *3FROM app.orders4WHERE customer_id = 1001;1EXPLAIN (ANALYZE, BUFFERS, VERBOSE)2SELECT *3FROM app.orders4WHERE customer_id = 1001;ANALYZE 会真正执行 SQL。对修改类 SQL 和高开销查询应谨慎使用。
1SELECT2 schemaname,3 relname,4 seq_scan,5 seq_tup_read,6 idx_scan,7 n_live_tup,8 n_dead_tup9FROM pg_stat_user_tables10ORDER BY seq_tup_read DESC;1SELECT2 schemaname,3 relname,4 indexrelname,5 idx_scan,6 idx_tup_read,7 idx_tup_fetch8FROM pg_stat_user_indexes9ORDER BY idx_scan;1SELECT2 schemaname,3 relname,4 indexrelname,5 pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,6 idx_scan7FROM pg_stat_user_indexes8WHERE idx_scan = 09ORDER BY pg_relation_size(indexrelid) DESC;统计为零不代表索引一定可以删除，还要结合统计重置时间、主键约束和完整业务周期判断。
1SELECT2 datname,3 xact_commit,4 xact_rollback,5 blks_read,6 blks_hit,7 tup_returned,8 tup_fetched,9 tup_inserted,10 tup_updated,11 tup_deleted12FROM pg_stat_database13ORDER BY datname;1SELECT2 datname,3 round(4 blks_hit * 100.0 /5 NULLIF(blks_hit + blks_read, 0),6 27 ) AS cache_hit_pct8FROM pg_stat_database9ORDER BY cache_hit_pct;1ANALYZE app.orders;1SELECT2 schemaname,3 relname,4 last_vacuum,5 last_autovacuum,6 last_analyze,7 last_autoanalyze8FROM pg_stat_user_tables9ORDER BY relname;1SELECT pg_stat_reset();重置会影响后续趋势分析和未使用索引判断，不应在生产环境随意执行。
1SELECT2 n.nspname AS schema_name,3 pg_size_pretty(SUM(pg_total_relation_size(c.oid))) AS total_size4FROM pg_class c5JOIN pg_namespace n ON n.oid = c.relnamespace6WHERE c.relkind IN ('r', 'm')7 AND n.nspname NOT IN ('pg_catalog', 'information_schema')8GROUP BY n.nspname9ORDER BY SUM(pg_total_relation_size(c.oid)) DESC;1SELECT2 schemaname,3 relname,4 pg_size_pretty(pg_relation_size(relid)) AS table_size,5 pg_size_pretty(pg_total_relation_size(relid)) AS total_size6FROM pg_stat_user_tables7ORDER BY pg_total_relation_size(relid) DESC8LIMIT 20;1SELECT2 pg_size_pretty(pg_relation_size('app.orders')) AS table_size,3 pg_size_pretty(pg_indexes_size('app.orders')) AS indexes_size,4 pg_size_pretty(pg_total_relation_size('app.orders')) AS total_size;1SELECT2 spcname,3 spcowner::regrole AS owner,4 pg_tablespace_location(oid) AS location,5 pg_size_pretty(pg_tablespace_size(oid)) AS size6FROM pg_tablespace7ORDER BY spcname;托管云数据库通常不能像自建数据库一样任意指定服务器文件系统目录。
1SELECT2 n.nspname AS schema_name,3 c.relname AS table_name,4 c.reltuples::bigint AS estimated_rows5FROM pg_class c6JOIN pg_namespace n ON n.oid = c.relnamespace7WHERE c.relkind = 'r'8 AND n.nspname NOT IN ('pg_catalog', 'information_schema')9ORDER BY estimated_rows DESC;reltuples 是统计信息估算值，不等同于精确 COUNT(*)。
1SELECT2 schemaname,3 relname,4 n_live_tup,5 n_dead_tup,6 round(7 n_dead_tup * 100.0 /8 NULLIF(n_live_tup + n_dead_tup, 0),9 210 ) AS dead_tuple_pct11FROM pg_stat_user_tables12ORDER BY dead_tuple_pct DESC NULLS LAST;1VACUUM ANALYZE app.orders;1VACUUM FULL app.orders;该操作会重写表并持有排他锁，应安排维护窗口。
1SELECT2 schemaname,3 tablename,4 indexname,5 indexdef6FROM pg_indexes7WHERE schemaname = 'app'8 AND tablename = 'orders'9ORDER BY indexname;1REINDEX INDEX app.idx_orders_customer;重建整张表的索引：
1REINDEX TABLE app.orders;1SELECT count(*)2FROM app.orders;大表执行全表计数可能消耗大量 I/O，应在低峰期执行或使用估算值。
1SELECT2 column_name,3 data_type,4 character_maximum_length,5 is_nullable,6 column_default7FROM information_schema.columns8WHERE table_schema = 'app'9 AND table_name = 'orders'10ORDER BY ordinal_position;1SELECT2 tc.constraint_name,3 kcu.column_name,4 kcu.ordinal_position5FROM information_schema.table_constraints tc6JOIN information_schema.key_column_usage kcu7 ON tc.constraint_name = kcu.constraint_name8 AND tc.table_schema = kcu.table_schema9 AND tc.table_name = kcu.table_name10WHERE tc.constraint_type = 'PRIMARY KEY'11 AND tc.table_schema = 'app'12 AND tc.table_name = 'orders'13ORDER BY kcu.ordinal_position;1SELECT2 constraint_name,3 constraint_type,4 is_deferrable,5 initially_deferred6FROM information_schema.table_constraints7WHERE table_schema = 'app'8 AND table_name = 'orders'9ORDER BY constraint_type, constraint_name;1CREATE INDEX idx_orders_customer2ON app.orders(customer_id);创建前应结合查询条件、选择性和写入成本评估，不能只因为 SQL 慢就盲目加索引。
1DROP INDEX app.idx_orders_customer;删除前应确认索引不是约束依赖对象，并观察完整业务周期内的使用情况。
1TRUNCATE TABLE app.stage_orders;TRUNCATE 会快速清空整张表，通常无法通过普通事务日志方式按行恢复。
1ALTER TABLE app.orders OWNER TO appuser;1SELECT2 grantee,3 privilege_type,4 is_grantable5FROM information_schema.role_table_grants6WHERE table_schema = 'app'7 AND table_name = 'orders'8ORDER BY grantee, privilege_type;在 gsql 中执行：
1\copy (SELECT * FROM app.orders WHERE order_date >= DATE '2026-07-01') TO '/tmp/orders.csv' WITH CSV HEADER\copy 在客户端读写文件，路径属于运行 gsql 的客户端主机。
1SELECT2 usename,3 usesuper,4 usecreatedb,5 valbegin,6 valuntil7FROM pg_user8ORDER BY usename;1SELECT2 rolname,3 rolsuper,4 rolcreaterole,5 rolcreatedb,6 rolcanlogin,7 rolconnlimit8FROM pg_roles9ORDER BY rolname;1CREATE USER appuser2IDENTIFIED BY 'Replace_With_Strong_Password';1ALTER USER appuser2IDENTIFIED BY 'Replace_With_New_Strong_Password';1ALTER USER appuser ACCOUNT LOCK;解锁：
1ALTER USER appuser ACCOUNT UNLOCK;1ALTER ROLE appuser CONNECTION LIMIT 50;1CREATE ROLE readonly_role;1GRANT USAGE ON SCHEMA app TO readonly_role;2GRANT SELECT ON ALL TABLES IN SCHEMA app TO readonly_role;1GRANT readonly_role TO report_user;1REVOKE INSERT, UPDATE, DELETE2ON TABLE app.orders3FROM appuser;需要同时检查用户直接权限和角色继承权限。
1gs_dump -h 192.168.1.10 -p 8000 -U backup_user \2 -F c -f /backup/appdb.dump appdb客户端工具版本应与目标 GaussDB 版本匹配。云服务正式备份仍应以控制台备份策略为主。
1gs_dump -h 192.168.1.10 -p 8000 -U backup_user \2 -n app -F c -f /backup/app_schema.dump appdb1gs_dump -h 192.168.1.10 -p 8000 -U backup_user \2 -t app.orders -F c -f /backup/orders.dump appdb1gs_restore -h 192.168.1.10 -p 8000 -U restore_user \2 -d appdb /backup/appdb.dump恢复前应确认目标模式、对象冲突、属主、权限和字符集。
1gsql -h 192.168.1.10 -p 8000 -U restore_user \2 -d appdb -f /backup/appdb.sql1SELECT *2FROM pg_stat_get_stream_replications();重点关注主备连接状态、发送与接收位置以及同步差距。普通业务账号可能没有完整查看权限。
1SELECT *2FROM pg_stat_get_wal_senders();该函数是否可见、返回哪些字段，与版本和权限有关。
1SELECT *2FROM pg_stat_bad_block3ORDER BY last_time DESC;如果视图中出现记录，应结合告警、日志和存储状态进一步确认，不能仅凭一条记录直接判断数据已经损坏。
1\d+ pg_stat_activity2\d+ pg_locks3\d+ pg_stat_user_tables当网上示例字段与现场版本不一致时，先查看当前环境对象定义，再调整 SQL。
1SELECT 'database_size' AS item,2 pg_size_pretty(pg_database_size(current_database())) AS value3UNION ALL4SELECT 'connections',5 count(*)::text6FROM pg_stat_activity7UNION ALL8SELECT 'active_sessions',9 count(*)::text10FROM pg_stat_activity11WHERE state = 'active'12UNION ALL13SELECT 'lock_waiting_sessions',14 count(*)::text15FROM pg_stat_activity16WHERE waiting;这条 SQL 只是一份快速摘要。正式巡检还应结合控制台监控、CPU、内存、磁盘、连接使用率、慢 SQL、备份状态、主备状态和告警记录综合判断。
GaussDB 集中式虽然在 SQL 使用方式上与 openGauss、PostgreSQL 有不少相似之处，但它仍然是一套独立演进的商业数据库产品，尤其是云服务中的权限模型、参数管理、备份恢复和高可用操作，都不能直接套用自建 openGauss 的处理方式。
日常排查时，建议先确认三个信息：数据库是否为集中式、具体内核版本、当前账号权限。确认这三点后，再根据连接、锁、执行计划、统计信息、空间和主备状态逐层排查。
对于控制台才能完成的实例启停、规格变更、备份恢复和主备切换，应按照华为云对应版本的操作指南执行，不要为了凑“命令数量”而使用未经官方确认的服务器端命令。