PostgreSQL — 基于 Recovery 流复制的数据备份

Stella981
• 阅读 722

目录

文章目录

  • 目录
  • Recovery 流复制
    • 主库上操作
    • 备库上操作
    • 功能测试
  • 相关配置参数
    • 归档恢复
    • 恢复目标
    • 备份服务器
  • PostgreSQL 12 的 Recovery
    • pg_basebackup 命令差异

Recovery 流复制

早在 PostgreSQL 9.1 推出的 pg_basebackup 工具,用来搭建流复制的备库。

主备环境:

  • PG SQL 版本:9.3
  • 主库 IP: 192.168.1.36
  • 备库 IP: 192.168.1.35

主库上操作

  1. 创建复制用户。

    CREATE USER repuser REPLICATION LOGIN CONNECTION LIMIT 2 ENCRYPTED PASSWORD 'rep123us345er';

  2. 设置 pg_hba.conf,添加以下:

    host replication repuser 192.168.1.35/32 md5

  3. 设置主库 postgresql.conf

    checkpoint_segments = 16 archive_mode = on archive_command = '/bin/date' max_wal_senders = 3 wal_keep_segments = 16 max_wal_senders = 3

  4. 重载配置文件

    $ pg_ctl reload -D $PGDATA

  5. 查看表空间目录

    postgres=# \db List of tablespaces Name | Owner | Location
    ---------------+----------+------------------------------------- pg_default | postgres | pg_global | postgres | tbs_francs | postgres | /database/pg93/pg_tbs/tbs_francs tbs_source_db | postgres | /database/pg93/pg_tbs/tbs_source_db (4 rows)

  6. 查看数据目录

    $ echo $PGDATA /database/pg93/pg_root

注:表空间目录和数据目录,因为这些目录需要在备库主机上手工创建。

备库上操作

  1. 创建表空间目录和数据目录并赋权

    $ mkdir -p /database/pg93/pg_tbs/tbs_francs $ mkdir -p /database/pg93/pg_tbs/tbs_source_db [root@redhat6 pgsql9.3beta1]# mkdir -p /database/pg93/pg_root

    $ chown -R pg93:pg93 /database/pg93/pg_tbs/tbs_francs $ chown -R pg93:pg93 /database/pg93/pg_tbs/tbs_source_db $ chown -R pg93:pg93 /database/pg93/pg_root $ chmod 0700 /database/pg93/pg_root

  2. 创建 .pgpass

    $ cat .pgpass 192.168.1.36:1925:replication:repuser:rep123us345er

    $ chmod 0600 .pgpass

  3. 使用 pg_basebackup 生成备库

    $ pg_basebackup -D /database/pg93/pg_root -Fp -Xs -v -P -h 192.168.1.36 -p 1925 -U repuser

    transaction log start point: 1/1B000024 on timeline 1 pg_basebackup: starting background WAL receiver 651493/651493 kB (100%), 3/3 tablespaces
    transaction log end point: 1/1B0000DC pg_basebackup: waiting for background process to finish streaming ... pg_basebackup: base backup completed

