暂无图片
暂无图片
1
暂无图片
暂无图片
暂无图片
首页 / 磐维数据库运维合集（一）：日常运维相关命令

磐维数据库运维合集（一）：日常运维相关命令

原创 钟一 2025-04-02
1237

1、常用的元命令

\copyright 显示版权信息
\conninfo 显示当前登录信息
\l	列出数据库信息
\l+ 列出数据库详细信息
\db 列出表空间信息
\di 列出索引
\dx 列出插件
\df 列出函数
\sf [function_name] 查看函数结构
\dv 列出view
\c [DBNAME] [-U USERNAME] [-W]: 连接到一个新的数据库。可以指定数据库名、用户名，并且可以选择是否需要密码。
\dn	列出所有的模式（schemas）。
\d	显示所有表、视图和序列的列表。类似于 SHOW TABLES 在 MySQL 中的作用。
\d [TABLE]: 显示表的结构，包括列、数据类型等。
\dt	列出所有可见的表。
\dt [TABLE] 列出表的详细信息（属主）
\dv: 列出所有可见的视图。
\du: 列出所有角色。
\q: 退出 psql 界面。
\h [COMMAND]: 提供 SQL 命令的帮助信息。
?: 显示 psql 的内部命令列表。
\x: 切换扩展显示模式，这对于查看宽表特别有用。
\a 用于切换对齐方式
\timing: 开启或关闭每条命令的执行时间统计。
\set VARIABLE value: 设置会话级别的配置参数。
\unset VARIABLE: 取消设置会话级别的配置参数。
\echo TEXT: 输出提供的文本。
\i FILE: 执行从文件读取的 SQL 命令。
! COMMAND: 执行外部 shell 命令。
\g [FILE]: 将查询结果发送到文件或标准输出。
\o [FILE]: 将后续查询的结果输出到文件。
\pset FORMAT [unaligned|aligned|wrapped|html|latex|troff-ms]: 设置查询结果的格式。
\s [STRING]: 显示或设置当前的搜索路径。
设置边框级别：\pset border 0 (无边框), \pset border 1 (默认), \pset border 2 (双线边框)
## 注：元命令后加上+号表示查看描述

2、查看数据大小

查看数据库大小

select datname,pg_size_pretty(pg_database_size(datname)) as dbsize from pg_database order by 2 desc;

or

SELECT 
    datname AS "Database Name",
    (pg_database_size(oid) / 1024 / 1024) AS "Size in MB"
FROM 
    pg_database
WHERE 
    datname NOT IN ('template0', 'template1', 'postgres') -- 排除系统数据库，可根据需要调整
ORDER BY 
    "Size in MB" DESC;

查看schema大小

SELECT
    nspname AS schemaname,
    pg_size_pretty(SUM(pg_total_relation_size(pg_class.oid))) AS total_size
FROM
    pg_class
JOIN
    pg_namespace ON pg_namespace.oid = pg_class.relnamespace
WHERE
    (relkind = 'r' OR relkind = 'i')  -- 'r' for tables, 'i' for indexes
GROUP BY
    nspname
order by 
	total_size;

查看表大小

-- 只查看表本身大小，不包括索引
select tableowner,schemaname,tablename,pg_size_pretty(pg_table_size(schemaname||'.'||tablename)) as table_size from pg_tables order by pg_table_size(schemaname||'.'||tablename) desc;

-- 查看表的总大小，包括索引、TOAST数据以及主表数据
SELECT
    schemaname AS "user",
	relname AS "Table",
    pg_size_pretty(pg_total_relation_size(relid)) AS "Size"
FROM
    pg_catalog.pg_statio_user_tables
ORDER BY
    pg_total_relation_size(relid) DESC;

-- 只查看索引大小
SELECT 
    schemaname AS schema_name,
    tablename AS table_name,
    indexname AS index_name,
    pg_size_pretty(pg_relation_size(C.oid)) AS index_size
FROM 
    pg_indexes 
JOIN 
    pg_class C ON C.relname = indexname
JOIN 
    pg_namespace N ON N.oid = C.relnamespace
ORDER BY 
    pg_relation_size(C.oid) DESC;

3、会话操作

--查看当前会话的 pid
SELECT pg_backend_pid();

--先查会话的pid
postgres=# select datid,pid,state,query from pg_stat_activity where state like '%active%';
 datid |      pid       | state  |                                      query                                      
-------+----------------+--------+-------------------------------------------------------
 20695 | 23413936617216 | active | 
 20695 | 23412700346112 | active | WLM fetch collect info from data nodes
 20695 | 23413654025984 | active | 
 20695 | 23412217403136 | active | SELECT pg_sleep(3600);
 20695 | 23412250965760 | active | select datid,pid,state,query from pg_stat_activity where state like '%active%';

(5 行记录)

--基于上一步查到的pid杀会话
postgres=# select * from pg_terminate_backend(23412217403136);
 pg_terminate_backend 
----------------------
 t
(1 row)
--再次查询，会话已消失
postgres=# select datid,pid,state,query from pg_stat_activity where state like '%active%';
 datid |      pid       | state  |                                      query                                      
-------+----------------+--------+-------------------------------------------------------
 20695 | 23413936617216 | active | 
 20695 | 23412700346112 | active | WLM fetch collect info from data nodes
 20695 | 23413654025984 | active | 
 20695 | 23412250965760 | active | select datid,pid,state,query from pg_stat_activity where state like '%active%';
(4 行记录)

设置查找路径

--查看当前查找路径：
postgres=# show search_path;
--设置数据库级别的查找路径：
postgres=# alter database database_name set search_path = "$user",public,schema_name;
--设置用户级别的查找路径
postgres=# alter user usern_ame set search_path = schema_name;

查看连接数

  1. 连接数情况

    select (select setting as "最大连接数" from   pg_settings where name='max_connections'),(select count(*)::text as "当前连接数" from   pg_stat_activity),(select count(*)::text as "活跃连接数" from   pg_stat_activity where state <> 'idle'),(select (select count(*)::int as  "当前连接数" from pg_stat_activity)/(select setting::int from   pg_settings where name='max_connections')::float8 * 100 as "当前连接数/最大连接数(%)");

  2. 连接的IP分布情况

    -- 全部连接
select datname,usename,client_addr,application_name,state,count(*) from pg_stat_activity group by datname,usename,application_name,client_addr,state order by datname,count(*);

-- 活跃连接
select datname,usename,client_addr,application_name,state,count(*) from pg_stat_activity where state='active' group by datname,usename,application_name,client_addr,state order by datname;

-- 带pid(需要查杀时)
select pid,datname,usename,client_addr,application_name,state,backend_start from pg_stat_activity order by datname,client_addr;

-- 带query
select pid,datname,usename,client_addr,application_name,state,backend_start,query from pg_stat_activity order by datname,client_addr;

  3. 条件查询

    -- ip 
select pid,datname,usename,client_addr,application_name,backend_start,state,query from pg_stat_activity where client_addr ='10.183.161.14' order by client_addr,backend_start;

-- application_name
select pid,datname,usename,client_addr,application_name,state,backend_start,query from pg_stat_activity where application_name='gsql' order by datname,client_addr;

