早在 PostgreSQL 9.1 版就已新出 pg_basebackup 工具，用来搭建流复制备库，之前一直没有
实践，今天补上。
传统的搭建流复制备库步骤为以下:
- select pg_start_backup();
- 复制数据文件;
- select pg_stop_backup();
而 pg_basebackup 则省略以上步骤，一步搞定，对于有多个数据目录的库来说，pg_basebackup
工具比上面步骤要简单多了，并且可以在线操作，下面演示下。
一 环境信息
主机： 笔记本虚拟机
系统： Red Hat Enterprise Linux Server release 6.2
版本： PostgreSQL 9.3beta1
主库IP： 192.168.1.36 主机名：redhatB
备库IP： 192.168.1.35 主机名 redhat6
备注： PostgreSQL 安装略。
二 主库上操作
--2.1 创建复制用户
|
CREATE USER repuser
REPLICATION
LOGIN
CONNECTION LIMIT 2
ENCRYPTED PASSWORD 'rep123us345er';
|
--2.2 设置 pg_hba.conf，添加以下
| host replication repuser 192.168.1.35/32 md5 |
--2.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
|
--2.4 重载配置文件
|
[pg93@redhatB ~]$ pg_ctl reload -D $PGDATA
server signaled
|
--2.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)
|
--2.6 查看数据目录
|
[pg93@redhatB pg_xlog]$ echo $PGDATA
/database/pg93/pg_root
|
三 备库操作
--3.1 创建目录并赋权
|
[root@redhat6 pgsql9.3beta1]# mkdir -p /database/pg93/pg_tbs/tbs_francs
[root@redhat6 pgsql9.3beta1]# mkdir -p /database/pg93/pg_tbs/tbs_source_db
[root@redhat6 pgsql9.3beta1]# mkdir -p /database/pg93/pg_root
[root@redhat6 pgsql9.3beta1]# chown -R pg93:pg93 /database/pg93/pg_tbs/tbs_francs
[root@redhat6 pgsql9.3beta1]# chown -R pg93:pg93 /database/pg93/pg_tbs/tbs_source_db
[root@redhat6 pgsql9.3beta1]# chown -R pg93:pg93 /database/pg93/pg_root
[root@redhat6 pgsql9.3beta1]# chmod 0700 /database/pg93/pg_root
|
--3.2 创建 .pgpass
|
[pg93@redhat6 ~]$ cat .pgpass
192.168.1.36:1925:replication:repuser:rep123us345er
[pg93@redhat6 ~]$ chmod 0600 .pgpass
|
--3.3 使用 pg_basebackup 生成备库
|
[pg93@redhat6 pg93]$ 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
|
会到主库上收集 pg_basebackup 执行期间产生的 WAL 日志，在 9.2 版本之后支持 -Xs 即，
stream 形式，这种模式不需要收集主库的 WAL 文件，而能以 stream 复制方式直接追赶主库。
--3.4 设置从库 postgresql.conf
| hot_standby = on |
--3.5 设置从库 recovery.conf
--3.5.1 生成 recovery.conf
| [pg93@redhat6 pg_root]$ cp /opt/pgsql9.3beta1/share/recovery.conf.sample recovery.conf |
--3.5.2 修改以下参数
|
standby_mode = on
primary_conninfo = 'host=192.168.1.36 port=1925 user=repuser'
trigger_file = '/database/pg93/pg_root/postgresql.trigger.1925'
|
--3.6 启服务
|
[pg93@redhat6 pg_root]$ pg_ctl start -D $PGDATA
server starting
|
--3.7 查看备库进程
|
[pg93@redhat6 pg_xlog]$ 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
|
--3.8 查看主库进程
|
[pg93@redhatB pg_xlog]$ 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
|
四 测试
--4.1 主库
|
[pg93@redhatB ~]$ 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)
|
--4.2 备库
|
[pg93@redhat6 pg_xlog]$ psql
psql (9.3beta1)
Type "help" for help.
postgres=# select * from test_1
postgres=# select * from test_1 ;
id | create_time
----+---------------------
1 | 2013-07-01 21:15:34
(1 row)
|
五 附: pg_basebackup 参数
|
[pg93@redhat6 pg_xlog]$ 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
|
六 参考
- pg_basebackup
- PG: Setting up streaming log replication (Hot Standby )
