首页 > 数据库 >PostgreSQL-unlogged table

PostgreSQL-unlogged table

时间：2023-08-06 20:01:12浏览次数：77  
标签：PostgreSQL postgres 22 unlogged 08 2023 CST table 05

PostgreSQL中，有一种表的类型为unlogged table，该种类型的表不会写入wal日志中，所以在写入的速度上比普通的堆表快很多，但是该表在数据库崩溃的时候，会被truncate,数据会丢失，而且该表也不支持流复制，所以在standby节点是无法查到该表数据的。在该表上创建的索引也是unlogged索引。

创建 unlogged table ：

CREATE UNLOGGED TABLE testtable  (id int);

转换 unlogged table ：

ALTER TABLE unloggedtable_name  SET UNLOGGED;   
ALTER TABLE unloggedtable_name  SET LOGGED;

unlogged table 的优点：

  • wal日志量变少，省去重做日志文件，节省了一定的空间，同时减少备份wal的工作量。
  • vacuum影响减少，因为vacuum动作也会被wal日志写入
  • 并行创建大型表性能得到提升。

unlogged table 的缺点：

  • 故障恢复时会截断表。比如，在实例奔溃后，unlogged表数据会被truncate,会丢失数据。比如，在使用PITR增量备份恢复的情况下，也会丢失数据。
  • 使用UNLOGGED的表只能在主服务器上访问，而不能在副本（备服务器）上访问。
  • 使用UNLOGGED的表不能用于逻辑备份（pg_dump、pg_dumpall）或物理备份（关闭数据库直接文件拷贝、pg_start_backup、pg_basebackup）。

一个图示：

PostgreSQL-unlogged table_逻辑复制

Faster writes：更快的写入。unlogged表不写wal,所以dml的效率更高，比如insert ,update会更快。

Less bloat/vacuum：无wal日志，低vacuum，因为unlogged表不需要写wal,vacuum的效率也会提升，正常表的vacuum会写wal。

Small backups：更小的备份集。因为不写wal，所以在备份的时候不会备份unlogged表，备份集会更小。

Durable on crash：实例崩溃时不支持，数据会丢失。

Available on replicas：副本不可用。这里指的应该是物理的备份。

Use logical replication：不支持逻辑复制。

普通表，也就是logged表，不会因为数据库崩溃而截断表，丢失所有数据，物理复制和逻辑复制都可用。

来个测试吧，让你更明白！

有点长，耐心看完，你会明白的。别忘了点赞。

#################################################
# 创建 unlogged 表 插入数据                     #
#################################################
# 创建 unlogged 表
postgres=# create unlogged table unlogged_table (id int primary key);
CREATE TABLE
# 执行时间查看 开启
postgres=# \timing
Timing is on.
# 插入 10000000 一千万条数据
postgres=# insert into unlogged_table select generate_series(1,10000000000);
INSERT 0 10000000
Time: 15608.489 ms (00:15.608)
# 查看插入的前10条数据
postgres=# select * from unlogged_table limit 20;
 id
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
(10 rows)

Time: 0.442 ms
postgres=# \q
#################################################
# 重启 PostgreSQL 数据库 数据并未丢失           #
#################################################
[postgres@pgccc ~]$ pg_ctl -D $PGDATA restart -l logfile
waiting for server to shut down.... done
server stopped
waiting for server to start.... done
server started
[postgres@pgccc ~]$ psql
psql (14.7)
Type "help" for help.

# 查看重启后的 unlogged 表的前10行数据
postgres=# select * from unlogged_table limit 20;
 id
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
(10 rows)

# 查看重启后的 unlogged 表的所有数据条数 无丢失
postgres=# select count(1) from unlogged_table;
  count
----------
 10000000
(1 row)

postgres=#

#################################################
# 服务器重启情况 数据并未丢失                   #
#################################################
shutdown -r now

# 重启数据库后，还可查到 unlogged 表的数据
postgres=# select count(1) from unlogged_table;
  count