-- user
select count(*),client_addr, datname,usename from pg_stat_activity where usename='secp' group by client_addr, datname,usename order by usename;

-- time
SELECT *
FROM pg_stat_activity
WHERE xact_start >= '2024-10-14 00:00:00'
  AND xact_start < '2024-10-16 00:00:00'
  AND state != 'idle'
  AND usename != 'omm';
  
SELECT *
FROM pg_stat_activity
WHERE xact_start BETWEEN '2024-10-14 00:00:00' AND '2024-10-15 23:59:59'
  AND state != 'idle'
  AND usename != 'omm';

  4. 连接信息

    select pid,datname,usename,client_addr,application_name,backend_start,state from pg_stat_activity order by datname,backend_start;

查看会话使用的内存

  1. 查看数据库内存使用情况

    select * from pg_total_memory_detail;

  2. 查看活动会话内存使用情况

    WITH session_memory AS (
    SELECT 
        sessid,
        SUM(totalsize) AS totalsize, 
        SUM(usedsize) AS usedsize
    FROM 
        dbe_perf.session_memory_detail
    GROUP BY 
        sessid
)
SELECT 
    sa.sessionid,
    sa.datname,
    sa.usename,
    sa.application_name,
    sa.client_addr,
    pg_size_pretty(sm.totalsize) AS total_memory,
    pg_size_pretty(sm.usedsize) AS used_memory,
    sa.query
FROM 
    pg_stat_activity sa
JOIN 
    session_memory sm
ON 
    split_part(sm.sessid, '.', '2') = sa.sessionid
WHERE 
    sa.datname NOT IN ('postgres', 'template0', 'template1')  -- 排除系统数据库
    AND sa.state <> 'idle'  -- 排除空闲会话
ORDER BY 
    sm.usedsize DESC
LIMIT 20;  -- 限制输出前 20 条记录

  3. 查询每个会话占用内存量

    with t1 as (select sessid, sum(totalsize) totalsize, sum(usedsize) usedsize from gs_session_memory_detail group by 1) select a.sessionid, a.datname, a.usename, a.application_name, a.client_addr, pg_size_pretty(totalsize) total_size, pg_size_pretty(usedsize) used_size, a.query from pg_stat_activity a, t1 where a.sessionid = split_part(sessid,'.',2) order by usedsize desc;

  4. 查询每个会话占用内存详情

    select pg_size_pretty(totalsize) totalsize,pg_size_pretty(usedsize) usedsize,datname,usename, contextname ,query from dbe_perf.session_memory_detail smd,pg_stat_activity sa where split_part(smd.sessid,'.','2')=sa.sessionid order by usedsize desc limit 10;

查杀阻塞

  1. 通过pg_locks视图查看检查阻塞情况

    select bl.pid as blocked_pid, a.usename as blocked_user, kl.pid as blocking_pid, ka.usename as blocking_user, a.query as blocked_statement from pg_locks bl join pg_stat_activity a on a.pid = bl.pid join pg_locks kl on kl.transactionid = bl.transactionid and kl.pid != bl.pid join pg_stat_activity ka on ka.pid = kl.pid where not bl.granted;
-- 解析：
SELECT 
    bl.pid AS blocked_pid,              -- 被阻塞会话的进程 ID
    a.usename AS blocked_user,          -- 被阻塞会话的用户名
    kl.pid AS blocking_pid,             -- 阻塞会话的进程 ID
    ka.usename AS blocking_user,        -- 阻塞会话的用户名
    a.query AS blocked_statement        -- 被阻塞会话正在执行的 SQL 语句
FROM 
    pg_locks bl                          -- 被阻塞锁信息
JOIN 
    pg_stat_activity a                   -- 被阻塞会话信息
    ON a.pid = bl.pid                    -- 关联被阻塞会话的进程 ID
JOIN 
    pg_locks kl                           -- 阻塞锁信息
    ON kl.transactionid = bl.transactionid -- 匹配事务 ID
    AND kl.pid != bl.pid                  -- 确保是不同会话
JOIN 
    pg_stat_activity ka                   -- 阻塞会话信息
    ON ka.pid = kl.pid                    -- 关联阻塞会话的进程 ID
WHERE 
    NOT bl.granted;                       -- 锁未被授予（表示阻塞）
    
-- 查看源头信息
select pid,datname,usename,client_addr,application_name,state,backend_start,query from pg_stat_activity where pid = blocking_pid order by datname,client_addr;

  2. 通过pg_thread_wait_status视图查看阻塞情况

    select 
    a.sessionid, 
    block_sessionid,
    thread_name,
    tid,
    lwtid,
    wait_event,
    lockmode,
    wait_status,
    hold.query hold_sql,
    hold.xact_start hold_starttime,
    hold.state hold_state,
    wait.query wait_sql,
    wait.xact_start wait_starttime
from pg_thread_wait_status a
join pg_stat_activity wait on a.sessionid = wait.pid
join pg_stat_activity hold on block_sessionid = hold.pid
where block_sessionid is not null
order by hold_starttime;
## 其中：block_sessionid为阻塞会话的源头

  3. 查杀会话

    -- 查杀blocking_pid/block_sessionid即可解除阻塞
SELECT pg_terminate_backend(<pid>);
SELECT pg_cancel_backend(<pid>);

锁检查

  1. 检查锁等待类型

    SELECT
    l.relation::regclass AS table_name,
    l.locktype,
    l.mode AS lock_mode,
    l.granted,
    a.datname AS dbname,
    a.usename AS username,
    a.pid,
    a.query
FROM
    pg_locks l
JOIN
    pg_stat_activity a ON l.pid = a.pid
WHERE
    l.relation IS NOT NULL and dbname!='postgres' and username !='omm'

  2. 检查行级锁或事务锁争用

    SELECT
    l.locktype,
    a.datname AS dbname,
    l.relation::regclass AS table_name,
    l.page,
    l.tuple,
    l.transactionid,
    l.pid,
    l.mode,
    l.granted
FROM
    pg_locks l
JOIN
    pg_stat_activity a ON l.pid = a.pid
WHERE
    l.tuple IS NOT NULL;

  3. 检查表级锁

    SELECT
l.relation::regclass AS table_name,
l.locktype,
l.mode AS lock_mode,
l.granted,
a.datname AS dbname,
a.usename AS username,
a.pid
FROM
	pg_locks l
JOIN
	pg_stat_activity a ON l.pid = a.pid
WHERE
	l.relation IS NOT NULL and dbname!='postgres' and username !='omm';

查杀会话

  1. 单独查杀会话

    -- 终止一个后台线程。
select pg_terminate_backend(pid);

-- 取消一个后端的当前查询。
select pg_cancel_backend(pid);

-- 终止一个后台session。
select pg_terminate_session(23214765438720,23214765438720);

-- A模式特有
SELECT sa.sessionid AS sid,0::integer AS serial#,ad.rolname AS username FROM pg_stat_get_activity(NULL) AS sa
LEFT JOIN pg_authid ad ON(sa.usesysid = ad.oid)WHERE sa.application_name <> 'JobScheduler';

ALTER SYSTEM KILL SESSION '23214765438720,0' IMMEDIATE;

  2. 按用户名批量查杀会话

    DO $$
DECLARE
  rec RECORD;
