SRE文档

深浅模式

1.wal_log增长 ​

https://www.cnblogs.com/believexin/p/14623345.html

具体过程如下

bash
1 关闭PostgreSQL的数据库服务
2 修改postgresql.conf中参数，查阅了多方资料显示min_wal_size 必须是 wal_segment_size 的两倍
于是修改
max_wal_size = 1GB
min_wal_size = 128MB
1 关闭PostgreSQL的数据库服务
2 修改postgresql.conf中参数，查阅了多方资料显示min_wal_size 必须是 wal_segment_size 的两倍
于是修改
max_wal_size = 1GB
min_wal_size = 128MB

修改后保存，重新启动postgreSQL显示 OK ，wal_log终于低增长

调整wal_log 大小

initdb 命令

$initdb -E UTF8 --locale=C --wal-segsize=64 -D /home/pg11/data01 -U postgres -W
$initdb -E UTF8 --locale=C --wal-segsize=64 -D /home/pg11/data01 -U postgres -W
#暴力修改
pg_resetwal --wal-segsize=64 -D /database/pg11/pg_root
#暴力修改
pg_resetwal --wal-segsize=64 -D /database/pg11/pg_root

2.开启归档 ​

sql
1.#查看归档是否开启
postgres=# select name,setting from pg_settings where name like 'archive%' or name = 'wal_level';
          name           |                                 setting                                  
-------------------------+--------------------------------------------------------------------------
 archive_cleanup_command | 
 archive_command         | test ! -f /data/pgdata/archivedir/%f && cp %p /data/pgdata/archivedir/%f
 archive_mode            | on
 archive_timeout         | 60
 wal_level               | replica
(5 rows)

或者
postgres=# show archive_mode;
 archive_mode 
--------------
 on
(1 row)

#修改postgresql的配置文件(postgresql.conf)
archive_mode =on 
archive_command ='DATE=`date +%Y%m%d`;DIR="/home/postgres/arch/$DATE";(test -d $DIR || mkdir -p $DIR)&& cp %p

%p表示wal日志文件的路径，%f表示wal日志文件名

#创建归档路径
mkdir -p /data/pgdata/archivedir
chown -R postgres:postgres /data/pgdata/archivedir

#重启数据库

#验证归档是否正常
postgres=# checkpoint;

#PG10之后
postgres=# select pg_switch_wal();

#PG10之前
postgres=# select pg_switch_xlog();
1.#查看归档是否开启
postgres=# select name,setting from pg_settings where name like 'archive%' or name = 'wal_level';
          name           |                                 setting                                  
-------------------------+--------------------------------------------------------------------------
 archive_cleanup_command | 
 archive_command         | test ! -f /data/pgdata/archivedir/%f && cp %p /data/pgdata/archivedir/%f
 archive_mode            | on
 archive_timeout         | 60
 wal_level               | replica
(5 rows)

或者
postgres=# show archive_mode;
 archive_mode 
--------------
 on
(1 row)

#修改postgresql的配置文件(postgresql.conf)
archive_mode =on 
archive_command ='DATE=`date +%Y%m%d`;DIR="/home/postgres/arch/$DATE";(test -d $DIR || mkdir -p $DIR)&& cp %p

%p表示wal日志文件的路径，%f表示wal日志文件名

#创建归档路径
mkdir -p /data/pgdata/archivedir
chown -R postgres:postgres /data/pgdata/archivedir

#重启数据库

#验证归档是否正常
postgres=# checkpoint;

#PG10之后
postgres=# select pg_switch_wal();

#PG10之前
postgres=# select pg_switch_xlog();

归档策略脚本 ​

archive_command ='/bin/bash /data/pgdata/data/pg_archive.sh %p %f'
archive_command ='/bin/bash /data/pgdata/data/pg_archive.sh %p %f'
#!/bin/bash
#source /home/postgres/.bash_profile

DATE=`date +%Y%m%d`
DIR="/data/pgdata/archivedir/$DATE"
BACK="/data/pgdata/archivedir/"`date -d '-20 day' +%Y%m%d`
if [ -d "$BACK" ]; then
   rm -rf $BACK
   echo "success rm $BACK" > hx.log
else
   echo "the old backup file not exists!" > hx.log
fi
(test -d $DIR || mkdir -p $DIR) && cp $1 $DIR/$2
#!/bin/bash
#source /home/postgres/.bash_profile

DATE=`date +%Y%m%d`
DIR="/data/pgdata/archivedir/$DATE"
BACK="/data/pgdata/archivedir/"`date -d '-20 day' +%Y%m%d`
if [ -d "$BACK" ]; then
   rm -rf $BACK
   echo "success rm $BACK" > hx.log
else
   echo "the old backup file not exists!" > hx.log
