Windows下面基于pgsql15的备份和恢复

一、基础备份

1.创建一个文件用来存储备份数据

2.备份指令

$CurrentDate = Get-Date -Format "yyyy-MM-dd"
$OutputDirectory = "D:\PgsqData\pg_base\$CurrentDate"
$Command = "./pg_basebackup -h 127.0.0.1 -U postgres -Ft -Pv -Xf -z -Z5 -D $OutputDirectory"
Invoke-Expression -Command $Command

3.备份成功之后会在你创建的文件夹生成一个压缩包

4.恢复操作。关闭pgsql服务,并将原pg的数据文件更改一个名字,将生成的文件解压到原pg数据文件夹

 $PGDATA = "C:\Program Files\PostgreSQL\14\data"
 ./pg_ctl stop -D $PGDATA

提示:备份的文件权限比较高,数据库启动不了,要将所有备份的文件给进行授权,给User用户完全的权限。

重启数据库就可以启动了,此方法也可以用做数据库的迁移,只需要把压缩包解压到新的数据库下面就可以了。

二、归档备份:最后时间

上面的方式简单是简单,但是每次备份都要重新备份整个库,库大的情况下,太过于浪费时间,推荐使用增量备份,每天进行一次归档保存,恢复的时候,只需要把基础数据恢复一次,然后逐次恢复归档数据就可以了。

红色字体为说明,可看可不看

归档备份的原理:PostgreSQL在执行写入操作时,对数据文件的任何修改信息,首先会写入WAL日志,然后才会对数据文件做物理修改。如果数据库服务器掉电或意外宕机,则PostgreSQL重新启动后首先会读取WAL日志,然后根据日志对数据进行恢复。一般来说,通过数据库的全量备份(基础备份)配合WAL日志,是可以将数据库恢复到任意时间点的。
WAL日志的文件个数不是无限增长的,当增长到第N个(N=wal_keep_segments+1)文件时,PostgreSQL会从头开始循环覆盖已生成的WAL日志文件。在恢复时,如果需要的WAL日志文件已经被覆盖,则恢复必然会失败。因此,用户需要根据业务情况来定时对WAL日志文件进行归档。
在恢复数据时,如果使用很久以前的全量备份和归档日志进行恢复,由于需要恢复的WAL日志文件非常多,所以恢复时间会很长。为了解决这个问题,用户应该定期对数据库重新做全量备份,这样在每次恢复时需要恢复的WAL日志文件就比较少,可以缩短恢复过程。
1.开启WAL归档
WAL归档实质上是把在线的WAL日志文件备份出来。在PostgreSQL中配置归档的方法是:设置archive_command参数(其值可以是一个shell命令或一个复杂的shell脚本),并把WAL日志文件复制到其他地方。在postgresql.conf文件中需要修改3个配置项,具体步骤如下。

#归档备份的级别
#wal_level = replica
#启动归档
#archive_mode = on
#设置归档位置
#archive_command = 'copy "%p" D:\\PgsqData\\pg_archive\\"%f"'

1.在配置文件种开启上述三个指令之后,然后做一个测试,假设数据库发生故障。需要用基础备份和归档进行恢复。

create table test (id integer);

insert into test values(generate_series(1,10));//在这个位置对基础数据进行备份,此时备份数据库中的数据只有10个
insert into test values(generate_series(1,10));//再加入十个数据,然后进行手动归档
select pg_switch_wal();  //手动触发一次归档,在手动

解释:在插入第一次10条数据后,进行数据库的备份,此时备份数据库只有前十个,插入第二次10个数据的时候,假设在之后的执行过程中,数据库发生错误,靠基础备份和归档文件恢复到最近的时间点。

2.然后重复上面的第四步,先进行基础数据恢复,在数据恢复之后,只有最开始的10条数据,现在启动时间点恢复,做两个配置。

restore_command = 'copy D:\\PgsqData\\pg_archive\\%f %p'  //归档文件目录
recovery_target_timeline = 'latest'                       //最后的时间线

3.删除pg_wal里面的数据

4.创建recovery.signal文件,这个文件是提示pg做恢复操作。

5.重新启动服务就可以了。

三、归档备份:基于时间点恢复

1.模拟一个误删操作,创建表数据,插入数据,记录当前时间点,一分钟后删除,更新归档文件。

2.删除data文件,把备份数据copy进来,继续上述流程,修改配置文件,其他操作还是和上面一样的。

recovery_target_time = '2024-01-03 14:44:25.180169+08'	# the time stamp up to which recovery will proceed

