定制插件

oraudb

本插件是基于orafce 的UDB定制版本，UDB在orafce的基础上对部分功能进行了增强。该插件的启用方法如下：

[root@pgtest1 orafce-VERSION_3_18_1]# ud_sql -c "CREATE EXTENSION oraudb;"

鉴于oraudb的一些对象建立在oracle schema下，可以根据需要在会话级、database级或实例级，设置search_path 包含oracle schema。例如：

ALTER ROLE role_name SET search_path to "$user",public,oracle ;

或者

ALTER DATABASE database_name  SET search_path = "$user",public,oracle ;

这样就配置完成了，但是需要注意时区问题，orafce 默认使用的是UDB-TX 数据库系统的时区，由单独的参数 orafce.timezone 控制。

[root@pgtest1 ~]# vi unvdbsvr.conf
log_timezone = 'PRC'
timezone = 'PRC'
orafce.timezone = 'PRC'
[root@pgtest1 ~]# 
[root@pgtest1 ~]# ud_sql
unvdbsvr=# show orafce.timezone;
 orafce.timezone
-----------------
 PRC
(1 row)
unvdbsvr=# select oracle.sysdate(),now(),current_timestamp,clock_timestamp();
       sysdate       |              now              |       current_timestamp       |        clock_timestamp
---------------------+-------------------------------+-------------------------------+-------------------------------
 2022-04-01 10:32:42 | 2022-04-01 10:32:41.543877+08 | 2022-04-01 10:32:41.543877+08 | 2022-04-01 10:32:41.543979+08
(1 row)

与 Oracle 数据库兼容的特性

数据类型

数据类型	说明
VARCHAR2	可变长度字符数据类型
NVARCHAR2	可变长度国家字符数据类型
DATE	存储日期和时间的数据类型

