PostgreSQL学习随笔13 Postgresql 13 时间点恢复（PITR）

CP的PostgreSQL厨房 2022-03-26

712

以下操作都在postgres用户下进行

配置归档命令

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

更新数据，模拟宕机

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