GaussDB 数据库管理命令

数据库管理命令

数据库管理命令用于创建、查看、修改和删除GaussDB数据库，是数据库管理员日常工作的基础。

创建数据库

创建数据库是使用GaussDB的第一步，您可以创建基本数据库，也可以根据需求指定所有者、编码、表空间等高级选项。

-- 创建基本数据库
CREATE DATABASE dbname;

-- 创建数据库并指定所有者、编码、表空间等高级选项
CREATE DATABASE dbname
  WITH OWNER = username          -- 指定数据库所有者
       ENCODING = 'UTF8'          -- 指定字符编码为UTF-8
       TABLESPACE = tsname        -- 指定默认表空间
       LC_COLLATE = 'zh_CN.UTF-8' -- 指定排序规则为中文
       LC_CTYPE = 'zh_CN.UTF-8'   -- 指定字符分类为中文
       CONNECTION LIMIT = 100;    -- 设置最大连接数限制

查看数据库

查看数据库信息是监控和管理GaussDB的重要操作，您可以查看数据库列表、详细信息、大小等。

-- 使用元命令查看所有数据库
\l

-- 使用元命令查看数据库详细信息，包括大小、所有者等
\l+

-- 使用SQL查询数据库基本信息
SELECT datname, datowner, datencoding FROM pg_database;

-- 查询指定数据库的大小，使用pg_size_pretty函数格式化输出
SELECT pg_size_pretty(pg_database_size('dbname'));

修改数据库

修改数据库命令用于更新数据库的属性，如所有者、名称、连接限制等。

-- 修改数据库所有者
ALTER DATABASE dbname OWNER TO new_username;

-- 修改数据库名称
ALTER DATABASE dbname RENAME TO new_dbname;

-- 修改数据库最大连接数限制
ALTER DATABASE dbname CONNECTION LIMIT = 200;

-- 修改数据库默认表空间
ALTER DATABASE dbname SET TABLESPACE new_tsname;

删除数据库

删除数据库命令用于移除不再需要的数据库，需谨慎使用，因为删除后数据无法恢复。

-- 删除数据库（需确保所有连接已断开）
DROP DATABASE dbname;

-- 强制删除数据库，即使有连接存在
DROP DATABASE IF EXISTS dbname WITH (FORCE);

用户和角色管理命令

用户和角色管理是GaussDB安全管理的核心，通过创建用户、角色并分配适当的权限，可以实现细粒度的访问控制。

创建用户/角色

GaussDB中，用户和角色的概念基本相同，用户是可以登录的角色。您可以创建基本用户/角色，也可以指定密码、有效期、权限等高级属性。

-- 创建基本用户
CREATE USER username WITH PASSWORD 'password';

-- 创建用户并指定多个高级属性
CREATE USER username
  WITH PASSWORD 'password'        -- 设置用户密码
       CREATEDB                   -- 允许创建数据库
       CREATEROLE                 -- 允许创建角色
       INHERIT                    -- 允许继承角色权限
       LOGIN                      -- 允许登录
       CONNECTION LIMIT 50        -- 设置最大连接数
       VALID UNTIL '2025-12-31';  -- 设置有效期

-- 创建基本角色（默认不可登录）
CREATE ROLE rolename;

-- 创建带有密码的角色
CREATE ROLE rolename WITH PASSWORD 'password';

查看用户/角色

查看用户和角色信息是管理GaussDB安全的重要操作，您可以查看所有用户/角色、详细信息、权限等。

-- 使用元命令查看所有角色
\du

-- 使用元命令查看角色详细信息，包括权限、属性等
\du+

-- 使用SQL查询角色基本信息
SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin
FROM pg_roles;

-- 查看用户所属角色
SELECT * FROM pg_user;

修改用户/角色

修改用户/角色命令用于更新用户/角色的属性，如密码、连接限制、权限等。

-- 修改用户密码
ALTER USER username WITH PASSWORD 'new_password';

-- 修改用户最大连接数限制
ALTER USER username CONNECTION LIMIT 100;

-- 允许用户创建数据库
ALTER USER username CREATEDB;

-- 禁止用户登录（将用户转换为角色）
ALTER USER username NOLOGIN;

-- 修改角色名称
ALTER ROLE rolename RENAME TO new_rolename;

删除用户/角色

删除用户/角色命令用于移除不再需要的用户或角色，需确保没有对象依赖该用户/角色。

-- 删除用户
DROP USER username;

-- 删除角色
DROP ROLE rolename;

-- 删除用户或角色（如果存在，避免错误）
DROP USER IF EXISTS username;

角色权限管理

角色权限管理用于将角色的权限授予用户，或从用户撤销角色权限，实现权限的继承和管理。

