PostgreSQL 15 preview - create database 增强, 支持策略 WAL_LOG (block-by-block strategy) , FILE_COPY

作者

digoal

日期

2022-03-31

标签

PostgreSQL , create database , wal_log , file_copy , checkpoint

PostgreSQL 15 create database 增强,支持策略 WAL_LOG , FILE_COPY .

之前的版本create database只有file_copy, 也不支持策略语法.

create database 需要选择一个模板, 根据模板创建数据库. 创建数据库需要拷贝模板的文件内容到新建数据库在表空间的对应OID目录中. 策略指的是拷贝策略.

wal_log: 块级别拷贝, 每个block都写wal日志. 非常适合模板库特别小时. 没有checkpoint动作.
file_copy: 文件级别拷贝, 调用文件系统接口完成, 在创建数据库前后都需要执行checkpoint, checkpoint可能对性能有较大影响. 但是非常适合模板库很大的情况, wal日志产生较少.

CREATE DATABASE name  
    [ [ WITH ] [ OWNER [=] user_name ]  
           [ TEMPLATE [=] template ]  
           [ ENCODING [=] encoding ]  
           [ STRATEGY [=] strategy ] ]   -- 新增了策略选项  
           [ LOCALE [=] locale ]  
           [ LC_COLLATE [=] lc_collate ]  
           [ LC_CTYPE [=] lc_ctype ]  
           [ ICU_LOCALE [=] icu_locale ]  
           [ LOCALE_PROVIDER [=] locale_provider ]  
           [ COLLATION_VERSION = collation_version ]  
           [ TABLESPACE [=] tablespace_name ]  
           [ ALLOW_CONNECTIONS [=] allowconn ]  
           [ CONNECTION LIMIT [=] connlimit ]  
           [ IS_TEMPLATE [=] istemplate ]  
           [ OID [=] oid ] ]  

strategy

Strategy to be used in creating the new database. If the WAL_LOG strategy is used, the database will be copied block by block and each block will be separately written to the write-ahead log. This is the most efficient strategy in cases where the template database is small, and therefore it is the default. The older FILE_COPY strategy is also available. This strategy writes a small record to the write-ahead log for each tablespace used by the target database. Each such record represents copying an entire directory to a new location at the filesystem level. While this does reduce the write-ahed log volume substantially, especially if the template database is large, it also forces the system to perform a checkpoint both before and after the creation of the new database. In some situations, this may have a noticeable negative impact on overall system performance.

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=9c08aea6a3090a396be334cc58c511edab05776a

Add new block-by-block strategy for CREATE DATABASE.  
author  Robert Haas <rhaas@postgresql.org>    
Tue, 29 Mar 2022 15:31:43 +0000 (11:31 -0400)  
committer   Robert Haas <rhaas@postgresql.org>    
Tue, 29 Mar 2022 15:48:36 +0000 (11:48 -0400)  
commit  9c08aea6a3090a396be334cc58c511edab05776a  
tree    c15e6e9fa45a18173a5bbd67ff4a4c889e616cde    tree  
parent  bf902c13930c268388644100663f2998868b6e85    commit | diff  
Add new block-by-block strategy for CREATE DATABASE.  
Because this strategy logs changes on a block-by-block basis, it  
avoids the need to checkpoint before and after the operation.  
However, because it logs each changed block individually, it might  
generate a lot of extra write-ahead logging if the template database  
is large. Therefore, the older strategy remains available via a new  
STRATEGY parameter to CREATE DATABASE, and a corresponding --strategy  
option to createdb.  
Somewhat controversially, this patch assembles the list of relations  
to be copied to the new database by reading the pg_class relation of  
the template database. Cross-database access like this isn't normally  
possible, but it can be made to work here because there can't be any  
connections to the database being copied, nor can it contain any  
in-doubt transactions. Even so, we have to use lower-level interfaces  
than normal, since the table scan and relcache interfaces will not  
work for a database to which we're not connected. The advantage of  
this approach is that we do not need to rely on the filesystem to  
determine what ought to be copied, but instead on PostgreSQL's own  
knowledge of the database structure. This avoids, for example,  
copying stray files that happen to be located in the source database  
directory.  
Dilip Kumar, with a fairly large number of cosmetic changes by me.  
Reviewed and tested by Ashutosh Sharma, Andres Freund, John Naylor,  
Greg Nancarrow, Neha Sharma. Additional feedback from Bruce Momjian,  
Heikki Linnakangas, Julien Rouhaud, Adam Brusselback, Kyotaro  
Horiguchi, Tomas Vondra, Andrew Dunstan, Álvaro Herrera, and others.  
Discussion: http://postgr.es/m/CA+TgmoYtcdxBjLh31DLxUXHxFVMPGzrU5_T=CYCvRyFHywSBUQ@mail.gmail.com  

期望 PostgreSQL 增加什么功能?

PolarDB for PostgreSQL云原生分布式开源数据库

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.