BEGIN
  FOR rec IN SELECT pid FROM pg_stat_activity WHERE usename = 'user_name' AND pid <> pg_backend_pid()
  LOOP
    PERFORM pg_terminate_backend(rec.pid);
  END LOOP;
END $$;

慢sql

通过以下三个视图相关sql查询

DBE_PERF.statement_history

-- 查看某个时间之后超过60s的sql
SELECT db_name, schema_name, start_time, finish_time, query, finish_time - start_time AS total_time
FROM DBE_PERF.statement_history
WHERE start_time >= '2025-03-26 15:50:12'
  AND (finish_time - start_time) > interval '60 seconds';


-- 按时间段查询超过10S的sql(在postgresql下执行)
SELECT db_name, schema_name, start_time, finish_time, query, finish_time - start_time AS total_time
FROM DBE_PERF.statement_history
WHERE start_time >= '2025-03-26 15:50:12'
  AND start_time < '2025-03-26 16:02:12'
  AND (finish_time - start_time) > interval '10 seconds';

or

SELECT db_name, schema_name, start_time, finish_time, query, finish_time - start_time AS total_time
FROM DBE_PERF.statement_history
WHERE start_time BETWEEN '2025-03-26 15:50:12' AND '2025-03-26 16:02:12'
  AND (finish_time - start_time) > interval '10 seconds';
  
-- 统计某个时间以后的慢sql条数
SELECT count(*)
	FROM DBE_PERF.statement_history
WHERE 
	start_time >= '2025-03-26 15:50:12'
AND
  (finish_time - start_time) > interval '10 seconds';

pg_stat_statements

-- 查看当前执行超过10s的会话
select pid,client_addr, datname,usename,backend_start, xact_start,query_start, state,query from pg_stat_activity where   current_timestamp - xact_start > interval '10s' and usename!='omm';


-- 查看当前超过10s的**活动会话**详情,xact_duration（事务持续时间），query_duration（查询持续时间）
select * from (select pid,datname,usename,query,xact_start,now() - xact_start as xact_duration,query_start,now() - query_start as query_duration,state from pg_stat_activity where state = 'active' and now()-xact_start > interval '10 SECOND'order by xact_start);


-- 提取当前活动会话的相关信息(只选择有查询开始时间的会话)
SELECT 
    datname,                                -- 数据库名称
    usename,                                -- 用户名
    client_addr,                            -- 客户端 IP 地址
    pid,                                    -- 进程 ID
    query_start::text,                      -- 查询开始时间 (以文本形式显示)
    EXTRACT(EPOCH FROM (now() - query_start)) AS query_runtime,  -- 查询运行时间 (秒)
    xact_start::text,                      -- 事务开始时间 (以文本形式显示)
    EXTRACT(EPOCH FROM (now() - xact_start)) AS xact_runtime,   -- 事务运行时间 (秒)
    state,                                  -- 当前会话状态
    query                                   -- 当前正在执行的查询
FROM pg_stat_activity                      -- PostgreSQL 系统视图，用于监控活动会话
WHERE 
    state NOT IN ('idle')                  -- 排除空闲状态的会话
    AND query_start IS NOT NULL           -- 只选择有查询开始时间的会话
ORDER BY query_runtime DESC;

-- 统计某个用户执行sql的情况：包括累计执行时间、被调用次次数，返回了多少行数据。
SELECT usename,usesysid FROM pg_user;

SELECT query,
       total_time / 1000.0 AS total_time_seconds,
       calls,
       rows
FROM pg_stat_statements
WHERE userid = <usesysid> and total_time > 20 * 1000;

dbe_perf.statement

-- 统计某个用户执行sql的情况：包括累计执行时间、被调用次次数，返回了多少行数据。
SELECT 
    query, 
    total_elapse_time / 1000.0 AS total_time_seconds, 
    n_calls, 
    n_returned_rows 
FROM 
    dbe_perf.statement 
WHERE 
    total_elapse_time > 20 * 1000 
ORDER BY 
    total_time_seconds DESC;

-- 查看某个时间点后的sql执行历史
select * from dbe_perf.statement where last_updated like '2024-07-19 22:01%' order by execution_time desc limit 5;

查看占用CPU高的SQL

SELECT 
    s.datname AS database_name,
    s.usename AS user_name,
    s.application_name,
    s.client_addr AS client_address,
    s.backend_start,
    s.state,
    s.query,
    c.min_cpu_time,
    c.max_cpu_time,
    c.total_cpu_time
FROM 
    pg_stat_activity s
JOIN 
    gs_session_cpu_statistics c ON s.pid = c.pid
ORDER BY 
    c.total_cpu_time DESC;

4、对象操作

4.1 数据库操作

wdr报告

  1. 执行以下命令连接postgres数据库（wdr只能在postgres库下生成）

    gsql -d postgres -p 端口号 -r

  2. 执行如下命令查询已经生成的快照，以获取快照的snapshot_id。

    select * from snapshot.snapshot;

  3. 没开启时可开启

    gs_guc reload -N all -D <datadir> -c " enable_wdr_snapshot=on"  --集群
gs_guc reload -D <datadir> -c " enable_wdr_snapshot=on" --单机

  4. （可选）可在单机节点或集群主节点上执行如下命令手动创建快照。数据库中只有一个快照或者需要查看在当前时间段数据库的监控数据，可以选择手动执行快照操作，该命令需要用户具有sysadmin权限。

    select create_wdr_snapshot(); --需要等候

  5. 执行如下命令，设置报告格式。\a: 不显示表行列符号， \t: 不显示列名 ，\o: 指定输出文件

    --生成格式化性能报告wdrTestNode.html。
\a \t \o /home/omm/scripts/database_check/20250318_wdrTestNode.html

  6. 执行如下命令，生成HTML格式的WDR报告

    --格式
select generate_wdr_report(begin_snap_id Oid, end_snap_id Oid, int report_type, int report_scope, int node_name );
--生成集群级别的报告
select generate_wdr_report(1, 2, 'all', 'cluster',null); 
--生成某个节点的报告
select generate_wdr_report(1, 2, 'all', 'node', pgxc_node_str()::cstring);

  7. 关闭性能报告wdrTestCluster.html

    \o \a \t

generate_wdr_report函数参数说明

参数 说明 取值范围
begin_snap_id 查询时间段开始的snapshot的id（表snapshot.snapshot中的snapshot_id）。 -
end_snap_id 查询时间段结束snapshot的id。默认end_snap_id大于begin_snap_id（表snapshot.snapshot中的snapshot_id）。 -
report_type 指定生成report的类型。例如，summary/detail/all。 summary：汇总数据。detail：明细数据。all：包含summary和detail。
report_scope 指定生成report的范围，可以为cluster或者node。 cluster：数据库级别的信息。node：节点级别的信息。
node_name 在report_scope指定为node时，需要把该参数指定为对应节点的名称。（节点名称可以执行select * from pg_node_env;查询）。在report_scope为cluster时，该值可以省略或者指定为空或NULL。 node：数据库中的节点名称。cluster：省

查看数据库信息

  1. 查看客户端版本

    gsql -V

  2. 查看服务端版本

    -- 磐维2.0
