openGauss/MogDB/PostgreSQL对比学习

本文将从日常使用的角度对比学习国产数据库openGauss/MogDB与开源数据库PostgreSQL之间的差异点。

1.服务的启动模式

在主备场景下，PG服务启动命令不区分角色，MogDB服务启动需要指定启动模式，参考如下链接：
《openGauss/MogDB数据库服务启动模式分析》

2.服务的关闭模式

PostgreSQL

smart quit after all clients have disconnected
fast quit directly, with proper shutdown (default)
immediate quit without complete shutdown; will lead to recovery on restart

openGauss/MogDB

fast quit directly, with proper shutdown
immediate quit without complete shutdown; will lead to recovery on restart

smart模式下，数据库会等待所有的客户端链接主动断开连接，此时数据库服务不允许接受新连接请求，但允许现有连接继续操作，直到所有连接都主动退出之后，数据库主进程才会发送SIGUSR2信号给checkpointer进程去执行shutdown checkpoint类型的检查点。这样所有客户端连接提交的数据都已刷盘，下次PG启动时不需要做crash恢复。

fast模式是默认关闭方式，这种模式下数据库会通过发送SIGTERM信息来立刻终止所有打开的事务，同时也不允许接受新的连接请求。终止客户端连接之后也会发起shutdown checkpoint类型的检查点来避免下次启动服务时做restart point。这种模式也是生产中推荐的使用方式，会终止当前的客户端连接。

immediate模式，这种模式数据库会强制关闭相关进程，数据库主进程会发送SIGQUIT信号给所有进程强制退出。这样情况下数据库关闭之前并没有做一次干净的checkpoint操作。下次服务启动需要从事务日志文件读取最后一次提交的位点及最后一次checkpoint位点之间的事务日志来做apply，刷新到磁盘数据文件（注意：这里的数据文件是指data file，不是xlog file）。

上述三种模式，smart其实极少使用，生产通常也是使用fast模式，immediate模式只有在紧急情况下才会考虑。

3.xlog

由于历史原因，PG对xlog的命名更新为了wal，包括相应的二进制工具、命令参数、系统相关函数等。

例如查看事务日志点位信息

PostgreSQL

postgres=# select pg_current_wal_lsn();
 pg_current_wal_lsn 
--------------------
 0/174E780
(1 row)
复制

openGauss/MogDB

openGauss=# select pg_current_xlog_location();
 pg_current_xlog_location 
--------------------------
 0/22331C0
(1 row)
复制

MogDB设置xlog目录路径设置的几种方式可以参考：《MogDB数据库安装部署之xlog目录设置》

4.归档

归档开关参数都是archive_mode，但是归档命令参数有区别。

PostgreSQL

archive_command = 'cp %p /opt/archive/%f'
复制

pg里面是配置archive_command参数，可以配置shell命令。

openGauss/MogDB

archive_dest='/opt/archive'
复制

MogDB是配置archive_dest参数，直接配置归档路径即可。

5.public模式的使用权限

PostgreSQL

pg里面创建的用户默认是具有public模式的使用及创建权限，owner用户可以管理自己的对象。

postgres=> select * from pg_namespace where nspname='public' ;
 oid  | nspname | nspowner |               nspacl                
------+---------+----------+-------------------------------------
 2200 | public  |       10 | {postgres=UC/postgres,=UC/postgres}
(1 row)
复制

上面从schema元数据列nspacl也可以看到默认是具有UC权限（U:usage,C create）

openGauss/MogDB

gs_initdb初始化时使用-S, --security后，用户默认将不具有public模式的使用权限。

openGauss=#  select nspacl from pg_namespace where nspname='public' ;
    nspacl    
--------------
 {omm=UC/omm}
(1 row)
复制

上面从schema元数据列nspacl也可以看到默认不具有模式的使用权限。

下面进行测试，可以看到有报错提示

openGauss=# \c - test
Password for user test: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "postgres" as user "test".
openGauss=> create table public.t(id int);
ERROR:  permission denied for schema public
DETAIL:  N/A
复制

6.ssl

《MogDB数据库与PostgreSQL对比测试SSL之自签名私有证书测试》
《MogDB数据库与PostgreSQL对比测试SSL之自签名CA证书单向认证测试》
《MogDB数据库与PostgreSQL对比测试SSL之自签名CA证书双向认证测试》

简单总结如下，详细测试对比参考上面的文章链接。

• PostgreSQL需要编译支持openssl，而openGauss/MogDB已经内置支持。
• openGauss/MogDB不会识别openssl默认配置文件，需要显示指定openssl.cnf文件路径，否则会报错找不到配置文件。
• openGauss/MogDB与PostgreSQL对clientcert=verify-full认证支持有差异。

7.sha256口令加密

用户口令加密的参数名称有区别，同时sha256算法有不同的实现，互相不兼容。

PostgreSQL

password_encryption = 'scram-sha-256'
复制

pg里面是配置password_encryption参数，可以配置md5或者scram-sha-256。

openGauss/MogDB

password_encryption_type=1
复制

MogDB是配置password_encryption_type参数，0 只支持标准的md5口令存储，与PG保持兼容，1 支持md5和国密的sha256，2 只支持国密sha256，不兼容PG。

