postgresql 利用xlog进行热备操作

一、验证postgresql增量合并的方案

结果：没有有效可行的增量合并方案，暂时放弃

二、梳理postgresql基于wal的增量备份

物理备份与还原适用于跨小版本的恢复但是不能跨平台

逻辑备份与还原备份数据适用于跨版本和跨平台的恢复

postgersql增量备份步骤

1.首先创建归档目录

例如:归档目录为/archive_pg_xlog/xlog

1>mkdir -p /archive_pg_xlog/xlog
2>chown -R postgres:postgres /archive_pg_xlog/xlog
3>chmod 700 /archive_pg_xlog/xlog

2.修改postgresql.conf配置文件

1>wal_level=archive(配置wal的级别)
2>archive_mode=on(开启归档)
3>archive_command='DATE=`date +%Y%m%d`;DIR="/archive_pg_xlog/xlog/$DATE";(test -d $DIR || mkdir -p $DIR) && cp %p $DIR/%f'(配置归档路径)

3.重新启动数据库服务

systemctl restart postgresql（使配置生效）

4.做全量备份

 1>psql -c 'select pg_start_backup(now()::text);'，#执行该命令表示开始备份
 2>tar -cf base.tar /var/lib/pgsql/data/*
 3>psql -c 'select pg_stop_backup();'#tar结束后要关闭备份

5.如果表空间存在，还需要备份表空间，没有则不需要

6.以上操作完成后，增量备份就自动开始了

当xlog写满后，就自动归档到我们指定的目录了

postgresql利用xlog进行还原

模拟灾难，直接删除data目录（或者是data目录下的所有文件丢失）

先检查数据库是否已经停止，如果还未停止需要停止

mkdir data;chown postgres:postgres data;chmod 700 data

将备份的base.tar全量包拷贝到data目录，解压

cp /usr/share/psql/recovery.conf.sample ./data
vim recovery.conf.sample配置restore_command=''
chown postgres:postgres recovery.conf.sample;chmod 700 recovery.conf.sample;mv recovery.conf.sample recovery.conf

启动数据库，当recovery.conf变成了recovery.done时即表示恢复完成

注:xlog文件名由时间线TimeLineID（8位16进制）、逻辑日志文件号（8位16进制）和段文件ID（8位16进制）组成

测试1.

第一次全量时，db有一百万条数据，然后再次插入一百万条数据，模拟灾难，正常恢复

第二次全量时，是在第一次模拟灾难后的场景，即第二次全量备份时有两百万条数据，再次插入十万条数据，模拟灾难，正常恢复

测试2.

再次模拟灾难，在第二次全备份新增十万条数据后，直接删掉data目录

用第一次的全备份数据结合产生的增量来进行恢复，结果，只有两百万条数据，说明该增量不可合并

测试3.

模拟某个xlog文件丢失时

结果：丢失的xlog文件只影响该丢失的xlog文件依赖的全量备份（会导致数据丢失），但是不会影响xlog文件丢失后面的全量备份

对于丢失这种情况暂时还没有找到恢复的办法

如果要还原到最近的那一次,也就是不是因为误操作的等原因的恢复，则不需要配置还原点

recovery_target_name=''(这个是还原点的名字)
recovery_target_time=''(这个是要还原到的具体时间点)
recovery_target_xid='' (以最后结束时的id为准)

创建还原点,也就是还原点的名字select pg_create_restore_point('pitr_test');

创建基于时间的还原点,select now();

创建基于xid的还原点,select txid_current();

补充：PostgreSQL中的log, xlog和clog

pg_log

$PGDATA/pg_log是数据库运行活动日志的默认保存目录，它包括错误信息，查询日志以及启动/关闭数据库的信息。当PostgreSQL启动失败时，这里应该是你第一个应该查看的信息。一些Linux发行版以及其他的软件包管理系统会将这个日志目录移到某些地方，比如：/var/log/postgresql

你可以在pg_log目录里自由地删除、重命名、压缩或者移动文件而不会有什么不好的结果，只要Postgres用户仍然有权限写该目录。如果pg_log随着许多大文件而膨胀，你可能需要在postgresql.conf里减小你想记录日志的事件。

pg_xlog

$PGDATA/pg_xlog是PostgreSQL的事务日志。 这是一些二进制日志文件的集合，文件名类似00000001000000000000008E，它包含最近事务的一些描述数据。这些日志也被用于二进制复制。如果复制、归档或者PITR失败了，当归档正在恢复时，这个目录保存的数据库日志可能会膨胀数GB。这可能会导致你用完你的磁盘空间。不像pg_log，你不能自由地删除、移动或者压缩这个目录的文件。你甚至不能在没有符号链接到该目录的情况下移动这个目录。删除pg_xlog的文件可能会导致不可恢复的数据库损坏。

如果你发现自己处在这样的情况：你发现有100G大小的文件在pg_xlog目录并且数据也启动不了，并且你已经禁止归档/复制并且尝试清理磁盘空间等任何其他的方式，请做以下两个步骤：

1、从pg_xlog目录里移动文件到一个备份磁盘或者共享网络驱动器中，也不要删除它们。

2、移动一些最老的文件，直到足够允许PostgreSQL启动起来。

pg_clog

$PGDATA/pg_clog包含了事务的元数据。这种日志用于告诉PostgreSQL哪个事务已经完成、哪个还没有完成。clog是比较小的并且没有任何理由会膨胀，所以，你应该没有任何理由去碰触它。在任何时候你都不应该从pg_clog里删除文件，如果你这样子做，还不如完全地删除整个数据库目录。缺少clog是不可恢复的。请注意，这意味着，如果你在$PGDATA目录里备份文件，你应该确定同时包含pg_clog和pg_xlog，否则你可能会发现你的备份是不可用的。

以上为个人经验，希望能给大家一个参考，也希望大家多多支持我们。如有错误或未考虑完全的地方，望不吝赐教。

赞 (0)

相关推荐

  • Postgresql去重函数distinct的用法说明

    在项目中我们常会对数据进行去重处理,有时候会用in或者EXISTS函数.或者通过group by也是可以实现查重 不过Postgresql还有自带去重函数:distinct 下面是distinct 的实例: 1.创建表:user CREATE TABLE `user` ( `name` varchar(30) DEFAULT NULL, `age` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `us

  • 开源数据库postgreSQL13在麒麟v10sp1源码安装过程详解

    一.中标麒麟v10sp1在飞腾2000+系统安装略 二.系统依赖包安装 [root@ft2000db opt]# yum install bzip* [root@ft2000db opt]# nkvers ############## Kylin Linux Version ################# Release: Kylin Linux Advanced Server release V10 (Tercel) Kernel: 4.19.90-17.ky10.aarch64 Buil

  • postgresql 切换 log、xlog日志的实现

    os: centos 7.4 db: postgresql 9.6 切换log: select pg_rotate_logfile(); 切换xlog: select pg_switch_xlog(); 补充:postgresql流复制主从切换+resetxlog 主 192.168.3.46 从 192.168.3.47 现象:由于46主库的xlog日志达到7T以上,磁盘空间不足,需要通过pg_resetxlog命令清日志. 解决: 1.修改postgresql参数 checkpoint_ti

  • Postgresql中xlog生成和清理逻辑操作

    0 前言 1.2部分是对XLOG生成和清理逻辑的分析,XLOG暴涨的处理直接看第3部分. 1 WAL归档 # 在自动的WAL检查点之间的日志文件段的最大数量 checkpoint_segments = # 在自动WAL检查点之间的最长时间 checkpoint_timeout = # 缓解io压力 checkpoint_completion_target = # 日志文件段的保存最小数量,为了备库保留更多段 wal_keep_segments = # 已完成的WAL段通过archive_comm

  • postgresql 12版本搭建及主备部署操作

    postgresql 12版本主备部署 环境搭建 centos 7+ postgresql 12.0 # 网络检查 ping -c2 baidu.com #关闭防火墙,selinux systemctl stop firewalld && sudo systemctl disable firewalld sed -ri s/SELINUX=enforcing/SELINUX=disabled/g /etc/selinux/config setenforce 0 ### 配置阿里云yum源

  • Postgres 创建Role并赋予权限的操作

    创建Role CREATE USER <role_name> PASSWORD '<role_password>'; 赋予权限 赋予database所有权限 GRANT ALL ON DATABASE <db_name> TO <role_name>; 赋予只读权限 (不能再db level直接赋予SELECT权限) GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO <role_na

  • postgresql 利用xlog进行热备操作

    一.验证postgresql增量合并的方案 结果:没有有效可行的增量合并方案,暂时放弃 二.梳理postgresql基于wal的增量备份 物理备份与还原适用于跨小版本的恢复但是不能跨平台 逻辑备份与还原备份数据适用于跨版本和跨平台的恢复 postgersql增量备份步骤 1.首先创建归档目录 例如:归档目录为/archive_pg_xlog/xlog 1>mkdir -p /archive_pg_xlog/xlog 2>chown -R postgres:postgres /archive_p

  • postgresql减少wal日志生成量的操作

    postgresql减少wal日志生成量的操作

    1.在繁忙的系统中,如果需要降低checkpoint发生的频率,减少WAL日志的生成量,减轻对系统IO的压力,可以通过以下两种方法. 1) 调整WAL segment大小,最高可以调整到64MB,不过只能通过编译来调整.对于已有系统不太方便: 2) 增大checkpoint_segments设置,使得checkpoint不会过于频繁地被触发: 2.在9.5中,checkpoint_segments被废弃,可以通过新增参数max_wal_size来调整,该参数缺省为1GB,已经是9.4的2倍.但如

  • MySQL备份与恢复之热备（3）

    MySQL备份与恢复之热备（3）

    在上两篇文章(MySQL备份与恢复之冷备,MySQL备份与恢复之真实环境使用冷备)中,我们提到了冷备和真实环境中使用冷备.那从这篇文章开始我们看下热备.显然热备和冷备是两个相对的概念,冷备是把数据库服务,比如MySQL,Oracle停下来,然后使用拷贝.打包或者压缩命令对数据目录进行备份:那么我们很容易想到热备就是在MySQL或者其他数据库服务在运行的情况下进行备份.但是,这里存在一个问题,因为生产库在运行的情况下,有对该库的读写,读写频率有可能高,也可能低,不管频率高低,总会就会造成备份出来的

  • Dell R730服务器6i阵列卡Raid5配置方法(热备)

    Dell R730服务器,有7块5t硬盘,默认做的RAID5.我们的目的是取其中6块硬盘做RAID5,留一块硬盘做热备. 一块SSD系统盘. 热备盘的用处 RAID阵列硬盘中的热备硬盘是没有数据的,它的作用是当有RAID中的硬盘损坏时自动替换损坏的硬盘.如果把热备的硬盘拿到其他不做阵列的机子上看只是一只空盘. 在这里,我具体解释一下 ①6块硬盘做成RAID5 ②6块硬盘做RAID5,一块硬盘做热备盘 这两种配置之间的区别.大家应该都知道RAID5最少需要三块硬盘,其中有一块硬盘是用来做数据冗余的

  • 联想服务器X3650 M2 配置RAID5与热备盘图文方法

    实验环境: 1. 服务器型号联想 System X3650 M2 2. 六块300G SAS硬盘 实验目的: 配置RAID 5 ,搭建重要文件备份服务器. 标注:本教程六块硬盘,其中五块硬盘做RAID 5,另外一块硬盘做热备盘,如果有一块硬或两块硬盘同时损坏及时更换硬盘数据依然正常运行 (热备盘作用是RAID5只允许其中有一块损坏,如果同时损坏两块硬盘以上数据就会丢失,但热备盘可以替换上去,RAID5同时损坏两块硬盘数据 依然正常运行),如果有三块或三块以上硬盘损坏就会发生灾难性数据丢失.RAI

  • 详解Mysql双机热备和负载均衡的实现步骤

    MySQL数据库没有增量备份的机制,但它提供了一种主从备份的机制,就是把主数据库的所有的数据同时写到备份数据库中.实现MySQL数据库的热备份. 下面是具体的主从热备份的步骤: 假设主服务器A(master).从服务器为B(slave) A:192.168.0.104 B:192.168.0.169 1.主服务器授权 授权副服务器可以连接主服务器并可以进行更新.这是在主服务器上进行的,创建一个username和password供副服务器访问时使用.也可以使用主服务器默认的帐号和密码. 2.数据复

  • mysql双机热备实现方案【可测试】

    一.概念 1.热备份和备份的区别 热备份指的是:High Available(HA)即高可用,而备份指的是Backup,数据备份的一种.这是两种不同的概念,应对的产品也是两种功能上完全不同的产品.热备份主要保障业务的连续性,实现的方法是故障点的转移.而备份,主要目的是为了防止数据丢失,而做的一份拷贝,所以备份强调的是数据恢复而不是应用的故障转移. 2.什么是双机热备? 双机热备从广义上讲,就是对于重要的服务,使用两台服务器,互相备份,共同执行同一服务.当一台服务器出现故障时,可以由另一台服务器承

  • Docker+keepalived+nginx实现主从热备的方法示例

    前言 为解决单点故障,我们需要配置主从热备方案,服务器数量有限,故使用Docker模拟安装配置. 本次配置默认已经安装了Docker. 配置环境:centos7 64位 docker版本:Docker version 17.12.1-ce, build 7390fc6 1,拉取centos7镜像 docker pull centos:7 2,创建容器 docker run -it -d --name centos1 -d centos:7 3,进入容器centos1 docker exec -i

  • PostgreSQL利用递归优化求稀疏列唯一值的方法

    在数据库中经常会碰到一些表的列是稀疏列,只有很少的值,例如性别字段,一般就只有2种不同的值. 但是当我们求这些稀疏列的唯一值时,如果表的数据量很大,速度还是会很慢. 例如: 创建测试表 bill=# create table t_sex (sex char(1), otherinfo text); CREATE TABLE bill=# insert into t_sex select 'm', generate_series(1,10000000)||'this is test'; INSER

  • 关于使用Keepalived实现Nginx的自动重启及双主热备高可用问题

    目录 1.概述 2.使用Keepalived实现Nginx的自动重启 2.1 借助Shell脚本,完成Nginx的重启 2.2 为脚本赋予运行权限 2.3 在Keepalived配置文件中增加配置 2.4 重启Keepalived服务 3.双主热备的搭建 3.1 双主热备概述 3.2 场景说明 3.3 修改A服务器配置 3.4 修改B服务器配置 3.5 重启两台服务器的Keepalived 3.6 设置DNS轮询 4.云服务器的负载均衡 5.综述 1.概述 之前我们使用Keepalived实现了

随机推荐