select pw_version;
-- 磐维1.0
select version;

  3. 查看当前数据库编码

    select getdatabaseencoding();

  4. 兼容模式

    -- 直接查看（A\B\PG）
show sql_compatibility;
-- 通过视图，字段datcompatibility表示兼容模式
select * from pg_database;

创建带指定编码格式的database

--utf8 是默认的格式，需要创建其他格式可使用以下命令（如 GBK格式）
create database tongyi with lc_collate = 'zh_CN.gbk' ENCODING='GBK' lc_ctype = 'zh_CN.gbk';
create database xgefp1with encoding 'GB18030' lc_collate 'zh_CN.gb18030' lc_ctype 'zh_CN.gb18030';

注：UTF-8对每个中文字符至少需要3个字节来表示，而GBK只需要2个字节。

创建表空间

创建表空间

  1. 执行如下命令创建用户jack。

    CREATE USER jack IDENTIFIED BY 'xxxxxxxxx';

    当结果显示为如下信息，则表示创建成功。

    CREATE ROLE

  2. 执行如下命令创建表空间。

    CREATE TABLESPACE fastspace RELATIVE LOCATION 'tablespace/tablespace_1';

    当结果显示为如下信息，则表示创建成功。

    CREATE TABLESPACE

    其中“fastspace”为新创建的表空间，“tablespace/tablespace_1”是用户拥有读写权限的空目录。

  3. 数据库系统管理员执行如下命令将“fastspace”表空间的访问权限赋予数据用户jack。

    GRANT CREATE ON TABLESPACE fastspace TO jack;

    当结果显示为如下信息，则表示赋予成功。

    GRANT

在表空间中创建对象

如果用户拥有表空间的CREATE权限，就可以在表空间上创建数据库对象，比如：表和索引等。

以创建表为例。

  • 方式1：执行如下命令在指定表空间创建表。

    CREATE TABLE foo(i int) TABLESPACE fastspace;

    当结果显示为如下信息，则表示创建成功。

    CREATE TABLE

  • 方式2：先使用set default_tablespace设置默认表空间，再创建表。

    SET default_tablespace = 'fastspace';
CREATE TABLE foo2(i int);
CREATE TABLE

假设设置“fastspace”为默认表空间，然后创建表foo2。

查询表空间

  • 方式1：检查pg_tablespace系统表。如下命令可查到系统和用户定义的全部表空间。

    SELECT spcname FROM pg_tablespace;

  • 方式2：使用gsql程序的元命令查询表空间。

    \db

查询表空间使用率

  1. 查询表空间的当前使用情况。

    SELECT PG_TABLESPACE_SIZE('example');

    返回如下信息：

     pg_tablespace_size 
--------------------
            2146304
(1 row)

    其中2146304表示表空间的大小，单位为字节。

  2. 计算表空间使用率。

    表空间使用率=PG_TABLESPACE_SIZE/表空间所在目录的磁盘大小。

修改表空间

执行如下命令对表空间fastspace重命名为fspace。

ALTER TABLESPACE fastspace RENAME TO fspace;
ALTER TABLESPACE

删除表空间

img 说明：

用户必须是表空间的owner或者系统管理员才能删除表空间。

  1. 执行如下命令删除用户jack。

    DROP USER jack CASCADE;

  2. 执行如下命令删除表foo和foo2。

    DROP TABLE foo;
DROP TABLE foo2;

    当结果显示为如下信息，则表示删除成功。

    DROP TABLE

  3. 执行如下命令删除表空间fspace。

    DROP TABLESPACE fspace;
DROP TABLESPACE

参数设置

  1. 查看参数生效方式

    -- 关注context字段的结果：包括internal、postmaster、sighup、backend、superuser、user。
selec * from pg_settings where name = 'server_version';

  2. 设置参数

    -- 以下命令仅供参考
## 需要重启
gs_guc set -I all -N all -c "log_statement_stats='on'" 
## 立即生效  
gs_guc reload -I all -N all -c "archive_mode='off'"
## 会话级别
SET paraname TO value;

开启归档：

  1. 开启归档

    gs_guc reload -N all -c "archive_mode = on" 
-- 若要使用PTRACK增量备份,需要开启以下参数
gs_guc reload -N all -c "enable_cbm_tracking = on"

  2. 设置归档路径

    gs_guc reload -N all -c "archive_dest = '/database/backup/wal/backup/'"
or
gs_guc reload -N all -c "archive_command = 'cp --remove-destination %p /database/backup/wal/backup/%f' "

    img 须知：

    • 当archive_dest和archive_command同时配置时，WAL日志优先保存到archive_dest所设置的目录中，archive_command配置的命令不生效。

    • 字符串中任何%p都被要归档的文件的绝对路径代替，而任何%f都只被该文件名代替（相对路径都相对于数据目录的）。如果需要在命令里嵌入%字符就必须双写%。

    • –remove-destination选项作用为：拷贝前如果目标文件已存在，会先删除已存在的目标文件，然后执行拷贝操作。

  3. 切换归档

    SELECT pg_switch_xlog();

创建新实例

  1. 新建目录授权（需要用到其他目录时可选）

    su - root
mkdir -p /database/panweidb2/data
chown -R omm:dbgrp /database/panweidb2/data

  2. 初始化实例

    gs_initdb -D /database/panweidb2/data -w "自定义密码" --nodename='data1' --dbcompatibility=A --encoding=UTF8

  3. 更改配置

    -- 更改配置文件 vi /database/panweidb2/data/postgresql.conf
	1）port
	2）max_process_memory
	3）listen_address
-- 更改日志位置
	export GAUSSLOG=/database/panweidb2/log/omm
-- 启动数据库
    pw_ctl start -D /database/panweidb2/data -Z single_node -l logfile
    panweidb -D /database/panweidb2/data

数据库白名单

--固定IP
gs_guc reload -N all -I all -h "host    all    all    10.183.98.234/32    sha256" 

--网段
gs_guc reload -N all -I all -h "host    all    all    10.239.32.0/24    sha256"  

-- 禁用白名单
gs_guc reload -N all -I all -h "host    all    all    0.0.0.0/0    sha256"    

0.0.0.0/0: 表示匹配所有的IP地址。这意味着任何网络位置的连接都会被接受。

-- 取消白名单（去掉sha256）
gs_guc reload -N all -I all -h "host    all    all    10.183.98.234/32"

示例：下面示例中配置允许IP地址为10.10.0.30的客户端访问本机。

gs_guc set -N all -I all -h "host all jack 10.10.0.30/32 sha256"

img 说明：

  • -N all 表示数据库的所有主机。
  • -I all 表示主机的所有实例。
  • -h 表示指定需要在“pg_hba.conf”增加的语句。
  • all 表示允许客户端连接到任意的数据库。
  • jack 表示连接数据库的用户。
  • 10.10.0.30/32表示只允许IP地址为10.10.0.30的主机连接。此处的IP地址不能为数据库内的IP，在使用过程中，请根据用户的网络进行配置修改。32表示子网掩码为1的位数，即255.255.255.255。
  • sha256表示连接时jack用户的密码使用sha256算法加密。

查看数据库过期时间

-- 临时许可时间
pw_licensetool --view --temporary

--dump后面接正式 license 路径 查正式许可证信息
pw_licensetool --dump=/database/panweidb/license