unvdbsvr=# set search_path="$user", public, oracle;
SET
unvdbsvr=# CREATE TABLE tt (
unvdbsvr(#  name VARCHAR2(64) NOT NULL,
unvdbsvr(#  status NVARCHAR2(2000),
unvdbsvr(#  update_time DATE default (SYSDATE())
unvdbsvr(# );
CREATE TABLE
unvdbsvr=# \d tt
                        Table "public.tt"
   Column    |      Type       | Collation | Nullable |  Default
-------------+-----------------+-----------+----------+-----------
 name        | varchar2(64)    |           | not null |
 status      | nvarchar2(2000) |           |          |
 update_time | date            |           |          | sysdate()
# 测试在 date 数据类型的字段上创建分区表
create table test_range(id serial, create_time date) partition by range(create_time);
create table test_range_20220301 PARTITION of test_range FOR VALUES FROM ('2022-03-01 00:00:00') TO ('2022-03-02 00:00:00');
create table test_range_20220302 PARTITION of test_range FOR VALUES FROM ('2022-03-02 00:00:00') TO ('2022-03-03 00:00:00');
create table test_range_20220303 PARTITION of test_range FOR VALUES FROM ('2022-03-03 00:00:00') TO ('2022-03-04 00:00:00');
create table test_range_20220304 PARTITION of test_range FOR VALUES FROM ('2022-03-04 00:00:00') TO ('2022-03-05 00:00:00');
create table test_range_20220305 PARTITION of test_range FOR VALUES FROM ('2022-03-05 00:00:00') TO ('2022-03-06 00:00:00');
create table test_range_default partition of test_range default;
unvdbsvr=# \d+ test_range
                                            Partitioned table "public.test_range"
   Column    |  Type   | Collation | Nullable |                Default                 | Storage | Stats target | Description
-------------+---------+-----------+----------+----------------------------------------+---------+--------------+-------------
 id          | integer |           | not null | nextval('test_range_id_seq'::regclass) | plain   |              |
 create_time | date    |           |          |                                        | plain   |              |
Partition key: RANGE (create_time)
Partitions: test_range_20220301 FOR VALUES FROM ('2022-03-01') TO ('2022-03-02'),
            test_range_20220302 FOR VALUES FROM ('2022-03-02') TO ('2022-03-03'),
            test_range_20220303 FOR VALUES FROM ('2022-03-03') TO ('2022-03-04'),
            test_range_20220304 FOR VALUES FROM ('2022-03-04') TO ('2022-03-05'),
            test_range_20220305 FOR VALUES FROM ('2022-03-05') TO ('2022-03-06'),
            test_range_default DEFAULT
# 向分区表中插入数据
unvdbsvr=# insert into test_range (create_time) values (sysdate());
INSERT 0 1
# 查询分区表里的数据
unvdbsvr=# select * from test_range;
 id | create_time
----+-------------
  2 | 2022-07-31
(1 row)
unvdbsvr=# select to_char(create_time,'YYYY-MM-DD HH24:MI:SS') from test_range;
       to_char
---------------------
 2022-07-31 00:00:00
(1 row)

为什么这里的 date 数据类型似乎只存储 ‘年月日’，而 Oracle 的 date 数据类型会存储 ‘年月日时分秒’。这里就要说一下使用 orafce 的注意事项，详见后面章节<使用 orafce 的注意事项>。

支持 DUAL 表

unvdbsvr=# \d+ dual
                                  View "public.dual"
 Column |       Type        | Collation | Nullable | Default | Storage  | Description
--------+-------------------+-----------+----------+---------+----------+-------------
 dummy  | character varying |           |          |         | extended |
View definition:
 SELECT 'X'::character varying AS dummy;
unvdbsvr=# \dv public.*
               List of relations
 Schema |        Name        | Type |  Owner
--------+--------------------+------+----------
 public | dual               | view | unvdbsvr
 public | pg_stat_statements | view | unvdbsvr
(2 rows)
unvdbsvr=# select 1 from dual;
 ?column?
----------
        1
(1 row)
unvdbsvrs=# select * from dual;
 dummy
-------
 X
(1 row)
unvdbsvr=# SELECT CURRENT_DATE "date" FROM DUAL;
    date
------------
 2022-04-01

SQL 函数

数学函数

函数名称	说明	对比无插件UDB-TX22.4
BITAND	Performs a bitwise AND operation	增强
COSH	Calculates the hyperbolic cosine of a number	自带
SINH	Calculates the hyperbolic sine of a number	自带
TANH	Calculates the hyperbolic tangent of a number	自带

字符串函数

函数名称	说明	对比无插件UDB-TX22.4
INSTR	Returns the position of a substring in a string	新增
LENGTH	Returns the length of a string in number of characters	增强
LENGTHB	Returns the length of a string in number of bytes	新增
LPAD	Left-pads a string to a specified length with a sequence of characters	增强
LTRIM	Removes the specified characters from the beginning of a string	增强
NLSSORT	Returns a byte string used to sort strings in linguistic sort sequence based on locale	新增
REGEXP_COUNT	searches a string for a regular expression, and returns a count of the matches	新增
REGEXP_INSTR	returns the beginning or ending position within the string where the match for a pattern was located	新增
REGEXP_LIKE	condition in the WHERE clause of a query, causing the query to return rows that match the given pattern	新增
REGEXP_SUBSTR	returns the string that matches the pattern specified in the call to the function	新增
REGEXP_REPLACE	replace substring(s) matching a POSIX regular expression	增强
RPAD	Right-pads a string to a specified length with a sequence of characters	增强
RTRIM	Removes the specified characters from the end of a string	增强
SUBSTR	Extracts part of a string using characters to specify position and length	增强
SUBSTRB	Extracts part of a string using bytes to specify position and length	新增

Date/time 函数

函数名称	说明	对比无插件UDB-TX22.4
ADD_MONTHS	Adds months to a date	新增
DBTIMEZONE	Returns the value of the database time zone	新增
LAST_DAY	Returns the last day of the month in which the specified date falls	新增
MONTHS_BETWEEN	Returns the number of months between two dates	新增
NEXT_DAY	Returns the date of the first instance of a particular day of the week that follows the specified date	新增
ROUND	Rounds a date	增强
SESSIONTIMEZONE	Returns the time zone of the session	新增
SYSDATE	Returns the system date	新增
TRUNC	Truncates a date	增强

数据类型格式化函数

函数名称	说明	对比无插件UDB-TX22.4
TO_CHAR	Converts a value to a string	增强
TO_DATE	Converts a string to a date in accordance with the specified format	增强
TO_MULTI_BYTE	Converts a single-byte string to a multibyte string	新增
TO_NUMBER	Converts a value to a number in accordance with the specified format	增强
TO_SINGLE_BYTE	Converts a multibyte string to a single-byte string	新增

条件表达式函数

函数名称	说明	对比无插件UDB-TX22.4
DECODE	Compares values, and if they match, returns a corresponding value	增强
GREATEST	Returns the greatest of the list of one or more expressions	增强
LEAST	Returns the least of the list of one or more expressions	增强
LNNVL	Evaluates if a value is false or unknown	新增
NANVL	Returns a substitute value when a value is not a number (NaN)	新增
NVL	Returns a substitute value when a value is NULL	新增
NVL2	Returns a substitute value based on whether a value is NULL or not NULL	新增

聚合函数

函数名称	说明	对比无插件UDB-TX22.4
LISTAGG	Returns a concatenated, delimited list of string values	新增
MEDIAN	Calculates the median of a set of values	新增

返回内部信息的函数

函数名称	说明	对比无插件UDB-TX22.4
DUMP	Returns internal information of a value	新增

SQL 运算符

名称	说明	对比无插件UDB-TX22.4
Datetime operator	Datetime operator for the DATE type	-

Packages 包

名称	说明	对比无插件UDB-TX22.4
DBMS_ALERT	Sends alerts to multiple sessions	-
DBMS_ASSERT	Validates the properties of an input value	-
DBMS_OUTPUT	Sends messages to clients	-
DBMS_PIPE	Creates a pipe for inter-session communication	-
DBMS_RANDOM	Generates random numbers	-
DBMS_UTILITY	Provides various utilities	-
UTL_FILE	Enables text file operations	-

unvdbGIS

unvdbgis 是一组基于PostGIS定制的适用于UDB-TX 数据库的GIS扩展。每个扩展提供的技术细节，可联机查询。类似命令如下：

unvdb=# \dx+ unvdbgis

unvdbgis

unvdbgis的基本核心功能，仅支持地理图形（矢量要素），在其他Extension前启用。这个是基础插件，必须要安装的。

unvdbgis_raster

对栅格数据的支持。安装过unvdbgis后可直接启用该扩展，默认不启用。

unvdbgis_topology

拓扑功能的支持。安装过unvdbgis后可直接启用该扩展，默认不启用。

unvdbgis_tiger_geocoder

TIGER指的是（拓扑集成地理编码和参考），这个是美国人口普查局的GIS数据，提供了美国全国的行政区划、交通道路、水系等空间数据。这个Extension提供了TIGER数据的地理编码支持，需要注意的是这个Extension启用前，需要先启用fuzzystrmatch（字符串模糊查询）Extension（至少需要启用这个），以及可选的address_standardizer（TIGER数据地址规则化）、address_standardizer_data_us（地址规则化示例数据集）Extension。

（先安装了fuzzystrmatch后，才可以启用unvdbgis_tiger_geocoder）

unvdbgis_sfcgal

这个Extension主要是集成了CGAL（Computational Geometry Algorithms Library，计算几何算法库），来进行三维空间数据的空间运算，例如ST_3DDifference、ST_3DUnion 等，可见是通常空间运算在三维空间上的拓展。可产生各种3d模型，进而利用产生的数据进行3d渲染。

ud_dirtyread-查看脏数据

简介

ud_dirtyread 是脏读插件，用于读取那些已经删除但未被清理的数据。当误操作delete数据时，可以使用它从脏数据中恢复出来。不支持 DDL 操作和 truncate 操作的恢复。

前提要求

未手动或自动执行 vacuum 清理操作

查询上一次 vacuum 执行时间，如已经执行了清理，则数据无法恢复。

select relname,last_vacuum, last_autovacuum, last_analyze, vacuum_count, autovacuum_count,  last_autoanalyze from pg_stat_user_tables;

注意事项

不建议在生产环境直接执行本节的所有操作。
建议将当前所有文件复制到一个独立的机器进行恢复操作，避免对当前环境造成更大的数据损坏。
在将结果恢复到生产环境时，建议先备份生产环境当前的所有数据。

安装/启用

udb-tx-v22.4.22及以后的版本,已启用 ud_dirtyread, 直接使用即可。 udb-tx-v22.4.22及以前的版本,按以下步骤安装启用。

安装

【附件】ud_dirtyread.zip 如下载失败请联系技术支持获取。

启用

ud_dirtyread.so 放到安装目录的lib/ 下
ud_dirtyread.control 和 ud_dirtyread–*.sql 放到安装目录的share/extension/ 下
chown -R udb.udb 安装目录/lib/ud_dirtyread.so
chown -R udb.udb 安装目录/share/extension/ud_dirtyread*

验证安装

waltest=#  CREATE EXTENSION ud_dirtyread;
CREATE EXTENSION

使用示例

创建测试数据

waltest=# select * from tb1;
 id | name 
----+------
  1 | a
  2 | b
(2 rows)
waltest=# \dt tb1;
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 public | tb1  | table | unvdb
(1 row)
waltest=# \d tb1;
                        Table "public.tb1"
 Column |          Type          | Collation | Nullable | Default 
--------+------------------------+-----------+----------+---------
 id     | integer                |           | not null | 
 name   | character varying(255) |           |          | 
Indexes:
    "tb1_pkey" PRIMARY KEY, btree (id)

查看当前数据元组信息

waltest=# select xmin,xmax,ctid,* from tb1;     
 xmin | xmax | ctid  | id | name 
------+------+-------+----+------
 1650 |    0 | (0,1) |  1 | a
 1651 |    0 | (0,2) |  2 | b
(2 rows)

查看是否为脏数据，dead列为f,表示非死数据

waltest=# select * from ud_dirtyread('tb1') as foo(tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boolean,id int,name varchar(255)); 
 tableoid | ctid  | xmin | xmax | cmin | cmax | dead | id | name 
----------+-------+------+------+------+------+------+----+------
    66027 | (0,1) | 1650 |    0 |    0 |    0 | f    |  1 | a
    66027 | (0,2) | 1651 |    0 |    0 |    0 | f    |  2 | b
(2 rows)

删除数据

waltest=# delete from tb1 where id=1;
DELETE 1
waltest=# select * from tb1;
 id | name 
----+------
  2 | b
(1 row)

修改数据

waltest=# update tb1 set name='bb' where id=2;
UPDATE 1

查看已删除未清理的脏数据

waltest=# select * from ud_dirtyread('tb1') as foo(tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boolean,id int,name varchar(255));
 tableoid | ctid  | xmin | xmax | cmin | cmax | dead | id | name 
----------+-------+------+------+------+------+------+----+------
    66027 | (0,1) | 1650 | 1652 |    0 |    0 | t    |  1 | a
    66027 | (0,2) | 1651 | 1653 |    0 |    0 | t    |  2 | b
    66027 | (0,3) | 1653 |    0 |    0 |    0 | f    |  2 | bb
(3 rows)

会发现id=1的那条数据状态 dead=t 更新的id=2那条数据的事务已经由1651变为1653，原来的状态变为了删除，值已经变更为bb

删除列

waltest=# alter table tb1 drop column name;
ALTER TABLE
waltest=# select * from tb1;
 id 
----
  2
(1 row)

查看已删除列的脏数据，此时要用 dropped_n 来代替

waltest=# select * from ud_dirtyread('tb1') as foo(tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boolean,id int,dropped_2 varchar(255)); 
 tableoid | ctid  | xmin | xmax | cmin | cmax | dead | id | dropped_2 
----------+-------+------+------+------+------+------+----+-----------
    66027 | (0,1) | 1650 | 1652 |    0 |    0 | t    |  1 | a
    66027 | (0,2) | 1651 | 1653 |    0 |    0 | t    |  2 | b
    66027 | (0,3) | 1653 |    0 |    0 |    0 | f    |  2 | bb
(3 rows)

ud_walminer-从wal日志中提取sql实现数据修复

简介

ud_walminer 可以解析 wal 日志，尽可能多的获取有用信息,进一步生成undo语句，实现恢复删除或丢失的数据。功能如下：

从wal日志中提取DML和少部分DDL，并生成对应的回滚语句；
磁盘部分损坏或执行了TRUNCATE等清除操作时，可以使用ud_walminer尝试从wal中提取数据，尽最大可能找回数据。不保证一定能恢复成功。

前提要求

表结构需要尽可能完整，例如字段名称，字段类型等
wal日志文件越多越好

注意事项

不建议在生产环境直接执行本节的所有操作。
建议将当前所有文件复制到一个独立的机器进行恢复操作，避免对当前环境造成更大的数据损坏。
在将结果恢复到生产环境时，建议先备份生产环境当前的所有数据。
在一次解析开始时会首先创建或truncate walminer_contents表，当得到想要的数据时建议先导出备份它们再执行第二次解析。

安装/启用

udb-tx-v22.4.22及以后的版本,已启用 ud_walminer, 直接使用即可。 udb-tx-v22.4.22及以前的版本,按以下步骤安装启用。

安装

【附件】ud_walminer.zip 如下载失败请联系技术支持获取。

启用

walminer.so 放到安装目录的lib/ 下
walminer.control和walminer–3.0.sql 放到安装目录的share/extension/ 下
chown -R udb.udb 安装目录/lib/walminer.so
chown -R udb.udb 安装目录/share/extension/walminer.*

验证安装

[udb@0a2593d70d5b udb]$ ud_sql 
ud_sql (2.4)
Type "help" for help.
unvdb=# create extension walminer;
-- 创建扩展插件
CREATE EXTENSION
unvdb=# 

使用说明

基础操作

create extension walminer; -- 创建扩展
select walminer_wal_add('/data/udb/ud_wal'); --添加要解析的wal日志目录或单个文件名
select walminer_wal_remove('/opt/test/wal'); --移除wal日志目录或单个文件名
select walminer_wal_list(); --列出wal文件

解析操作

#普通解析
select walminer_all(); --解析已添加的全部wal日志
select walminer_by_time(starttime, endtime); --在add的wal日志中查找对应时间范围的wal记录
select walminer_by_lsn(startlsn, endlsn); --在add的wal日志中查找对应lsn范围的wal记录
select walminer_by_xid(xid); --在add的wal日志中查找对应xid的wal记录
#精确解析
select walminer_by_time(starttime, endtime,'true');  --精确查找对应时间范围的wal记录
select walminer_by_lsn(startlsn, endlsn,'true');  --精确查找对应lsn范围的wal记录
select walminer_by_xid(xid,'true'); --精确查找对应xid的wal记录
#普通解析会严格匹配给定的范围，但可能没找到之前的checkpoint点，所以会出现记录不全的情况；
#精确解析会自动查找给定范围之前的checkpoint点，更完美的解析出结果。当普通解析结果不全时可以尝试精确解析。
#当本机没有数据结构时，可以加载其它机器生成的的数据结构
select walminer_build_dictionary('/tmp/store_dictionary'); --根据当前数据库结构生成数据字典
select walminer_load_dictionary('/tmp/store_dictionary'); --加载数据字典
#reloid 参数可以查找指定表，要用表的oid
select walminer_by_time(starttime, endtime,'false',reloid); --非精确模式查找对应时间范围的reloid表oid的wal记录
select walminer_by_lsn(startlsn, endlsn,'true',reloid); --在add的wal日志中查找对应lsn范围的wal记录
select walminer_by_xid(xid,'true',reloid);--在add的wal日志中查找对应xid的wal记录
#当表结构不完整时创建临时表并使用临时表映射解析。
create table tmp_avatar(i int); --创建临时表
select walminer_table_avatar(tmp_avatar, missed_relfilenode); --映射到临时表。此时的解析结果将按临时表格式输出。
select wal2sql(); --执行解析，wal文件越多，需要的时间也越多。
select * from walminer_contents; --查看解析结果
-- 表walminer_contents 
(
 sqlno int,         --本条sql在其事务内的序号
 xid bigint,        --事务ID
 topxid bigint,        --如果为子事务，这是是其父事务；否则为0
 sqlkind int,        --sql类型1->insert;2->update;3->delete(待优化项目)
 minerd bool,        --解析结果是否完整(缺失checkpoint情况下可能无法解析出正确结果)
 timestamp timestampTz, --这个SQL所在事务提交的时间
 op_text text,        --sql
 undo_text text,    --undo sql
 complete bool,        --如果为false，说明有可能这个sql所在的事务是不完整解析的
 schema text,        --目标表所在的模式
 relation text,        --目标表表名
 start_lsn pg_lsn,    --这个记录的开始LSN
 commit_lsn pg_lsn    --这个事务的提交LSN
)
select walminer_stop(); --结束分析操作，并释放内存。

实践演示

delete 操作恢复

模拟删除操作

waltest=# select * from tb1;         
 id | name 
----+------
  1 | a
  2 | b
(2 rows)
waltest=# delete from tb1;
DELETE 2
waltest=# select * from tb1;
 id | name 
----+------
(0 rows)

配置ud_walminer

waltest=# create extension walminer;--创建扩展
CREATE EXTENSION
waltest=# select walminer_wal_add('/data/udb/ud_wal');--添加wal目录
  walminer_wal_add   
---------------------
 24 file add success
(1 row)
waltest=# select walminer_wal_list();--列出wal文件
              walminer_wal_list              
---------------------------------------------
 (/data/udb/ud_wal/00000001000000090000001C)
 (/data/udb/ud_wal/00000001000000090000001D)
 (/data/udb/ud_wal/00000001000000090000001E)
 (/data/udb/ud_wal/00000001000000090000001F)
 (/data/udb/ud_wal/000000010000000900000020)
 (/data/udb/ud_wal/000000010000000900000021)
 (/data/udb/ud_wal/000000010000000900000022)
 (/data/udb/ud_wal/000000010000000900000023)
 (/data/udb/ud_wal/000000010000000900000024)
 (/data/udb/ud_wal/000000010000000900000025)
 (/data/udb/ud_wal/000000010000000900000026)
 (/data/udb/ud_wal/000000010000000900000027)
 (/data/udb/ud_wal/000000010000000900000028)
 (/data/udb/ud_wal/000000010000000900000029)
 (/data/udb/ud_wal/00000001000000090000002A)
 (/data/udb/ud_wal/00000001000000090000002B)
 (/data/udb/ud_wal/00000001000000090000002C)
 (/data/udb/ud_wal/00000001000000090000002D)
 (/data/udb/ud_wal/00000001000000090000002E)
 (/data/udb/ud_wal/00000001000000090000002F)
 (/data/udb/ud_wal/000000010000000900000030)
 (/data/udb/ud_wal/000000010000000900000031)
 (/data/udb/ud_wal/000000010000000900000032)
 (/data/udb/ud_wal/000000010000000900000033)
(24 rows)
waltest=# select walminer_all(); --提取wal日志
NOTICE:  Switch wal to 00000001000000090000001C on time 2023-03-27 13:58:30.446893+08
NOTICE:  Switch wal to 00000001000000090000001D on time 2023-03-27 13:58:30.447915+08
NOTICE:  Switch wal to 00000001000000090000001E on time 2023-03-27 13:58:30.44841+08
    walminer_all     
---------------------
 ud_minerwal success
(1 row)
waltest=# select * from walminer_contents; --查看提取结果
 sqlno | xid  | topxid | sqlkind | minerd |           timestamp           |              op_text              |                    undo_text                    | complete | schema | relat
ion | start_lsn  | commit_lsn 
-------+------+--------+---------+--------+-------------------------------+-----------------------------------+-------------------------------------------------+----------+--------+------
----+------------+------------
     1 | 1528 |      0 |       3 | t      | 2023-03-27 13:56:45.925757+08 | DELETE FROM public.tb1 WHERE id=1 | INSERT INTO public.tb1(id ,name) VALUES(1 ,'a') | t        | public | tb1  
    | 9/1C0058F8 | 9/1C005A10
     2 | 1528 |      0 |       3 | t      | 2023-03-27 13:56:45.925757+08 | DELETE FROM public.tb1 WHERE id=2 | INSERT INTO public.tb1(id ,name) VALUES(2 ,'b') | t        | public | tb1  
    | 9/1C0059A0 | 9/1C005A10
(2 rows)
--执行undo_text修复数据
waltest=# INSERT INTO public.tb1(id ,name) VALUES(1 ,'a');
INSERT 0 1
waltest=# INSERT INTO public.tb1(id ,name) VALUES(2 ,'b');
INSERT 0 1
waltest=# select * from tb1;--验证结果                                                                                                    
 id | name 
----+------
  1 | a
  2 | b
(2 rows)
select walminer_stop();--结束分析并释放内存。

DDL操作恢复

注意：ddl删除的列，只能恢复列信息，不能恢复列数据。

模拟删除列操作

unvdb=# \c waltest;
You are now connected to database "waltest" as user "unvdb".
waltest=# select * from tb1;
 id | name | age 
----+------+-----
  1 | a    |   1
  2 | b    |   2
(2 rows)
waltest=# ALTER TABLE "public"."tb1" DROP COLUMN "age";
ALTER TABLE
waltest=# select * from tb1;                           
 id | name 
----+------
  1 | a
  2 | b
(2 rows)

恢复配置

waltest=# CREATE EXTENSION  walminer;
CREATE EXTENSION
waltest=# select walminer_wal_add('/data/udb/ud_wal');
  walminer_wal_add   
---------------------
 11 file add success
(1 row)
waltest=# select walminer_wal_list();
              walminer_wal_list              
---------------------------------------------
 (/data/udb/ud_wal/000000010000000900000029)
 (/data/udb/ud_wal/00000001000000090000002A)
 (/data/udb/ud_wal/00000001000000090000002B)
 (/data/udb/ud_wal/00000001000000090000002C)
 (/data/udb/ud_wal/00000001000000090000002D)
 (/data/udb/ud_wal/00000001000000090000002E)
 (/data/udb/ud_wal/00000001000000090000002F)
 (/data/udb/ud_wal/000000010000000900000030)
 (/data/udb/ud_wal/000000010000000900000031)
 (/data/udb/ud_wal/000000010000000900000032)
 (/data/udb/ud_wal/000000010000000900000033)
(11 rows)
waltest=# select wal2sql_with_ddl(); --开启ddl解析
 wal2sql_with_ddl 
------------------
 t
(1 row)
waltest=# select walminer_by_time('2023-03-27 15:00', '2023-03-27 15:15','true'); --必须使用精确解析
NOTICE:  Switch wal to 000000010000000900000029 on time 2023-03-27 15:12:59.812091+08
NOTICE:  Con not find relfilenode 65692 in dictionary, ignored related records
NOTICE:  Con not find relfilenode 65693 in dictionary, ignored related records
  walminer_by_time   
---------------------
 ud_minerwal success
(1 row)
waltest=# select * from walminer_contents;--检查解析结果
 sqlno | xid  | topxid | sqlkind | minerd |           timestamp           |                     op_text                     |             undo_text             | complete |   schema   |  
 relation   | start_lsn  | commit_lsn 
-------+------+--------+---------+--------+-------------------------------+-------------------------------------------------+-----------------------------------+----------+------------+--
------------+------------+------------
     1 | 1578 |      0 |       4 | t      | 2023-03-27 15:01:03.11249+08  | TRUNCATE TABLE public.tb1                       |                                   | t        | pg_catalog | p
g_class     | 9/291A7EB8 | 9/291AA7E0
     1 | 1583 |      0 |       1 | t      | 2023-03-27 15:09:24.706606+08 | INSERT INTO public.tb1(id ,name) VALUES(1 ,'a') | DELETE FROM public.tb1 WHERE id=1 | t        | public     | t
b1          | 9/291DA7F8 | 9/291DA910
     1 | 1584 |      0 |       1 | t      | 2023-03-27 15:09:27.201411+08 | INSERT INTO public.tb1(id ,name) VALUES(2 ,'b') | DELETE FROM public.tb1 WHERE id=2 | t        | public     | t
b1          | 9/291DA948 | 9/291DA9F8
     1 | 1585 |      0 |       2 | t      | 2023-03-27 15:09:31.79967+08  | UPDATE public.tb1 SET  WHERE id=2               | UPDATE public.tb1 SET  WHERE id=2 | t        | public     | t
b1          | 9/291DA9F8 | 9/291DAA78
     1 | 1586 |      0 |       4 | t      | 2023-03-27 15:10:15.078618+08 | ALTER TABLE public.tb1 DROP COLUMN age          |                                   | t        | pg_catalog | p
g_attribute | 9/291DCB98 | 9/291E0BF0
(5 rows)

修复数据

op_text列显示执行过 ALTER TABLE public.tb1 DROP COLUMN age，使用alter add 把它添加进来即可，但此列的数据无法恢复。

异常处理

无

cstore-列式存储

简介

cstore插件是列式存储扩展，列式存储仅从磁盘读取相关的数据，可以更快速的分析统计数据。数据大小比普通表减少4-10倍的磁盘空间，能够更快速的将数据加载到内存处理。

优点：

压缩：磁盘占用空间减少4-10倍
列投影：只读取和查询相关的数据，提高IO性能
跳过索引：存储行组的最小最大统计信息，并使用它们跳过不相关的行

使用限制：

不支持 DELETE 删除数据
不支持 UPDATE 更新表
不支持 INSERT INTO 单行数据，需多行批量插入。因为插入操作时会flush，如果插入一行也会占用一个条带的磁盘空间，会导致性能下降的严重的磁盘浪费。

使用

CREATE EXTENSION cstore_fdw; -- 创建扩展
CREATE SERVER cstore_server FOREIGN DATA WRAPPER cstore_fdw; -- 创建server指定外部数据
CREATE FOREIGN TABLE t1 -- 创建 FOREIGN 表
(
    customer_id TEXT,
    review_date DATE,
    review_rating INTEGER,
    review_votes INTEGER,
    review_helpful_votes INTEGER,
    product_id CHAR(10),
    product_title TEXT,
    product_sales_rank BIGINT,
    product_group TEXT,
    product_category TEXT,
    product_subcategory TEXT,
    similar_product_ids CHAR(10)[]
) SERVER cstore_server -- 指定server
OPTIONS(filename '/data/udb/cstore/cstb1.cstore',compression 'pglz'); -- 其它选项
\COPY t1 FROM '/data/udb/file/data.csv' WITH CSV; --导入数据

OPTIONS 参数

filename（可选）：表数据存储位置目录。默认值为 $UDBDATA/cstore_fdw
compression压缩（可选）：用于压缩价值流的压缩。默认值为 none，不压缩；可选值 pglz
stripe_row_count（可选）：每个条带的行数。默认值为 150000。减少它会减少用于加载数据和查询的内存量，但也会降低性能。
block_row_count（可选）：每个列块的行数。默认值为 10000。cstore 以块粒度压缩、创建，跳过索引并从磁盘读取。增加此值有助于压缩并减少从磁盘读取的结果。但是，较高的值也会降低跳过不相关行块的可能性。

空间对比

相同数据量下，cstore 列式存储占用磁盘空间比普通表减少4倍左右。

普通表大小

du -sh  /data/udb/udb22.4.21-1/data/udb/base/66354/
521M    /data/udb/udb22.4.21-1/data/udb/base/66354/
test=# SELECT pg_size_pretty(pg_table_size('t1'));
 pg_size_pretty 
----------------
 412 MB
(1 row)

列式存储表大小

du -sh  /data/udb/udb22.4.21-1/data/udb/cstore
100M    /data/udb/udb22.4.21-1/data/udb/cstore
cstore=# SELECT pg_size_pretty(cstore_table_size('t1'));
 pg_size_pretty 
----------------
 100 MB
(1 row)

查询对比

相同数据量下，cstore 列式存储查询比普通表性能提升 15倍左右。

test=# select count(*) from t1 where review_date ='2023-04-06';
 count 
-------
  4024
(1 row)
Time: 59.165 ms
test=# \c cstore
You are now connected to database "cstore" as user "unvdb".
cstore=# select count(*) from t1 where review_date ='2023-04-06';
 count 
-------
  4024
(1 row)
Time: 4.758 ms