----------
 10000000
(1 row)

#################################################
# 模拟宕机 unlogged 表数据 被 truncate 数据丢失 #
#################################################
# 修改参数 shared_buffers = 32MB                   # min 128kB # (change requires restart) 默认值 128MB 重启生效
# 创建使实例宕机的表 插入数据
create table test01(id integer, val char(1024));
insert into test01 values(generate_series(1,10000000),repeat( chr(int4(random()*26)+65),1024));

# 插入数据过程中告警提示
2023-08-05 22:48:27.654 CST [11765] LOG:  checkpoints are occurring too frequently (8 seconds apart)
2023-08-05 22:48:27.654 CST [11765] HINT:  Consider increasing the configuration parameter "max_wal_size".
... ...
2023-08-05 22:52:19.143 CST [11765] LOG:  checkpoints are occurring too frequently (9 seconds apart)
2023-08-05 22:52:19.143 CST [11765] HINT:  Consider increasing the configuration parameter "max_wal_size".
2023-08-05 22:52:25.509 CST [11802] ERROR:  could not extend file "base/13892/16836.10": No space left on device
2023-08-05 22:52:25.509 CST [11802] HINT:  Check free disk space.
2023-08-05 22:52:25.509 CST [11802] STATEMENT:  insert into test01 values(generate_series(1,10000000),repeat( chr(int4(random()*26)+65),1024));
ERROR:  could not extend file "base/13892/16836.10": No space left on device
HINT:  Check free disk space.
postgres=# 2023-08-05 22:52:43.154 CST [11769] LOG:  could not open temporary statistics file "pg_stat_tmp/global.tmp": No space left on device
2023-08-05 22:52:44.378 CST [11769] LOG:  could not open temporary statistics file "pg_stat_tmp/global.tmp": No space left on device
2023-08-05 22:52:44.378 CST [11769] LOG:  could not open temporary statistics file "pg_stat_tmp/global.tmp": No space left on device
2023-08-05 22:52:45.610 CST [11769] LOG:  could not open temporary statistics file "pg_stat_tmp/global.tmp": No space left on device
2023-08-05 22:52:45.611 CST [11769] LOG:  could not open temporary statistics file "pg_stat_tmp/global.tmp": No space left on device
2023-08-05 22:52:46.547 CST [11769] LOG:  could not open temporary statistics file "pg_stat_tmp/global.tmp": No space left on device
2023-08-05 22:52:46.547 CST [11769] LOG:  could not open temporary statistics file "pg_stat_tmp/global.tmp": No space left on device
2023-08-05 22:52:47.360 CST [11769] LOG:  could not open temporary statistics file "pg_stat_tmp/global.tmp": No space left on device
... ...
2023-08-05 22:52:55.874 CST [11768] LOG:  using stale statistics instead of current ones because stats collector is not responding
... ...
2023-08-05 22:53:46.616 CST [11769] LOG:  could not open temporary statistics file "pg_stat_tmp/global.tmp": No space left on device
2023-08-05 22:53:47.306 CST [11769] LOG:  could not open temporary statistics file "pg_stat_tmp/global.tmp": No space left on device
2023-08-05 22:53:47.306 CST [11769] LOG:  could not open temporary statistics file "pg_stat_tmp/global.tmp": No space left on device
2023-08-05 22:53:47.846 CST [11763] LOG:  received fast shutdown request
2023-08-05 22:53:47.847 CST [11763] LOG:  aborting any active transactions
2023-08-05 22:53:47.847 CST [11802] FATAL:  terminating connection due to administrator command
2023-08-05 22:53:47.847 CST [14322] FATAL:  terminating connection due to administrator command
2023-08-05 22:53:47.851 CST [11769] LOG:  could not open temporary statistics file "pg_stat_tmp/global.tmp": No space left on device
2023-08-05 22:53:47.851 CST [11769] LOG:  could not open temporary statistics file "pg_stat_tmp/global.tmp": No space left on device
2023-08-05 22:53:47.851 CST [11769] LOG:  could not open temporary statistics file "pg_stat_tmp/global.tmp": No space left on device
2023-08-05 22:53:47.851 CST [11769] LOG:  could not open temporary statistics file "pg_stat_tmp/global.tmp": No space left on device
2023-08-05 22:53:47.851 CST [11769] LOG:  could not open temporary statistics file "pg_stat_tmp/global.tmp": No space left on device
2023-08-05 22:53:47.851 CST [11763] LOG:  background worker "logical replication launcher" (PID 11770) exited with exit code 1
2023-08-05 22:53:47.992 CST [11769] LOG:  could not open temporary statistics file "pg_stat_tmp/global.tmp": No space left on device
2023-08-05 22:53:47.993 CST [11769] LOG:  could not open temporary statistics file "pg_stat_tmp/global.tmp": No space left on device
... ...
2023-08-05 22:53:54.218 CST [11769] LOG:  could not open temporary statistics file "pg_stat_tmp/global.tmp": No space left on device
2023-08-05 22:53:54.686 CST [11768] LOG:  using stale statistics instead of current ones because stats collector is not responding
2023-08-05 22:53:54.687 CST [11765] LOG:  shutting down
2023-08-05 22:53:54.688 CST [11765] PANIC:  could not create file "pg_logical/replorigin_checkpoint.tmp": No space left on device
2023-08-05 22:53:54.763 CST [11763] LOG:  checkpointer process (PID 11765) was terminated by signal 6: Aborted
2023-08-05 22:53:54.763 CST [11763] LOG:  terminating any other active server processes
2023-08-05 22:53:54.763 CST [11769] LOG:  could not open temporary statistics file "pg_stat/global.tmp": No space left on device
2023-08-05 22:53:54.764 CST [11763] LOG:  abnormal database system shutdown
2023-08-05 22:53:54.770 CST [11763] LOG:  database system is shut down