8.copy

copy的分隔符：

PostgreSQL

postgres=# \copy t to t.dat delimiter '@$'
ERROR:  COPY delimiter must be a single one-byte character
复制

openGauss/MogDB

copy的容错机制

openGauss/MogDB

表结构

CREATE TEMP TABLE foo (a bigint, b text);
复制

数据文件tab.dat

 1	one
 2	
 3	three	111
 four    4
 5	five
复制

普通的copy导入，因为有多余的数据项会报错

omm=# copy foo from '/home/omm/tab.dat';
ERROR:  extra data after last expected column
CONTEXT:  COPY foo, line 3: " 3	three	111"
复制

copy使用ignore_extra_data忽略多余的列

omm=#  copy foo from '/home/omm/tab.dat' with(ignore_extra_data);
ERROR:  invalid input syntax for type bigint: " four    4"
CONTEXT:  COPY foo, line 4, column a: " four    4"
复制

copy使用log_errors和reject_limit

omm=#  copy foo from '/home/omm/tab.dat' with(ignore_extra_data,log_errors,reject_limit 'unlimited');
COPY 4
复制

reject_limit参数对copy from的容错机制设置数值上限，一旦错误数据超过选项指定条数，则会按照原有机制报错。取值范围：正整数（1-INTMAX），‘unlimited’（无最大值限制）
此时已经导入成功了4条数据，可以从下面的查询看出。

omm=# select * from foo;
 a |   b   
---+-------
 1 | one
 2 | 
 3 | three
 5 | five
(4 rows)
复制

还有一条报错的信息记录在系统自动创建的pgxc_copy_error_log表中。

omm=# \x
Expanded display is on.
omm=# select * from pgxc_copy_error_log ;
-[ RECORD 1 ]-------------------------------------------------
relname   | pg_temp_gaussdb_8_1_140368156813056.foo
begintime | 2021-06-27 11:29:00.529073+00
filename  | /home/omm/tab.dat
lineno    | 4
rawrecord | 
detail    | invalid input syntax for type bigint: " four    4"
复制

上面rawrecord是没有内容的，使用log_errors_data替代log_errors会记录rawrecord

truncate table pgxc_copy_error_log;
truncate table foo;
copy foo from '/home/omm/tab.dat' with(ignore_extra_data,log_errors_data,reject_limit 'unlimited');

omm=# select * from pgxc_copy_error_log ;
-[ RECORD 1 ]-------------------------------------------------
relname   | pg_temp_gaussdb_8_1_140368156813056.foo
begintime | 2021-06-27 11:34:34.104653+00
filename  | /home/omm/tab.dat
lineno    | 4
rawrecord |  four    4
detail    | invalid input syntax for type bigint: " four    4"
复制

9.Upsert(insert or update)

PostgreSQL

create table test_upsert (id int primary key, info varchar);

postgres=# insert into test_upsert  (id,info) 
postgres-# values (1,'aaa');
INSERT 0 1
复制

下面插入主键重复的数据

postgres=# insert into test_upsert  (id,info) 
postgres-# values (1,'bbb');
ERROR:  duplicate key value violates unique constraint "test_upsert_pkey"
DETAIL:  Key (id)=(1) already exists.
复制

不使用upsert语法，会报错返回给客户端，不太友好

postgres=# insert into test_upsert  (id,info) 
postgres-# values (1,'bbb') 
postgres-# on conflict(id) do update set info=excluded.info;
INSERT 0 1
postgres=# select * from test_upsert;
 id | info 
----+------
  1 | bbb
(1 row)
复制

使用upsert语法，有冲突时可选择do nothing跳过或者do update覆盖更新

openGauss/MogDB

omm=# create table test_upsert (id int primary key, info varchar);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_upsert_pkey" for table "test_upsert"
CREATE TABLE
omm=# insert into test_upsert  (id,info) 
values (1,'aaa');omm-# 
INSERT 0 1
omm=# insert into test_upsert  (id,info) 
values (1,'bbb'); 
ERROR:  duplicate key value violates unique constraint "test_upsert_pkey"
DETAIL:  Key (id)=(1) already exists.

omm=# insert into test_upsert  (id,info) 
values (1,'bbb')
 ON DUPLICATE KEY  update  info=excluded.info;
INSERT 0 1
omm=# select * from test_upsert;
 id | info 
----+------
  1 | bbb
(1 row)
复制

10.分区表

《openGauss/MogDB数据库PostgreSQL表分区语法测试》

简单总结如下，详细测试对比参考上面的文章链接。

• openGauss/MogDB目前只支持声明式分区，支持范围分区、列表分区、哈希分区以及INTERVAL-RANGE的自动扩展间隔分区。PostgreSQL支持继承及声明式分区，不支持自动扩展间隔分区。
• 自动扩展间隔分区的分区字段目前只支持时间类型(date或timestamp)。
• openGauss/MogDB目前不支持子分区，PG支持声明式子分区。
• 对于声明式分区的分区来说，分区必须具有和分区表正好相同的列集合，表结构必须严格一致，而在表继承中，子表可以有父表中没有出现过的额外列，同时表继承允许多继承。