OpenGauss DBA 远程支持命令指南

gsql -d postgres -p 5432

gsql -d <db_name> -U <user> -p <port> -W <password>

gsql -h <ip> -d <db_name> -U <user> -p <port> -W

gsql -d <db_name> -p <port> -r

gsql -d <db_name> -p <port> -c "SELECT version();"

gsql -d <db_name> -p <port> -f /path/to/script.sql

\l -- 列出所有数据库 \dn -- 列出所有schema \dt -- 列出当前库所有表 \d <table> -- 查看表结构 \du -- 列出所有用户/角色 \dx -- 列出已安装扩展 \conninfo -- 查看当前连接信息 \x -- 切换扩展显示模式（宽表友好） \timing -- 开启SQL执行计时 \q -- 退出 gsql

gs_om -t status --detail

gs_om -t status

cm_ctl query -Cvdi

cm_ctl query -Cvip

gs_ctl status -D <GAUSSDATA>/dn1

gs_ctl start -D <GAUSSDATA>/dn1

gs_ctl stop -D <GAUSSDATA>/dn1

gs_ctl restart -D <GAUSSDATA>/dn1

gsql -d postgres -p <port> -c "SELECT version();"

gsql -d postgres -p <port> -c "SELECT local_role, static_connect, state FROM pg_stat_replication;"

gsql -d postgres -p <port> -c "SELECT pg_is_in_recovery();"

gs_om -t status --all

SELECT pid, usename, datname, client_addr, state, query, query_start, state_change FROM pg_stat_activity WHERE state <> 'idle' ORDER BY query_start;

SELECT pid, usename, datname, client_addr, state, query, query_start, backend_start FROM pg_stat_activity ORDER BY backend_start;

SELECT count(*) FROM pg_stat_activity;

SELECT usename, count(*) as cnt FROM pg_stat_activity GROUP BY usename ORDER BY cnt DESC;

SELECT datname, count(*) as cnt FROM pg_stat_activity GROUP BY datname ORDER BY cnt DESC;

SHOW max_connections;

SELECT pid, usename, datname, query, now() - query_start AS duration FROM pg_stat_activity WHERE state = 'active' AND now() - query_start > interval '5 min' ORDER BY duration DESC;

SELECT pg_terminate_backend(<pid>);

SELECT pg_cancel_backend(<pid>);

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE usename = '<username>' AND pid <> pg_backend_pid();

SELECT pid, usename, datname, client_addr, state, now() - state_change AS idle_duration FROM pg_stat_activity WHERE state = 'idle' AND now() - state_change > interval '30 min' ORDER BY idle_duration DESC;

EXPLAIN (ANALYZE, BUFFERS) <SQL语句>;

SELECT pid, usename, datname, query, query_start, now() - query_start AS exec_time FROM pg_stat_activity WHERE state = 'active' ORDER BY exec_time DESC;

-- 查看慢SQL配置 SHOW log_min_duration_statement; -- 设置慢SQL阈值（如超过3秒记录） ALTER SYSTEM SET log_min_duration_statement = 3000;

SELECT query, calls, total_time, mean_time, rows, round((total_time / sum(total_time) OVER ()) * 100, 2) AS pct FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;

SELECT datname, round(blks_hit::numeric / (blks_hit + blks_read) * 100, 2) AS hit_ratio_pct FROM pg_stat_database WHERE blks_hit + blks_read > 0 ORDER BY hit_ratio_pct;

SELECT schemaname, relname, pg_size_pretty(pg_total_relation_size(relid)) AS total_size, pg_size_pretty(pg_relation_size(relid)) AS table_size, pg_size_pretty(pg_indexes_size(relid)) AS index_size FROM pg_stat_all_tables WHERE schemaname NOT IN ('pg_catalog','pg_toast','information_schema') ORDER BY pg_total_relation_size(relid) DESC LIMIT 20;

SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_all_indexes WHERE schemaname NOT IN ('pg_catalog','pg_toast','information_schema') AND idx_scan = 0 ORDER BY pg_relation_size(indexrelid) DESC;

SELECT schemaname, relname, n_live_tup, n_dead_tup, round(n_dead_tup::numeric / NULLIF(n_live_tup,0) * 100, 2) AS dead_pct, last_vacuum, last_autovacuum FROM pg_stat_all_tables WHERE n_dead_tup > 0 AND schemaname NOT IN ('pg_catalog','pg_toast','information_schema') ORDER BY dead_pct DESC;

