1传统的搭建流复制备库步骤为以下:
select pg_start_backup();
复制数据文件;
select pg_stop_backup();
而 pg_basebackup 则省略以上步骤，一步搞定，对于有多个数据目录的库来说，pg_basebackup工具比上面步骤要简单多了，并且可以在线操作，下面演示下。
2主库上操作
2.1创建复制用户
| CREATE USER wslu REPLICATION LOGIN CONNECTION LIMIT 2 ENCRYPTED PASSWORD ' wslu1234'; |
2.2设置pg_hba.conf，添加以下
| host replication wslu 192.168.10.150/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重载配置文件
| [wslu@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查看数据目录
| [wslu@redhatB pg_xlog]$ echo $PGDATA /database/pg93/pg_root |
备注：先查看表空间目录和数据目录，因为这些目录需要在备库主机上手工创建。
3备库操作
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
| [wslu@redhat6 ~]$ cat .pgpass 192.168.1.36:1925:replication:wslu: wslu1234 [wslu@redhat6 ~]$ chmod 0600 .pgpass |
备注：注意 .pgpass文件权限为 0600。
3.3使用pg_basebackup生成备库
| [wslu@redhat6 pg93]$ pg_basebackup -D /database/pg93/pg_root -Fp -Xs -v -P -h 192.168.1.36 -p 1925 -U wslu 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 复制方式直接追赶主库。
3.4设置从库postgresql.conf
| hot_standby = on |
3.5设置从库recovery.conf
3.5.1 生成recovery.conf
| [wslu@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=wslu' trigger_file = '/database/pg93/pg_root/postgresql.trigger.1925' |
3.6启服务
| [wslu@redhat6 pg_root]$ pg_ctl start -D $PGDATA server starting |
3.7@H_424_301@查看备库进程
| [wslu@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查看主库进程
| [wslu@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 wslu 192.168.10.150(39545) idle |
4测试
4.1主库
| [wslu@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备库
| [wslu@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) |
备注：流复制搭建完成。
5附：pg_basebackup参数
| [wslu@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) |
