postgresql 利用xlog进行热备操作

 更新时间:2021年1月16日 14:46  点击:2088

一、验证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,否则你可能会发现你的备份是不可用的。

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

相关文章

  • PostgreSQL判断字符串是否包含目标字符串的多种方法

    这篇文章主要介绍了PostgreSQL判断字符串是否包含目标字符串的多种方法,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友可以参考下...2021-02-23
  • postgresql 实现多表关联删除

    这篇文章主要介绍了postgresql 实现多表关联删除操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-01-02
  • postgresql数据添加两个字段联合唯一的操作

    这篇文章主要介绍了postgresql数据添加两个字段联合唯一的操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-04
  • PostgreSQL TIMESTAMP类型 时间戳操作

    这篇文章主要介绍了PostgreSQL TIMESTAMP类型 时间戳操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2020-12-26
  • PostgreSQL 中的单引号与双引号用法说明

    这篇文章主要介绍了PostgreSQL 中的单引号与双引号用法说明,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-01
  • postgresql的jsonb数据查询和修改的方法

    这篇文章主要介绍了postgresql的jsonb数据查询和修改的方法,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友可以参考下...2021-03-03
  • 基于Postgresql 事务的提交与回滚解析

    这篇文章主要介绍了基于Postgresql 事务的提交与回滚解析,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-01-18
  • postgresql 补齐空值、自定义查询字段并赋值操作

    这篇文章主要介绍了postgresql 补齐空值、自定义查询字段并赋值操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-01-16
  • PostgreSQL之分区表(partitioning)

    通过合理的设计,可以将选择一定的规则,将大表切分多个不重不漏的子表,这就是传说中的partitioning。比如,我们可以按时间切分,每天一张子表,比如我们可以按照某其他字段分割,总之了就是化整为零,提高查询的效能...2020-07-11
  • Postgresql数据库之创建和修改序列的操作

    这篇文章主要介绍了Postgresql数据库之创建和修改序列的操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-04
  • postgresql 实现查询出的数据为空,则设为0的操作

    这篇文章主要介绍了postgresql 实现查询出的数据为空,则设为0的操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-01-16
  • python 插入Null值数据到Postgresql的操作

    这篇文章主要介绍了python 插入Null值数据到Postgresql的操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-03-06
  • 解决postgresql 自增id作为key重复的问题

    这篇文章主要介绍了解决postgresql 自增id作为key重复的问题,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-04
  • PostgreSQL实现交叉表(行列转换)的5种方法示例

    这篇文章主要给大家介绍了关于PostgreSQL实现交叉表(行列转换)的5种方法,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧...2020-07-11
  • postgresql 利用xlog进行热备操作

    这篇文章主要介绍了postgresql 利用xlog进行热备操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-01-16
  • PostgreSQL 实现定时job执行(pgAgent)

    这篇文章主要介绍了PostgreSQL 实现定时job执行(pgAgent),具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-01-16
  • Postgresql 解决pg掉电后无法重启的问题

    这篇文章主要介绍了Postgresql 解决pg掉电后无法重启的问题,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-01-18
  • Postgresql主从异步流复制方案的深入探究

    这篇文章主要给大家介绍了关于Postgresql主从异步流复制方案的相关资料,文中通过示例代码介绍的非常详细,对大家学习或者使用Postgresql具有一起的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧...2020-07-11
  • PostgreSQL数据库事务出现未知状态的处理方法

    这篇文章主要给大家介绍了PostgreSQL数据库事务出现未知状态的处理方法,需要的朋友可以参考下...2020-07-11
  • PostgreSQL实现批量插入、更新与合并操作的方法

    这篇文章主要给大家介绍了关于PostgreSQL实现批量插入、更新与合并操作的相关资料,文中通过图文以及示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧...2020-07-11