VACUUM ANALYZE <schema>.<table>;

-- 查看当前事务ID SELECT txid_current(); -- 查看活跃事务（长事务排查） SELECT pid, usename, xact_start, query, now() - xact_start AS xact_duration FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY xact_start;

SELECT pid, datname, usename, query, query_start FROM pg_stat_activity WHERE query LIKE '%autovacuum%' AND state = 'active';

SELECT datname, numbackends, xact_commit, xact_rollback, blks_read, blks_hit, tup_returned, tup_fetched, tup_inserted, tup_updated, tup_deleted, conflicts, temp_files, temp_bytes, deadlocks FROM pg_stat_database WHERE datname IS NOT NULL ORDER BY numbackends DESC;

SELECT schemaname, relname, seq_scan, seq_tup_read, idx_scan, idx_tup_fetch, CASE WHEN seq_scan > 0 THEN round(seq_tup_read::numeric / seq_scan, 0) ELSE 0 END AS avg_seq_tup FROM pg_stat_all_tables WHERE schemaname NOT IN ('pg_catalog','pg_toast','information_schema') AND seq_scan > 100 ORDER BY avg_seq_tup DESC LIMIT 20;

SELECT t1.pid AS blocked_pid, t1.usename AS blocked_user, t1.query AS blocked_query, t2.pid AS blocking_pid, t2.usename AS blocking_user, t2.query AS blocking_query, now() - t1.query_start AS blocked_duration FROM pg_stat_activity t1 JOIN pg_stat_activity t2 ON t2.pid = ANY (pg_blocking_pids(t1.pid)) WHERE t1.state = 'active';

SELECT locktype, relation::regclass, mode, granted, pid, transactionid, virtualtransaction FROM pg_locks ORDER BY granted, relation;

SELECT locktype, relation::regclass, mode, pid, transactionid, (SELECT query FROM pg_stat_activity WHERE pid = l.pid) AS query FROM pg_locks l WHERE granted = false;

SELECT waiting.locktype AS waiting_locktype, waiting.relation::regclass AS waiting_table, waiting.mode AS waiting_mode, waiting.pid AS waiting_pid, active.mode AS holding_mode, active.pid AS holding_pid, (SELECT query FROM pg_stat_activity WHERE pid = waiting.pid) AS waiting_query, (SELECT query FROM pg_stat_activity WHERE pid = active.pid) AS holding_query FROM pg_locks waiting JOIN pg_locks active ON waiting.relation = active.relation AND waiting.granted = false AND active.granted = true AND waiting.pid <> active.pid;

SELECT relation::regclass AS table_name, mode, granted, count(*) AS lock_count FROM pg_locks WHERE relation IS NOT NULL GROUP BY relation, mode, granted ORDER BY lock_count DESC;

