GreenPlum数据库日常维护

1FATAL: database is not accepting commands to avoid wraparound data loss in database "database_name"

1#!/bin/bash
2DBNAME="<database-name>"
3SYSTABLES="' pg_catalog.' || relname || ';' FROM pg_class a, pg_namespace b 
4WHERE a.relnamespace=b.oid AND b.nspname='pg_catalog' AND a.relkind='r'"
5reindexdb --system -d $DBNAME
6psql -tc "SELECT 'VACUUM' || $SYSTABLES" $DBNAME | psql -a $DBNAME
7analyzedb -s pg_catalog -d $DBNAME

1=# VACUUM ANALYZE mytable;

1ANALYZE cust_info;VACUUM cust_info;

1script_name_date.log

1timestamp:utility:host:user:[INFO|WARN|FATAL]:message

1select * from gp_configuration order by 1 ;

1select * from gp_segment_configuration;

1select * from gp_toolkit.gp_disk_free;
2
3select dfsegment,dfhostname,dfdevice,round(dfspace/1024,2) as free_disk_MB 
4from gp_toolkit.gp_disk_free 
5ORDER BY free_disk_MB DESC ;
6
7-- dfspace 实例所在的文件系统的空闲磁盘空闲空间(千字节为单位)

1gpssh -f host_list -e “df -h |grep data”

 1select pg_size_pretty(pg_relation_size('schema.tablename'));
 2
 3select pg_size_pretty(pg_database_size('databasename'));
 4
 5--  1.查看所有库大小
 6select sodddatname,sodddatsize/1024/1024 as db_size_MB from gp_toolkit.gp_size_of_database;
 7
 8-- 2.查看库下面的所有schema 占用的磁盘空间
 9select sosdnsp,round(sosdschematablesize/1024/1024,2) schema_tb_size_MB,round(sosdschemaidxsize /1024/1024,2) as schema_idx_size_MB from gp_toolkit.gp_size_of_schema_disk;
10
11-- 3.查看schema下所有表和索引 占用的磁盘空间
12SELECT soatioid,soatischemaname,soatitablename,round(soatisize/1024/1024,2) as INDEX_SIZE_MB FROM gp_toolkit.gp_size_of_all_table_indexes 
13order by INDEX_SIZE_MB desc,soatischemaname,soatitablename ;
14
15-- 4.查看表占用空间
16
17select sotuoid, sotuschemaname,sotutablename,round(cast(sotusize as integer )/1024/1024,2) as table_SIZE_MB   from gp_toolkit.gp_size_of_table_uncompressed order by sotuschemaname, table_SIZE_MB desc,sotutablename
18
19-- 5.查看膨胀表
20select * from gp_toolkit.gp_bloat_diag order by bdinspname,(bdirelpages-bdiexppages)desc,bdirelname;
21
22 -- 没有统计信息且可能需要ANALYZE的表。
23select * from gp_toolkit.gp_stats_missing order by smischema,smicols desc;

1Select gp_segment_id,count(*) from tablename group by 1 ;
2
3
4在命令运行： gpskew -t public.ate -a postgres

1SELECT locktype, database, c.relname, l.relation, l.transactionid, l.transaction, l.pid, l.mode, l.granted, a.current_query
2FROM pg_locks l, pg_class c, pg_stat_activity a
3WHERE l.relation=c.oid AND l.pid=a.procpid
4ORDER BY c.relname;

 1#正常登陆
 2
 3psql gpdb
 4
 5psql -d gpdb -h gphostm -p 5432 -U gpadmin
 6
 7
 8#使用utility方式
 9
10PGOPTIONS="-c gp_session_role=utility" psql -h -d dbname hostname -p port
11
12#退出
13
14在psql命令行执行q

1psql -c 'SHOW ALL;' -d gpdb
2
3gpconfig --show max_connections

1createdb -h localhost -p 5432 dhdw

 1文件系统名
 2
 3gpfsdw
 4
 5子节点，视segment数创建目录
 6
 7mkdir -p /gpfsdw/seg1
 8mkdir -p /gpfsdw/seg2
 9chown -R gpadmin:gpadmin /gpfsdw
10
11主节点
12
13mkdir -p /gpfsdw/master
14chown -R gpadmin:gpadmin /gpfsdw
15gpfilespace -o gpfilespace_config
16gpfilespace -c gpfilespace_config

1psql gpdb
2
3create tablespace TBS_DW_DATA filespace gpfsdw;
4
5SET default_tablespace = TBS_DW_DATA;

