To WAL or not to WAL? When unlogged becomes logged…

Creating and populating a database to test

testdb=# CREATE DATABASE rep_test WITH OWNER ansel;CREATE DATABASE

rep_test=> CREATE TABLE t_norm( pk int GENERATED ALWAYS AS IDENTITY,                  t text,                  primary key( pk ) );rep_test=> CREATE UNLOGGED TABLE            t_unlogged( like t_norm including all );rep_test=> CREATE TEMPORARY TABLE            t_temp( like t_norm including all );           rep_test=> INSERT INTO t_norm( t )               SELECT 'Row #' || v               FROM generate_series( 1, 1000000 ) v;INSERT 0 1000000Time: 4712.185 ms (00:04.712)rep_test=> INSERT INTO t_temp( t )            SELECT 'Row #' || v            FROM generate_series( 1, 1000000 ) v;INSERT 0 1000000Time: 1789.473 ms (00:01.789)rep_test=> INSERT INTO t_unlogged( t )               SELECT 'Unlogged #' || v               FROM generate_series( 1, 1000000 ) v;INSERT 0 1000000Time: 1746.729 ms (00:01.747)

Doing the physical replication

% pg_basebackup -X stream --create-slot --slot 'carmensita_physical_replication_slot' -R -r 100M -D postgres/12/replica -l "Test unlogged tables" -P -d "dbname=backup user=backup host=miguel" -T wal=/postgres/12

‍

       原始集群位于名为 的机器上miguel，而复制槽位于名为 的机器上carmensita。这是我经常用来做一些实验工作的两台机器。
       另请注意，我使用backup数据库和角色来传输信息；可以想象，您需要在以下位置启用复制连接pg_hba.conf：

% tail $PGDATA/pg_hba.confhost    replication     backup  carmensita  trust

% usr/pgsql-12/bin/pg_ctl -D postgres/12/replica start in attesa che il server si avvii.... LOG:  starting PostgreSQL 12.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 10.2.1 20201125 (Red Hat 10.2.1-9), 64-bit LOG:  listening on IPv4 address "0.0.0.0", port 5432 LOG:  listening on IPv6 address "::", port 5432 LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432" LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432" LOG:  redirecting log output to logging collector process HINT:  Future log output will appear in directory "log".

Check the tables on the replication side

% psql -h carmensita -U ansel rep_testrep_test=> \d                Lista delle relazioni Schema |       Nome        |   Tipo   | Proprietario --------|-------------------|----------|-------------- public | t_norm            | tabella  | ansel public | t_norm_pk_seq     | sequenza | 
ansel public | t_unlogged        | tabella  | ansel public | t_unlogged_pk_seq | sequenza | ansel(4 righe)rep_test=> select count(*) from t_norm;  count  --------- 1000000(1 riga)rep_test=> select count(*) from t_unlogged;ERROR:  cannot access temporary or unlogged relations during recovery

       如您所见，临时表丢失了，即使在另一个主连接上仍然可用。这并不奇怪，临时表只能在每个连接的基础上使用，因此不会被复制。
       更有趣的是，未记录的表t_unlogged和相关序列已被复制，但它们只是作为占位符存在，实际上无法对未记录的表进行操作。
       因此，未记录的表在其结构中被复制，而不是在其数据中！

Switching from unlogged to logged

       在主节点上，现在是时候将未记录状态更改为已t_unlogged记录，这可以使用ALTER TABLE命令快速完成。让我们也检查一下relpersistence标志的状态，pg_class看看它是如何从u( u nlogged) 变为p( persistent ) 的：

rep_test=> \d                Lista delle relazioni Schema |       Nome        |   Tipo   | Proprietario --------|-------------------|----------|-------------- public | t_norm            | tabella  | 
ansel public | t_norm_pk_seq     | sequenza | ansel public | t_unlogged        | tabella  | ansel public | t_unlogged_pk_seq | sequenza | ansel(4 righe)rep_test=> select count(*) from t_norm;  count  --------- 1000000(1 riga)rep_test=> select count(*) from t_unlogged;ERROR:  cannot access temporary or unlogged relations during recovery

       这里要注意的有趣部分是，将未记录状态更改为已记录 required 11 secs，这比普通表上的插入时间要多。这里的想法是 PostgreSQL 必须将表中的所有记录插入到 WAL中，因为INSERT每一行的记录刚刚发生。

rep_test=# alter table t_unlogged set logged;ALTER TABLETime: 11485.505 ms (00:11.486)

       之后，在复制备用表上也变得普通：

rep_test=> select count(*) from t_unlogged;  count  --------- 1000000

Switching from logged to unlogged

       如果t_unlogged退货再次取消记录，现在会发生什么：

rep_test=# alter table t_unlogged set unlogged;ALTER TABLETime: 5236.165 ms (00:05.236)rep_test=# truncate t_unlogged;TRUNCATE TABLETime: 21.498 ms

       这里要注意的有趣部分是，同样，存储更改需要花费大量时间。
       在备用服务器上，表再次变得不可用：

rep_test=> select count(*) from t_unlogged;ERROR:  cannot access temporary or unlogged relations during recoveryrep_test=> select relpages, reltuples from pg_class where oid = 't_unlogged'::regclass; relpages | reltuples ----------|-----------        0 |         0

Does the replica knows about the unlogged tables?

       当然可以，而且实际上pg_class知道表使用了多少元组和页。
       但是，该表不会占用复制主机上的存储空间。换句话说，复制端的数据库知道表在主节点上占用了多少，因为pg_class（和其他目录）也被复制了。磁盘上缺少表数据。
       让我们在主端看看这个：

rep_test=# select relpages, reltuples,                   pg_size_pretty( pg_relation_size( 't_unlogged') ),                   pg_relation_filepath( oid )                   from pg_class where oid = 't_unlogged'::regclass; relpages | reltuples | pg_size_pretty | pg_relation_filepath ----------|-----------|----------------|----------------------    12738 |     2e+06 | 100 MB         | base/41441/41555

       在磁盘上文件的大小是

% sudo du -h $PGDATA/base/41441/41555100M    postgres/12/data/base/41441/41555

       复制主机上有什么？信息是一样的，但是磁盘上什么都没有：

rep_test=# select relpages, reltuples,                   pg_size_pretty( pg_relation_size( 't_unlogged') ),                   pg_relation_filepath( oid )                   from pg_class where oid = 't_unlogged'::regclass;                   relpages | reltuples | pg_size_pretty | pg_relation_filepath ----------|-----------|----------------|----------------------    12738 |     2e+06 | 0 bytes        | base/41441/41555                 

       实际上，在磁盘上，表没有占用空间：

% sudo du -h postgres/12/replica/base/41441/41550       postgres/12/replica/base/41441/4155

Unlogged but replicated ~ ordinary

       复制的未记录表失去了未记录的速度优势。
       为什么？因为一旦表被记录，系统必须提供所有机制来同步表。如果你“停止”复制，删除槽和其他相关的东西，表就会加快速度。

声明

       此文为外文翻译，因小编个人水平有限，专栏中难免存在错漏之处，请勿直接复制文档中的命令、方法直接应用于线上环境中操作。

PostgreSQL 逻辑复制模块介绍

PostgreSQL "表膨胀" 的救世主

PostgreSQL之base和global目录完全解析