Postgresql学习笔记之——流复制搭建（主从结构异步、同步流复制）

PostgreSQL 可以通过流复制技术，从实例级复制出一个与主库一模一样的从库（也称之为备库） 举个简单的例子，在主机 local128 上创建了一个 PostgreSQL 实例，并在实例上创建多个数据库，通过流复制技术可以在另外一台主机如 local29 上创建 个热备只读 PostgreSQL实例，我们通常将 local128 上的数据库称为主库（ Primary Database Master local129 上的数据库称为备库（ Standby Database Slav 时， local128 称为 主节点， local129 称为备节点。

流复制同步方式有同步、异步两种，如果主节点和备节点不是很忙，通常异步模式下备库和主库的延迟时间能控制在毫秒级，，异步流复是指主库上提交事务 不需要等待备库接 WAL 志流并写入备库 WAL 志文件时便返回，而同步流复制相反。

异步流复制部署主要有两种方式，一种方式是拷数据文 方式，另一 方式是 pg_baseback命令行工具，这两方式绝大部分部署步骤都一样，只是数据复制的方式不同而己，

一、异步流复制搭建

实验环境：

角色	主机名	IP	操作系统	Postgresql版本
主节点	local128	192.168.172.128	CenOS 6.5	Postgresql 12
备节点	local129	192.168.172.129	CenOS 6.5	Postgresql 12

部署过程：

1.上传源码包至两台服务器local128、local129。然后分别解压源码包。
local128：

[root@local128 Desktop]# ls
postgresql-12.1  postgresql-12.1.tar.gz

local129：

[root@local129 Desktop]# ls
postgresql-12.1  postgresql-12.1.tar.gz

2.安装依赖包（以下操作需要在local128、local129上分别操作）：
此次安装依赖包使用yum命令安装，至于如何手动配置本地yum源参考文章：Linux配置本地yum源

yum -y install gcc gcc-c++ zlib* readline*

PS：依赖包也可以在源码编译时根据提示进行安装。

3.切换到解压后的目录，然后进行编译和安装（以下操作需要在local128、local129上分别操作）。

编译：

cd postgresql-12.1
./configure --prefix=/data/pg12.1 --with-pgport=1921 

PS：编译简单，只是指定了安装目录与端口号。目录提前创建好，编译时不会验证目录是否存在，到正式安装时就必须要有目录了

4.安装第三方插件，因为很多会用到，所以先提前安装，gmake world是将解压包下contrid目录中所有第三方插件都安装的命令（以下操作需要在local128、local129上分别操作）：

gmake world
......
PostgreSQL, contrib, and documentation successfully made. Ready to install.

5.插件安装完成后，正式安装PG（以下操作需要在local128、local129上分别操作）：

[root@local postgresql-12.1]# gmake install-world
......
PostgreSQL, contrib, and documentation installation complete.

6.安装完毕后，在系统中创建一个普通用户，用于初始化数据库，以及关闭、启动数据库（以下操作需要在local128、local129上分别操作）。

useradd postgres

passwd postgres

7.将安装好的数据库链接到一个常用的目录下，目的是为了以后方便升级（以下操作需要在local128、local129上分别操作）。

[root@local ~]# ln -s /data/pg12.1 /data/pgsql
[root@local ~]# cd /data/
[root@local data]# ls
pg12.1  pgsql

pgsql目录就是使用的安装目录

8.配置环境变量（以下操作需要在local128、local129上分别操作）：

配置文件信息如下：

su - postgres

vim .bash_profile 

cat .bash_profile 

# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
	. ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH

export PGPOST=1921
export PGDATA=/data/pgsql_data
export LANG=en_US.UTF-8
export PGHOME=/data/pgsql
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
export DATE=`date +"%Y%m%d%H%M"`
export PATH=$PGHOME/bin:$PATH:.
export MANPATH=$PGHOME/share/man:$MANPATH
export PGUSER=postgres
export PGHOST=$PGDATA
alias rm='rm -i'
alias ll='ls -lh'
export PGDATABASE=postgres

根据配置文件创建pg使用的数据存放目录。

mkdir /data/pgsql_data

更改属主和属组，在此有点需要注意，我使用ln连接的目录也在/data目录下，不能直接 chown -R进行全部更改属主和属组，先更改/data目录，然后在更改/pgsql_data，保持原始安装目录pgsql12.1和链接形成的pgsql目录属性不变。

chown postgres:postgres /data
cd /data
chown postgres:postgres pgsql_data