-- 将角色权限授予用户，用户将继承该角色的所有权限
GRANT rolename TO username;

-- 从用户撤销角色权限
REVOKE rolename FROM username;

-- 查看用户拥有的角色
SELECT * FROM pg_user_mappings WHERE usename = 'username';

权限管理命令

授予权限

-- 授予数据库权限
GRANT ALL PRIVILEGES ON DATABASE dbname TO username;

-- 授予表权限
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE tablename TO username;

-- 授予表的所有权限
GRANT ALL PRIVILEGES ON TABLE tablename TO username;

-- 授予模式权限
GRANT USAGE ON SCHEMA schemaname TO username;
GRANT CREATE ON SCHEMA schemaname TO username;

-- 授予序列权限
GRANT USAGE, SELECT ON SEQUENCE sequencename TO username;

-- 授予函数权限
GRANT EXECUTE ON FUNCTION funcname TO username;

撤销权限

-- 撤销数据库权限
REVOKE ALL PRIVILEGES ON DATABASE dbname FROM username;

-- 撤销表权限
REVOKE UPDATE, DELETE ON TABLE tablename FROM username;

-- 撤销表的所有权限
REVOKE ALL PRIVILEGES ON TABLE tablename FROM username;

-- 撤销模式权限
REVOKE CREATE ON SCHEMA schemaname FROM username;

查看权限

-- 查看数据库权限
\l+ dbname

-- 查看表权限
\dp tablename

-- 查看用户在表上的权限
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name = 'tablename';

-- 查看函数权限
\df+ funcname

表空间管理命令

创建表空间

-- 创建表空间
CREATE TABLESPACE tsname
  OWNER username
  LOCATION '/path/to/tablespace';

-- 创建表空间并指定大小限制
CREATE TABLESPACE tsname
  OWNER username
  LOCATION '/path/to/tablespace'
  WITH (MAX_SIZE = '100GB');

查看表空间

-- 查看所有表空间
\db

-- 查看表空间详细信息
\db+

-- 使用SQL查询表空间信息
SELECT spcname, spcowner, spclocation FROM pg_tablespace;

-- 查看表空间大小
SELECT pg_size_pretty(pg_tablespace_size('tsname'));

修改表空间

-- 修改表空间所有者
ALTER TABLESPACE tsname OWNER TO new_username;

-- 修改表空间名称
ALTER TABLESPACE tsname RENAME TO new_tsname;

-- 设置表空间只读
ALTER TABLESPACE tsname READ ONLY;

-- 设置表空间可写
ALTER TABLESPACE tsname READ WRITE;

删除表空间

-- 删除表空间（需确保无对象使用）
DROP TABLESPACE tsname;

-- 删除表空间（如果存在）
DROP TABLESPACE IF EXISTS tsname;

表管理命令

创建表