这时表空间目录,数据($PGDATA)目录都复制过来了,这里使用了 -X 参数,在备份完成之后,会到主库上收集 pg_basebackup 执行期间产生的 WAL 日志,在 9.2 版本之后支持 -Xs 即 stream(流)形式,这种模式不需要收集主库的 WAL 文件,而能以 stream 复制方式直接同步主库。

  1. 设置备库 postgresql.conf

    hot_standby = on

  2. 设置从库 recovery.conf

    $ cp /opt/pgsql9.3beta1/share/recovery.conf.sample recovery.conf

    vi recovery.conf

    standby_mode = on primary_conninfo = 'host=192.168.1.36 port=1925 user=repuser' trigger_file = '/database/pg93/pg_root/postgresql.trigger.1925'

  3. 启动服务

    $ pg_ctl start -D $PGDATA server starting

  4. 查看备库进程

    $ ps -ef | grep pg93 pg93 31398 1 0 21:09 pts/0 00:00:00 /opt/pgsql9.3beta1/bin/postgres -D /database/pg93/pg_root pg93 31399 31398 0 21:09 ? 00:00:00 postgres: logger process
    pg93 31400 31398 0 21:09 ? 00:00:00 postgres: startup process waiting for 00000001000000010000001A pg93 31401 31398 0 21:09 ? 00:00:00 postgres: checkpointer process
    pg93 31402 31398 0 21:09 ? 00:00:00 postgres: writer process
    pg93 31403 31398 0 21:09 ? 00:00:00 postgres: stats collector process
    pg93 31404 31398 0 21:09 ? 00:00:00 postgres: wal receiver process

  5. 查看主库进程

    $ ps -ef | grep pg93 pg93 2504 1 0 Jun28 ? 00:00:26 /opt/pgsql9.3beta1/bin/postgres -D /database/pg93/pg_root pg93 2505 2504 0 Jun28 ? 00:00:00 postgres: logger process
    pg93 2507 2504 0 Jun28 ? 00:00:08 postgres: checkpointer process
    pg93 2508 2504 0 Jun28 ? 00:00:28 postgres: writer process
    pg93 2509 2504 0 Jun28 ? 00:00:08 postgres: wal writer process
    pg93 2510 2504 0 Jun28 ? 00:00:19 postgres: autovacuum launcher process
    pg93 2511 2504 0 Jun28 ? 00:00:00 postgres: archiver process last was 000000010000000100000019.00000024.backup pg93 2512 2504 0 Jun28 ? 00:00:44 postgres: stats collector process
    pg93 31898 2504 0 21:09 ? 00:00:00 postgres: wal sender process repuser 192.168.1.35(39545) idle

功能测试

  • 主库

    $ psql psql (9.3beta1) Type "help" for help.

    postgres=# create table test_1 (id int4,create_time timestamp(0) without time zone); CREATE TABLE

    postgres=# insert into test_1 values (1,now()); INSERT 0 1

    postgres=# select * from test_1; id | create_time
    ----+--------------------- 1 | 2013-07-01 21:15:34 (1 row)

  • 备库

    $ psql psql (9.3beta1) Type "help" for help.

    postgres=# select * from test_1 ; id | create_time
    ----+--------------------- 1 | 2013-07-01 21:15:34 (1 row)

相关配置参数

归档恢复

  • restore_command (string):获取 WAL 文件的一个已归档段的 Shell 指令。这个参数是归档恢复所必需的,但是对于流复制是可选的。

    restore_command = 'cp /mnt/server/archivedir/%f "%p"'

  • archive_cleanup_command (string):清除不再被备份服务器需要的旧的已归档 WAL 文件。%r 会被替换为包含最后一个可用重启点的文件的名称。因此比 %r 更早的所有文件可以被安全地移除。

    archive_cleanup_command = 'pg_archivecleanup /mnt/server/archivedir %r'

  • recovery_end_command (string):归档恢复结束后执行的指令。目的是为复制或恢复之后的清除提供一种机制。与 archive_cleanup_command 相似,任何 %r 会被替换为包含最后一个可用重启点的文件的名称。

恢复目标

默认情况下,恢复会一直恢复到 WAL 日志的末尾。在 recovery_target、recovery_target_lsn、recovery_target_name、recovery_target_time 和 recovery_target_xid 中,最多只能使用一个,如果在配置文件中使用了多个,将使用最后一个。

  • recovery_target = ‘immediate’:指定恢复应该在达到一致状态后尽快结束。在从一个在线备份中恢复时,这意味着备份结束的那个点。
  • recovery_target_name (string):指定 pg_create_restore_point() 所创建的已命名的恢复点,进行恢复。
  • recovery_target_time (timestamp):指定按时间戳恢复。
  • recovery_target_xid (string):指定按事务 ID 进行恢复。
  • recovery_target_lsn (pg_lsn):指定按继续进行的预写日志位置的 LSN 进行恢复。
  • recovery_target_inclusive (boolean):指定是否仅在指定的恢复目标之后停止(true), 或者仅在恢复目标之前停止(false)。 适用于 recovery_target_lsn、recovery_target_time 或者 recovery_target_xid被 指定的情况。这个设置分别控制事务是否有准确的目标 WAL 位置(LAN)、提交时间或事务 ID 将被包括在该恢复中。 默认值为 true。
  • recovery_target_timeline (string):指定恢复到一个特定的时间线中。默认值是沿着基础备份建立时的当前时间线恢复。将这个参数设置为 latest 会恢复到该归档中能找到的最新的时间线。
  • recovery_target_action (enum):指定在达到恢复目标时服务器应该立刻采取的动作,包括 pause(暂停)、promote(接受连接)、shutdown(停止服务器),其中 pause 为默认动作。