数据库关键字

  1. 查看关键字

    select * from pg_get_keywords();
select * from pg_get_keywords() where word like '%user%' ; --小写

    • word: 关键词本身。

    • catcode: 关键词的分类代码。

      U 表示通用关键字。
      C 表示可用于列名的关键字。
      T 表示可用于类型名或函数名的关键字。
      R 表示保留关键字，通常不能用作标识符。

    • catdesc: 分类描述，这里所有关键词的描述都是 ‘reserved’，表示它们是保留字。

    • isfixed: 表示关键词是否固定。‘Y’ 表示是固定的，意味着在 SQL 语句中使用时必须加引号；‘N’ 表示不是固定的

    备注：pg_get_keywords返回一组关于描述服务器识别SQL关键字的记录。word列包含关键字。catcode列包含一个分类代码：U表示通用的，C表示列名，T表示类型或函数名，或R表示保留。catdesc列包含了一个可能本地化描述分类的字符串。

  2. 屏蔽关键字

    -- 重启磐维数据库生效
gs_guc set -N all -I all -c "pw_exclude_reserved_words='关键字'"

-- 示例
gs_guc set -N all -I all -c "pw_exclude_reserved_words='key,index,XMIN,XMAX,user'"

数据大小写敏感（B模式）

  1. 查看看当前数据库下参数值。（取值范围： 0，1 默认为0）

    -- 查看控制数据库返回对象名的大小写敏感性，对象名包含schema名、表名、用户名等数据库对象名称。
show lower_case_table_names;
-- 查看控制数据库返回字段名的大小写敏感性参数值
show lower_case_column_names;

  2. 配置大小写不敏感（需要重启数据库）

    -- 需要编辑配置文件$PGDATA/postgresql.conf 追加
lower_case_table_names = 1;
lower_case_column_names= 1;

安装数据库模板

gs_install -X /app/database/panweidb2/soft/panweidb2m.xml \
--gsinit-parameter="--encoding=UTF8" \
--gsinit-parameter="--locale=en_US.UTF8" \
--gsinit-parameter="--lc-collate=en_US.UTF8" \
--gsinit-parameter="--lc-ctype=en_US.UTF8" \
--gsinit-parameter="--lc-messages=en_US.UTF8" \
--dn-guc="max_process_memory=50GB" \
--gsinit-parameter="--dbcompatibility=B"

查询TPS和QPS

  1. TPS（每秒事务数，通常用于评估数据库在处理包含一个或多个操作的事务时的性能。）

    select sum(xact_commit+xact_rollback) as s from pg_stat_database;
select pg_sleep(10);
select sum(xact_commit+xact_rollback) as s from pg_stat_database;

  2. QPS（每秒查询数，主要用于衡量数据库每秒能够执行的查询数量。）

    SELECT
    datname AS database_name,
    sum(xact_commit + xact_rollback) AS total_transactions,
    sum(tup_returned + tup_fetched) AS total_rows_returned,
    (sum(xact_commit + xact_rollback) + sum(tup_returned + tup_fetched)) AS total_queries,
    (sum(xact_commit + xact_rollback) + sum(tup_returned + tup_fetched)) / extract(epoch from now() - pg_postmaster_start_time()) AS qps
FROM
    pg_stat_database
WHERE
    datname NOT IN ('template0', 'template1')
GROUP BY
    datname, pg_postmaster_start_time()
ORDER BY
    qps DESC;

创建常用插件

  1. SQL统计插件

    -- 需要登录业务库创建
create extension pg_stat_statements;

  2. wal日志解析工具

    -- 只在A模式下成功
create extension walminer;

获取机器识别码申请许可

  1. 安装java环境

    yum -y install java-1.8.0-openjdk*

  2. 使用方式

    -- 解压后，进入bin目录，执行startup.sh脚本，启动即可。
-- 启动License CLI后，执行fingerprints命令：
$ fingerprints

临时文件使用情况

SELECT 
    datname, 
    temp_files, 
    (temp_bytes / 1024 / 1024 / 1024) AS temp_gb
FROM 
    pg_stat_database 
WHERE 
    temp_files > 0;

4.2 schema操作

设置查找路径

  1. 查看当前用户

    select user;

  2. 查看当前schema(搜索路径)

    show search_path;

  3. 切换schema

    set search_path to schema_name

  4. 设置查找路径

    -- 数据库级别的查找路径
alter database [database_name] set search_path = "$user",public,[schema_name]; 
-- 用户级别的查找路径
alter user [username] set search_path = [schema_name]; 
-- 检查
show search_path;

修改schema属性

  1. 修改schema属组

    ALTER SCHEMA [schema_name] OWNER TO [new_owner];

  2. 修改schema名字

    ALTER SCHEMA [schema_name] RENAME TO [new_name];

    举例：当schema名字出现太多不符合规则的字符，需要用""
    dcp_business_20240103=> alter schema dcp_business_cbf31ea9-f49b-47ad-937d-3368c046ae13 rename schema dcp_business_cbf31ea9;
    ERROR: syntax error at or near “-”
    LINE 1: alter schema dcp_business_cbf31ea9-f49b-47ad-937d-3368c046ae…
    dcp_business_20240103=> alter schema “dcp_business_cbf31ea9-f49b-47ad-937d-3368c046ae13” rename to dcp_business_cbf31ea9;
    ALTER SCHEMA

4.3 表操作

查看用户表的统计信息

-- 可以看到包括插入、更新、删除的行数、vacuum、analyze次数、死亡元组数等。
SELECT * FROM pg_stat_user_tables WHERE relname = '[table_name]';

统计信息收集

  1. 查看哪些table未及时做vacuum，以及未及时收集统计信息

    -- 先设置一下格式
\pset border 2
-- 执行命令查看
SELECT 
	relname, 
	n_live_tup, 
	n_dead_tup, 
	trunc(100*n_dead_tup/(n_live_tup+1))::float "ratio%",
    to_char(last_autovacuum, 'YYYY-MM-DD HH24:MI:SS') as autovacuum_date,
    to_char(last_autoanalyze, 'YYYY-MM-DD HH24:MI:SS') as autoanalyze_date
FROM
	pg_stat_all_tables
ORDER BY last_autovacuum;

  2. 查看表的死亡元组情况

    SELECT 
    schemaname AS schema_name,
    relname AS table_name,
    n_live_tup AS live_tuples,
    n_dead_tup AS dead_tuples,
    ROUND((n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0)) * 100, 2) AS dead_ratio_percentage,
    to_char(last_autovacuum, 'YYYY-MM-DD HH24:MI:SS') AS last_autovacuum_time,
    to_char(last_autoanalyze, 'YYYY-MM-DD HH24:MI:SS') AS last_autoanalyze_time
FROM 
    pg_stat_user_tables
WHERE 
    n_dead_tup > 0
ORDER BY 
    dead_ratio_percentage DESC NULLS LAST;

  3. 更新统计信息

    ANALYZE table_name

  4. 清理表回收空间

    vacuum ANALYZE table_name

