开启归档

• 创建归档目录

postgres@zjarn2021-Qh:~$ mkdir 14_bak
mkdir: cannot create directory ‘14_bak’: File exists
postgres@zjarn2021-Qh:~$ pwd
/var/lib/postgresql

• postgresql.conf相关参数

cat /etc/postgresql/14/main/postgresql.conf 
archive_mode = 'ON'
archive_command = 'cp %p /var/lib/postgresql/14_bak/archive%f'
wal_level=replica 
archive_timeout='900s'
wal_keep_size=128
max_wal_senders=10

• 重启数据库

/usr/lib/postgresql/14/bin/pg_ctl -D /var/lib/postgresql/14/main restart

• 查看归档模式是否开启

模拟业务写入

创建表

#创建表
create table tmpbm (id int,f1 text,f2 text) ;

创建脚本

postgres@zjarn2021-Qh:~$ cat insert.sql 
\set id random(1, 1000000)
insert into tmpbm values(:id,repeat('testa',20),repeat('name',10)) ;

开始写入

pgbench -c 40 -j 1 -n -M prepared -T 60 -r -f insert.sql

切换日志

postgres=# select pg_switch_wal();pg_switch_wal 
---------------0/E0000F0
(1 row)postgres=# select pg_switch_wal();pg_switch_wal 
---------------0/F000078
(1 row)

查看归档

postgres=# \x
Expanded display is on.
postgres=#  SELECT * FROM pg_stat_archiver;
-[ RECORD 1 ]------+------------------------------
archived_count     | 4
last_archived_wal  | 000000010000000000000007
last_archived_time | 2022-01-24 23:50:10.447931+08
failed_count       | 0
last_failed_wal    | 
last_failed_time   | 
stats_reset        | 2022-01-24 00:56:12.460452+08