pg_basebackup实现流复制

环境

CentOS7
pg14.2

主库的初始化与构建

# 初始化主库
$ initdb -D /tmp/pri_db
# 修改配置文件，全端口监听
$ vim /tmp/pri_db/postgresql.conf
	listen_addresses='*'
	port=5432
# 启动主库，并添加用户repl用于从库的复制访问
$ pg_ctl -D /tmp/pri_db start
$ psql --port 5432 -Upostgres
	CREATE USER repl REPLICATION;
	\q
$ vim /tmp/pri_db/pg_hba.conf
	#增加一行
	host	replication		repl	addr/32	trust
# 重启主库
$ pg_ctl -D /tmp/pri_db/ restart

构建从库并与主库建立流复制连接

Q：主库宕机如何将从库作为主库?

Q：从库升级为主库后如何与其他从库进行连接?

如果要将已有库作为从库，需要先将该库关闭，并确保data目录为空。

如果从库无法连接到主库，请检查系统防火墙配置

$ pg_basebackup -h [主库地址] -U repl -c fast -D /tmp/rep_db -R --slot=[随便起一个槽名] -C\
	--port=[主库端口]
# 查看自动生成的配置文件内容
$ cat /tmp/rep_db/postgresql.auto.conf
# 按需修改从库配置文件
$ vim /tmp/rep_db/postgresql.conf
# 启动从库
$ pg_ctl -D /tmp/rep_db/ start

至此，主从库流复制构建完成。

TEST

#分别登录主从库

#打开拓展视图
\X
#查看主库的复制信息
SELECT * FROM pg_stat_replication;
#查看从库的接收信息
SELECT * FROM pg_stat_wal_recevier;

从库建立连接时的一些参数的解释

-D directory
--pgdata=directory

Sets the target directory to write the output to. pg_basebackup will create this directory (and any missing parent directories) if it does not exist. If it already exists, it must be empty.

When the backup is in tar format, the target directory may be specified as - (dash), causing the tar file to be written to stdout.

This option is required.

-R`
`--write-recovery-conf

Creates a standby.signal file and appends connection settings to the postgresql.auto.conf file in the target directory (or within the base archive file when using tar format). This eases setting up a standby server using the results of the backup.

The postgresql.auto.conf file will record the connection settings and, if specified, the replication slot that pg_basebackup is using, so that streaming replication will use the same settings later on.

-h host
--host=host

Specifies the host name of the machine on which the server is running. If the value begins with a slash, it is used as the directory for a Unix domain socket. The default is taken from the PGHOST environment variable, if set, else a Unix domain socket connection is attempted.

-U username
--username=username

Specifies the user name to connect as.

-c fast|spread
--checkpoint=*`fast|spread

Sets checkpoint mode to fast (immediate) or spread (the default) (see Section 26.3.3 ).

-C
--create-slot

Specifies that the replication slot named by the --slot option should be created before starting the backup. An error is raised if the slot already exists.

-p *`port`*`
`--port=*`port`*

Specifies the TCP port or local Unix domain socket file extension on which the server is listening for connections. Defaults to the PGPORT environment variable, if set, or a compiled-in default.

参考文献

PG官方文档：Chapter 27. High Availability, Load Balancing, and Replication
CentOS 7防火墙快速开放端口配置方法