3.通过时间点恢复的文件没有插入的功能,但是通过时间线恢复的就有插入功能,按道理来说应该是一样的。

如果不能插入,执行一下就可以了

select pg_wal_replay_resume()

提示一下,在进行时间点还原的时候,要把原data替换掉,不能直接根据归档文件还原,具体原因等我搞懂,我再更新。看到的一个解释是这么说的PostgreSQL 只能向后恢复, 而不能前恢复。因此PostgreSQL恢复时,必须要搭配基础备份实施。

四、时间点恢复与时间线历史文件

时间线历史文件在第二次及后续 PITR 过程中起着重要作用。通过尝试第二次恢复,我们将探索如何使用它。同样,假设你在12:15:00时间点又犯了一个错误,错误发生在时间线ID为2的数据库集簇上

模拟操作,找到数据库data文件,完成备份之后,会在归档文件中生成如下文件。

执行以下sql,生成新表

create table test3 (id integer);

insert into test3 values(generate_series(1,10));
select *from test3
select pg_switch_wal();

 此时归档文件变成下面这样

 假设在后面操作的时候数据库发生崩溃,将数据恢复到test3 ,重复上面恢复数据的流程。

第一次恢复,因为是从归档进行恢复,删除pg_wal里的所有数据。开启数据库,查询到表里面有test3,说明第一次恢复成功,恢复成功,归档文件会生成一个.history结尾的文件

 第二次恢复,在恢复之后的数据里面接着插入一个表

create table test4 (id integer);

insert into test4 values(generate_series(1,10));
select *from test4
select pg_switch_wal();

假设在后面操作的时候数据库发生崩溃,将数据恢复到test3 ,重复上面恢复数据的流程。先移除data,再解压备份。

第二次恢复成功之后,生成一个新的.history文件。

第二天恢复,这是在所有流程绝对合法的情况下进行,假设第一天过完了,我们保存了第一天的归档,从第二天开始出现了错误,因为备份的文件不在一个文件夹,第一天没有用完的wal第二天接着用,所以在恢复的时候应该把所有wal文件都集中到一个文件夹里面,这样新的同名文件会自动覆盖,应该怎么恢复呢。

create table test7 (id integer);

insert into test7 values(generate_series(1,10));
select *from test7


select pg_switch_wal();

猜测,在没有开启归档文件的情况下,数据误删之后,可以通过data里面pg_wal数据进行恢复吗。

如果有基础备份的情况下,并且pg_wal里面的归档没有被自动更新的情况下,可以恢复。

五、主从库设计

存储数据库副本的每个节点被称为副本。每一次对数据库的写入操作都需要传播到所有副本上,否则副本就会包含不一样的数据。最常见的解决方案是基于领导者的复制,也称为主动/被动或主/从复制。其中,副本之一被指定为领导者,也称为主库或首要。当客户端要向数据库写入时,它必须将请求发送给领导者,领导者会将新数据写入其本地存储。其他副本被称为追随者,也称为只读副本、从库、次要或热备。

这种原生复制功能是基于日志传输实现的,是一种通用的复制技术:主库不断发送WAL数据,而每个备库接受WAL数据,并立即重放日志。

· 流复制的启动

· 如何实施流复制

· 管理多个备库

· 备库的故障检测

实践出真知,现在我们安装两个数据库

主库192.168.1.12 5433

从库192.168.1.6   5433

主库开启配置

主库添加一个用于复制的用户replica

CREATE ROLE replica REPLICATION LOGIN PASSWORD '123456';

主库添加白名单

在文件 /var/lib/pgsql/13/data/pg_hba.conf 下添加:

host    all             all             0.0.0.0/0              md5
host    all             all             127.0.0.1/32          trust
host   replication      replica       192.168.1.6/32          trust

主库创建归档目录

mkdir /var/lib/pgsql/13/archivelog

主库设置,开启归档

/var/lib/pgsql/13/data/postgresql.conf

listen_addresses = '*' 
port = 5432
max_connections = 100 
max_wal_size = 1GB
min_wal_size = 80MB
log_timezone = 'Asia/Shanghai'
archive_mode = on
archive_command = 'test ! -f /var/lib/pgsql/13/archivelog/%f && cp %p /var/lib/pgsql/13/archivelog/%f'
wal_level = replica
max_wal_senders = 10
wal_sender_timeout = 60s

配置完重启主库

systemctl restart postgresql-13.service

从库配置

同步主库的data目录

