PostgreSQL的流复制工具pg_basebackup使用

前端之家收集整理的这篇文章主要介绍了PostgreSQL的流复制工具pg_basebackup使用前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
之前写过怎么在线搭流复制的,参考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在线部署,简化了之前的三步操作,另外支持在线数据同步,不需要另外关闭服务保证数据统一

原文链接:https://www.f2er.com/postgresql/195864.html

猜你在找的Postgre SQL相关文章