-- 创建基本表
CREATE TABLE tablename (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT CHECK (age > 0),
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建带有表空间的表
CREATE TABLE tablename (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL
)
TABLESPACE tsname;

-- 创建临时表
CREATE TEMPORARY TABLE temp_tablename (
    id INT,
    name VARCHAR(50)
);

查看表

-- 查看当前数据库中的表
\dt

-- 查看表详细信息
\dt+

-- 查看指定表结构
\d tablename

-- 查看表的详细结构（包括索引、约束等）
\d+ tablename

-- 查看表大小
SELECT pg_size_pretty(pg_total_relation_size('tablename'));

-- 查看表数据大小
SELECT pg_size_pretty(pg_table_size('tablename'));

-- 查看表索引大小
SELECT pg_size_pretty(pg_indexes_size('tablename'));

修改表

-- 添加列
ALTER TABLE tablename ADD COLUMN new_column VARCHAR(50);

-- 添加列并设置默认值
ALTER TABLE tablename ADD COLUMN status INT DEFAULT 1;

-- 修改列类型
ALTER TABLE tablename ALTER COLUMN columnname TYPE VARCHAR(100);

-- 修改列默认值
ALTER TABLE tablename ALTER COLUMN columnname SET DEFAULT 'default_value';

-- 重命名列
ALTER TABLE tablename RENAME COLUMN old_column TO new_column;

-- 删除列
ALTER TABLE tablename DROP COLUMN columnname;

-- 重命名表
ALTER TABLE tablename RENAME TO new_tablename;

-- 修改表所有者
ALTER TABLE tablename OWNER TO new_username;

-- 修改表空间
ALTER TABLE tablename SET TABLESPACE new_tsname;

删除表

-- 删除表
DROP TABLE tablename;

-- 删除表及依赖对象
DROP TABLE tablename CASCADE;

-- 删除表（如果存在）
DROP TABLE IF EXISTS tablename;

索引管理命令

创建索引

-- 创建普通索引
CREATE INDEX idx_tablename_columnname ON tablename(columnname);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_tablename_unique_column ON tablename(unique_column);

-- 创建复合索引
CREATE INDEX idx_tablename_col1_col2 ON tablename(column1, column2);

-- 创建部分索引
CREATE INDEX idx_tablename_active ON tablename(columnname) WHERE status = 1;

-- 创建表达式索引
CREATE INDEX idx_tablename_lower_name ON tablename(LOWER(name));

-- 创建覆盖索引
CREATE INDEX idx_tablename_covering ON tablename(column1) INCLUDE (column2, column3);

查看索引

-- 查看表的索引
\di

-- 查看索引详细信息
\di+

-- 查看指定表的索引
\d tablename

-- 使用SQL查询索引信息
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'tablename';

修改索引

-- 重命名索引
ALTER INDEX idx_old_name RENAME TO idx_new_name;

-- 设置索引表空间
ALTER INDEX idx_name SET TABLESPACE tsname;

-- 重建索引
REINDEX INDEX idx_name;

-- 重建表的所有索引
REINDEX TABLE tablename;

删除索引

-- 删除索引
DROP INDEX idx_name;

-- 删除索引（如果存在）
DROP INDEX IF EXISTS idx_name;

-- 强制删除索引
DROP INDEX idx_name CASCADE;

视图管理命令

创建视图

-- 创建视图
CREATE VIEW viewname AS
SELECT id, name, age
FROM tablename
WHERE status = 1;

-- 创建物化视图
CREATE MATERIALIZED VIEW mvname AS
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

-- 刷新物化视图
REFRESH MATERIALIZED VIEW mvname;

查看视图

-- 查看所有视图
\dv

-- 查看视图详细信息
\dv+

-- 查看视图定义
\d+ viewname

-- 使用SQL查询视图定义
SELECT definition FROM pg_views WHERE viewname = 'viewname';

修改视图

-- 修改视图定义
CREATE OR REPLACE VIEW viewname AS
SELECT id, name, age, email
FROM tablename
WHERE status = 1;

-- 重命名视图
ALTER VIEW viewname RENAME TO new_viewname;

-- 修改视图所有者
ALTER VIEW viewname OWNER TO new_username;

删除视图

-- 删除视图
DROP VIEW viewname;

-- 删除物化视图
DROP MATERIALIZED VIEW mvname;

-- 删除视图（如果存在）
DROP VIEW IF EXISTS viewname;

函数和存储过程管理命令

创建函数

-- 创建PL/pgSQL函数
CREATE OR REPLACE FUNCTION funcname(param1 INT, param2 VARCHAR(50))
RETURNS INT
LANGUAGE plpgsql
AS $$
DECLARE
    result INT;
BEGIN
    -- 函数逻辑
    SELECT COUNT(*) INTO result
    FROM tablename
    WHERE id = param1 AND name = param2;
    
    RETURN result;
END;
$$;

-- 创建SQL函数
CREATE OR REPLACE FUNCTION funcname(param INT)
RETURNS TABLE (id INT, name VARCHAR(50))
AS $$
    SELECT id, name FROM tablename WHERE id > $1;
$$ LANGUAGE sql;

查看函数

-- 查看所有函数
\df

-- 查看函数详细信息
\df+

-- 查看特定函数
\df funcname

-- 使用SQL查询函数信息
SELECT proname, proargtypes, prorettype FROM pg_proc WHERE proname = 'funcname';

修改函数

-- 修改函数定义（使用CREATE OR REPLACE）
CREATE OR REPLACE FUNCTION funcname(param INT)
RETURNS INT
LANGUAGE plpgsql
AS $$
-- 新的函数逻辑
BEGIN
    RETURN param * 2;
END;
$$;

-- 修改函数所有者
ALTER FUNCTION funcname(INT) OWNER TO new_username;

-- 修改函数语言
ALTER FUNCTION funcname(INT) LANGUAGE plpython3u;

删除函数

-- 删除函数
DROP FUNCTION funcname(INT);

-- 删除函数（如果存在）
DROP FUNCTION IF EXISTS funcname(INT);

-- 删除函数及依赖对象
DROP FUNCTION funcname(INT) CASCADE;

事务管理命令

事务控制

-- 开始事务
BEGIN;
-- 或
START TRANSACTION;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

-- 保存点
SAVEPOINT savepoint_name;

-- 回滚到保存点
ROLLBACK TO savepoint_name;

-- 释放保存点
RELEASE SAVEPOINT savepoint_name;

设置事务隔离级别

-- 设置事务隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 或
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- 查看当前隔离级别
SHOW TRANSACTION ISOLATION LEVEL;

系统管理命令

查看系统信息

-- 查看数据库版本
SELECT version();

-- 查看当前用户
SELECT current_user;

-- 查看当前数据库
SELECT current_database();

-- 查看当前时间
SELECT now();

查看和修改参数

-- 查看所有参数
SHOW ALL;

-- 查看特定参数
SHOW parameter_name;

-- 使用SQL查询参数
SELECT name, setting, unit FROM pg_settings WHERE name = 'shared_buffers';

-- 修改会话级参数
SET parameter_name = value;

-- 修改系统级参数（需要重启）
ALTER SYSTEM SET parameter_name = value;

-- 重置参数
RESET parameter_name;

-- 重新加载配置
SELECT pg_reload_conf();

统计信息管理

-- 更新所有表的统计信息
ANALYZE;

-- 更新特定表的统计信息
ANALYZE tablename;

-- 更新表特定列的统计信息
ANALYZE tablename(column1, column2);

-- 更新统计信息并显示进度
ANALYZE VERBOSE tablename;

-- 查看统计信息
SELECT * FROM pg_stats WHERE tablename = 'tablename';

维护命令

-- 清理无用数据（VACUUM）
VACUUM;

-- 清理并分析
VACUUM ANALYZE;

-- 完全清理（需要排他锁）
VACUUM FULL tablename;

-- 重建索引
REINDEX TABLE tablename;

-- 检查数据库完整性
CHECKPOINT;

常用 psql 元命令

连接和断开

-- 连接到数据库
\c dbname username host port

-- 断开连接
\q

查看帮助

-- 查看所有元命令
\?

-- 查看SQL命令帮助
\h

-- 查看特定SQL命令帮助
\h CREATE TABLE

格式化输出

-- 扩展显示模式
\x

-- 设置输出格式
\pset format aligned
\pset border 2

-- 设置列宽
\pset columns 120

-- 显示执行时间
\timing

文件操作

-- 执行SQL文件
\i filename.sql

-- 将查询结果输出到文件
\o filename.txt

-- 恢复标准输出
\o

常见问题（FAQ）

Q1: 如何快速查看数据库中的所有表？

A1: 使用 \dt 命令查看所有表，使用 \dt+ 查看表的详细信息，包括大小、所有者等。

Q2: 如何查看表的索引信息？

A2: 使用 \d tablename 命令可以查看表的结构，包括索引信息。也可以使用 \di 查看所有索引，或使用 SELECT * FROM pg_indexes WHERE tablename = 'tablename'; 查询索引详细信息。

Q3: 如何创建一个带有自增主键的表？

A3: 使用 SERIAL 或 BIGSERIAL 类型创建自增主键：

CREATE TABLE tablename (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50)
);