# 删除从库的data目录
rm -rf /var/lib/pgsql/13/data
# 同步主库的data目录,pg_basebackup是PostgreSQL自带的基础备份工具
pg_basebackup -h 192.168.0.100 -U replica -D /var/lib/pgsql/13/data -X stream -P

修改data目录的权限

chmod -R 700 /var/lib/pgsql/13/data

创建文件standby.signal(版本11开始)

/var/lib/pgsql/13/data/standby.signal
(pg版本11后已经废除recovery.conf)

# 表示该节点是从库
standby_mode = on 

修改从库的postgresql.conf文件

primary_conninfo = 'host=192.168.0.100  port=5432  user=replica  password=123456'
hot_standby = on
max_standby_streaming_delay = 30s
wal_receiver_status_interval = 10s
hot_standby_feedback = on

重启从库

systemctl restart postgresql-13.service

验证主库从库时候同步成功

主库查询

postgres=# select * from pg_stat_replication;
  pid  | usesysid | usename | application_name |  client_addr  | client_hostname | client_port |         backend_start         | 
backend_xmin |   state   | sent_lsn  | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | 
sync_state |          reply_time           
-------+----------+---------+------------------+---------------+-----------------+-------------+-------------------------------+-
-------------+-----------+-----------+-----------+-----------+------------+-----------+-----------+------------+---------------+-
-----------+-------------------------------
 21228 |    16384 | replica | walreceiver      | 192.168.0.101 |                 |       39558 | 2022-03-22 23:16:39.903294+08 | 
         490 | streaming | 0/C000A58 | 0/C000A58 | 0/C000A58 | 0/C000A58  |           |           |            |             0 | 
async      | 2022-03-22 23:19:00.131093+08
(1 row)

六、postgresql.conf配置文件学习。

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:/a/319263.html

如若内容造成侵权/违法违规/事实不符,请联系我们进行投诉反馈qq邮箱809451989@qq.com,一经查实,立即删除!

相关文章

Navicat 16 for MySQL:打造高效数据库开发管理工具

随着数据的快速增长和复杂性的提升,数据库成为了现代应用开发中不可或缺的一部分。而在MySQL数据库领域,Navicat 16 for MySQL作为一款强大的数据库开发管理工具,正受到越来越多开发者的青睐。 Navicat 16 for MySQL拥有丰富的功能和直观的界…

功能权限篇

文章目录 1. 如何设计一套权限系统1.1 目标1.2 权限模型1.2.1 模型一RBAC1.2.2 模型二ABAC 2.如何实现菜单的创建?2.1 表结构2.2 前端实现2.3 后端实现 3. 如何实现角色的创建?4.如何给用户分配权限 —— 将菜单赋予角色?5.如何给用户分配权限…

sqlilabs第五十三五十四关

Less-51(GET - GET - Error based - ORDER BY CLAUSE-String- Stacked injection) 手工注入 单引号闭合,和上一关一样堆叠注入解决 自动注入 和上一关一样 Less-52(GET - challenge - Union- 10 queries allowed -Variation 1) 手工注入 这一关开始后面的可以看…

设计模式之备忘录模式【行为型模式】

提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档> 学习的最大理由是想摆脱平庸,早一天就多一份人生的精彩;迟一天就多一天平庸的困扰。各位小伙伴,如果您: 想系统/深入学习某…

QT上位机开发(函数运行时间分析)

【 声明:版权所有,欢迎转载,请勿用于商业用途。 联系信箱:feixiaoxing 163.com】 软件除了功能开发、debug之外,另外一个很重要的部分就是软件的优化和提高。这里面的优化,当然就是希望软件能够处理更多的数…

[Beego]1.Beego简介以及beego环境搭建,bee脚手架的使用,创建,运行项目

一.Beego介绍 Beego是一个开源的基于Golang的MVC框架,主要用于Golang Web开发,Beego可以用来快速开发API、Web、后端服务等各种应用。 Golang 的Web开发框架有很多,从 github star 数量来看Gin>Beego>lris>Echo>Revel>Buffalo 目前国内用的比较多的就…

在ubuntu平台上安装minecraft

一、获取minecraft启动器安装包 登陆minecraft官网Welcome to the Minecraft Official Site | Minecraft,使用已经购买minecraft的microsoft或者mojang账号登陆。 点击Download Launcher 对于ubuntu系统,使用点击debian版本 此后便会自动下载Minecraft.…

基于pyradiomics影像组学特征提取

基于pyradiomics影像组学特征提取 特征提取:1 pyradiomics的使用:1.1,在python环境下安装pyradiomics:1.2,设置特征提取器,获得想要特征:1.2.1 图像类型1.2.2 目标特征设置1.2.3 特征提取器设置 2 代码示例;参考&#…