postgres=#

# 在另一个窗口 查看表情况 连接失效
postgres=# \d
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: Failed.
!?>
!?> \q

# 启动失败
[postgres@pgccc ~]$ pg_start
waiting for server to start....2023-08-05 22:56:05.062 CST [15425] FATAL:  could not create lock file "postmaster.pid": No space left on device
 stopped waiting
pg_ctl: could not start server
Examine the log output.

# 查看磁盘使用情况 /占满
[postgres@pgccc ~]$ df -h
Filesystem               Size  Used Avail Use% Mounted on
devtmpfs                 470M     0  470M   0% /dev
tmpfs                    487M     0  487M   0% /dev/shm
tmpfs                    487M  8.5M  478M   2% /run
tmpfs                    487M     0  487M   0% /sys/fs/cgroup
/dev/mapper/centos-root   17G   17G   24K 100% /
/dev/sda1               1014M  172M  843M  17% /boot
tmpfs                     98M   12K   98M   1% /run/user/42
tmpfs                     98M     0   98M   0% /run/user/0
[postgres@pgccc ~]$ cd /

# 删除部分文件 
[root@pgccc home]# rm -rf rpm
[root@pgccc home]# df -h
Filesystem               Size  Used Avail Use% Mounted on
devtmpfs                 470M     0  470M   0% /dev
tmpfs                    487M     0  487M   0% /dev/shm
tmpfs                    487M  8.5M  478M   2% /run
tmpfs                    487M     0  487M   0% /sys/fs/cgroup
/dev/mapper/centos-root   17G   17G  133M 100% /
/dev/sda1               1014M  172M  843M  17% /boot
tmpfs                     98M   12K   98M   1% /run/user/42
tmpfs                     98M     0   98M   0% /run/user/0
[root@pgccc home]# su - postgres
Last login: Sat Aug  5 22:49:03 CST 2023 on pts/2

