postgres主备查询

转载

架构领航博主 2024-10-29 13:13:55

文章标签 postgres主备查询 sql 数据库 postgresql 文章分类 云原生云计算

二、主从搭建

2.1测试目标

测试postgresql主从搭建安装过程

2.2环境准备

实例级别的复制

流复制主库可读写，但从库只允许查询不允许写人，而逻辑复制的从库可读写

流复制实验环境

主机	主机名	Ip地址	操作系统	Postgresql版本
主节点	pgsql	192.168.231.131	Redhat7.2	PostgreSQL10
备节点	pgstandby	192.168.231.132	Redhat7.2	PostgreSQL10

在 pgsql 和 pgstandby上创建操作系统用户和相关目录，如下所示：

# groupadd pgsql
# useradd pgsql -g pgsql
# passwd pgsql
# mkdir -p /database/pg10/pg_root
# mkdir -p /database/pg10/pg_tbs
# chown -R pgsql:pgsql /database/pg10
 
/database/pg10/pg_root 目录存储数据库系统数据文件，
/database/pg10/pg_tbs 存储用户自定义表空间文件。
设置 postgres 操作系统用户环境变量，
/home/postgres/.bash _profile 文件添加以下内容：
export PGPORT=5432
export PGUSER=pgsql
export PGDATA=/database/pg10/pg_root
export LANG=en_US.utf8
export PGHOME=/usr/pgsql-10
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib
export PATH=$PGHOME/bin:$PATH:.
export MANPATH＝$PGHOME/share/man:$MANPATH

2.3初始化数据库

[pgsql@pgstandby ~]$ initdb -D /database/pg10/pg_root -E UTF8 --locale=C -U pgsql -W   （初始化创建数据库实例）
-A  authmethod这个选项声明本地用户在 pg_hba.conf 里面使用的认证方法。除非你相信所有本地用户，否则不要使用 trust(缺省)。

-D directory这个选项声明数据库集群应该存放在哪个目录。这是 initdb 需要的唯一信息，但是你可以通过设置 PGDATA 环境变量来避免键入，这样做可能方便一些，因为稍后数据库服务器(postgres)可以通过同一个变量找到数据库目录。

-E encoding 选择模板数据库的编码方式。这将是你以后创建的数据库的缺省编码方式，除非你创建数据库时覆盖了它。缺省是从区域设置中获得的，如果没有区域设置，就是 SQL_ASCII 。

--locale=locale 为数据库集群设置缺省的区域。如果没有声明这个选项，那么区域是从 initdb 运行的环境中继承过来的。

-U username 选择数据库超级用户的用户名。缺省是运行 initdb 的用户的有效用户。超级用户的名字是什么并不重要，但是可以选择习惯的名字 postgres ，即使操作系统的用户名字不一样也没关系。

-W 令 initdb 提示输入数据库超级用户的口令。如果你不准备使用口令认证，这个东西并不重要。否则你将不能使用口令认证，直到你设置了口令。

--pwfile=filename令 initdb 从一个文件里读取数据库超级用户的口令。该文件的第一行将被当作口令使用。

其它不常用的参数还有：

-d 从初始化后端打印调试输出以及一些其它的一些普通用户不太感兴趣的信息。初始化后端是 initdb 用于创建系统表的程序。这个选项生成大量非常枯燥的输出。

-L directory 告诉 initdb 到哪里找初始化数据库所需要的输入文件。通常是不必要的。如果需要你明确声明的话，程序会提示你输入。

-n 缺省时，当 initdb 发现一些错误妨碍它完成创建数据库集群的工作时，它将在检测到不能结束工作之前将其创建的所有文件删除。这个选项禁止任何清理动作，因而对调试很有用。

之后配置 $PGDATA/postgresql.conf 设置以下参数：

listen_addresses = ‘*’             
wal_level = replica # minimal, replica, or logical
archive_mode = on         # enables archiving; off, on, or always
archive_command = ’/ bin/date’   # command to use to archive a logfile segment
max_wal_senders = 10 # max number of walsender processes
wal_keep_segments = 512     # in logfile segment s, 16MB each; 0 disables
hot_standby = on

listen_addresses参数指定哪些ip可以访问，’*’代表所有ip可以访问数据库。

