首页 > 资讯 > 其他数据库 > 正文

PostgreSQL：使用 pg_basebackup 搭建流复制环境

16-01-11 来源：[db:作者]

收藏我要投稿

早在 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

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

备注：注意 .pgpass文件权限为 0600。

--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

备注：这时表空间目录，$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

[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 <pgsql-bugs@postgresql.org>.

点击复制链接与好友分享!回本站首页