ls -l
total 8
drwxr-xr-x 6 root     root     4096 Feb 12 21:03 pg12.1
lrwxrwxrwx 1 root     root       12 Feb 12 21:10 pgsql -> /data/pg12.1
drwxr-xr-x 2 postgres postgres 4096 Feb 12 21:17 pgsql_data

9.初始化数据库（只需要在local128主库上进行操作）。

切换postgres用户，可以使用psql命令查看数据库版本：

[root@local128 data]# su - postgres
[postgres@local128 ~]$ psql -V
psql (PostgreSQL) 12.1

开始初始化数据库，初始化开始会提示输入用户密码，此处用户指的是参数-U后面的postgres用户，postgres初始化后会成为超级用户：

[postgres@local128 ~]$ initdb -D $PGDATA -E UTF8 --locale=C -U postgres -W
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "C".
The default text search configuration will be set to "english".

Data page checksums are disabled.

Enter new superuser password: 
Enter it again: 

fixing permissions on existing directory /data/pgsql_data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... PRC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D /data/pgsql_data -l logfile start

初始化完成后会提示启动数据库，先不启动数据库，启动前修改一下数据库的参数文件与系统的内核参数。
Postgresql数据库的主要参数文件有两个：pg_hba.conf、postgresql.conf，位置在$PGDATA下。

[postgres@local128 ~]$ cd $PGDATA
[postgres@local128  pgsql_data]$ ls
base          pg_dynshmem    pg_logical    pg_replslot   pg_stat      pg_tblspc    pg_wal                postgresql.conf
global        pg_hba.conf    pg_multixact  pg_serial     pg_stat_tmp  pg_twophase  pg_xact
pg_commit_ts  pg_ident.conf  pg_notify     pg_snapshots  pg_subtrans  PG_VERSION   postgresql.auto.conf

10.修改Linux系统内核参数（此操作需要在local128、local129上分别操作）：

vim /etc/sysctl.conf
添加如下参数：
kernel.shmmni = 4096
kernel.sem = 50100 64128000 50100 1280
fs.file-max = 7672460
net.ipv4.ip_local_port_range = 9000 65000
net.core.rmem_default = 1048576
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
是参数生效：
[root@local ~]# sysctl -p

vim /etc/security/limits.conf
添加如下参数：
* soft nofile 131072
* hard nofile 131072
* soft nproc 131072
* hard nproc 131072
* soft core unlimited
* hard core unlimited
* soft memlock 50000000
* hard memlock 50000000

至此部署异步流复制的前期环境已经准备好了，主要是local128、local129两台服务器上都安装了Postgresql 12的软件，local128服务器上初始化了数据库，下面开始进行异步流复制相关的主要的配置与部署。

1.配置主库local128：

（1）修改pg_hba.conf文件，最后一行添加host all all 0.0.0.0/0 md5 所有ip都可通过密码访问数据库，并添加流复制用户，本次用postgres管理员用户进行配置：

[postgres@local128 pgsql_data]$ cat pg_hba.conf 


# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust
host    all             all             0.0.0.0/0            md5
host    replication     postgres     192.168.172.0/0   md5

注意添加的流复制用户配置信息为host replication postgres 192.168.172.0/24 md5，其中192.168.172.0/24指定了网段

（2）修改postgresql.conf参数文件：

（因为是测试环境，所以只修改流复制所需参数即可）

listen_addresses = '*'
port = 1921	
max_connections = 512  # 数据库最大连接数
unix_socket_directories = '.'  # 此参数打开后，本地登录不用指定 -h 127.0.0.1
unix_socket_permissions = 0777
shared_buffers = 128MB
dynamic_shared_memory_type = posix
wal_level = replica    # 日志级别为流复制
max_wal_size = 1GB
min_wal_size = 80MB
archive_mode = on   # 开启归档
#archive_command = 'cp %p /data/pg_archive/%f'  # 配置归档目录
archive_command = 'DATE=`date+%Y%m%d`;DIR="/data/pgsql/arch/$DATE";(test -d $DIR||mkdir -p $DIR)&&cp %p $DIR/%f'
max_wal_senders = 10  # 配置日志发送进程
wal_keep_segments = 10240
logging_collector = on  # 开启数据库运行日志（随着时间日志增大要常处理）
log_directory = '/data/pg_log'   # 配置日志目录
log_timezone = 'PRC'
datestyle = 'iso, mdy'
timezone = 'PRC'
lc_messages = 'C'
lc_monetary = 'C'
lc_numeric = 'C'
lc_time = 'C'
default_text_search_config = 'pg_catalog.english'