1gpdeletesystem -d /gpmaster/gpseg-1 -f

1select * from pg_filespace_entry;

1SELECT * FROM gp_toolkit.gp_disk_free ORDER BY dfsegment;
2
3SELECT * FROM gp_toolkit.gp_size_of_database ORDER BY sodddatname;

1SELECT * FROM gp_toolkit.__gp_log_master_ext;
2
3SELECT * FROM gp_toolkit.__gp_log_segment_ext;

1SELECT gp_segment_id, count(*) FROM <table_name> GROUP BY gp_segment_id;

1SELECT relname as name, sotdsize/1024/1024 as size_MB, sotdtoastsize as toast, sotdadditionalsize as other
2
3FROM gp_toolkit.gp_size_of_table_disk as sotd, pg_class
4
5WHERE sotd.sotdoid = pg_class.oid ORDER BY relname;

1SELECT soisize/1024/1024 as size_MB, relname as indexname
2
3FROM pg_class, gp_toolkit.gp_size_of_index
4
5WHERE pg_class.oid = gp_size_of_index.soioid
6
7AND pg_class.relkind='i';

1SELECT schemaname as schema, objname as table, usename as role, actionname as action, subtype as type, statime as time
2
3FROM pg_stat_operations
4
5WHERE objname = '<name>';

1SELECT * FROM pg_resqueue_status;

 1# 启动服务
 2
 3gpfdist -d /share/txt -p 8081 –l /share/txt/gpfdist.log &
 4
 5# 创建外部表，分隔符为’/t’
 6
 7drop EXTERNAL TABLE TD_APP_LOG_BUYER;
 8CREATE EXTERNAL TABLE TD_APP_LOG_BUYER (
 9IP text,
10ACCESSTIME text,
11REQMETHOD text,
12URL text,
13STATUSCODE int,
14REF text,
15name text,
16VID text)
17LOCATION ('gpfdist://gphostm:8081/xxx.txt')
18FORMAT 'TEXT' (DELIMITER E'/t'
19FILL MISSING FIELDS) SEGMENT REJECT LIMIT 1 percent;
20
21
22# 创建普通表
23
24create table test select * from TD_APP_LOG_BUYER;
25
26# 索引
27CREATE INDEX idx_test ON test USING bitmap (ip);
28
29# 查询数据
30
31select ip , count(*) from test group by ip order by count(*);

 1# 创建可写外部表
 2
 3CREATE WRITABLE EXTERNAL TABLE unload_expenses
 4( LIKE expenses )
 5LOCATION ('gpfdist://etlhost-1:8081/expenses1.out',
 6'gpfdist://etlhost-2:8081/expenses2.out')
 7FORMAT 'TEXT' (DELIMITER ',')
 8DISTRIBUTED BY (exp_id);
 9
10# 写权限
11
12GRANT INSERT ON writable_ext_table TO <name>;
13
14# 写数据
15
16INSERT INTO writable_ext_table SELECT * FROM regular_table;

1# 创建控制文件
2
3# 加载数据
4
5gpload -f my_load.yml

1COPY country FROM '/data/gpdb/country_data'
2WITH DELIMITER '|' LOG ERRORS INTO err_country
3SEGMENT REJECT LIMIT 10 ROWS;
4
5
6COPY (SELECT * FROM country WHERE country_name LIKE 'A%') TO '/home/gpadmin/a_list_countries.out';

1psql gpdbname –f yoursqlfile.sql

1i yoursqlfile.sql

1SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
2FROM pg_stat_user_tables ORDER BY last_vacuum
3SELECT procpid, current_timestamp - xact_start AS xact_runtime, current_query
4FROM pg_stat_activity ORDER BY xact_start;

 11.vacuum
 2-- 生成 vacuum ANALYZE命令（或者使用 VACUUM FULL）
 3select 'VACUUM FULL' || ' gpmg.' || relname || ';' as vacuum_sql from pg_class a,pg_namespace b where a.relnamespace=b.oid and b.nspname= 'gpmg' and a.relkind='r';
 4
 5-- 脚本执行命令
 6-- VCOMMAND="VACUUM ANALYZE"
 7-- psql -tc "select '$VCOMMAND' || ' pg_catalog.' || relname || ';' from pg_class a,pg_namespace b   where a.relnamespace=b.oid and b.nspname= 'pg_catalog' and a.relkind='r'" $DBNAME | psql -a  $DBNAME
 8
 9 2. 清理日志