fi
(test -d $DIR || mkdir -p $DIR) && cp $1 $DIR/$2

3.手动清理归档文件 ​

pg_wal（Write-AHead Logging） 说明 ​

wal日志位置： $PGDATA/pg_wal(pg10之前叫pg_xlog)

#
#wal日志文件命名规则：
#我们看到的wal日志是这样的：000000010000000100000092
#其中前8位:00000001表示timeline；
#中间8位：00000001表示logid；
#最后8位：00000092表示logseg
#
#wal日志文件命名规则：
#我们看到的wal日志是这样的：000000010000000100000092
#其中前8位:00000001表示timeline；
#中间8位：00000001表示logid；
#最后8位：00000092表示logseg

清除检查点以前的XLOG文件 ​

root@~> pg_controldata $PGDATA

Latest checkpoint location:           1/E000028  
Prior checkpoint location:            1/D18C068  
Latest checkpoint's REDO location:    1/E000028  
Latest checkpoint's REDO WAL file:    00000001000000010000000E  
  
表示00000001000000010000000E之前的pg_wal文件可以删除
root@~> pg_controldata $PGDATA

Latest checkpoint location:           1/E000028  
Prior checkpoint location:            1/D18C068  
Latest checkpoint's REDO location:    1/E000028  
Latest checkpoint's REDO WAL file:    00000001000000010000000E  
  
表示00000001000000010000000E之前的pg_wal文件可以删除

比如你配置了archive_mode=on，但是没有配置archive_command，那么xlog文件会一直堆积（pg_wal写完后，会写.ready，但是由于没有配置archive_command，也就是说不会触发归档命令，所以一直都不会写.done）。

从而导致pg_wal一直不会被清理。

然后使用pg_archivecleanup命令来清理

# 1. 读取控制文件，找到哪个文件是可以被清理的
pg_controldata $PGDATA  
  
Latest checkpoint location:           1/E000028  
Prior checkpoint location:            1/D18C068  
Latest checkpoint's REDO location:    1/E000028  
Latest checkpoint's REDO WAL file:    00000001000000010000000E  
  
# 表示00000001000000010000000E之前的pg_wal文件可以删除 (pg10以前的叫做pg_xlog)

[ptgres@k8s1 pg_wal]$ pg_archivecleanup -d /data/pgdata/data/pg_wal 000000010000000000000006 

pg_archivecleanup: keeping WAL file "/data/pgdata/data/pg_wal/000000010000000000000006" and later
pg_archivecleanup: removing file "/data/pgdata/data/pg_wal/000000010000000000000001"
pg_archivecleanup: removing file "/data/pgdata/data/pg_wal/000000010000000000000005"
pg_archivecleanup: removing file "/data/pgdata/data/pg_wal/000000010000000000000004"
pg_archivecleanup: removing file "/data/pgdata/data/pg_wal/000000010000000000000002"
pg_archivecleanup: removing file "/data/pgdata/data/pg_wal/000000010000000000000003"
# 1. 读取控制文件，找到哪个文件是可以被清理的
pg_controldata $PGDATA  
  
Latest checkpoint location:           1/E000028  
Prior checkpoint location:            1/D18C068  
Latest checkpoint's REDO location:    1/E000028  
Latest checkpoint's REDO WAL file:    00000001000000010000000E  
  
# 表示00000001000000010000000E之前的pg_wal文件可以删除 (pg10以前的叫做pg_xlog)

[ptgres@k8s1 pg_wal]$ pg_archivecleanup -d /data/pgdata/data/pg_wal 000000010000000000000006 

pg_archivecleanup: keeping WAL file "/data/pgdata/data/pg_wal/000000010000000000000006" and later
pg_archivecleanup: removing file "/data/pgdata/data/pg_wal/000000010000000000000001"
pg_archivecleanup: removing file "/data/pgdata/data/pg_wal/000000010000000000000005"
pg_archivecleanup: removing file "/data/pgdata/data/pg_wal/000000010000000000000004"
pg_archivecleanup: removing file "/data/pgdata/data/pg_wal/000000010000000000000002"
pg_archivecleanup: removing file "/data/pgdata/data/pg_wal/000000010000000000000003"
  • 脚本清理
#!/bin/bash
export PGHOME=/data/apps/pgsql/12
export PGPORT=5532  
export PGDATA=/data/pgdata/data  
export PATH=$PGHOME/bin:$PATH

# database user
PGIP=127.0.0.1
PORT=5532
PGUSER_NAME=postgres

PG_WAL_NUM=`pg_controldata $PGDATA |head |grep "WAL file"|awk -F':' '{print $2}'`
if [ ${PG_WAL_NUM} == "" ]
then
	exit 1