备份服务器

  • standby_mode (boolean):on 表示作为一个备库,否则作为主库。为 on 时,当到达已归档 WAL 末尾时该服务器将不会停止恢复,但是将通过使用r estore_command 获得新的 WAL 段以及/或者通过使用 primary_conninfo 设置连接到主服务器来尝试继续恢复。
  • primary_conninfo (string):指定备库用来连接主库的连接字符串。
  • primary_slot_name (string):有选择地指定通过流复制连接到主库时,使用一个现有的复制槽来控制上游节点上的资源移除。如果没有设置 primary_conninfo 则这个设置无效。
  • promote_trigger_file (string):指定一个触发器文件,如果该文件存在,则会结束备库中的恢复,即升级备库为一个独立的主库。即使这个值没有被设置,你也可以通过 pg_ctl promote 来提升备库。如果 standby_mode 为 off,那么这个设置没有效果。
  • recovery_min_apply_delay (integer):允许你将恢复延迟一段固定的时间,如果没有指定单位则以毫秒为单位。例如,如果你设置这个参数为 5min,对于一个事务提交,只有当备库上的系统时钟超过主库报告的提交时间至少 5 分钟时,备库才会重放该事务。

注意:当 synchronous_commit 被设置为 remote_apply 时,同步复制会受到这个设置的影响,每一个 COMMIT 都需要等待被应用。

PostgreSQL 12 的 Recovery

PostgreSQL 12 的一个重要变化就是将 Recovery.conf 文件参数合并到了 postgresql.conf。之前版本 PostgreSQL 的流复制备库是通过在 $PGDATA 目录中创建 recovery.conf 文件来标识的,这是流复制部署的重要文件,若 $PGDATA 目录下不存在此文件,数据库无法以流复制备库角色启动。PostgreSQL 12 之后 recovery.conf 不再使用,若 recovery.conf 文件存在,数据库将无法启动。

此外,还:

  • 新增 recovery.signal 标识文件,表示数据库处于 recovery 模式。
  • 新增加 standby.signal 标识文件,表示数据库处于 standby 模式。
  • trigger_file 参数更名为 promote_trigger_file。
  • standby_mode 参数不再支持。

pg_basebackup 命令差异

12 版本pg_basebackup 命令的 -R 参数的效果和之前不同,主要体现在:

  • 命令执行后在 $PGDATA 目录创建 standby.signal 标识文件,文件内容为空。
  • 命令执行后在 $PGDATA 目录的 postgresql.auto.conf 文件中添加 primary_conninfo 参数信息。
点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
待兔 待兔
4个月前
手写Java HashMap源码
HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22
Jacquelyn38 Jacquelyn38
3年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
Wesley13 Wesley13
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
3年前
Java日期时间API系列36
  十二时辰,古代劳动人民把一昼夜划分成十二个时段,每一个时段叫一个时辰。二十四小时和十二时辰对照表:时辰时间24时制子时深夜11:00凌晨01:0023:0001:00丑时上午01:00上午03:0001:0003:00寅时上午03:00上午0
Wesley13 Wesley13
3年前
00:Java简单了解
浅谈Java之概述Java是SUN(StanfordUniversityNetwork),斯坦福大学网络公司)1995年推出的一门高级编程语言。Java是一种面向Internet的编程语言。随着Java技术在web方面的不断成熟,已经成为Web应用程序的首选开发语言。Java是简单易学,完全面向对象,安全可靠,与平台无关的编程语言。
Stella981 Stella981
3年前
Django中Admin中的一些参数配置
设置在列表中显示的字段,id为django模型默认的主键list_display('id','name','sex','profession','email','qq','phone','status','create_time')设置在列表可编辑字段list_editable
Wesley13 Wesley13
3年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
Python进阶者 Python进阶者
10个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这