Alert: Don’t Migrate “nologing” Table in Oracle Database to PostgreSQL “unlogged”

by

因 PostgreSQL 功能丰富，且在处理复杂 SQL 时的性能表现不俗，越来越多客户正从 Oracle 迁移到基于 PostgreSQL 的国产数据库，例如 GaussDB、Kingbase、HighgoDB 等。但要注意：换皮容易换骨难。有些语句看起来“形似”，底层机制却截然不同，仅做语法层面的转换，可能带来致命后果。本文总结 Oracle → PostgreSQL 迁移中NOLOGGING 与 UNLOGGED 这种看似相似、实则差异巨大的特性。

最近遇到一个典型案例：某套数据库的 Oracle 存储过程中包含大量数据加载操作，因此在流程开头会执行 alter table xx nologging，后续再批量 insert。迁移到 HighgoDB 后，存储过程中的这条语句被直接转换成了 alter table xx set nologged。

注意，这样的转换非常危险。NOLOGGING ≠ UNLOGGED（最容易犯的错误）

原因在于，Oracle 的 nologging 主要减少 Redo 日志生成，从而提高数据加载性能，但其操作本身不会破坏数据的可恢复性。而在 HighgoDB（PostgreSQL 内核）中，set nologged 会直接将表标记为“已记录日志不可用”状态，表在后续异常恢复时将无法追回数据。这是迁移团队最常踩的坑之一。

  • Oracle NOLOGGING：减少 Redo 日志生成，数据仍然持久化。实例崩溃通常不会丢失数据。
  • PostgreSQL UNLOGGED：完全不写 WAL。服务器一旦崩溃或异常重启，表中所有数据会被自动清空！

对standby 从库的影响也不同，在Oracle中配置dataguard时，标准建议配置alter database force logging强制记录日志, table级配置nogging不会生效，如果有nologging的blog传到了standby，也会标记为currupted block，可以修复。但是在PostgreSQL中并没有database级的force logging, 会因为没有写入wal日志，导致不同同步到replicat 从库中。

PostgreSQL中的unlogged table数据丢失

下面我在highgoDB测试环境中模拟 –env highgodb v9

anbob=# insert into t_unlogged values (123456);
INSERT 0 1

[root@pg73 ~]# su - highgo
Last login: Mon May 11 08:55:10 CST 2026 on pts/0

[highgo@pg73 ~]$ pg_ctl stop -m immediate
waiting for server to shut down.... done
server stopped

[highgo@pg73 ~]$ pg_ctl start
waiting for server to start....2026-05-22 15:04:55.100 CST [1345] LOG:  redirecting log output to logging collector process
2026-05-22 15:04:55.100 CST [1345] HINT:  Future log output will appear in directory "/data/highgo/data/log".
. done
server started


anbob=# select * from t_unlogged;
WARNING:  terminating connection due to immediate shutdown command
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
hgdb-client-V9.0.5
The connection to the server was lost. Attempting reset: 
 id 
----
(0 rows)

anbob=# select * from t_unlogged;
 id 
----
(0 rows)

Note: immediate stop 注意表的第一批insert的数据自动清空并丢失。

anbob=# select * from t_unlogged;
 id 
----
(0 rows)

anbob=# insert into t_unlogged values (123456);
INSERT 0 1
anbob=# insert into t_unlogged values (12345);
INSERT 0 1
anbob=# insert into t_unlogged values (1234);
INSERT 0 1

[highgo@pg73 ~]$ pg_ctl stop
waiting for server to shut down.... done
server stopped
[highgo@pg73 ~]$ pg_ctl start
waiting for server to start....2026-05-22 15:06:38.507 CST [1390] LOG:  redirecting log output to logging collector process
2026-05-22 15:06:38.507 CST [1390] HINT:  Future log output will appear in directory "/data/highgo/data/log".
 done
server started

anbob=# select * from t_unlogged;
FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
hgdb-client-V9.0.5
The connection to the server was lost. Attempting reset: 
   id   
--------
 123456
 12345
 1234
(3 rows)

Note： normal stop , 第二批数据会持久化，并可查询。

[highgo@pg73 ~]$ pg_ctl stop -m immediate
waiting for server to shut down.... done
server stopped
[highgo@pg73 ~]$ pg_ctl start
waiting for server to start....2026-05-22 15:15:04.246 CST [1493] LOG:  redirecting log output to logging collector process
2026-05-22 15:15:04.246 CST [1493] HINT:  Future log output will appear in directory "/data/highgo/data/log".
. done
server started
[highgo@pg73 ~]$ 

anbob=# select * from t_unlogged;
 id 
----
(0 rows)

Note: 后期再immedaite stop, 之前虽然持久化的unlogged table的数据，一样会在启动数据库时被清空。

这可能会导致即使很久前创建的表，前几次normal stop，数据没有丢失，但后期突然一天异常崩溃，之前unlogged的table还是会丢失数据。

unlogged table检查

在迁移后要验证实例中每个数据库中是否存在unlogged的表，请使用以下命令：


SELECT oid, relfilenode, relname, relpersistence, relkind 
FROM pg_class 
WHERE relpersistence ='u';

要将unlogged的表还原为logged的表，请使用以下命令：


ALTER TABLE table_name SET LOGGED;

此操作会覆盖整个表，并设置独占锁直至完成。对于大型表，这可能会导致严重的停机时间。

PostgreSQL unlogged影响

Things I triedLog outputReplicationthoughts
Table CopyNo log output is generated.Not propagated to the slaveThe table is not created on the slave side.
Index creationNo log is generated.
Data insertionNo log is generated.
Change settings
(unlogged → normal)		Log will now be generated.Propagated to the slaveA table will be created on the slave side. This process involves copying data from the master side, and if the existing table contains a large amount of data, it may take some time. The load on the system is also a concern.
Change settings
(Normal → Unlogged)		Log will stop.Not propagated to the slaveThe table will no longer exist on the slave side.

小结

如果把oracle的nologging迁移到了Postgresql中的unlogged, 结果Oracle 里的“性能优化手段”，到了 PostgreSQL 体系中可能变成“破坏日志链的行为”。语法虽相似，语义却不相同。

因此，在做数据库迁移时，不能只依赖自动化转换工具或逐句翻译 SQL，更要理解每一条语句在目标库中的实际效果。尤其对于存储过程、批量操作和涉及日志行为的语句，务必逐条复核、测试，甚至重构业务逻辑，避免因“形合实不合”而酿成线上事故。

迁移不是翻译，而是理解后的重建。

Leave a Comment

Free Web Hosting