以上参数有默认开启的有需要修改的，自己可以对照参考，不再一一说明了。
另外修改的参数中有需要新建目录的，需要手动创建，比如归档目录和日志目录。
这些目录也是需要在备库服务器local129上创建的，这在备库配置时另说。

（3）启动Postgresql数据库：

[postgres@local128 ~]$ pg_ctl start -D $PGDATA
waiting for server to start....2020-02-12 21:40:05.006 CST [18067] LOG:  starting PostgreSQL 12.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-4), 64-bit
2020-02-12 21:40:05.006 CST [18067] LOG:  listening on IPv4 address "0.0.0.0", port 1921
2020-02-12 21:40:05.006 CST [18067] LOG:  listening on IPv6 address "::", port 1921
2020-02-12 21:40:05.016 CST [18067] LOG:  listening on Unix socket "/tmp/.s.PGSQL.1921"
2020-02-12 21:40:05.091 CST [18068] LOG:  database system was shut down at 2020-02-12 21:25:28 CST
2020-02-12 21:40:05.113 CST [18067] LOG:  database system is ready to accept connections
 done
server started

2.备库local129配置

备库local129不用初始化数据库。

（1）在local129上远程使用工具 pg_basebackup 工具将主库local128上的数据备份拷贝到local129上，指定拷贝路径$PGDATA，也就是和local128相同目录下：

[postgres@local129 ~]$ pg_basebackup -h 192.168.172.128 -U postgres -D $PGDATA -X stream -P
.....

[postgres@local129 pgsql_data]$ ls
backup_label.old  pg_hba.conf    pg_replslot   pg_subtrans  pg_xact               
base              pg_ident.conf  pg_serial     pg_tblspc    postgresql.auto.conf
global            pg_logical     pg_snapshots  pg_twophase  postgresql.conf
pg_commit_ts      pg_multixact   pg_stat       PG_VERSION   postmaster.opts
pg_dynshmem       pg_notify      pg_stat_tmp   pg_wal       postmaster.pid

（2）编辑standby.signal文件（在数据文件夹$PGDATA内，以此标识备库）

[postgres@local129 pgsql_data]$ vim standby.signal
#添加一下内容
standby_mode = 'on'

（3）修改备库的postgresql.conf配置文件：

listen_addresses = '*'
port = 1921
max_connections = 1000  #备库需要修改，比主库大
unix_socket_directories = '.'	
unix_socket_permissions = 0777
shared_buffers = 128MB
dynamic_shared_memory_type = posix
wal_level = replica
max_wal_size = 1GB
min_wal_size = 80MB
archive_mode = on
archive_command = 'cp %p /data/pg_archive/%f'
recovery_target_timeline = 'latest'  # 备库需要修改
max_wal_senders = 10
wal_keep_segments = 10240
primary_conninfo = 'host=192.168.172.128 port=1921 user=postgres password=postgres'  # 备库需要修改
hot_standby = on  # 备库需要修改
max_standby_streaming_delay = 30s  # 备库需要修改
wal_receiver_status_interval = 10s  # 备库需要修改
hot_standby_feedback = on  # 备库需要修改
logging_collector = on
log_directory = '/data/pg_log'
log_timezone = 'PRC'
datestyle = 'iso, mdy'
timezone = 'PRC'
lc_messages = 'C'
lc_monetary = 'C'
lc_numeric = 'C'
lc_time = 'C'	
default_text_search_config = 'pg_catalog.english'

（4）启动备库：

[postgres@local129 ~]$ pg_ctl start -D $PGDATA

注意：主库特有的参数配置可以不用修改，保持原样，并不影响流复制。

（3）通过视图pg_stat_replication查看异步流复制信息：

postgres=# select pid,state,client_addr,sync_priority,sync_state,sent_lsn,write_lsn from pg_stat_replication;
  pid  |   state   |   client_addr   | sync_priority | sync_state |  sent_lsn  | write_lsn  
-------+-----------+-----------------+---------------+------------+------------+------------
 15441 | streaming | 192.168.172.129 |             0 | async      | 0/1E4D4BA8 | 0/1E4D4BA8
(1 row)

查看备库状态：

备库是 Hot Standby

1.使用**pg_is_in_recovery()**函数，如果在主库上执行，返回结果为 false：

postgres=# select pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 f
(1 row)