else
	pg_archivecleanup -d ${PGDATA}/pg_wal ${PG_WAL_NUM} > /dev/null 2>&1
fi
#!/bin/bash
export PGHOME=/data/apps/pgsql/12
export PGPORT=5532  
export PGDATA=/data/pgdata/data  
export PATH=$PGHOME/bin:$PATH

# database user
PGIP=127.0.0.1
PORT=5532
PGUSER_NAME=postgres

PG_WAL_NUM=`pg_controldata $PGDATA |head |grep "WAL file"|awk -F':' '{print $2}'`
if [ ${PG_WAL_NUM} == "" ]
then
	exit 1
else
	pg_archivecleanup -d ${PGDATA}/pg_wal ${PG_WAL_NUM} > /dev/null 2>&1
fi

4.关闭或修改归档模式 ​

  • 修改archive_mode配置

当启用archive_mode时，通过设置archive_command将已完成的WAL段发送到归档存储。除了off，disable，还有两种模式：on,always。在正常操作期间，两种模式之间没有区别，但是当设置为always的情况下,WAL archiver在存档恢复或待机模式下也被启用。在always模式下，从归档还原或流式复制流的所有文件都将被归档（再次）。archive_mode和archive_command是单独的变量，因此可以在不更改存档模式的情况下更改archive_command。此参数只能在服务器启动时设置。当wal_level设置为minimal时，无法启用archive_mode

  • 修改wal_level配置

1、minimal是默认的值，它仅写入崩溃或者突发关机时所需要的信息（不建议使用）。

2、archive是增加wal归档所需的日志（最常用）。

3、hot_standby是在备用服务器上增加了运行只读查询所需的信息，一般实在流复制的时候使用到

5.触发 WAL 日志归档 ​

手动切换 WAL 日志 ​

#10之前
postgres=# select pg_switch_xlog();

#10之后
postgres=# select pg_switch_wal();
#10之前
postgres=# select pg_switch_xlog();

#10之后
postgres=# select pg_switch_wal();

WAL 日志写满后触发归档 ​

设置 archive_timeout ​

另外可以设置archive 超时参数 archive_timeout ，假如设置 archive_timeout=60 ，那么每 60 s ，会触发一次 WAL 日志切换，同时触发日志归档

6.设置wal_log 大小 ​

#首先停止PostgreSQL服务
pg_ctl -D /data/pgdata stop -m fast

#修改wal-segsize段大小,默认单位是MB,值必须是2的幂并且在1到1024MB之间
#以postgresql 用户运行
#pg_resetwal --wal-segsize=32 /data/pgdata/data;
Write-ahead log reset


#查看
show block_size;
show wal_block_size;
show segment_size;
show wal_segment_size;
ls /data/pgdata/data/pg_wal -lh

postgres@127.0.0.1 ~=#\! ls 
pg_healthcheck.report  pg_healthcheck.sh

#实验segment_size
postgres=# select database_block_size,blocks_per_segment,database_block_size*blocks_per_segment::bigint/1024/1024/1024 as "segment_size(GB)" from pg_control_init();
 database_block_size | blocks_per_segment | segment_size(GB) 
---------------------+--------------------+------------------
                8192 |             131072 |                1
(1 row)

#插入数据来验证

create table test(id int,name text);
insert into test select n,n||'_test' from generate_series(1,40000000) n;

#查看表位置
postgres=# select pg_relation_filepath('table_name');
#首先停止PostgreSQL服务
pg_ctl -D /data/pgdata stop -m fast

#修改wal-segsize段大小,默认单位是MB,值必须是2的幂并且在1到1024MB之间
#以postgresql 用户运行
#pg_resetwal --wal-segsize=32 /data/pgdata/data;
Write-ahead log reset


#查看
show block_size;
show wal_block_size;
show segment_size;
show wal_segment_size;
ls /data/pgdata/data/pg_wal -lh

postgres@127.0.0.1 ~=#\! ls 
pg_healthcheck.report  pg_healthcheck.sh

#实验segment_size
postgres=# select database_block_size,blocks_per_segment,database_block_size*blocks_per_segment::bigint/1024/1024/1024 as "segment_size(GB)" from pg_control_init();
 database_block_size | blocks_per_segment | segment_size(GB) 
---------------------+--------------------+------------------
                8192 |             131072 |                1
(1 row)

#插入数据来验证

create table test(id int,name text);
insert into test select n,n||'_test' from generate_series(1,40000000) n;

#查看表位置
postgres=# select pg_relation_filepath('table_name');

或者暴力清理，这个会丢失数据，不建议使用

pg_resetwal -f /data/pgdata/data

7.pg_wal ​

https://www.modb.pro/db/48191

https://www.modb.pro/db/48193

https://www.modb.pro/db/28548

https://www.modb.pro/db/22844