postgresql主从复制、主从切换
一、 不再需要配置recovery.conf文件
从PostgreSQL 12开始,在执行通过流复制来配置主备数据库的时候,不再需要配置额外配置recovery.conf文件了。取而代之的是在备库环境的$PGDATA路径下配置一个standby.signal文件,注意该文件是1个普通的文本文件,内容为空。理解起来就是,该文件是一个标识文件。如果备库通过执行pg_ctl promote提升为主库的话,那么该文件将自动消失。
二、 备库执行基础备份时新的命令行选项-R
PostgreSQL 9.4.10版本:
[postgres@psql ~]$ pg_basebackup -Vpg_basebackup (PostgreSQL) 9.4.10[postgres@psql ~]$ pg_basebackup --helppg_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, --max-rate=RATE maximum transfer rate to transfer data directory (in kB/s, or use suffix "k" or "M") -R, --write-recovery-conf write recovery.conf after backup -T, --tablespace-mapping=OLDDIR=NEWDIR relocate tablespace in OLDDIR to NEWDIR -x, --xlog include required WAL files in backup (fetch mode) -X, --xlog-method=fetch|stream include required WAL files with specified method
PostgreSQL 12.8版本:
[postgres@psql ~]$ pg_basebackup -Vpg_basebackup (PostgreSQL) 12.8[postgres@psql ~]$ pg_basebackup --helppg_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, --max-rate=RATE maximum transfer rate to transfer data directory (in kB/s, or use suffix "k" or "M") -R, --write-recovery-conf write configuration for replication -T, --tablespace-mapping=OLDDIR=NEWDIR relocate tablespace in OLDDIR to NEWDIR --waldir=WALDIR location for the write-ahead log directory -X, --wal-method=none|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
从对比中,可以看到在9.4.10版本中,-R选项用于创建recovery.conf文件,而在12.8版本中,-R则是用于创建用于replication的配置文件,其实就是生成$PGDATA/standby.signal文件。
三、 如何生成standby.signal文件
两种方式,一种是在备库执行基础备份的时候,加上-R选项用于自动创建$PGDATA/standby.signal文件:
[postgres@psql pg12.8]$ pwd/postgres/pg12.8[postgres@psql pg12.8]$ lltotal 20drwxrwxr-x 2 postgres postgres 4096 Oct 19 15:01 bindrwxrwxr-x 6 postgres postgres 4096 Oct 19 15:01 includedrwxrwxr-x 4 postgres postgres 4096 Oct 19 15:01 libdrwxrwxr-x 8 postgres postgres 4096 Oct 19 15:01 share[postgres@psql pg12.8]$ pg_basebackup -h 192.168.40.133 -p 5432 -U replica --password -X stream -Fp --progress -D $PGDATA -RPassword: 24597/24597 kB (100%), 1/1 tablespace[postgres@psql pg12.8]$ ll datatotal 120-rw------- 1 postgres postgres 224 Oct 20 15:11 backup_labeldrwx------ 5 postgres postgres 4096 Oct 20 15:11 basedrwx------ 2 postgres postgres 4096 Oct 20 15:11 globaldrwx------ 2 postgres postgres 4096 Oct 20 15:11 pg_commit_tsdrwx------ 2 postgres postgres 4096 Oct 20 15:11 pg_dynshmem-rw------- 1 postgres postgres 4867 Oct 20 15:11 pg_hba.conf-rw------- 1 postgres postgres 1636 Oct 20 15:11 pg_ident.confdrwx------ 4 postgres postgres 4096 Oct 20 15:11 pg_logicaldrwx------ 4 postgres postgres 4096 Oct 20 15:11 pg_multixactdrwx------ 2 postgres postgres 4096 Oct 20 15:11 pg_notifydrwx------ 2 postgres postgres 4096 Oct 20 15:11 pg_replslotdrwx------ 2 postgres postgres 4096 Oct 20 15:11 pg_serialdrwx------ 2 postgres postgres 4096 Oct 20 15:11 pg_snapshotsdrwx------ 2 postgres postgres 4096 Oct 20 15:11 pg_statdrwx------ 2 postgres postgres 4096 Oct 20 15:11 pg_stat_tmpdrwx------ 2 postgres postgres 4096 Oct 20 15:11 pg_subtransdrwx------ 2 postgres postgres 4096 Oct 20 15:11 pg_tblspcdrwx------ 2 postgres postgres 4096 Oct 20 15:11 pg_twophase-rw------- 1 postgres postgres 3 Oct 20 15:11 PG_VERSIONdrwx------ 3 postgres postgres 4096 Oct 20 15:11 pg_waldrwx------ 2 postgres postgres 4096 Oct 20 15:11 pg_xact-rw------- 1 postgres postgres 337 Oct 20 15:11 postgresql.auto.conf-rw------- 1 postgres postgres 26718 Oct 20 15:11 postgresql.conf-rw------- 1 postgres postgres 0 Oct 20 15:11 standby.signal[postgres@psql pg12.8]$
方式2:如果在备库上执行pg_basebackup对主库进行备份的时候,没有使用-R选项的话,我们可以在备库的$PGDATA路径下,touch standby.signal就好了。
记住:该文件只是一个标识文件,它的存在就是告诉数据库,当我们执行pg_ctl start启动的时候,当前库的角色是standby,不是primary角色。
四、 部署和配置环境
主机 | IP地址 | 版本 |
---|---|---|
psql-master(主库) | 192.168.40.133 | psql-14 |
psql-slave(备库) | 192.168.40.147 | psql-14 |
1 修改主机名
[root@localhost ~]# hostnamectl set-hostname pgsql-master[root@localhost ~]# bash[root@localhost ~]# hostnamectl set-hostname pgsql-slave[root@localhost ~]# bash
2 配置防火墙规则,放行5432端口
[root@pgsql-master ~]# firewall-cmd --add-port=5432/tcp --permanentsuccess[root@pgsql-master ~]# firewall-cmd --reloadsuccess
3 安装postgresql-14
#安装常用工具[root@pgsql-master ~]# yum -y install vim bash-com*#创建脚本文件[root@pgsql-master ~]# touch install.sh[root@pgsql-master ~]# chmod +x install.sh [root@pgsql-master ~]# cat install.sh #!/bin/bash#配置pgsql网络源sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm#安装pgsqlsudo yum install -y postgresql14-server#初始化sudo /usr/pgsql-14/bin/postgresql-14-setup initdb#设置开机自启sudo systemctl enable postgresql-14sudo systemctl start postgresql-14#执行脚本[root@pgsql-master ~]# ./install.sh#查看服务是否开启[root@pgsql-master ~]# ss -antlState Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 *:22 *:* LISTEN 0 128 127.0.0.1:5432 *:* LISTEN 0 100 127.0.0.1:25 *:* LISTEN 0 128 :::22 :::* LISTEN 0 128 ::1:5432 :::* LISTEN 0 100 ::1:25 :::*
4 配置环境变量
#切换用户[root@pgsql-master ~]# su - postgres上一次登录:三 5月 11 13:57:38 CST 2022pts/0 上-bash-4.2$ #配置环境变量-bash-4.2$ vi ~/.bash_profileexport PATH=$PATH:$HOME/bin:/usr/pgsql-14/bin #在最后一行下面添加此行#添加完后执行:使它立即生效-bash-4.2$ source ~/.bash_profile
5 修改postgres用户密码
[root@pgsql-master ~]# su - postgres-bash-4.2$ -bash-4.2$ psqlpsql (14.2)输入 "help" 来获取帮助信息.postgres=# postgres=# select user; user ---------- postgres(1 行记录)postgres=# postgres=# alter role postgres with password 'postgres' ;ALTER ROLEpostgres=# postgres=# exit-bash-4.2$ exit
6 修改数据存放路径(默认的可能在/下,这不太安全)
#建目录/赋权限mkdir -p /ofa/postgres_datahown -R postgres:root /ofa/postgres_datals -lld /ofadrwxr-xr-x 3 root root 27 Dec 1 12:28 /ofals -lld /ofa/postgres_datadrwxr-xr-x 2 postgres root 6 Dec 1 12:28 /ofa/postgres_datavi /var/lib/pgsql/14/data/postgresql.confdata_directory='/ofa/postgres_data'#同步数据rsync -av /var/lib/pgsql/14/data/ /ofa/postgres_data/#重启数据库systemctl restart postgresql-14
也可以使用默认数据存放路径:/var/lib/pgsql/14/data/
五、 执行stream主备配置流程
主体思路跟PostgreSQL 11及以前版本的配置流程大同小异,甚至是更简单一些了。
1 主库切换用户,修改监听地址
[root@pgsql-master ~]# su - postgres上一次登录:一 5月 9 10:48:28 CST 2022pts/0 上-bash-4.2$ #修改监听地址-bash-4.2$ psql -c "ALTER SYSTEM SET listen_addresses TO '*';"用户 postgres 的口令:ALTER SYSTEM-bash-4.2$ #改配置文件-bash-4.2$ vim /var/lib/pgsql/14/data/postgresql.conf...#listen_addresses = 'localhost' # what IP address(es) to listen on;listen_addresses = '*'...
2 主库创建流复制的用户
方式一:-bash-4.2$ psql -c "CREATE ROLE replicator login replication encrypted password 'postgres';"用户 postgres 的口令:CREATE ROLE-bash-4.2$ 方式二:-bash-4.2$ createuser --replication -P -e replicator为新角色输入的口令: 再输入一遍: 口令: SELECT pg_catalog.set_config('search_path', '', false);CREATE ROLE replicator PASSWORD 'SCRAM-SHA-256$4096:gY5s1JPnO4nueRyTiEcHEQ==$C9KLSfPPrX/9XQ9kdzzhY65E01B4AaS9g5WbEl1Bl50=:qiMzW4B+9kaZxXhfQlxu73mwf3S9TORjOBC9RWVaR9c=' NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN REPLICATION;-bash-4.2$
3 主库修改pg_hba.conf文件,允许备库IP通过复制用户访问数据库
-bash-4.2$ vim /var/lib/pgsql/14/data/pg_hba.conf...#在最下面添加host replication replicator 192.168.40.147/24 md5...
最后一行,添加了replicaor用户可以从备库IP 192.168.40.147访问主库。
4 备库备份旧数据,删掉,然后备份主库数据
-bash-4.2$ mkdir /opt/psql_data-bash-4.2$ cp -R /var/lib/pgsql/14/data/ /opt/psql_data-bash-4.2$ cd /var/lib/pgsql/14/data/ -bash-4.2$ rm -rf *
5 备库上执行对于主库的基础备份
-bash-4.2$ pg_basebackup -h 192.168.40.133 -D /var/lib/pgsql/14/data -U replicator -P -v -R -X stream -C -S pgstandby1口令: pg_basebackup: 开始基础备份,等待检查点完成pg_basebackup: 已完成检查点pg_basebackup: 预写日志起始于时间点: 0/2000028, 基于时间轴1pg_basebackup: 启动后台 WAL 接收进程pg_basebackup: 已创建复制槽"pgstandby1"26952/26952 kB (100%), 1/1 表空间 pg_basebackup: 预写日志结束点: 0/2000100pg_basebackup: 等待后台进程结束流操作...pg_basebackup: 同步数据到磁盘...pg_basebackup: 将backup_manifest.tmp重命名为backup_manifestpg_basebackup: 基础备份已完成
注意,备份选项上带有-R选项。
4 备库就可以执行pg_ctl start启动了
这时,就可以看到备库服务器上自动生成了standby.signal文件。同时,也看到在$PGDATA路径下,数据库自动帮我们配置了关于流复制的主库的信息:
-bash-4.2$ cat data/postgresql.auto.conf# Do not edit this file manually!# It will be overwritten by the ALTER SYSTEM command.primary_conninfo = 'user=replicator password=postgres host=192.168.40.133 port=5432 sslmode=disable sslcompression=0 gssencmode=disable krbsrvname=postgres target_session_attrs=any'
当然了,如果我们没有使用-R来备份主库的话。我们完全可以在备库上手工创建standby.signal文件,然后手工编辑postgresql.auto.conf,并在其内容中配置主库的信息。
5 备库数据库进程信息
-bash-4.2$ ps -ef|grep postgrespostgres 8911 1 0 Oct20 ? 00:00:00 /postgres/pg12.8/bin/postgrespostgres 8912 8911 0 Oct20 ? 00:00:00 postgres: startup recovering 000000010000000000000003postgres 8913 8911 0 Oct20 ? 00:00:00 postgres: checkpointer postgres 8914 8911 0 Oct20 ? 00:00:00 postgres: background writer postgres 8915 8911 0 Oct20 ? 00:00:00 postgres: stats collector postgres 8916 8911 0 Oct20 ? 00:00:32 postgres: walreceiver streaming 0/3000148root 18015 18014 0 16:13 pts/0 00:00:00 su - postgrespostgres 18016 18015 0 16:13 pts/0 00:00:00 -bashpostgres 18055 18016 0 16:13 pts/0 00:00:00 ps -efpostgres 18056 18016 0 16:13 pts/0 00:00:00 grep --color=auto postgres
备库上,可以看到walreceiver进程,正在读取日志streaming 0/3000148,执行恢复recovering 000000010000000000000003。
6 主库数据库进程信息
-bash-4.2$ ps -ef|grep postgrespostgres 3708 1 0 Oct20 ? 00:00:00 /postgres/pg12.8/bin/postgrespostgres 3710 3708 0 Oct20 ? 00:00:00 postgres: checkpointer postgres 3711 3708 0 Oct20 ? 00:00:00 postgres: background writer postgres 3712 3708 0 Oct20 ? 00:00:00 postgres: walwriter postgres 3713 3708 0 Oct20 ? 00:00:00 postgres: autovacuum launcher postgres 3714 3708 0 Oct20 ? 00:00:00 postgres: stats collector postgres 3715 3708 0 Oct20 ? 00:00:00 postgres: logical replication launcher postgres 4174 3708 0 Oct20 ? 00:00:00 postgres: walsender replicator 192.168.40.147(57926) streaming 0/3000148root 30410 30409 0 16:11 pts/1 00:00:00 su - postgrespostgres 30411 30410 0 16:11 pts/1 00:00:00 -bashpostgres 30868 30411 0 16:13 pts/1 00:00:00 ps -efpostgres 30869 30411 0 16:13 pts/1 00:00:00 grep --color=auto postgres
主库上看到,后台进程walsender,正在向replicator 192.168.40.147(57926) streaming 0/3000148推送日志信息。
7 主库查看数据库复制信息
-bash-4.2$ psql -xc "select * from pg_stat_replication"-[ RECORD 1 ]----+------------------------------pid | 4174usesysid | 16384usename | replicatorapplication_name | walreceiverclient_addr | 192.168.40.147client_hostname | client_port | 57926backend_start | 2021-10-20 15:45:37.817863+08backend_xmin | state | streamingsent_lsn | 0/3000148write_lsn | 0/3000148flush_lsn | 0/3000148replay_lsn | 0/3000148write_lag | flush_lag | replay_lag | sync_priority | 0sync_state | asyncreply_time | 2021-10-21 16:11:58.438541+08
六、 主备切换及注意事项
如果因为意外或故障导致主库不可用的情况下,可以直接将备库提升为主库对外提供服务。然后视具体情况看原来的主库是否需要重建,或者是否待故障恢复之后,可以直接作为新的备库,然后从新的主库(原备库)同步数据。
下面是模拟切换步骤:
1 主库停止,模拟故障
-bash-4.2$ ps -ef|grep postgrespostgres 3708 1 0 Oct20 ? 00:00:00 /postgres/pg12.8/bin/postgrespostgres 3710 3708 0 Oct20 ? 00:00:00 postgres: checkpointer postgres 3711 3708 0 Oct20 ? 00:00:00 postgres: background writer postgres 3712 3708 0 Oct20 ? 00:00:00 postgres: walwriter postgres 3713 3708 0 Oct20 ? 00:00:00 postgres: autovacuum launcher postgres 3714 3708 0 Oct20 ? 00:00:00 postgres: stats collector postgres 3715 3708 0 Oct20 ? 00:00:00 postgres: logical replication launcher postgres 4174 3708 0 Oct20 ? 00:00:00 postgres: walsender replicator 192.168.40.147(57926) streaming 0/3000148postgres 4747 30411 0 16:42 pts/1 00:00:00 ps -efpostgres 4748 30411 0 16:42 pts/1 00:00:00 grep --color=auto postgresroot 30410 30409 0 16:11 pts/1 00:00:00 su - postgrespostgres 30411 30410 0 16:11 pts/1 00:00:00 -bash-bash-4.2$ pg_ctl statuspg_ctl: server is running (PID: 3708)/postgres/pg12.8/bin/postgres-bash-4.2$ pg_ctl stop -m fastwaiting for server to shut down.... doneserver stopped-bash-4.2$ ps -ef|grep postgrespostgres 4843 30411 0 16:43 pts/1 00:00:00 ps -efpostgres 4844 30411 0 16:43 pts/1 00:00:00 grep --color=auto postgresroot 30410 30409 0 16:11 pts/1 00:00:00 su - postgrespostgres 30411 30410 0 16:11 pts/1 00:00:00 -bash-bash-4.2$ pg_ctl statuspg_ctl: no server running
通过pg_ctl stop -m fast停止原来的主库之后,数据库后台进程都没有了。
2 备库提升为新主库,对外提供服务
-bash-4.2$ ps -ef|grep postgrespostgres 8911 1 0 Oct20 ? 00:00:00 /postgres/pg12.8/bin/postgrespostgres 8912 8911 0 Oct20 ? 00:00:00 postgres: startup recovering 000000010000000000000003postgres 8913 8911 0 Oct20 ? 00:00:00 postgres: checkpointer postgres 8914 8911 0 Oct20 ? 00:00:00 postgres: background writer postgres 8915 8911 0 Oct20 ? 00:00:00 postgres: stats collector root 18015 18014 0 16:13 pts/0 00:00:00 su - postgrespostgres 18016 18015 0 16:13 pts/0 00:00:00 -bashpostgres 25404 18016 0 16:43 pts/0 00:00:00 ps -efpostgres 25405 18016 0 16:43 pts/0 00:00:00 grep --color=auto postgres-bash-4.2$ pg_ctl statuspg_ctl: server is running (PID: 8911)/postgres/pg12.8/bin/postgres-bash-4.2$ pg_ctl promotewaiting for server to promote.... doneserver promoted-bash-4.2$ ps -ef|grep postgrespostgres 8911 1 0 Oct20 ? 00:00:00 /postgres/pg12.8/bin/postgrespostgres 8913 8911 0 Oct20 ? 00:00:00 postgres: checkpointer postgres 8914 8911 0 Oct20 ? 00:00:00 postgres: background writer postgres 8915 8911 0 Oct20 ? 00:00:00 postgres: stats collector root 18015 18014 0 16:13 pts/0 00:00:00 su - postgrespostgres 18016 18015 0 16:13 pts/0 00:00:00 -bashpostgres 25622 8911 0 16:44 ? 00:00:00 postgres: walwriter postgres 25623 8911 0 16:44 ? 00:00:00 postgres: autovacuum launcher postgres 25624 8911 0 16:44 ? 00:00:00 postgres: logical replication launcher postgres 25628 18016 0 16:44 pts/0 00:00:00 ps -efpostgres 25629 18016 0 16:44 pts/0 00:00:00 grep --color=auto postgres-bash-4.2$
重要1:启动备库为新主库的命令是pg_ctl promote。
提升备库为主库之后,可以看到,后台进程中不再有startup recovering,以及walreceiver streaming进程了。同时,多了postgres: walwriter 写进程。
重要2:$PGDATA/standby.signal文件自动消失了。这是告诉PostgreSQL,我现在不再是备库了,我的身份是主库了。
3 新主库修改pg_hba.conf文件
修改新主库(原备库192.168.40.147)的$PGDATA/pg_hba.conf文件,在其中添加允许新备库(原主库192.168.40.133)可以通过replica用户访问数据库的条目信息。
host replication replicaotr 192.168.40.133/24 md5
如果不做这一步配置的话,将来启动原主库为新备库的时候,可能会遇到下述错误。
2021-10-21 17:13:20.464 CST [11394] FATAL: could not connect to the primary server: FATAL: no pg_hba.conf entry for replication connection from host "192.168.40.133", user "replica", SSL off2021-10-21 17:13:20.466 CST [11395] FATAL: could not connect to the primary server: FATAL: no pg_hba.conf entry for replication connection from host "192.168.40.133", user "replica", SSL off
注意:如果主从环境的数据库没有配置浮动IP的话,则这里的IP地址,应该直接填原主库的实际IP地址。
4 原主库新建$PGDATA/standby.signal文件
-bash-4.2$ pwd/var/lib/pgsql/14/data-bash-4.2$ touch standby.signal-bash-4.2$ ll standby.signal -rw-rw-r-- 1 postgres postgres 0 Oct 21 16:54 standby.signal
注意:这一步骤非常非常重要,如果不配置该文件的话,那么原来的主库一旦重新启动话,就将成为了1个新的独立主库,脱离了主从数据库环境。
5 原主库修改$PGDATA/postgresql.auto.conf文件
注意,应该用单引号,而不是双引号。否则遇到下述错误。
-bash-4.2$ cat postgresql.auto.conf # Do not edit this file manually!# It will be overwritten by the ALTER SYSTEM command.primary_conninfo="user=replica password=replica host=192.168.40.147 port=5432"-bash-4.2$ -bash-4.2$ pg_ctl start -l ~/pg.log waiting for server to start.... stopped waitingpg_ctl: could not start serverExamine the log output.-bash-4.2$ tailf ~/pg.log数据库启动日志2021-10-21 09:07:06.512 GMT [10045] LOG: syntax error in file "/postgres/pg12.8/data/postgresql.auto.conf" line 3, near token """2021-10-21 09:07:06.512 GMT [10045] FATAL: configuration file "postgresql.auto.conf" contains errors
修改$PGDATA/postgresql.auto.conf配置文件为下述正确的格式:
-bash-4.2$ cat postgresql.auto.conf # Do not edit this file manually!# It will be overwritten by the ALTER SYSTEM command.primary_conninfo='user=replica password=replica host=192.168.40.147 port=5432'
6 启动原主库,变为新备库
-bash-4.2$ pg_ctl start -l ~/pg.log waiting for server to start.... doneserver started-bash-4.2$ ps -ef|grep postgresroot 8116 8115 0 16:58 pts/0 00:00:00 su - postgrespostgres 8118 8116 0 16:58 pts/0 00:00:00 -bashroot 8598 8597 0 17:00 pts/2 00:00:00 su - postgrespostgres 8600 8598 0 17:00 pts/2 00:00:00 -bashpostgres 11368 8118 0 17:13 pts/0 00:00:00 tailf pg.logpostgres 11389 1 0 17:13 ? 00:00:00 /postgres/pg12.8/bin/postgrespostgres 11390 11389 0 17:13 ? 00:00:00 postgres: startup recovering 000000020000000000000003postgres 11391 11389 0 17:13 ? 00:00:00 postgres: checkpointer postgres 11392 11389 0 17:13 ? 00:00:00 postgres: background writer postgres 11393 11389 0 17:13 ? 00:00:00 postgres: stats collector postgres 11440 11389 0 17:13 ? 00:00:00 postgres: walreceiver streaming 0/3013AC8postgres 12545 30411 0 17:18 pts/1 00:00:00 ps -efpostgres 12546 30411 0 17:18 pts/1 00:00:00 grep --color=auto postgresroot 30410 30409 0 16:11 pts/1 00:00:00 su - postgrespostgres 30411 30410 0 16:11 pts/1 00:00:00 -bash-bash-4.2$ tailf pg.log 2021-10-21 17:13:45.488 CST [11440] LOG: fetching timeline history file for timeline 2 from primary server2021-10-21 17:13:45.493 CST [11440] LOG: started streaming WAL from primary at 0/3000000 on timeline 12021-10-21 17:13:45.493 CST [11440] LOG: replication terminated by primary server2021-10-21 17:13:45.493 CST [11440] DETAIL: End of WAL reached on timeline 1 at 0/30001C0.2021-10-21 17:13:45.494 CST [11390] LOG: new target timeline is 22021-10-21 17:13:45.494 CST [11440] LOG: restarted WAL streaming at 0/3000000 on timeline 22021-10-21 17:13:45.539 CST [11390] LOG: redo starts at 0/30001C0
这样,就完成了一次主从数据库环境的切换操作了。
七、小结
- 随着新版本的发行,数据库的配置和使用也越来越简单顺手了。
- 备库提升为主库的命令:pg_ctl promote;
- 新主库(原备库)的pg_hba.conf文件,要开放允许流复制访问数据库的信息给原主库的IP地址;
- 原主库配置为新备库的时候,务必要创建$PGDATA/standby.signal文件;
- 原主库配置为新备库的时候,务必要修改$PGDATA/postgresql.auto.conf文件,添加主库primary_conninfo的信息;