PostgreSQL的流复制工具pg_basebackup使用

发布时间：2020-12-13 17:31:36 所属栏目：百科来源：网络整理

导读：之前写过怎么在线搭流复制的，参考http://my.oschina.net/Kenyon/blog/54967，步骤相对比较多，需要走3步。其实还有一个工具可以一步到位的，叫pg_basebackup. 一、介绍 [postgres@test ~]$ pg_basebackup --helppg_basebackup takes a base backup of a runn

之前写过怎么在线搭流复制的，参考http://my.oschina.net/Kenyon/blog/54967，步骤相对比较多，需要走3步。其实还有一个工具可以一步到位的，叫pg_basebackup.

一、介绍

[postgres@test ~]$ pg_basebackup --help
pg_basebackup takes a base backup of a running PostgreSQL server.

Usage:
  pg_basebackup [OPTION]...

Options controlling the output:
  -D,--pgdata=DIRECTORY receive base backup into directory
  -F,--format=p|t       output format (plain (default),tar)
  -R,--write-recovery-conf
                         write recovery.conf after backup
  -x,--xlog             include required WAL files in backup (fetch mode)
  -X,--xlog-method=fetch|stream
                         include required WAL files with specified method
  -z,--gzip             compress tar output
  -Z,--compress=0-9     compress tar output with given compression level

General options:
  -c,--checkpoint=fast|spread
                         set fast or spread checkpointing
  -l,--label=LABEL      set backup label
  -P,--progress         show progress information
  -v,--verbose          output verbose messages
  -V,--version          output version information,then exit
  -?,--help             show this help,then exit

Connection options:
  -d,--dbname=CONNSTR   connection string
  -h,--host=HOSTNAME    database server host or socket directory
  -p,--port=PORT        database server port number
  -s,--status-interval=INTERVAL
                         time between status packets sent to server (in seconds)
  -U,--username=NAME    connect as specified database user
  -w,--no-password      never prompt for password
  -W,--password         force password prompt (should happen automatically)

Report bugs to .

二、准备
主：10.1.11.71
从：10.1.11.72
VIP：10.1.11.73
PG 9.2.3

1.主备机安装(略)，以下修改都是在主机上修改除去第二点
备机只需要安装软件，不需要init数据库，主机开启归档和热备参数，在postgresql.conf文件里面配置
max_wal_senders = 2
wal_level = hot_standby
archive_mode = on
archive_command = 'cd ./'
hot_standby = on
wal_keep_segments = 32

2.配置信任关系(主机上都配置，减少切换后的操作)
a.配置
vi .pgpass
10.1.11.73:5678:postgres:repuser:123456
chmod 0400 .pgpass

b.配置pg_hba.conf
host all all 10.1.11.73/32 md5

c.创建流复制用户
postgres=# CREATE USER repuser replication LOGIN CONNECTION LIMIT 3 ENCRYPTED PASSWORD 'Rep_123';

d.检查备机的表空间和需要同步的数据文件路径，确保是空的，否则会报错

三、同步

在从机上执行

[postgres@test ~]$ pg_basebackup -D /database/pgdata/ -Fp -Xs -v -P -h 10.1.11.73 -U repuser -p 5678
transaction log start point: 2/DF000028 on timeline 3
pg_basebackup: starting background WAL receiver
177422/177422 kB (100%),1/1 tablespace                                        
transaction log end point: 2/DF015EE0
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed

四、同步后配置

1.修改reconvery.conf文件
如果主机上是recovery.done，那备机上传过来的这个文件需要改为recovery.conf，另外修改下内容,一般是改下IP密码居多

standby_mode = 'on'
trigger_file = '/database/pgdata/postgresql.trigger.1949'
primary_conninfo = 'host=10.1.11.73 port=5678 user=repuser password=123456 keepalives_idle=60'

五、启动验证
pg_ctl -$PGDATA start

六、参考
http://my.oschina.net/Kenyon/blog/54967
http://www.depesz.com/2011/01/24/waiting-for-9-1-pg_basebackup/
http://francs3.blog.163.com/blog/static/4057672720136210240967/

七、其他
这个工具印象上是linux的rsync功能外加支持DB在线部署，简化了之前的三步操作，另外支持在线数据同步，不需要另外关闭服务保证数据统一

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!