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

早在 PostgreSQL 9.1 版就已新出 pg_basebackup 工具,用来搭建流复制备库,之前一直没有实践,今天补上。

传统的搭建流复制备库步骤为以下:

  1. select pg_start_backup();
  2. 复制数据文件;
  3. 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 创建复制用户

1
2
3
4
5
CREATE USER repuser
REPLICATION
LOGIN
CONNECTION LIMIT 2
ENCRYPTED PASSWORD 'rep123us345er';

2.2 设置 pg_hba.conf,添加以下

1
host  replication   repuser     192.168.1.35/32     md5

2.3 设置主库 postgresql.conf

1
2
3
4
5
6
checkpoint_segments = 16
archive_mode = on
archive_command = '/bin/date'
max_wal_senders = 3
wal_keep_segments = 16
max_wal_senders = 3

备注:仅列出主要参数,其它参数根据实际情况设置。

2.4 重载配置文件

1
2
[pg93@redhatB ~]$ pg_ctl reload -D $PGDATA
server signaled

2.5 查看表空间目录

1
2
3
4
5
6
7
8
9
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 查看数据目录

1
2
[pg93@redhatB pg_xlog]$ echo $PGDATA
/database/pg93/pg_root

备注:先查看表空间目录和数据目录,因为这些目录需要在备库主机上手工创建。

备库上操作

3.1 创建目录并赋权

1
2
3
4
5
6
7
8
[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

1
2
3
4
5
[pg93@redhat6 ~]$ cat .pgpass
192.168.1.36:1925:replication:repuser:rep123us345er

[pg93@redhat6 ~]$ chmod 0600 .pgpass
备注:注意 .pgpass文件权限为 0600

3.3 使用 pg_basebackup 生成备库

1
2
3
4
5
6
7
8
[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

1
hot_standby = on

3.5 设置从库 recovery.conf

3.5.1 生成 recovery.conf

1
[pg93@redhat6 pg_root]$ cp /opt/pgsql9.3beta1/share/recovery.conf.sample recovery.conf

3.5.2 修改以下参数

1
2
3
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 启服务

1
2
[pg93@redhat6 pg_root]$ pg_ctl start -D $PGDATA
server starting

3.7 查看备库进程

1
2
3
4
5
6
7
8
[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 查看主库进程

1
2
3
4
5
6
7
8
9
10
[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 主库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[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 备库

1
2
3
4
5
6
7
8
9
10
11
[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 参数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
[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>.

参考

最后推荐和张文升共同编写的《PostgreSQL实战》,本书基于PostgreSQL 10 编写,共18章,重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等,涵盖大量实战用例!

购买链接:https://item.jd.com/12405774.html

PostgreSQL实战
感谢支持!
0%