加入收藏 | 设为首页 | 会员中心 | 我要投稿 李大同 （https://www.lidatong.com.cn/）- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置： 首页 > 百科 > 正文

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

发布时间：2020-12-13 17:08:30 所属栏目：百科 来源：网络整理
导读：1传统的搭建流复制备库步骤为以下: select pg_start_backup(); 复制数据文件; select pg_stop_backup(); 而 pg_basebackup 则省略以上步骤，一步搞定，对于有多个数据目录的库来说，pg_basebackup工具比上面步骤要简单多了，并且可以在线操作，下面演示下。

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查看备库进程

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

Report bugs to <pgsql-bugs@postgresql.org>.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
    相关内容
      推荐文章
        站长推荐
        热点阅读

          【免责声明】本站内容转载自互联网，其发布内容言论不代表本站观点，如果其链接、内容的侵犯您的权益，烦请提交相关链接至邮箱bqsm@foxmail.com我们将及时予以处理。

          建议您使用1920×1080分辨率、谷歌浏览器Google Chrome、Microsoft Edge以获得本站的最佳浏览效果

          Copygight © 2008-2022 https://www.lidatong.com.cn/ All Rights Reserved. 李大同