-- 选择“FULL”清理，这样可以恢复更多的空间，但是需要耗时更多，并且在表上施加了排他锁。
vacuum fuu table_name

    在一般的数据库操作里，那些已经DELETE的行并没有从它们所属的表中物理删除；在完成VACUUM之前它们仍然存在

  • VACUUM 主要关注于清理死元组和管理磁盘空间，同时也帮助避免事务ID回绕的问题。
  • ANALYZE 则专注于更新查询优化器使用的统计信息，以确保查询能够高效执行。

查看某张表的死亡元组

SELECT
    schemaname,
    relname AS table_name,
    n_live_tup AS live_tuples,
    n_dead_tup AS dead_tuples
FROM
    pg_stat_user_tables
WHERE
    relname = 'table_name';

复制表

  1. 复制表结构

    CREATE TABLE table1 (LIKE table2 INCLUDING ALL);

  2. 复制表结构和数据

    CREATE TABLE [new_talble] AS table [old_table];

  3. 选项条件复制

    -- 全表复制
CREATE TABLE  [new_talble] AS SELECT * FROM [old_table] WHERE [column] > '4795';
-- 字段复制
CREATE TABLE  [new_talble] AS SELECT [column1],[column2] FROM [old_table] WHERE [column] > '4795';

查看表结构

-- 查看创建表/视图的语句
SELECT * FROM pg_get_tabledef('table_name');
SELECT * FROM pg_get_viewdef('view_name',true);

检查失效索引

  1. 检查失效索引

    -- 查询目标：查找状态异常的索引（indisvalid 或 indisusable 不为 TRUE）
-- 输出字段包括：
--   - 索引是否准备好 (indisready)
--   - 索引是否可用 (indisusable)
--   - 索引是否有效 (indisvalid)
--   - 索引所属的表名 (tablename)
--   - 索引的名字 (index_name)
SELECT 
    a.indisready,
    a.indisusable,
    a.indisvalid,
    b.tablename,
    c.relname AS index_name
FROM 
    pg_class c
JOIN 
    pg_index a ON a.indexrelid = c.oid
JOIN 
    pg_indexes b ON c.relname = b.indexname
WHERE 
    (a.indisvalid <> 't' OR a.indisusable <> 't')
ORDER BY 
    b.tablename, c.relname;

  2. 重建失效索引

    alter index xxxx rebuild;

创建索引

  1. 单索引

    create index index_name_1 on table_name(colunm1);
create index index_name_2 on table_name(colunm2);

  2. 复合索引

    CREATE INDEX index_name ON table_name (colunm1,colunm2);

4.4 用户操作

密码操作

  1. 修改用户密码

    alter user usename password '';

  2. 用户密码有效期参数

    show password_effect_time;

  3. 输入密码错误锁定参数

    -- failed_login_attempts 如果用户输入密码次数超过一定次数（failed_login_attempts），系统将自动锁定该帐户，默认5次。
-- password_lock_time 当帐户被锁定时间超过设定值（password_lock_time），则当前帐户自动解锁，默认1440天
-- 不支持数据库级别、用户级别和会话级别的修改，只能全局修改
gs_guc reload -D datadir -c "failed_login_attempts=3600"

  4. 用户密码有效期查询

    select b.usename,a.passwordtime,a.passwordtime+numtodsinterval(to_number((select setting from pg_settings where name='password_effect_time')),'DAY') as passwordexpiredtime  from 
(select roloid,max(passwordtime) as passwordtime from pg_catalog.pg_auth_history group by roloid) a 
right join 
(select usename,usesysid from pg_user) b 
on a.roloid=b.usesysid;

授权操作

  1. 基础查询

    -- 数据库级别
grant select any sequence to user_name;	查询任意用户下的序列
grant select any table to user_name;	查询任意用户下的表
grant create on schema public to user_name ;	创建public下的表

-- schema级别
grant usage, on schema xxx to user_name ;	
grant create on schema public to user_name ;
grant select on all tables in schema xxx to user_name ;
grant select on all sequences in schema xxx to user_name ;

  2. 增删改

    -- database级别
grant select any sequence to user_name;
grant select any table to user_name;
grant create on schema public to user_name ;
grant insert any table to user_name;
grant update any table to user_name;
grant delete any table to user_name;

-- schema级别
grant usage,create on schema xxxx to user_name ;
grant create on schema public to user_name ;
grant select,insert,update,delete on all tables in schema xxxx to user_name  ;
grant select on all sequences in schema xxxx to user_name ;

  3. 开发权限

    -- database级别
grant create any index to user_name;
grant alter any index to user_name;
grant select any sequence to user_name;
grant create any sequence to user_name;
grant create any synonym  to user_name;
grant create any table to user_name;
grant alter any table to user_name;
grant select any table to user_name;
grant insert any table to user_name;
grant update any table to user_name;
grant delete any table to user_name;
grant comment on database xxx to user_name;
grant create any type to user_name;
grant alter any type to user_name;
grant EXECUTE ANY function to user_name;

-- schema级别
grant usage on schema xxxx to user_name ; 
grant create on schema xxxx to user_name ; 
grant create on schema public to user_name ;
grant alter,select,insert,update,delete,comment,index on all tables in schema xxxx to user_name ;
grant select on all sequences in schema xxxx to user_name ;
grant execute on all functions in schema  xxxx to user_name;

  4. 测试库的开发权限

    grant create any index to user_name;
grant alter any index to user_name;
grant drop any index to user_name;
grant select any sequence to user_name;
grant create any sequence to user_name;
grant drop any sequence to user_name;
grant create any synonym  to user_name;
grant drop any synonym  to user_name;
grant create any table to user_name;
grant alter any table to user_name;
grant select any table to user_name;
grant insert any table to user_name;
grant update any table to user_name;
grant delete any table to user_name;
grant drop any table to user_name;
grant create any type to user_name;
grant alter any type to user_name;
grant drop any type to user_name;
GRANT TRUNCATE ON ALL TABLES IN SCHEMA xxxx TO user_name;

  5. public的权限(可选)

    grant all on schema public to <应用user_name>;
alter default privileges for user <业务schema所属owner> in schema public grant all on tables to <应用user_name>;
grant select on all sequences in schema public to <应用user_name>;
alter default privileges for user <业务schema所属owner> in schema public grant select on sequences to <应用user_name>;

  6. 回收权限

    REVOKE ALL PRIVILEGEs FROM <user_name>;

  7. 其他

    -- 涉及多个用户,需要互相访问时
ALTER DEFAULT PRIVILEGES FOR USER  <业务schema所属owner>  IN SCHEMA <业务schema> GRANT SELECT,INSERT,UPDATE,DELETE ON TABLES TO <owner> ;
ALTER DEFAULT PRIVILEGES FOR USER  <业务schema所属owner>  IN SCHEMA <业务schema> GRANT USAGE,SELECT,UPDATE ON SEQUENCES TO <owner> ;
-- 或者
grant <业务schema所属owner> to <owner>;

查询用户权限

  1. B模式

    show grants for [user_name];

  2. A模式

    -- 查看指定用户对表级别的**实际**权限
SELECT
    grantee,
    table_schema,
    table_name,
    string_agg(privilege_type, ', ') AS privileges
FROM
    information_schema.table_privileges
WHERE
    grantee = '用户'
GROUP BY
    grantee, table_schema, table_name;