# 启动成功
[postgres@pgccc ~]$ pg_start
waiting for server to start....2023-08-05 22:59:41.244 CST [16803] LOG:  starting PostgreSQL 14.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2023-08-05 22:59:41.244 CST [16803] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2023-08-05 22:59:41.244 CST [16803] LOG:  listening on IPv6 address "::", port 5432
2023-08-05 22:59:41.247 CST [16803] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2023-08-05 22:59:41.253 CST [16804] LOG:  database system shutdown was interrupted; last known up at 2023-08-05 22:53:54 CST
2023-08-05 22:59:41.348 CST [16804] LOG:  database system was not properly shut down; automatic recovery in progress
2023-08-05 22:59:41.351 CST [16804] LOG:  redo starts at 8/1416A760
..2023-08-05 22:59:43.312 CST [16804] LOG:  invalid record length at 8/2CCEE0D0: wanted 24, got 0
2023-08-05 22:59:43.312 CST [16804] LOG:  redo done at 8/2CCEE098 system usage: CPU: user: 0.36 s, system: 1.54 s, elapsed: 1.96 s
2023-08-05 22:59:43.512 CST [16803] LOG:  database system is ready to accept connections
 done
server started
[postgres@pgccc ~]$ psql
psql (14.7)
Type "help" for help.

postgres=# \d
                 List of relations
 Schema |      Name      |     Type      |  Owner
--------+----------------+---------------+----------
 public | a              | table         | postgres
 public | b              | table         | postgres
 public | passwd         | foreign table | postgres
 public | test01         | table         | postgres
 public | txtfiles       | foreign table | postgres
 public | unlogged_table | table         | postgres
(6 rows)

# unlogged 表数据丢失 被 truncate
postgres=# select * from unlogged_table limit 10;
 id
----
(0 rows)

postgres=#


#################################################
# 模拟 PITR 增量备份 unlogged 表数据未被复制    #
#################################################
# 配置 postgresql.conf 完成后重启 PostgreSQL
archive_mode = on/off
archive_command = 'cp -i %p /pgccc/archive/%f'

# 切换 wal 日志 
select pg_switch_wal();

# unlogged 表插入数据
postgres=# select * from unlogged_table;
 id
----
(0 rows)

postgres=# insert into unlogged_table select generate_series(1,10000000);
INSERT 0 10000000
postgres=# select count(1) from unlogged_table;
  count
----------
 10000000
(1 row)

postgres=#


# 生成基础备份
pg_basebackup -Fp -P -v -D pgdata_bak

# 修改备份配置
port = 5433
restore_command = 'cp /pgccc/archive/ %p'
recovery_target_action = 'pause'

# 停止源库 
pg_stop

# 备库生成 recovery.signal
touch recovery.signal

# 启动备库查看 nologged 表 数据未被复制
[postgres@pgccc ~]$ pg_ctl -D pgdata_bak/ start -l logfile
waiting for server to start.... done
server started
[postgres@pgccc ~]$
[postgres@pgccc ~]$ psql -p 5433
psql (14.7)
Type "help" for help.

postgres=# \d
                 List of relations
 Schema |      Name      |     Type      |  Owner
--------+----------------+---------------+----------
 public | a              | table         | postgres
 public | b              | table         | postgres
 public | passwd         | foreign table | postgres
 public | txtfiles       | foreign table | postgres
 public | unlogged_table | table         | postgres
(5 rows)

postgres=# select * from unlogged_table;
 id
----
(0 rows)

postgres=#

复制别人的总结：

  1. 一般情况下我们是不需要使用unlogged表的，因为不能保证数据的持久性，这对于数据库来说是致命的。
  2. 正常的关闭数据库，或者正常的重启数据库，unlogged表的数据不会丢失，只有在不干净的关闭数据库，比如断电，或者因为其他原因数据库crash了，那么unlogged表的数据会被截断，用一个空的xxxx_init表文件代替。
  3. unlogged表也是要写物理文件的，只是不写wal日志，所以不是内存表，不要以为unlogged表都是在内存中操作的。
  4. unlogged表在生产环境建议不要使用。

参考两篇，在此谢谢：