10-- pg_log目录下并使用约定命名方式：gpdb-YYYY-MM-DD.log

1nohup psql -c "show all;" >./test.log &
2-- ps -fe | grep test.log

1select * from gp_segment_configuration where status='d' or mode <>'s' --查看segment处于down的状态
2
3select * from gp_segment_configuration where status='d' or  mode <>'s'--观察数据库是否发生切换

1select * from gp_segment_configuration;
2-- 以上语句查询结果中status存在'd'结果，则是存在实例宕机，可以往下继续判断
3-- 如果均为'u'，可以参考后面的2.6小节
4select pid,usename,query_start,client_addr,xact_start,waiting ,waiting_reason,query from pg_stat_activity where state <>'idle' and query_start < now()-interval '1 hour' order by query_start;

 1    Select con.time,
 2    con.dbid,
 3    seg.content,
 4    seg.status,
 5    seg.port,
 6    seg.hostname,
 7    seg.datadir ,
 8    con."desc"
 9    from gp_segment_configuration seg ,gp_configuration_history con
10    where con.dbid=seg.dbid and seg.status='d' order by con.time desc limit 10;

1nohup gprecoverseg -a &

1 nohup gprecoverseg -ra &

1gpstate -e

1nohup gprecoverseg -Fa &

1nohup gprecoverseg -ra &

1 create role test with login  resource queue pg_default password 'passwd';

1 Create schema test;

1Create schema  authorization test;

1create resource queue rq_df with(active_statements=1,priority=medium);

1alter role test with resource queue rq_df;

1select rolname,rsqname from pg_roles,gp_toolkit.gp_resqueue_status where pg_roles.rolresqueue=gp_toolkit.gp_resqueue_status.queueid;

1GRANT { {SELECT | INSERT | UPDATE | DELETE | REFERENCES |
2    TRIGGER | TRUNCATE } [, ...] | ALL [PRIVILEGES] }
3        ON { [TABLE] table_name [, ...]
4             | ALL TABLES IN SCHEMA schema_name [, ...] }
5    TO { [ GROUP ] role_name | PUBLIC} [, ...] [ WITH GRANT OPTION ]
6    GRANT { {USAGE | SELECT | UPDATE} [, ...] | ALL [PRIVILEGES] }
7        ON { SEQUENCE sequence_name [, ...]
8             | ALL SEQUENCES IN SCHEMA schema_name [, ...] }
9        TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

1 GRANT USAGE ON SCHEMA schema to user;

1grant select on tablename to user;

1alter default privileges for user test in schema test grant select on tables to test1;
2--举例中，test用户在test模式下再建新表时，test1用户自动对test模式下新建的表有select权限

1REVOKE  USAGE ON SCHEMA schema from user;

1alter default privileges for user test in schema test revoke select on tables from test1;

1select * from information_schema.table_privileges where table_name ='test';

1select * from information_schema.table_privileges where grantee='test' or grantor='test';

1alter role test with nologin;

1DROP SCHEMA [IF EXISTS] name [, ...] [CASCADE | RESTRICT];

1DROP ROLE [IF EXISTS] test;

 1select datid,  --数据库OID
 2      datname,  --数据库名
 3      pid,  --后台服务器进程号(即对应该会话在master节点操作系统进程ID号)
 4      sess_id,  --会话线程号
 5      usesysid,  --role ID号
 6      usename,  --role名称
 7      client_addr,  --客户端地址
 8      client_port,  --客户端端口
 9      application_name,  --客户端应用名称
10      substr(query,1,80)  --请求SQL，较长SQL截取部分
11      query_start,  --请求开始时间
12      backend_start,  --backend进程开始时间，即用户连接数据库时建立会话的时间
13      xact_start,  --事务开始时间
14      waiting,  --是否等待True if waiting on a lock, false if not waiting
15      waiting_reason  --等待原因. The value can be:lock, replication, or resgroup
16    from pg_stat_activity where state<>'idle';

 1SELECT pg_locks.pid as pid,  --进程ID
 2           transactionid as transaction_id, --事务ID
 3           nspname as schemaname,  --schema名
 4           relname as object_name,  --对象名
 5           locktype as lock_type,  --锁类型
 6           mode lock_mode,  --锁模式
 7           CASE
 8             WHEN granted = 'f' THEN
 9              'get_lock'