-- 查看所有用户对所有表的权限
SELECT
    grantee,
    table_schema,
    table_name,
    string_agg(privilege_type, ', ') AS privileges
FROM
    information_schema.table_privileges
GROUP BY
    grantee, table_schema, table_name
order by
	grantee,table_schema;
 
-- 查看用户**被授予**的表权限
SELECT 
    grantee, 
    table_schema, 
    table_name, 
    privilege_type, 
    is_grantable
FROM 
    information_schema.role_table_grants
WHERE 
    grantee = '用户'
    AND table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY 
    table_schema, table_name, privilege_type;
 
-- 查看当前用户是否有特定权限
SELECT has_table_privilege('表名', '权限类型'); -- 示例：has_table_privilege('my_table', 'SELECT')

-- 查看指定用户是否对某个表有特定权限
SELECT 
    unnest(array['SELECT', 'INSERT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER', 'ALTER', 'DROP', 'COMMENT']) AS privilege_type,
    unnest(array[
        has_table_privilege('用户', '表名', 'SELECT'),
        has_table_privilege('用户', '表名', 'INSERT'),
        has_table_privilege('用户', '表名', 'UPDATE'),
        has_table_privilege('用户', '表名', 'DELETE'),
        has_table_privilege('用户', '表名', 'TRUNCATE'),
        has_table_privilege('用户', '表名', 'REFERENCES'),
        has_table_privilege('用户', '表名', 'TRIGGER'),
        has_table_privilege('用户', '表名', 'ALTER'),
        has_table_privilege('用户', '表名', 'DROP'),
        has_table_privilege('用户', '表名', 'COMMENT')
    ]) AS has_privilege;


-- 查看指定用户对schema级别的权限
SELECT 
    n.nspname AS schema_name,
    pg_catalog.has_schema_privilege('用户', n.oid, 'USAGE') AS has_usage,
    pg_catalog.has_schema_privilege('用户', n.oid, 'CREATE') AS has_create,
	pg_catalog.has_schema_privilege('用户', n.oid, 'ALTER') AS has_alter,
	pg_catalog.has_schema_privilege('用户', n.oid, 'DROP') AS has_drop,
	pg_catalog.has_schema_privilege('用户', n.oid, 'COMMENT') AS has_comment
FROM 
    pg_catalog.pg_namespace n order by has_usage desc;

-- 查看指定用户对数据库级别的权限
SELECT 
    datname AS database_name,
    has_database_privilege('用户', datname, 'CONNECT') AS has_connect,
    has_database_privilege('用户', datname, 'CREATE') AS has_create,
    has_database_privilege('用户', datname, 'TEMPORARY') AS has_temporary,
    has_database_privilege('用户', datname, 'ALTER') AS has_alter,
    has_database_privilege('用户', datname, 'DROP') AS has_drop,
    has_database_privilege('用户', datname, 'COMMENT') AS has_comment
FROM 
    pg_database
WHERE 
    datistemplate = false AND
    datname NOT IN ('panweidb', 'postgres')
	order by has_create desc;

-- 查看用户的角色级别的权限
SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication 
FROM pg_roles 
WHERE rolname = '用户';


-- 磐维较高版本新增了一个dba_tab_privs视图

4.5 其他操作

查看同义词

SELECT * FROM pg_catalog.pg_synonym;

查看函数定义

select prosrc from pg_proc where proname='function_name';

主机操作

  • 模拟端口占用 nc -l -p 8080
  • 跟踪网络路由 traceroute ip

序列操作

  1. 查看序列列表

    \ds

  2. 查看序列值

    -- 查看下一个序列值
SELECT nextval('my_sequence');
-- 查看当前序列值
SELECT currval('my_sequence');

  3. 批量增加序列的当前值

    -- 单用户