Postgresql中的unlogged table_postgresql

PostgreSQL unlog表_postgresql unlogged

标签：PostgreSQL,postgres,22,unlogged,08,2023,CST,table,05
From： https://blog.51cto.com/baoyw/6986501

相关文章

  • Java8 高级功能CompletableFuture
    CompletableFuture功能测试CompletableFuture类实现了CompletionStage和Future接口。Future是Java5添加的类，用来描述一个异步计算的结果，但是获取一个结果时方法较少,要么通过轮询isDone，确认完成后，调用get()获取值，要么调用get()设置一个超时时间。但是这个get()方法会阻塞住调用......
  • 七月学习之Iptables连接追踪state
    7、Iptables连接追踪state7.1、什么是连接追踪state(conntrack)连接追踪，顾名思义，就是跟踪(并记录)连接的状态如下图:是一台IP地址为10.1.1.2的linux机器，我们能看到这台机器上有三条连接1、机器访问外部HTTP服务的连接(目的端口80)2、外部访问机器内FTP服务的连接(目的端口21)3......
  • vue-table-width-grid 以及axios下载时报错
    importVuefrom'vue'importAppfrom'./App.vue'importrouterfrom'./router'importstorefrom'./store'//导入字体图标import'./assets/fonts/iconfont.css'importElementUIfrom'element-ui'impor......
  • 七月学习之Iptables基本匹配
    5、Iptables基本匹配我们前面在练习规则操作时，使用的"匹配条件"比较少，下面我们来了解下iptables匹配条件更多的用法5.1、iptables匹配参数5.2、iptables匹配示例1仅允许10.0.0.1访问10.0.0.200服务器的22端口、其他地址全部拒绝。#-I插入规则至第一行、—A追加规则、-s源地址、-d......
  • VxeTable 列动态数据过滤 FilterContent
    1、加入组件，并注册：下载官网实例将VxeTable的v4下的位置中的画框的几个都拷到自己的项目中，然后打开filter.tsx，将组件的引用路径调整自己的项目一致，如果是一样就不改了。这一步，要保证filter.tsx中引用到4个vue文件就可以。  2、引入到项目中，保证项目能读到filter.tsx，这样就......
  • Could not extract response: no suitable `HttpMessageConverter` found for respons
    1.问题复现话不多说，先贴出问题代码：这里的GetUserInfoByAccessToken是我自定义的一个实体类。GetUserInfoByAccessTokengetUserInfoByAccessTokenString=restTemplate.getForObject(userInfoByAccessCodeURL,GetUserInfoByAccessToken.class);异常信息：Couldnotextractr......
  • table rowspan 用法
    <tableborder="1"><tr><th>AA1</th><th>BB1</th> <th>CC1</th><th>DD1</th><th>EE1</th></tr><tr><tdrowspan="5">A1......
  • postgresql执行计划
    SQL优化主要从三个角度进行：（1）扫描方式；（2）连接方式；（3）连接顺序。如果解决好这三方面的问题，那么这条SQL的执行效率就基本上是靠谱的。看懂SQL的执行计划的关键也是要首先了解这三方面的基本概念，只有搞清楚了这些基本概念，才能够更好的看懂SQL的执行计划，下面我们分别来学习这些预备知识。......
  • PostgreSQL-错误处理集（持续更新... ...）
    1无法删除用户ERROR：角色不能被删除，因为有对象依赖于它LINE1：模式public的权限/用户下有表存在解决方法：收回赋予此用户的模式public的权限，并删除用户下的表，即可正常删除。postgres=#\duListofrolesRolename|......
  • iptables: No chain/target/match by that name
    部署完docker后执行脚本报错在启动run.sh脚本后出现iptables:Nochain/target/matchbythatname.报错解决办法：重启dockerPS：重启docker后，通过以下命令可以查看最新防火墙配置：iptables-L应该看到iptables配置中ChainDocker设置已更新（与1中历史结果进行对比）原因解释重新......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99