Q4: 如何修改表的列类型？

A4: 使用 ALTER TABLE 命令修改列类型：

ALTER TABLE tablename ALTER COLUMN columnname TYPE new_type;

注意：修改列类型可能会导致数据转换，建议在低峰期执行，并确保有备份。

Q5: 如何查看数据库的连接数？

A5: 使用以下SQL查询数据库连接数：

SELECT count(*) FROM pg_stat_activity WHERE datname = 'dbname';

Q6: 如何终止长时间运行的查询？

A6: 首先查找查询的进程ID，然后终止：

-- 查找长时间运行的查询
SELECT pid, query_start, query FROM pg_stat_activity WHERE state = 'active' ORDER BY query_start;

-- 终止查询
SELECT pg_terminate_backend(pid);

Q7: 如何优化查询性能？

A7: 优化查询性能的方法：

1. 为查询条件添加合适的索引
2. 优化查询语句，避免全表扫描
3. 更新统计信息
4. 调整数据库参数
5. 考虑表分区

Q8: 如何备份和恢复数据库？

A8: 使用 gs_basebackup 进行物理备份，使用 gs_restore 进行恢复：

# 备份数据库
gs_basebackup -D /backup -F t -X stream -v

# 恢复数据库
gs_restore -d dbname backup_file.tar

Q9: 如何查看慢查询日志？

A9: 首先确保慢查询日志已开启，然后查看日志文件：

-- 查看慢查询日志配置
SHOW log_min_duration_statement;

-- 设置慢查询日志阈值（毫秒）
ALTER SYSTEM SET log_min_duration_statement = 1000;

慢查询日志默认位于数据目录的 log 子目录中。

Q10: 如何监控数据库性能？

A10: 监控数据库性能的方法：

1. 使用内置视图如 pg_stat_activity、pg_stat_database 等
2. 使用系统工具如 top、iostat、vmstat 等
3. 部署第三方监控系统如 Prometheus + Grafana
4. 使用 GaussDB 内置的监控工具