在备库上执行返回 true：

postgres=# select pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 t
(1 row)

备库不是 Hot Standby

无法登录备库，可以使用工具pg_controldata来判断。

主库执行，Database cluster state:in production：

[postgres@local128 ~]$ pg_controldata
pg_control version number:            1201
Catalog version number:               201909212
Database system identifier:           6817703304888402639
Database cluster state:               in production
pg_control last modified:             Tue 28 Apr 2020 11:08:42 AM CST
Latest checkpoint location:           0/1E4D4AF8
Latest checkpoint's REDO location:    0/1E4D4AC0
Latest checkpoint's REDO WAL file:    00000001000000000000001E
Latest checkpoint's TimeLineID:       1
Latest checkpoint's PrevTimeLineID:   1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0:10492
Latest checkpoint's NextOID:          66653
Latest checkpoint's NextMultiXactId:  1
Latest checkpoint's NextMultiOffset:  0
Latest checkpoint's oldestXID:        480
Latest checkpoint's oldestXID's DB:   1
Latest checkpoint's oldestActiveXID:  10492
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint:            Tue 28 Apr 2020 11:08:37 AM CST
Fake LSN counter for unlogged rels:   0/3E8
Minimum recovery ending location:     0/0
Min recovery ending loc's timeline:   0
Backup start location:                0/0
Backup end location:                  0/0
End-of-backup record required:        no
wal_level setting:                    replica
wal_log_hints setting:                off
max_connections setting:              512
max_worker_processes setting:         8
max_wal_senders setting:              10
max_prepared_xacts setting:           0
max_locks_per_xact setting:           64
track_commit_timestamp setting:       off
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 131072
WAL block size:                       8192
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996
Size of a large-object chunk:         2048
Date/time type storage:               64-bit integers
Float4 argument passing:              by value
Float8 argument passing:              by value
Data page checksum version:           0
Mock authentication nonce:            10b179a566c05d41bbf0b682118743a56778eb280406e562cfd06e41d02707b4

备库执行结果，Database cluster state: in archive recovery：

[postgres@local129 ~]$ pg_controldata
pg_control version number:            1201
Catalog version number:               201909212
Database system identifier:           6817703304888402639
Database cluster state:               in archive recovery
pg_control last modified:             Tue 28 Apr 2020 11:08:51 AM CST
Latest checkpoint location:           0/1E4D4AF8
Latest checkpoint's REDO location:    0/1E4D4AC0
Latest checkpoint's REDO WAL file:    00000001000000000000001E
Latest checkpoint's TimeLineID:       1
Latest checkpoint's PrevTimeLineID:   1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0:10492
Latest checkpoint's NextOID:          66653
Latest checkpoint's NextMultiXactId:  1
Latest checkpoint's NextMultiOffset:  0
Latest checkpoint's oldestXID:        480
Latest checkpoint's oldestXID's DB:   1
Latest checkpoint's oldestActiveXID:  10492
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint:            Tue 28 Apr 2020 11:08:37 AM CST
Fake LSN counter for unlogged rels:   0/3E8
Minimum recovery ending location:     0/1E4D4BA8
Min recovery ending loc's timeline:   1
Backup start location:                0/0
Backup end location:                  0/0
End-of-backup record required:        no
wal_level setting:                    replica
wal_log_hints setting:                off
max_connections setting:              512
max_worker_processes setting:         8
max_wal_senders setting:              10
max_prepared_xacts setting:           0
max_locks_per_xact setting:           64
track_commit_timestamp setting:       off
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 131072
WAL block size:                       8192
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996
Size of a large-object chunk:         2048
Date/time type storage:               64-bit integers
Float4 argument passing:              by value
Float8 argument passing:              by value
Data page checksum version:           0
Mock authentication nonce:            10b179a566c05d41bbf0b682118743a56778eb280406e562cfd06e41d02707b4

3.测试

登陆主库，然后创建一张测试表：
local128：

postgres@local128 ~]$ psql
psql (12.1)
Type "help" for help.

postgres=# create table test(id int,name text);
CREATE TABLE

登陆备库，查看表是否已经同步：
local129：

[postgres@local ~]$ psql
psql (12.1)
Type "help" for help.

postgres=# \d
         List of relations
 Schema |  Name  | Type  |  Owner   
--------+--------+-------+----------
 public | test   | table | postgres
(1 rows)

至此，异步流复制搭建完毕！

二、同步流复制搭建

等待更新…