Wal_level参数控制WAL日志信息的输出级别，有minimal、replica、logical三种模式，minimal记录的WAL日志信息最少，除了记录数据库异常关闭需要恢复时的WAL信息外，其他操作信息都不记录；replica记录的WAL信息比minimal信息多些，会记录支持WAL归档、复制和备库中启用只读查询等操作所需的WAL信息；logical记录的WAL日志信息最多，包含了支持逻辑解析(10版本的新特性，逻辑复制使用这种模式，本章后面会介绍）所需的WAL;replica模式记录的WAL信息包含了minimal记录的信息，logical模式记录的WAL信息包含了replica记录的信息，此参数默认值为replica，调整此参数需重启数据库生效，开启流复制至少需要设置此参数为replica级别

Archive_mode参数控制是否启用归档，off表示不启用归档，on表示启用归档并使用archive_command参数的配置命令将WAL日志归档到归档存储上，此参数设置后需重启数据库生效，这里通常设置成on。

archive_command参数设置WAL归档命令，可以将WAL归档到本机目录，也可以归档到远程其他主机上，由于流复制的配置并不一定需要依赖配置归档命令，我们将归档命令暂且设置成伪归档命令/bin/date，后期如果需要打开归档直接配置归档命令即可。

max_wal_senders参数控制主库上的最大WAL发送进程数，通过pg_base_backup命令在主库上做基准备份时也会消耗WAL进程，此参数设置不能比max_connections参数值高，默认值为10，一个流复制备库通常只需要消耗流复制主库一个WAL发送进程。

wal_keep_segments参数设置主库pg_wal目录保留的最小WAL日志文件数，以便备库落后主库时可以通过主库保留的WAL进行追回，这个参数设置得越大，理论上备库在异常断开时追平主库的机率越大，如果归档存储空间充足，建议将此参数配置得大些，由于默认情况下每个WAL文件为16MB（编译时可通过--with-walsegsize参数设置WAL文件大小），因此pg_wal目录大概占用空间为wal_keep_segments参数值×16MB，这里为512×16MB=8GB，实际情况下pg_wal目录下的WAL文件数会比此参数的值稍大。

hot_standby参数控制数据库恢复过程中是否启用读操作，这个参数通常用在流复制备库，开启此参数后流复制备库支持只读SQL，但备库不支持写操作，主库上也设置此参数为on。

以上是流复制配置过程中主要的 postgresqI. conf参数，其他参数没有列出，主库和备库的 postgresqI. conf配置建议完全一致。

配置主库的pg_hba.conf文件，添加以下内容：

host    replication     repuser         192.168.231.131/24      md5
host    replication     repuser         192.168.231.132/24      md5

意思为允许repuser用户从外部地址通过密码连接任何数据库，配置两个信息是因为以后方便主备切换。

2.4 异步流复制

2.4.1拷贝数据文件形式部署流复制（方案一）

#  chown -R pgsql:pgsql /var/run/postgresql/     --若开启postgresql数据库失败，报/var/run/postgresql/权限不足的情况下，请赋权
drwxr-xr-x.  2 pgsql          pgsql            80 Aug  9 11:03 postgresql
 
$  pg ctl start
使用超级用户pgsql登录到数据库创建流复制用户 repuser，流复制用户需要有 REPLICATION 权限和 LOGIN 权限
[pgsql@pgsql ~]$ psql postgres
psql (10.9)
Type "help" for help.
postgres=# create user repuser replication login connection limit 5 encrypted password 'hufj123';
CREATE ROLE
postgres=# SELECT pg_start_backup('frans_bk1');
 pg_start_backup
-----------------
 0/2000028
(1 row)
pg_ start_ backup（）函数在主库上发起一个在线备份
[pgsql@pgsql pg10]$ tar czvf pg_root.tar.gz pg_root --exclude=pg_root/pg_wal    --主库将$PGDATA路径下的东西进行备份，将pg_wal排除在外是因为，备库会同步这些日志
[pgsql@pgsql pg10]$ scp pg_root.tar.gz pgsql@192.168.231.132:/database/pg10/   --将备份好的tar文件传到备库上去
备库操作：
[pgsql@pgstandby pg10]$ tar xvf pg_root.tar.gz                               --备库进行解压，在$PGDATA目录下
主库操作：
[pgsql@pgsql pg10]$ psql postgres
postgres=# SELECT pg_stop_backup();                                       --主库关闭备份，其实在备份好tar文件之后也可以进行关闭备份
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived
 pg_stop_backup
----------------
 0/2000130
(1 row)
 
[pgsql@pgstandby pg_root]$ cp /usr/pgsql-10/share/recovery.conf.sample  $PGDATA/recovery.conf  --备库拷贝一份recovery.conf文件
[pgsql@pgstandby pg_root]$ vi recovery.conf
recovery_target_timeline = 'latest'                      
standby_mode = on
primary_conninfo = 'host=192.168.231.131 port=5432 user=repuser'
 
[pgsql@pgstandby ~]$ touch .pgpass            --在家目录下面创建.pgpass文件，目的是在连接主库的时候不需要输入密码
[pgsql@pgstandby ~]$ chmod 0600 .pgpass       --注意赋予0600权限
[pgsql@pgstandby ~]$ cat .pgpass
192.168.231.132:5432:replication:repuser:hufj123
192.168.231.131:5432:replication:repuser:hufj123
 
不配置 .pgpass文件则警告日志显示下面信息
2019-08-05 11:37:42.556 CST [76967] FATAL:  could not connect to the primary ser
ver: fe_sendauth: no password supplied
 
遇到错误查看日志 $PGDATA/log/路径的日志。
如果此步没报错，并且主库上可以查看到 WAL 发送进程，同时备库上可以看到 WAL 接收进程说明流复制配置成功，查看主库上的 WAL 发送进程， 如下所示：
 
[pgsql@pgsql ~]$ ps -ef|grep wal
pgsql     35001  34996  0 22:27 ?        00:00:00 postgres: wal writer process
pgsql     35006  34996  0 22:27 ?        00:00:00 postgres: wal sender process repuser 192.168.231.132(55152) streaming 0/7000098
pgsql     35008  34790  0 22:27 pts/1    00:00:00 grep --color=auto wal

查看备库上的 WAL 接收进程，如下所示：

[pgsql@pgstandby ~]$ ps -ef|grep wal
pgsql     59336  59310  1 22:27 ?        00:00:00 postgres: wal receiver process   streaming 0/70000D0
pgsql     59347  58742  0 22:27 pts/3    00:00:00 grep --color=auto wal
接着在主库上创建一个测试表并插入数据，如下所示：
postgres=# create table test_sr(id int4);
CREATE TABLE
postgres=# insert into test_sr values(1);
INSERT 0 1
备库上验证数据是否同步：
 
postgres=# select * from test_sr;
 id
----
  1
(1 row)

2.4.2 pg_basebackup（实例级的备份）方式部署流复制（方案二）

将备库停库，删除数据库，进行测试

[pgsql@pgstandby ~]$ pg_ctl stop
waiting for server to shut down.... done
server stopped
[pgsql@pgstandby ~]$ rm -rf /database/pg10/pg_root/*
[pgsql@pgstandby ~]$ rm -rf /database/pg10/pg_tbs/*
[pgsql@pgstandby ~]$ pg_basebackup -D /database/pg10/pg_root -Fp -Xs -v -P -h 192.168.231.131 -p 5432 -U repuser

-D 参数表示指定备节点用来接收主库数据的目标路径，这里和主库保持一致，依然是／database/pg10/pg_root目录。

-F参数指定pg_basebackup命令生成的备份数据格式，支持两种格式，p(plain)格式和t(tar）格式，p(plain）格式是指生成的备份数据和主库上的数据文件布局一样，也就是说类似于操作系统命令将数据库$PGDATA系统数据文件、表空间文件完全拷贝到备节点；t(tar）格式是指将备份文件打个tar包并存储在指定目录里，系统文件被打包成base.tar，其他表空间文件被打包成oid.tar，其中OID为表空间的OID。

-X参数设置在备份的过程中产生的WAL日志包含在备份中的方式，有两种可选方式，f(fetch）和s(stream),f(fetch）是指WAL日志在基准备份完成后被传送到备节点，这时主库上的wal_keep_segments参数需要设置得较大，以免备份过程中产生的WAL还没发送到备节点之前被主库覆盖掉，如果出现这种情况创建基准备份将会失败，f(fetch）方式下主库将会启动一个基准备份WAL发送进程；s(stream）方式中主库上除了启动一个基准备份WAL发送进程外还会额外启动一个WAL发送进程用于发送主库产生的WAL增量日志流，这种方式避免了f(fetch）方式过程中主库的WAL被覆盖掉的情况，生产环境流复制部署推荐这种方式，特别是比较繁忙的库或者是大库。

-v参数表示启用verbose模式，命令执行过程中打印出各阶段的日志，建议启用此参数，了解命令执行到哪个阶段。

-P参数显示数据文件、表空间文件近似传输百分比，由于执行pg_basebackup命令过程中主库数据文件会变化，因此这只是一个估算值；建议启用此选项，了解数据复制的进度。

[pgsql@pgstandby~]$cp /usr/pgsql-10/share/recovery.conf.sample $PGDATA/recovery.conf    --此文件在pghome/share/下面
[pgsql@pgstandby ~]$ vi /database/pg10/pg_root/recovery.conf
recovery_target_timeline = 'latest'                      
standby_mode = on
primary_conninfo = 'host=192.168.231.131 port=5432 user=repuser'
 
[pgsql@pgstandby ~]$ pg_ctl start
 
postgres=# select usename,application_name,client_addr,sync_state from pg_stat_replication;     --主库查询查看同步模式
 usename | application_name |   client_addr   | sync_state
---------+------------------+-----------------+------------
 repuser | walreceiver      | 192.168.231.132 | async

2.5 同步流复制

异步流复制指主库上提交事务时不需要等待备库接收井写入WAL日志时便返回成功，如果主库异常看机，主库上已提交的事务可能还没来得及发送给备库，就会造成备库数据丢失，备库丢失的数据量和WAL复制延迟有关，WAL复制延迟越大，备库上丢失的数据量越大。同步流复制在主库上提交事务时需等待备库接收并WAL日志，当主库至少收到一个备库发回的确认信息时便返回成功，同步流复制确保了至少一个备库收到了主库发送的WAL日志，一方面保障了数据的完整性，另一方面增加了事务响应时间，因此同步流复制主库的吞吐量相比异步流复制主库吞吐量低。

配置同步流复制

备库recovery.conf配置文件设置以下参数，如下所示：

primary_conninfo=’host=192.168.231.131 port=5432 user=repuser application_name=node2’
 
primary_conninfo参数添加application_name选项，application_name选项指定备节点的别名，主库postgresql.conf的synchronous_standby_names参数可引用备库application_name选项设置的值，这里设置成node2。
主库上postgresql.conf配置文件设置以下参数，其他参数和异步流复制配置一致。
synchronous_commit = on或remote_apply     --建议设置为on,remote_apply是备库应用后才返回信息
synchronous_standby_narnes = ’node2’
 
wal_level配置也和异步流复制配置一致，设置成replica或logical即可。
重启主库与备库查询复制状态为sync
[pgsql@pgsql pg_root]$ psql postgres
psql (10.9)
Type "help" for help.
 
postgres=# select usename,application_name ,client_addr ,sync_state from pg_stat_replication;
 usename | application_name |   client_addr   | sync_state
---------+------------------+-----------------+------------
 repuser | node2            | 192.168.231.132 | sync
(1 row)
 
postgres=#

流复制监控

pg_stat_replication视图

application_name：连接WAL发送进程的应用别名，此参数显示值为备库recovery.conf配置文件中primary_conninfo参数application_name选项的值。

client_addr：连接到WAL发送进程的客户端IP地址，也就是备库的IP。

backend_start:WAL发送进程的启动时间。

state：显示WAL发送进程的状态，startup表示WAL进程在启动过程中；catchup表示备库正在追赶主库；streaming表示备库已经追赶上了主库，并且主库向备库发送WAL日志流，这个状态是流复制的常规状态；backup表示通过pg_basebackup正在进行备份；stopping表示WAL发送进程正在关闭。

sent_lsn:WAL发送进程最近发送的WAL日志位置。

write_lsn：备库最近写人的WAL日志位置，这时WAL日志流还在操作系统缓存中，还没写人备库WAL日志文件。

flush_lsn：备库最近写人的WAL日志位置，这时WAL日志流已写入备库WAL日志文件。

replay_lsn：备库最近应用的WAL日志位置。

write_lag：主库上WAL日志落盘后等待备库接收WAL日志（这时WAL日志流还没写人备库WAL日志文件，还在操作系统缓存中）并返回确认信息的时间。

flush_lag：主库上WAL日志落盘后等待备库接收WAL日志（这时WAL日志流已写人备库WAL日志文件，但还没有应用WAL日志）并返回确认信息的时间。

replay_lag：主库上WAL日志落盘后等待备库接收WAL日志（这时WAL日志流已写入备库WAL日志文件，并且己应用WAL日志）并返回确认信息的时间。

sync_priority：基于优先级的模式中备库被选中成为同步备库的优先级，对于基于quorum的选举模式此字段则无影响。

sync_state：同步状态，有以下状态值，async表示备库为异步同步模式；potential表示备库当前为异步同步模式，如果当前的同步备库岩机，异步备库可升级成为同步备库；sync表示当前备库为同步模式；quorum表示备库为quorum standbys的候选。

pg_stat_wal_receiver显示wal发送进程的详细信息

主要字段信息

pid:WAL接收进程的进程号。

status:WAL接收进程的状态。

Receive_start_lsn:WAL接收进程启动后使用的第一个WAL日志位置。

Received_lsn：最近接收井写人WAL日志文件的WAL位置。

last_msg_send_time：备库接收到发送进程最后一个消息后，向主库发回确认消息的发送时间。

last_msg_receipt_time：备库接收到发送进程最后一个消息的接收时间。

conninfo:WAL接收进程使用的连接串，连接信息由备库$PGDATA目录的recovery.conf配置文件的primary_conninfo参数配置。

三、流复制主备切换

3.1判断主备角色
方式一：ps -ef|grep wal 查看wal进程描述信息，判断主备

方式二: 主库查询SELECT * from pg_stat_replication，备库查此视图是无记录的；备库查询select * from pg_stat_wal_receiver，同主库查此视图无记录。

方式三：SELECT pg_is_in_recovery(); 返回t说明为备库，返回f为主库。

方式四：通过pg_controldata查看数据库控制信息；通过Database cluster state信息判断；结果显示 in production 为主库；结果显示 in archive recovery为备库。

方式五：通过recover.conf配置文件查看。

3.2 判断主备是否正常

(1) 查看主备库的进程

主库发送进程：

[pgsql@pgsql ~]$ ps -ef|grep wal
pgsql     35001  34996  0 22:27 ?        00:00:00 postgres: wal writer process
pgsql     35006  34996  0 22:27 ?        00:00:00 postgres: wal sender process repuser 192.168.231.132(55152) streaming 0/7000098
pgsql     35008  34790  0 22:27 pts/1    00:00:00 grep --color=auto wal
备库为接收进程：
[pgsql@pgstandby ~]$ ps -ef|grep wal
pgsql     59336  59310  1 22:27 ?        00:00:00 postgres: wal receiver process   streaming 0/70000D0
pgsql     59347  58742  0 22:27 pts/3    00:00:00 grep --color=auto wal
(2) 主库插入数据，观察备库是否能收到数据。
(3) 主库查询复制状态参数，是否正常。
[pgsql@pgsql pg_root]$ psql postgres
psql (10.9)
Type "help" for help.
 
postgres=# select usename,application_name ,client_addr ,sync_state from pg_stat_replication;
 usename | application_name |   client_addr   | sync_state
---------+------------------+-----------------+------------
 repuser | node2            | 192.168.231.132 | sync
(1 row)

3.3切换方式

通过触发器文件方式触发主备切换和通过pg_ctl promot命令触发主备切换

一、文件触发方式

1）配置备库recovery.conf 文件的tigger_file参数，设置激活备库的触发文件路径和名称。添加tigger_file参数。

trigger_file = ‘/database/pg10/pg_root/.postgresql.trigger.5432’

2）关闭主库，使用 -m fast 模式关闭。（数据库关闭时首先做一次checkpoint,wal进程会将截止此次checkpoint的wal日志流发送给备库，备库在应用wal。）

3）在备库创建触发文件激活备库，当recovery.conf变为recovery.done表示备库已切换为主库。

[pgsql@pgstandby ~]$ cat /database/pg10/pg_root/recovery.conf |grep ^[^#]
recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo='host=192.168.231.131 port=5432 user=repuser application_name=node2'
trigger_file='/database/pg10/pg_root/.postgresql.trigger.5432'
[pgsql@pgstandby ~]$ touch /database/pg10/pg_root/.postgresql.trigger.5432   --创建触发文件，检测不到主库后会自动切换为主库。

4）在老主库下创建recovery.conf文件，并配置相应参数，跟备库的参数一样。在根目录下配置.pgpass免密文件。

[pgsql@pgstandby ~]$ vi /database/pg10/pg_root/recovery.conf     --主要依据原来备库的recovery.conf参数来进行设置。
recovery_target_timeline = 'latest'                       
standby_mode = on
primary_conninfo = 'host=192.168.231.131 port=5432 user=repuser'
        
[pgsql@pgstandby ~]$ cat .pgpass
192.168.231.132:5432:replication:repuser:hufj123
192.168.231.131:5432:replication:repuser:hufj123

5）启动老的主库，观察主备库是否正常。

二、主备切换之pg_ctl promote方式

pg_ctl promote [ D datadir]

-D 是指数据目录，如果不指定会使用环境变量 $PGDATA 设置的值。 promote 命令发出后，运行中的备库将停止恢复模式并切换成读写模式的主库。 pg_ctl promote 主备切换步骤和文件触发方式大体相同。

1）关闭主库，使用-m fast模式

2）备库执行pg_ctl promote命令激活备库

3）原主库切换为备库，创建recovery.conf文件，配置参数。

4）重新启动原主库，查看主备进程是否正常。