Postgresql 基于时间点恢复

st780206

已于 2024-04-24 09:12:04 修改

阅读量243

点赞数 3

分类专栏： postgresql 文章标签： postgresql

于 2024-03-29 13:29:33 首次发布

本文链接：https://blog.csdn.net/st780206/article/details/137142337

版权

postgresql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

linux服务器时间改为24小时制

vi /etc/profile
在最后一行加上：export LC_TIME=POSIX
source /etc/profile
执行date，此时变为了24小时制，断开终端重新连接，依然为24小时制

编辑修改postgresql.conf

#日志级别
wal_level = replica

1.简谈PostgreSQL的wal_level=logic
https://blog.csdn.net/Auspicious_air/article/details/134776155

2.postgresql中wal_level的三个参数用法说明
https://www.jb51.net/article/203220.htm

#归档模式
archive_mode = on

archive_mode参数有3种模式：
off：关闭归档
on：开启归档，但不允许在recovery模式下进行归档
always：开启归档，且允许在recovery模式下进行归档

#归档wal命令
archive_command = 'cp %p /postgres_archive_data/archive_data/%f && echo %p >> /postgres_archive_data/archive_log/archive.list && echo %f >> /postgres_archive_data/archive_log/archive.list'

pg会启动一个辅助进程，作用是实时监控事务日志，发现能归档的日志则会通过用户设置的archive_command参数中的命令进行归档。归档命令可以很自由地被指定，一般是cp或者加上压缩命令，如果设置该参数，或者命令有错误，则无法真正归档。

%p：源文件路径
%f：源文件名

archive_timeout参数
如果只在日志切换时归档，假如在日志段未满时宕机，则归档日志会缺失一部分，可能造成数据丢失。另外，如果业务写请求较少，日志可能长期不归档。此时，可以通过archive_timeout参数设置超时强制归档，提高归档频率。

取值范围 0 到 1073741823

注意，每次日志切换时，即使未写满日志大小依然是16M，因此该参数如果设置太小，可能导致归档过于频繁并且大量浪费空间。
理解:归档文件最小值就是16M,不设置archive_timeout参数,写满16M以上时,才会生成归档文件,如果数据操作少的话,设置了此参数,就会在超过这个时间就生成归档文件,并且归档文件最小是16M.

一文总结postgresql wal日志参数、归档、清理及常用命令 - 墨天轮

#WAL日志占用空间大小

#PostgreSQL13版本后，wal_keep_segments参数废弃了，由wal_keep_size替代此参数

wal_keep_segments
查看pg_wal目录下所能保留的过去日志文件段的最小数目，建议设置大于等于4096，防止全量备份完成之后由于源实例wal日志被移除导致需要重新备份。

说明

适用于PostgreSQL 10、11、12。
SHOW wal_keep_segments;

wal_keep_size

查看pg_wal目录下所能保留的过去日志文件段的最小大小，建议设置大于等于64 GB，防止全量备份完成之后由于源实例wal日志被移除导致需要重新备份。

说明
适用于PostgreSQL 13、14和15。
SHOW wal_keep_size;

wal_keep_size = 2048

重启postgresql

查看归档目录
ls /postgres_archive_data/archive_data/

如果目录中没有文件,执行以下sql后再查看

-- 手动切WAL日志
SELECT pg_switch_wal();

-----------------------------------------------------------

http://www.postgres.cn/docs/12/app-pgbasebackup.html

进行数据库备份.

pg_basebackup -Ft -Pv -Xs -z -D <backupdir> -Z5 -h '<hostname>' -p <port> -U <username> -W

backupdir

导出的备份文件路径。系统会自动创建目录，但是如果该目录已经存在且不为空，则会报错。

hostname

RDS PostgreSQL实例连接地址。

如果相同账号内的ECS实例和RDS实例处于相同地域，且网络类型相同（都为VPC时则必须处于同一个VPC），建议使用内网连接地址。

其他情形建议使用外网连接地址。

说明
通过外网连接地址访问RDS实例，需要实例已申请外网连接地址。详情请参见申请或释放外网地址。

port

RDS PostgreSQL实例端口。

username

RDS PostgreSQL实例账号名称。

-------------------------------------------------

数据恢复

停止当前数据库

sudo systemctl stop postgresql@16-main.service

备份就数据库目录

sudo su - postgres

mv /var/lib/postgresql/16/main /var/lib/postgresql/16/main_orig

mkdir -m 700 /var/lib/postgresql/16/main

cd /var/lib/postgresql/16/main

tar zxvf /postgres_archive/backup_20240123_150500/base.tar.gz

cd pg_wal

tar zxvf /postgres_archive/backup_20240123_150500/pg_wal.tar.gz

修改postgresql.conf

restore_command = 'if [ ! -f /postgres_archive/data/%f ]; then cp /var/lib/postgresql/16/main_orig/pg_wal/%f %p; else cp /postgres_archive/data/%f %p; fi'

恢复数据库到指定时间点：

修改postgresql.conf

recovery_target_time = '2024-04-23 15:30:00'

在/var/lib/postgresql/16/main 生成recovery.signal文件

touch recovery.signal

---------------------------

启动数据库

exit

sudo systemctl start postgresql@16-main.service

~~完整恢复，不设置时间的话，正常启动后，查看/var/lib/postgresql/16/main下没有recovery.signal文件了。~~

判断是否是只读

SELECT pg_is_in_recovery();