关于提高IDEA运行速度的说明

1.作者IDEA软件版本和计算机内存 Ultimate 2022.1.2版 2.修改配置提高IDEA运行速度的误区 很多文章会教调内存,但大多是让你调高堆内存,-Xms -Xmx 是最小堆内存和最大堆内存。堆内存越高,说明堆区可放入新对象的数量越多,由于…

关闭免费版pycharm社区版双击shift时出现的搜索框

Pycharm 在双击 shift 的时候总是弹出搜索框,但作为中国玩家,经常需要双击 shift 循环切换中英文。这就很困恼。 下面就解决这个问题。单独关闭双击shift的功能。 步骤 1.左上角 File -> Settings 2. 如图,输入‘advan’ 找到高级设置&…

分享从零开始学习网络设备配置--任务4.4 使用动态路由OSPFv3实现网络连通

任务描述 由于RIPng不适用于复杂的网络,考虑到公司的未来发展,需要不断扩大网络规模。某公司在企业网络升级时,选择 OSPFv3路由协议实现网络连通,降低网络拓扑变化引发的人工维护工作量并加快网络收敛的速度。 公司内部的所有设…

Leetcode26——引出c++ vector中erase()的内部原理

erase是对数组中某个元素进行删除的操作,实际的时间复杂度为O(n) 预备知识 数组在内存中是连续存储的,删除某个位置的时候不能直接删除,只能用后序的元素覆盖 以nums数组为例,target为需要删除的目标数据 方法: ①…

Windows无法登录管理路由器故障排查

问题描述 家里的路由器使用拨号上网,路由器DHCP分发IP的范围是192.168.1.0/24。默认使用192.168.1.1管理路由器。然后拨号上网成功后,修改了私网IP的分发范围:192.168.5.1-192.168.5.10。为了防止有人蹭网,只分配的10个IP地址。修…

Android Studio由于开启代理无法下载依赖,一直在Build model

一、问题描述 正常打开AS项目,一直显示Build model就是不下载依赖 二、问题解决 1、首先选择No Proxy,可以看到这位同学之前是使用的代理。 2、打开下面文件,然后删除某尾的4行。 3、面对提示框,直接点击OK。 4、然后停…

Trans论文复现:基于数据驱动的新能源充电站两阶段规划方法程序代码!

适用平台:MatlabYalmipCplex/Gurobi; 文章提出了一种电动汽车充电站的两阶段规划方法,第一阶段通过蒙特卡洛法模拟充电车辆需求和电池充放电数据来确定充电站位置;第二阶段通过数据驱动的分布鲁棒优化方法优化充电站的新能源和电池…

用Lisp的方言HY跑飞桨训练和推理

用Lisp的方言HY跑飞桨训练和推理 整个项目可以在AIStudio一键跑通:用Lisp的方言HY跑飞桨训练和推理 - 飞桨AI Studio星河社区 初见Lisp 估计很多人都看过《黑客与画家》这本书,百度百科中的条目:《黑客与画家:硅谷创业之父paul…

NUS CS1101S:SICP JavaScript 描述:一、使用函数构建抽象

原文:1 Building Abstractions with Functions 译者:飞龙 协议:CC BY-NC-SA 4.0 心灵的行为,其中它对简单的想法施加其力量,主要有以下三种:1.将几个简单的想法组合成一个复合的想法,从而形成所…

阿里状态机引擎实现

状态机的技术选型看这篇就够了&#xff0c;最后一个直叫好&#xff01; - 掘金 实现一个状态机引擎&#xff0c;教你看清DSL的本质_cola状态机-CSDN博客 一、引入jar包 <!--阿里状态机jar--> <dependency><groupId>com.alibaba.cola</groupId><a…

Python | 三、函数

函数的形参和实参&#xff08;对应卡码网11题句子缩写&#xff09; 除非实参是可变对象&#xff0c;如列表、字典和集合&#xff0c;则此时形参会复制实参的地址&#xff0c;即此时二者指向同一个地址&#xff0c;因此在函数内对形参的操作会影响到实参除这种情况外&#xff0…

Spring自带分布式锁你用过吗?

环境&#xff1a;SpringBoot2.7.12 本篇文章将会为大家介绍有关spring integration提供的分布式锁功能。 1. 简介 Spring Integration 是一个框架&#xff0c;用于构建事件驱动的应用程序。在 Spring Integration 中&#xff0c;LockRegistry 是一个接口&#xff0c;用于管理…