10             WHEN granted = 't' THEN
11              'wait_lock'
12           END lock_satus,   --锁状态：持有锁|等待锁
13           CASE
14             WHEN waiting = 'false' THEN
15              'already get lock,sql executing'
16             WHEN waiting = 'true' THEN
17              'waiting get lock,sql waiting execute'
18           END waitting_satus,  --当前会话状态：执行中|等待中
19           least(query_start, xact_start) AS query_start,  --query请求开始时间
20           substr(query, 1, 25) AS query_text   --当前SQL语句
21      FROM pg_locks
22      LEFT OUTER JOIN pg_class
23        ON (pg_locks.relation = pg_class.oid)
24      LEFT OUTER JOIN pg_namespace
25        ON (pg_namespace.oid = pg_class.relnamespace),  pg_stat_activity
26     WHERE NOT pg_locks.pid = pg_backend_pid()
27       AND pg_locks.pid = pg_stat_activity.pid
28     ORDER BY query_start;
29
30
31
32SELECT locktype, database, c.relname, l.relation, l.transactionid, l.transaction, l.pid, l.mode, l.granted, a.current_query
33FROM pg_locks l, pg_class c, pg_stat_activity a
34WHERE l.relation=c.oid
35AND l.pid=a.procpid
36ORDER BY c.relname;
37
38-- 正被锁定的关系
39select 
40lorlocktype 被锁的关系的类型,
41lordatabase 对象标识符_共享对象为0,
42lorrelname 关系的名字,
43lorrelation 关系的对象标识符,
44lortransaction 锁影响到的事务标识符,
45lorpid 获取或等待锁的服务器进程号_准备型0,
46lormode 锁模式,
47lorgranted 锁是T否F,
48lorcurrentquery 会话当前的查询
49from gp_toolkit.gp_locks_on_relation;
50
51
52select 
53lorusename 当前会话用户,
54lorrsqname 资源队列,
55lorlocktype 锁对象类型,
56lorobjid 锁定的事务对象标识符,
57lortransaction 锁影响到的事务标识符,
58lorpid 锁影响到的事务的服务器进程号,
59lormode 锁模式的名字,
60lorgranted 锁是True否alse,
61lorwaiting 会话是否正在等待
62 from gp_toolkit.gp_locks_on_resqueue;

1select pg_cancel_backend(pid);

1select pg_terminate_backend(pid);

 1 gpdw=# select pg_cancel_backend(2243);
 2     pg_cancel_backend
 3    -------------------
 4     t
 5    (1 row)
 6    gpdw=# select pg_terminate_backend(2243);
 7     pg_terminate_backend
 8    ----------------------
 9     t
10    (1 row)

1select pid from pg_stat_activity where state='idle';

1select pid from pg_stat_activity where query_start>CAST('2022-06-26 11:12:10' AS TIMESTAMP);

1select * from pg_stat_activity where backend_start>(now() - interval '30 min'

1 ps -ef |grep -i postgres |grep -i idle|grep -v grep | awk '{print $2}' |xargs kill -15

 1 登陆客户端类型 允许访问数据库 数据库角色名 允许连接客户端IP(段) 验证方式
 2    登陆客户端类型：host|local    --远程登录|本地登录
 3    允许访问数据库：dbname|all  --具体数据库名|所有数据库
 4    数据库角色名：rolename|all  --具体用户名|所有用户
 5    允许连接客户端IP(段)        --设置IP地址或IP地址段,格式如下：
 6    192.168.32.100/32        --指定IP地址192.168.32.100
 7    192.168.32.0/24            --指定192.168.32.0网段
 8    192.168.0.0/16            --指定192.168.0.0网段
 9    192.0.0.0/8                --指定192.0.0.0网段
10    验证方式：ident|md5|password|trust|reject  --说明如下：
11    ident：本地验证，即使用操作系统gpadmin用户验证。
12    md5：密码验证
13    password：发送明文密码至数据库验证(建议不用)
14    trust:免密登录
15    reject:拒绝登录，即黑名单。建议黑名单放在pg_hba.conf文件最末

1gpstop -u

1psql -d gp  -h ip -U test

1gp_dump 默认在master 的data 目录上产生这些文件：
2
3gp_catalog_1_<dbid>_<timestamp> ：关于数据库系统配置的备份文件
4
5gp_cdatabase_1_<dbid>_<timestamp>：数据库创建语句的备份文件
6
7gp_dump_1_<dbid>_<timestamp>：数据库对象ddl语句
8
9gp_dump_status_1_<dbid>_<timestamp>：备份操作的日志

1gp_dump_0_<dbid>_<timestamp>：用户数据备份文件
2
3gp_dump_status_0_<dbid>_<timestamp>：备份日志