【技术文章】PostgreSQL配置优化(二)

其中两个参数分别为：
autovacuum_vacuum_threshold = 50 #阈值
autovacuum_vacuum_scale_factor = 0.2 #比例因子
复制

ALTER TABLE test SET (autovacuum_vacuum_threshold = 100);
复制

1.将事务提交的修改写进disk（写脏数据）；保证数据库的完整性和一致性。 

2.缩短恢复时间,将脏页写入相应的数据文件,确保修改后的文件通过fsync()写入到磁盘。

检查点触发条件：

• 1.checkpoint_timeout 设置的间隔时间自上一个检查点已经过去（默认间隔为 300 秒（5 分钟））。

• 2.在 9.4 或更早版本中，为checkpoint_segments设置的 WAL 段文件的数量自上一个检查点以来已经被消耗（默认数量为 3）。

• 3.在 9.5 或更高版本中，pg_xlog（在 10 或更高版本中为 pg_wal）中的 WAL 段文件的总大小已超过参数max_wal_size的值（默认值为 1GB（64 个文件））。

• 4.PostgreSQL 服务器在smart或fast模式下停止。

• 5.当超级用户手动发出 CHECKPOINT 命令时，它的进程也会这样做。

• 6.写入WAL的数据量已达到参数max_wal_size（默认值：1GB）

• 7.执行pg_start_backup函数时

• 8.在进行数据库配置时（例如CREATE DATABASE DROP DATABASE语句）

restore_command ='cp opt/pg_arch/%f %p' 


recovery_target_time = "2018-7-16 12:15:00 GMT" 


recovery_target_timeline = 2


参数recovery_target_time被设置为犯下新错误的时间，而recovery_target_ timeline被设置为2，以便沿着这条时间线恢复。
复制

配置归档命令
mkdir opt/arch
vi $PGDATA/postgresql.auto.conf
archive_mode = on
archive_command = 'DATE=`date +%Y%m%d`; DIR="/opt/arch/$DATE"; (test -d $DIR || mkdir -p $DIR) && cp %p $DIR/%f'
wal_level = replica


使用pg_basebackup备份数据库
mkdir home/postgres/bak/
pg_basebackup -D home/postgres/bak/ -Ft -P -R -Upostgres


select * from t_rec;




更新数据，模拟宕机create 
table t_rec (id int,time timestamp);
insert into t_rec values (1,now());
insert into t_rec values (2,now());
insert into t_rec values (3,now());
insert into t_rec values (4,now());
insert into t_rec values (5,now());
select pg_switch_wal();
checkpoint;
select * from t_rec;


关闭实例
pg_ctl -m f stop
删除pg_root13数据库目录
rm -rf /opt/pg_root13/*


基于时间点恢复
将备份的数据文件解压到$PGDATA目录，wal日志解压放到$PGDATA/pg_wal目录
mkdir /opt/pg_root13/pg_wal
tar -xvf /home/postgres/bak/base.tar -C /opt/pg_root13
tar -xvf /home/postgres/bak/pg_wal.tar -C /opt/pg_root13/pg_wal
chmod 0700 /opt/pg_root13
rm -f opt/pg_root13/standby.signal
touch opt/pg_root13/recovery.signal
chmod 600 opt/pg_root13/recovery.signal


编辑$PGDATA/postgresql.auto.conf文件
restore_command = 'cp /opt/arch/20220325/%f %p'
recovery_target_timeline = 'latest'
启动数据库


%p 表示wal文件名$PGDATA的相对路径, 如pg_wal/00000001000000190000007D
%f 表示wal文件名, 如00000001000000190000007D


启动DB
pg_ctl start
复制

postgresql.auto.conf内容参考




listen_addresses = '0.0.0.0'
port = 5432
max_connections = 1000
shared_buffers = 410MB
wal_buffers = 120MB
superuser_reserved_connections = 20
unix_socket_directories = '.'
unix_socket_permissions = 0700
tcp_keepalives_idle = 60
tcp_keepalives_interval = 10
tcp_keepalives_count = 10
vacuum_cost_delay = 10
bgwriter_delay = 10ms
synchronous_commit = off
wal_writer_delay = 10ms
log_destination = 'csvlog'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_file_mode = 0600
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 10MB
hot_standby = on
archive_mode = on
archive_command = 'DATE=`date +%Y%m%d`; DIR="/opt/arch/$DATE"; (test -d $DIR || mkdir -p $DIR) && cp %p $DIR/%f'
wal_level = replica
restore_command = 'cp opt/arch/20220325/%f %p'
recovery_target_timeline = 'latest'
复制