WITH user_sequences AS (
    SELECT n.nspname AS schemaname, c.relname AS sequencename
    FROM pg_class c
    JOIN pg_namespace n ON n.oid = c.relnamespace
    JOIN pg_roles r ON r.oid = c.relowner
    WHERE r.rolname = '[user_name]'
)
SELECT 'SELECT setval('''
     || quote_ident(schemaname) || '.' || quote_ident(sequencename)
     || ''', (SELECT last_value + 10000 FROM ' || quote_ident(schemaname) || '.' || quote_ident(sequencename) || '), true);'
FROM user_sequences;

-- 多用户
WITH user_sequences AS (
    SELECT n.nspname AS schemaname, 
           c.relname AS sequencename,
           r.rolname AS username
    FROM pg_class c
    JOIN pg_namespace n ON n.oid = c.relnamespace
    JOIN pg_roles r ON r.oid = c.relowner
    WHERE r.rolname IN ('[user_name1]', '[user_name2]', '[user_name3]','...')
)
SELECT 'SELECT setval('''
     || quote_ident(schemaname) || '.' || quote_ident(sequencename)
     || ''', (SELECT last_value + 10000 FROM ' || quote_ident(schemaname) || '.' || quote_ident(sequencename) || '), true);'
FROM user_sequences;

  4. 批量更改序列的cache值

    -- 普通序列
SELECT 'ALTER SEQUENCE ' || sequence_schema || '.' || sequence_name || ' CACHE 1;' AS alter_sequence_statement FROM information_schema.sequences;
-- 大序列 
SELECT 'ALTER LARGE SEQUENCE ' || sequence_schema || '.' || sequence_name || ' CACHE 1;' AS alter_sequence_statement FROM information_schema.sequences;

5、数据导入导出

CSV格式

  1. 导出csv格式

    \o /tmp/outputfile.csv 
\copy (SELECT * FROM sm.sys_config) TO STDOUT WITH CSV HEADER;
\copy (SELECT * FROM sm.sys_config_desc) TO STDOUT WITH CSV HEADER;
\o

  2. 导入csv格式

    gsql -r -d postgres -c "\copy a from '/tmp/config_0305.csv' WITH CSV HEADER;"

txt格式

  1. 导出某个命令的结果

    \o /tmp/outputfile.txt
command
\o

  2. 导入txt文件

    gsql -r -d xxxx -f /tmp/outputfile.txt > /tmp/result.txt

dump工具

  1. 导出全部数据库

    -- 导出全部数据库
gs_dumpall -p 1700 -f /tmp/all_database_$(date +%Y%m%d).sql

-- 后台运行
nohup gs_dumpall -p 17700 -f /tmp/all_database_$(date +%Y%m%d).sql > a_10_90_5_43_full_dll.log 2>&1 &

  2. 导出database

    -- 格式为纯文本格式。(可以使用gsql程序从纯文本导出文件中导入数据。)
gs_dump database_name -f /tmp/database_name_$(date +%Y%m%d).sql -v -F p

-- 文件格式为tar格式。
gs_dump database_name -f /tmp/database_name_$(date +%Y%m%d).tar  -v -F t

-- 文件格式为自定义归档格式。（一种二进制文件）
gs_dump database_name -f /tmp/database_name_$(date +%Y%m%d).dmp -v -F c

-- 文件格式为目录格式。(仅该格式下支持并发操作)
gs_dump database_name -f /tmp/database_name_$(date +%Y%m%d) -v -F d -j 8

  3. 导出schema

    -- 导出某个schema，用-n
gs_dump database_name -n sch1 -n sch2  -f /tmp/schema_name_$(date +%Y%m%d).sql  -v -F p
-- 排除某个schema，用-N 
gs_dump -h host_name -p port_number postgres -f backup/bkp_shl2.sql -N sch1 -N sch2

  4. 导出table

    -- 导出某个table，用-t
gs_dump  database_name -t schema_name.table1 -f /tmp/table_name_$(date +%Y%m%d).sql -v -F p
-- 导出某个table，用-T
gs_dump database_name -T table1 -T table2 -f /tmp/table_name_$(date +%Y%m%d).sql -v -F p

6、集群操作

主备同步情况

  1. wal日志传输情况

    select * from pg_stat_replication;

  2. 复制槽使用情况

    select * from pg_replication_slots;

  3. 删除复制槽（迁移时可用）

    select pg_drop_replication_slot('slot_name');

  4. 查看数据同步延迟情况

    select pid,client_addr,application_name,state,sync_state,lsn,
lsn - sent_location as sent_diff,lsn - write_location as write_diff,lsn - flush_location as flush_diff,round((lsn - replay_location)/1024/1024,2) as replay_diff_mb,
sent_location,write_location,flush_location,replay_location,replay_lag,backend_uptime,sync_priority
from
(
select pr.pid,client_addr,application_name,pr.state,pr.sync_state,
pg_xlog_location_diff (case when pg_is_in_recovery() then pg_last_xlog_receive_location() else pg_current_xlog_location() end, '0/0') as lsn,
pg_xlog_location_diff(pr.sender_sent_location,'0/0') as sent_location,
pg_xlog_location_diff(pr.receiver_write_location,'0/0') as write_location,
pg_xlog_location_diff(pr.receiver_flush_location,'0/0') as flush_location,
pg_xlog_location_diff(pr.receiver_replay_location,'0/0') as replay_location,
pg_xlog_location_diff(pr.receiver_replay_location, pg_current_xlog_location()) as replay_lag,
extract(EPOCH from now() - backend_start) as backend_uptime,pr.sync_priority
from
pg_stat_replication pr
);

  5. 查看集群同步模式

    show synchronous_standby_names;

主节点切换

  1. gs_ctl

    -- 需要升主的节点执行
gs_ctl switchover -D /database/panweidb/data

  2. cm_ctl

    -- 任意节点执行
cm_ctl switchover -n 1/2/3 -D /database/panweidb/data

集群启停

  1. 重启集群

    --cm
cm_ctl stop && cm_ctl start
--gs
gs_om -t stop && gs_om -t start

  2. 依次启停

    -- 停止事先从**备库**开始
cm_ctl stop -n 3 -D /database/panweidb/data
cm_ctl stop -n 2 -D /database/panweidb/data
cm_ctl stop -n 1 -D /database/panweidb/data
-- 启动时先从**主库**开始
cm_ctl start -n 1 -D /database/panweidb/data
cm_ctl start -n 2 -D /database/panweidb/data
cm_ctl start -n 3 -D /database/panweidb/data

集群只读参数修改

datastorage_threshold_value_check

参数说明： 设置数据库只读模式的磁盘占用阈值，当某个节点的数据目录所在磁盘占用阈值超过这个阈值，如果该节点为备机，自动将该节点设置为只读，如果该节点为主机，则会自动将主机切换到一个合适的主机上。如果所有节点的阈值均达到阈值，则会将集群设置为只读。

默认值： 85

enable_transaction_read_only

参数说明： 控制数据库是否为只读模式开关。

-- 关闭相关参数
gs_guc reload -Z cmserver -N all -I all -c "datastorage_threshold_value_check=98" 
gs_guc reload -Z cmserver -N all -I all -c " enable_transaction_read_only=off"

-- 检查参数
cm_ctl list --param --server | grep enable_transaction_read_only

7、问题处理

cm_server无主节点

  1. 强制升主

    cm_ctl set --cmsPromoteMode=PRIMARY_F -I 1

  2. 主动选主

    cm_ctl set --cmsPromoteMode=AUTO -I 1

  3. 重新初始化

    -- 删除元数据，会自动加载信息
rm -rf /database/panweidb/cm/dcf_data/*
rm -rf /database/panweidb/cm/gstor/*

dtp报错out of memory

  1. 问题处理：迁移工具页面out of memory，工具日志显示Java heap space
  2. 适当加大服务配置文件（panwei_dtp.service）中xms和xmx参数
  3. 原配置：-xms256m -xmx4096m，一般调后面那个就可以，最大调个主机内存80%

group by出错（B模式）

在磐维的B模式下，group by 后面的字段与需要查询的字段不一致时会报错

  1. 修改postgresql.conf ，新增参数

    panweidb_sql_mode='ansi_quotes,pipes_as_concat,pad_char_to_full_length,sql_mode_strict,no_zero_date'

  2. 重启磐维数据库生效

    gs_om -t stop && gs_om -t start

使用函数trim( ’ ’ )，返回null值

执行时函数trim( ’ ’ )，在gsql客户端显示空值，在dbeaver变成null了，返回结果不符合预期。

  1. 可修改数据库参数解决

    gs_guc reload -D $data_dir -c "behavior_compat_options='reduce_tailing_zero,skip_insert_gs_source,select_into_return_null,accept_empty_str'"

系统表无法执行truncated

  1. 修改参数 allow_system_table_mods 为 on

    gs_guc set -I all -N all -c "allow_system_table_mods=true"

  2. 重启数据库

    cm_ctl stop && cm_ctl start

  3. 情况表数据

    \dt+ statement_history
truncate statement_history

  4. 清理元组

    vacuum full statement_history

  5. 调回参数 allow_system_table_mods 为off

    gs_guc set -I all -N all -c "allow_system_table_mods=''"

  6. 再重启数据库

    cm_ctl stop && cm_ctl start

误删xlog文件导致数据库无法启动

强制生成一个wal日志 pg_resetxlog path_dir -f

磁盘空间满

  1. 调整关闭数据库只读参数

    -- 关闭相关参数
gs_guc reload -Z cmserver -N all -I all -c "datastorage_threshold_value_check=98" 
gs_guc reload -Z cmserver -N all -I all -c " enable_transaction_read_only=off"
-- 检查参数
cm_ctl list --param --server | grep enable_transaction_read_only

  2. 登录数据库检查大表，然后对大表执行vacuum操作

    -- 查看大表
select tableowner,schemaname,tablename,pg_size_pretty(pg_table_size(schemaname||'.'||tablename)) as table_size from pg_tables order by pg_table_size(schemaname||'.'||tablename) desc;
-- 执行清理
vacuum full 大表名;

  3. 检查磁盘使用率是否已经下降

    df -h

  4. 主库上检查数据库参数

    -- 主库上需保证两个参数transaction_read_only、default_transaction_read_only都是off，
show transaction_read_only;
show default_transaction_read_only;

-- 如果不是off，用下面语句修改：
alter system set default_transaction_read only = off.
alter system set transaction_read_only = off;

  5. 清理归档

    find /archive -mtime +${fnum} -type f -name "0000*" | xargs rm -f
磐维数据库磐维panweidbpanwei数据库运维
最后修改时间：2025-05-19 10:06:12
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论