-- 在数据库日志目录中搜索 deadlock grep -i "deadlock" $GAUSSLOG/pg_log/*.log | tail -50

SELECT datname, deadlocks FROM pg_stat_database WHERE deadlocks > 0;

SELECT datname, pg_size_pretty(pg_database_size(datname)) AS db_size FROM pg_database ORDER BY pg_database_size(datname) DESC;

SELECT pg_size_pretty(pg_total_relation_size('<schema>.<table>'));

SELECT spcname, pg_size_pretty(pg_tablespace_size(oid)) AS tspc_size FROM pg_tablespace ORDER BY pg_tablespace_size(oid) DESC;

-- 查看WAL日志文件大小 SELECT pg_size_pretty(sum(size)) AS wal_size FROM pg_ls_waldir();

SELECT name, size, modification FROM pg_ls_waldir() ORDER BY modification DESC;

SHOW wal_level; SHOW max_wal_size; SHOW min_wal_size; SHOW wal_keep_segments; SHOW checkpoint_segments; -- 旧版

SELECT pg_current_wal_lsn(); SELECT pg_current_wal_lsn(), pg_walfile_name(pg_current_wal_lsn());

df -h

du -sh <GAUSSDATA>/

du -sh $GAUSSLOG/*

-- 查找7天前的日志文件 find $GAUSSLOG/pg_log/ -name "*.log" -mtime +7 -ls -- 确认后删除（谨慎操作） find $GAUSSLOG/pg_log/ -name "*.log" -mtime +7 -delete

SELECT pid, usename, application_name, client_addr, state, sync_state, sync_priority, sent_lsn, write_lsn, flush_lsn, replay_lsn, now() - replay_lsn AS replication_lag FROM pg_stat_replication;

SELECT status, receive_start_lsn, receive_start_lsn, received_lsn, last_msg_send_time, last_msg_receipt_time FROM pg_stat_wal_receiver;

SELECT application_name, client_addr, state, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS lag_size FROM pg_stat_replication;

SHOW synchronous_commit; SHOW synchronous_standby_names;

-- 手动 switchover（主备互换） cm_ctl switchover -n <node_id> -D <data_dir> -- failover（主库故障强制切换） cm_ctl failover -n <node_id> -D <data_dir>

gs_om -t switch --primary

-- 查看归档是否开启 SHOW archive_mode; SHOW archive_command; -- 查看归档状态 SELECT * FROM pg_stat_archiver;

SELECT slot_name, plugin, slot_type, database, active, restart_lsn, xmin FROM pg_replication_slots;

gs_ctl build -D <standby_data_dir> -b full

-- 备库执行 SELECT pg_last_wal_receive_lsn() AS receive_lsn, pg_last_wal_replay_lsn() AS replay_lsn, pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()) AS lag_bytes;

SHOW <param_name>; -- 查看所有参数 SHOW ALL;

SELECT name, setting, unit, source, minval, maxval, boot_val, resetval FROM pg_settings WHERE name LIKE '%<keyword>%' ORDER BY name;

SET work_mem = '256MB';

ALTER SYSTEM SET <param_name> = <value>;

-- gsql 中执行 SELECT pg_reload_conf(); -- 操作系统层 gs_ctl reload -D <GAUSSDATA>/dn1

-- 内存相关 SHOW shared_buffers; SHOW work_mem; SHOW maintenance_work_mem; -- 连接相关 SHOW max_connections; -- WAL相关 SHOW wal_level; SHOW max_wal_size; SHOW wal_keep_segments; SHOW synchronous_commit; -- 超时相关 SHOW session_timeout; SHOW statement_timeout; SHOW lock_timeout; -- 日志相关 SHOW log_min_duration_statement; SHOW logging_collector; SHOW log_directory;

ALTER SYSTEM RESET <param_name>;

SELECT name, setting, context FROM pg_settings WHERE name = '<param_name>'; -- context = 'postmaster' 表示需要重启 -- context = 'sighup' 表示 reload 即可 -- context = 'user' / 'superuser' 表示即时生效

SHOW log_directory; SHOW data_directory;

-- 查看最近的错误 tail -100 $GAUSSLOG/pg_log/postgresql-$(date +%Y-%m-%d).log | grep -i error -- 查看所有日志中的 ERROR grep -n "ERROR" $GAUSSLOG/pg_log/*.log | tail -50

grep -n "FATAL\|PANIC" $GAUSSLOG/pg_log/*.log | tail -20

tail -f $GAUSSLOG/pg_log/postgresql-$(date +%Y-%m-%d).log

-- 查看今天14:00-15:00的日志 grep "2026-08-23 14:" $GAUSSLOG/pg_log/*.log | more -- 按关键字搜索 grep -i "<关键字>" $GAUSSLOG/pg_log/*.log

grep "duration:" $GAUSSLOG/pg_log/*.log | sort -t: -k3 -rn | head -20

grep -i "checkpoint" $GAUSSLOG/pg_log/*.log | tail -30

grep -i "connection\|disconnect\|timeout" $GAUSSLOG/pg_log/*.log | tail -30

-- CM server 日志 tail -100 $GAUSSHOME/log/cm/cms/cm_server.log -- CM agent 日志 tail -100 $GAUSSHOME/log/cm/agent/cm_agent.log -- 查看集群事件日志 grep -i "switchover\|failover\|restart" $GAUSSHOME/log/cm/cms/cm_server-*.log | tail -20

-- OM 操作日志 ls -lt $GAUSSHOME/log/om/ tail -100 $GAUSSHOME/log/om/gs_om-$(date +%Y-%m-%d).log

tail -100 <GAUSSDATA>/dn1/gs_ctl.log

du -sh <GAUSSDATA>/dn1/pg_xlog/ ls -lhS <GAUSSDATA>/dn1/pg_xlog/ | head -20

gs_dump -d <db_name> -t <schema>.<table> -f /backup/table.sql -p <port>

gs_dump -d <db_name> -f /backup/db_dump.sql -p <port>

-- 自定义格式（支持并行恢复） gs_dump -d <db_name> -Fc -f /backup/db.dump -p <port> -- 目录格式（支持并行） gs_dump -d <db_name> -Fd -f /backup/db_dir -p <port> -- 压缩级别（0-9） gs_dump -d <db_name> -Fc -Z6 -f /backup/db.dump -p <port>

gs_dump -d <db_name> -Fd -j 4 -f /backup/db_dir -p <port>

gsql -d <db_name> -p <port> -f /backup/db_dump.sql

-- 恢复到指定数据库 gs_restore -d <db_name> -p <port> /backup/db.dump -- 并行恢复 gs_restore -d <db_name> -p <port> -j 4 /backup/db.dump -- 仅恢复指定表 gs_restore -d <db_name> -p <port> -t <table> /backup/db.dump

-- 全库备份（含角色和表空间定义） gs_dumpall -f /backup/all.sql -p <port> -- 仅导出全局对象（角色、表空间） gs_dumpall -g -f /backup/globals.sql -p <port>

gs_loader -d <db_name> -p <port> -U <user> -W <password> \ -t <table_name> \ -f /path/to/data.csv \ -c /path/to/control.ctl

-- 1. 恢复基础备份到数据目录 gs_ctl restore -D <data_dir> -b full -- 2. 配置恢复目标时间 -- 在 postgresql.conf 或 recovery.conf 中设置 restore_command = 'cp /archive/%f %p' recovery_target_time = '2026-08-23 14:00:00' recovery_target_action = 'promote' -- 3. 启动恢复 gs_ctl start -D <data_dir> -M recovery

-- 查看正在执行的备份 SELECT pid, usename, query, query_start FROM pg_stat_activity WHERE query LIKE '%dump%' OR query LIKE '%backup%';

SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolcanlogin, rolconnlimit, rolexpire FROM pg_roles ORDER BY rolname;

CREATE USER <username> PASSWORD '<password>';

ALTER USER <username> PASSWORD '<new_password>';

-- 锁定 ALTER USER <username> ACCOUNT LOCK; -- 解锁 ALTER USER <username> ACCOUNT UNLOCK;

ALTER USER <username> CONNECTION LIMIT 10;

ALTER USER <username> VALID UNTIL '2026-12-31 23:59:59';

-- 授予库权限 GRANT ALL ON DATABASE <db_name> TO <username>; -- 授予schema权限 GRANT USAGE, CREATE ON SCHEMA <schema> TO <username>; -- 授予表权限 GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA <schema> TO <username>; -- 授予序列权限 GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA <schema> TO <username>;

-- 查看用户对表的权限 SELECT grantee, table_schema, table_name, privilege_type FROM information_schema.table_privileges WHERE grantee = '<username>' ORDER BY table_schema, table_name;

SELECT pid, usename, datname, client_addr, state, query_start FROM pg_stat_activity WHERE usename = '<username>';

-- 1. 检查数据库是否运行 gs_om -t status cm_ctl query -Cvdi -- 2. 检查端口是否监听 netstat -tlnp | grep <port> ss -tlnp | grep <port> -- 3. 检查防火墙 iptables -L -n | grep <port> firewall-cmd --list-ports -- 4. 检查最大连接数是否已满 gsql -d postgres -p <port> -c "SELECT count(*), max_connections FROM pg_stat_activity, (SELECT setting::int AS max_connections FROM pg_settings WHERE name='max_connections') t;" -- 5. 检查 pg_hba.conf 是否允许远程连接 cat <GAUSSDATA>/dn1/pg_hba.conf | grep -v "^#" | grep -v "^$"

-- 1. 查看连接数使用情况 SELECT count(*) AS total, state, usename FROM pg_stat_activity GROUP BY state, usename; -- 2. 杀掉空闲会话释放连接 SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle' AND pid <> pg_backend_pid() AND usename = '<app_user>'; -- 3. 临时调大 max_connections（需reload） ALTER SYSTEM SET max_connections = 500; SELECT pg_reload_conf();

-- 1. 检查磁盘空间 df -h -- 2. 检查数据目录大小 du -sh <GAUSSDATA>/dn1/ -- 3. 检查 WAL 日志堆积 du -sh <GAUSSDATA>/dn1/pg_xlog/ ls -lhS <GAUSSDATA>/dn1/pg_xlog/ | head -20 -- 4. 手动触发 checkpoint 回收 WAL CHECKPOINT; -- 5. 清理旧日志 find $GAUSSLOG/pg_log/ -name "*.log" -mtime +7 -delete -- 6. 检查是否有大临时文件 du -sh <GAUSSDATA>/dn1/pgsql_tmp/

-- 1. 查看消耗CPU的SQL SELECT pid, usename, datname, query, query_start, now() - query_start AS duration FROM pg_stat_activity WHERE state = 'active' ORDER BY duration DESC; -- 2. 查看消耗CPU的会话PID -- 在操作系统层执行 top -c -- 记下占用CPU高的 gaussdb/gsql 进程PID -- 3. 根据PID查看对应SQL SELECT pid, usename, datname, query FROM pg_stat_activity WHERE pid = <os_pid>; -- 4. 限制或取消该SQL SELECT pg_cancel_backend(<pid>);

-- 1. 主库查看复制延迟 SELECT application_name, client_addr, state, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS lag_size FROM pg_stat_replication; -- 2. 检查备库状态 -- 切换到备库执行 gs_om -t status --detail -- 3. 检查备库是否在恢复 SELECT pg_is_in_recovery(); SELECT status, received_lsn, last_msg_receipt_time FROM pg_stat_wal_receiver; -- 4. 检查网络带宽 ping <standby_ip> iperf3 -c <standby_ip> -- 需安装iperf3 -- 5. 如果延迟持续增大，考虑重建备库 gs_ctl build -D <standby_data_dir> -b incremental

-- 1. 尝试本地连接（排除网络问题） gsql -d postgres -p <port> -- 2. 如果能连上，检查锁等待 SELECT pid, query, now() - query_start AS duration FROM pg_stat_activity WHERE state = 'active'; -- 3. 检查是否有锁等待 SELECT * FROM pg_locks WHERE granted = false; -- 4. 检查磁盘IO是否打满（操作系统层） iostat -x 2 5 -- 5. 检查内存 free -m vmstat 2 5 -- 6. 如果完全无响应，查看日志 tail -200 $GAUSSLOG/pg_log/postgresql-$(date +%Y-%m-%d).log -- 7. 最后手段：强制重启（谨慎） gs_ctl restart -D <GAUSSDATA>/dn1 -M fast

-- 检查最新日志 tail -200 $GAUSSLOG/pg_log/postgresql-$(date +%Y-%m-%d).log -- 常见启动失败原因： -- 1. 端口被占用 → netstat -tlnp | grep <port> -- 2. 参数配置错误 → 检查 postgresql.conf -- 3. 数据目录权限问题 → chown -R <user>:<group> <GAUSSDATA> -- 4. 共享内存不足 → ipcs -m | grep <user> -- 5. WAL损坏 → 尝试 gs_ctl start -D <dir> -M immediate

-- 查看当前配置 cat <GAUSSDATA>/dn1/pg_hba.conf | grep -v "^#" | grep -v "^$" -- 添加允许远程访问的规则（追加到文件末尾） echo "host all all 0.0.0.0/0 sha256" >> <GAUSSDATA>/dn1/pg_hba.conf -- 重载配置 gs_ctl reload -D <GAUSSDATA>/dn1

pg_stat_activity -- 当前会话/连接信息 pg_stat_replication -- 主备复制状态 pg_stat_wal_receiver -- 备库WAL接收状态 pg_stat_archiver -- 归档进程状态

pg_locks -- 当前锁信息 pg_blocking_pids() -- 获取阻塞某会话的PID列表

pg_stat_database -- 数据库级统计（事务/缓存/死锁等） pg_stat_all_tables -- 表级统计（扫描/元组/VACUUM） pg_stat_all_indexes -- 索引使用统计 pg_stat_statements -- SQL执行统计（需扩展）

pg_database_size() -- 数据库大小 pg_relation_size() -- 表/索引大小 pg_total_relation_size() -- 表+索引+TOAST总大小 pg_tablespace_size() -- 表空间大小 pg_ls_waldir() -- WAL目录文件列表

pg_current_wal_lsn() -- 当前WAL LSN pg_walfile_name() -- LSN转WAL文件名 pg_wal_lsn_diff() -- 两个LSN的字节差 pg_last_wal_receive_lsn() -- 备库最后接收的LSN pg_last_wal_replay_lsn() -- 备库最后回放的LSN

pg_settings -- 所有GUC参数及来源 SHOW <param> -- 查看单个参数 pg_reload_conf() -- 重载配置文件