网站大全免费入口,网页设计免费下载,wordpress 颜色,易思企业网站一直想搭建一个PG流复制#xff0c;最近正好有一个新环境#xff0c;操作系统是最新的,rhel 9.3#xff0c;数据库是最新的 pg 16.1,借鉴了网上的步骤#xff0c;尤其是小工到专家的内容#xff0c;在此谢过。 1.安装环境 1#xff09;IP: 主#xff1a;192.168.133.151…一直想搭建一个PG流复制最近正好有一个新环境操作系统是最新的,rhel 9.3数据库是最新的 pg 16.1,借鉴了网上的步骤尤其是小工到专家的内容在此谢过。 1.安装环境 1IP: 主192.168.133.151 server1 备192.168.133.152 server2 2操作系统版本 [postgresserver2 data]$ cat /etc/redhat-release Red Hat Enterprise Linux release 9.3 (Plow)
3数据库版本
postgres[local]:5432/postgres-11653#select version(); version ---------------------------------------------------------------------------------------------------------------- PostgreSQL 16.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.4.1 20230605 (Red Hat 11.4.1-2.1.0.1), 64-bit (1 row) 2.创建主备流复制 2.1 修改 /etc/hosts 文件主备
vim /etc/hosts
192.168.133.151 server1 192.168.133.152 server2 2.2 主库设置 1建立同步用户
postgres# create role rep1 login replication encrypted password rep1;
2) 配置复制认证配置
vim $PGDATA/pg_hba.conf
host replication rep1 server2 md5 3) 修改$PGDATA/postgres.conf
listen_addresses * port5432 wal_level replica max_wal_senders10 archive_mode on archive_timeout1800
archive_command cp %p /home/postgres/arch/%f restore_command cp /home/postgres/arch/%f %p recovery_target_timeline latest full_page_writes on wal_log_hints on
logging_collector on log_directory pg_log log_filename postgresql-%Y-%m-%d.log
--下面参数设置大一些否则后面主备切换可能出现找不到日志文件的问题 min_wal_size 800MB wal_keep_size 1024
4).重启主库让参数生效
pg_ctl restart 2.3 备库设置
1初始化直接从主库备份就行
pg_basebackup -h server1 -p 5432 -U repl -X stream -R -Fp -P -D /pgdata/data
-h指定要连接的服务器的主机名或IP地址。 -U指定连接使用的用户名。 -Fp指定备份的格式。在这种情况下备份以纯文本格式存储。 -P指定在备份过程中显示进度信息。 -X stream指定备份的流式传输方法。 -R指定包括所有必需的WAL文件以进行一致性备份。 -D指定备份存储的目录。
2) 修改参数加上下面参数
hot_standby on #在备份的同时允许查询默认值 max_standby_streaming_delay 30s #可选流复制最大延迟 wal_receiver_status_interval 10s #可选从向主报告状态的最大间隔时间 hot_standby_feedback on #可选查询冲突时向主反馈 3配置$PGDATA/data/pg_hba.conf host replication rep1 server1 md5 #这里修改主机名为主机的
4) 重启让配置生效 pg_ctl start 3.验证 3.1 查看主备进程 1主库端
[postgresserver1 data]$ ps -ef|grep wal postgres 3003 2998 0 21:57 ? 00:00:00 postgres: walwriter postgres 3063 2998 0 22:08 ? 00:00:00 postgres: walsender rep1 192.168.133.152(49190) streaming 0/260021C8 postgres 3080 1518 0 22:13 pts/0 00:00:00 grep --colorauto wal [postgresserver1 data]$
2备库端
[postgresserver2 data]$ ps -ef|grep wal postgres 11355 11345 0 22:08 ? 00:00:00 postgres: walreceiver streaming 0/260021C8 postgres 11373 9351 0 22:14 pts/0 00:00:00 grep --colorauto wal [postgresserver2 data]$ 3.2 主库切换日志 postgres[local]:5432/postgres-3100#select pg_switch_wal(); pg_switch_wal --------------- 0/260021E0 (1 row)
postgres[local]:5432/postgres-3100#\! ls -ltr /pgdata/data/pg_wal 总用量 458768 -rw------- 1 postgres postgres 41 12月 15 23:16 00000002.history -rw------- 1 postgres postgres 16777216 12月 17 20:09 00000002000000000000000D -rw------- 1 postgres postgres 16777216 12月 17 20:22 00000002000000000000000E -rw------- 1 postgres postgres 16777216 12月 17 20:22 00000002000000000000000F -rw------- 1 postgres postgres 16777216 12月 17 20:25 000000020000000000000010 -rw------- 1 postgres postgres 16777216 12月 17 20:28 000000020000000000000011 -rw------- 1 postgres postgres 16777216 12月 17 20:28 000000020000000000000012.partial -rw------- 1 postgres postgres 84 12月 17 20:29 00000003.history -rw------- 1 postgres postgres 16777216 12月 17 20:34 000000030000000000000012 -rw------- 1 postgres postgres 16777216 12月 17 21:04 000000030000000000000013 -rw------- 1 postgres postgres 16777216 12月 17 21:26 000000030000000000000014 -rw------- 1 postgres postgres 16777216 12月 17 21:29 000000030000000000000015 -rw------- 1 postgres postgres 16777216 12月 17 21:29 000000030000000000000016 -rw------- 1 postgres postgres 16777216 12月 17 21:30 000000030000000000000017 -rw------- 1 postgres postgres 16777216 12月 17 21:30 000000030000000000000018 -rw------- 1 postgres postgres 16777216 12月 17 21:31 000000030000000000000019 -rw------- 1 postgres postgres 16777216 12月 17 21:31 00000003000000000000001A -rw------- 1 postgres postgres 16777216 12月 17 21:33 00000003000000000000001B -rw------- 1 postgres postgres 16777216 12月 17 21:33 00000003000000000000001C -rw------- 1 postgres postgres 16777216 12月 17 21:47 00000003000000000000001D -rw------- 1 postgres postgres 16777216 12月 17 21:47 00000003000000000000001E -rw------- 1 postgres postgres 16777216 12月 17 21:50 00000003000000000000001F -rw------- 1 postgres postgres 16777216 12月 17 21:50 000000030000000000000020 -rw------- 1 postgres postgres 16777216 12月 17 21:53 000000030000000000000021 -rw------- 1 postgres postgres 16777216 12月 17 21:53 000000030000000000000022 -rw------- 1 postgres postgres 16777216 12月 17 21:57 000000030000000000000023 -rw------- 1 postgres postgres 16777216 12月 17 21:58 000000030000000000000024 -rw------- 1 postgres postgres 16777216 12月 17 21:58 000000030000000000000025 -rw------- 1 postgres postgres 341 12月 17 21:58 000000030000000000000025.00000028.backup -rw------- 1 postgres postgres 16777216 12月 17 22:17 000000030000000000000026 drwx------ 2 postgres postgres 4096 12月 17 22:17 archive_status -rw------- 1 postgres postgres 16777216 12月 17 22:17 000000030000000000000027 postgres[local]:5432/postgres-3100#
--备库
[postgresserver2 data]$ ls -ltr /pgdata/data/pg_wal 总用量 49156 -rw------- 1 postgres postgres 84 12月 17 21:58 00000003.history -rw------- 1 postgres postgres 16777216 12月 17 21:58 000000030000000000000025 drwx------ 2 postgres postgres 72 12月 17 22:17 archive_status -rw------- 1 postgres postgres 16777216 12月 17 22:17 000000030000000000000026 -rw------- 1 postgres postgres 16777216 12月 17 22:17 000000030000000000000027 3.3 查看当前备库状态 [postgresserver2 data]$ psql psql (16.1) Type help for help.
postgresserver2:5432/postgres-11485#select pg_is_in_recovery(); pg_is_in_recovery ------------------- t (1 row) postgresserver1:5432/postgres-3210#select pg_is_in_recovery(); pg_is_in_recovery ------------------- f (1 row) t true意味着处于 recovery 状态 f false意味着处于正常服务状态 3.4 主库查询 postgresserver1:5432/postgres-3210#select pid, usename,client_addr,state,sync_state,reply_time from pg_stat_replication; pid | usename | client_addr | state | sync_state | reply_time -------------------------------------------------------------------------------------- 3188 | rep1 | 192.168.133.152 | streaming | async | 2023-12-17 22:31:28.77559608 (1 row)
postgresserver1:5432/postgres-3210#select pid, usename,client_addr,state,sync_state,reply_time from pg_stat_replication; -[ RECORD 1 ]------------------------------ pid | 3188 usename | rep1 client_addr | 192.168.133.152 state | streaming sync_state | async reply_time | 2023-12-17 22:31:38.76111608
sync_state表示同步模式 sent_lsn表示发送日志的起点 reply_time表示应用日志的起点
postgresserver1:5432/postgres-3210#select * from pg_stat_replication; -[ RECORD 1 ]---------------------------------- pid | 3188 usesysid | 16409 usename | rep1 application_name | walreceiver client_addr | 192.168.133.152 client_hostname | server2 client_port | 59278 backend_start | 2023-12-17 22:25:07.17446508 backend_xmin | state | streaming sent_lsn | 0/28000C88 write_lsn | 0/28000C88 flush_lsn | 0/28000C88 replay_lsn | 0/28000C88 write_lag | flush_lag | replay_lag | sync_priority | 0 sync_state | async reply_time | 2023-12-17 22:33:18.80973908 3.5 备库查询 postgresserver2:5432/postgres-11485#select * from pg_stat_wal_receiver;-[ RECORD 1 ]--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- pid | 11386 status | streaming receive_start_lsn | 0/28000000 receive_start_tli | 3 written_lsn | 0/28000C88 flushed_lsn | 0/28000C88 received_tli | 3 last_msg_send_time | 2023-12-17 22:35:08.85059308 last_msg_receipt_time | 2023-12-17 22:35:08.8523808 latest_end_lsn | 0/28000C88 latest_end_time | 2023-12-17 22:30:08.73556508 slot_name | sender_host | server1 sender_port | 5432 conninfo | userrep1 password******** channel_bindingdisable dbnamereplication hostserver1 port5432 client_encodingGBK fallback_application_namewalreceiver sslmodedisable sslcompression0 sslcertmodedisable sslsni1 ssl_min_protocol_versionTLSv1.2 gssencmodedisable krbsrvnamepostgres gssdelegation0 target_session_attrsany load_balance_hostsdisable
--备库日志
023-12-17 22:25:07.215 CST [11386] LOG: started streaming WAL from primary at 0/28000000 on timeline 3 2023-12-17 22:25:08.324 CST [11347] LOG: restartpoint starting: time 2023-12-17 22:25:08.331 CST [11347] LOG: restartpoint complete: wrote 0 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write0.001 s, sync0.001 s, total0.007 s; sync files0, longest0.000 s, average0.000 s; distance16383 kB, estimate16389 kB; lsn0/28000028, redo lsn0/28000028 2023-12-17 22:25:08.331 CST [11347] LOG: recovery restart point at 0/28000028 2023-12-17 22:30:08.225 CST [11347] LOG: restartpoint starting: time 2023-12-17 22:30:08.332 CST [11347] LOG: restartpoint complete: wrote 2 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write0.103 s, sync0.001 s, total0.107 s; sync files2, longest0.001 s, average0.001 s; distance2 kB, estimate14751 kB; lsn0/28000BD8, redo lsn0/28000BA0 2023-12-17 22:30:08.332 CST [11347] LOG: recovery restart point at 0/28000BA0 2023-12-17 22:30:08.332 CST [11347] DETAIL: Last completed transaction was at log time 2023-12-17 22:27:29.98613208. 3.6 测试数据同步 1主库创建表
postgresserver1:5432/postgres-3210#create table emp(emp int); CREATE TABLE postgresserver1:5432/postgres-3210#insert into emp values(100); INSERT 0 1 postgresserver1:5432/postgres-3210#select * from emp; emp ----- 100 (1 row)
2 备库验证
[postgresserver2 pg_log]$ psql -h server2 Password for user postgres: psql (16.1) Type help for help.
postgresserver2:5432/postgres-11497#\d List of relations Schema | Name | Type | Owner ----------------------------------------- public | emp | table | postgres public | pg_buffercache | view | postgres (2 rows)
postgresserver2:5432/postgres-11497#select * from emp; emp ----- 100 (1 row)
通过以上可以看出主库新增数据已经同步到备库 4.主备切换
这里模拟主库出现问题 4.1 停止主库 [postgresserver1 data]$ pg_ctl stop waiting for server to shut down.... done server stopped [postgresserver1 data]$ [postgresserver1 data]$ [postgresserver1 data]$ pg_ctl status pg_ctl: no server running 4.2 提升备库为主 pg_ctl promote
postgresserver2 pg_log]$ pg_ctl promote waiting for server to promote.... done server promoted [postgresserver2 pg_log]$ pg_ctl status pg_ctl: server is running (PID: 11345) /u01/app/postgres/bin/postgres [postgresserver2 pg_log]$ pg_controldata |grep stat Database cluster state: in production [postgresserver2 pg_log]$
[postgresserver2 data]$ ls -ltr standby* ls: 无法访问 stand*: 没有那个文件或目录 [postgresserver2 data]$
[postgresserver2 data]$ ps -ef|grep wal postgres 11569 11345 0 22:44 ? 00:00:00 postgres: walwriter postgres 11592 9351 0 22:46 pts/0 00:00:00 grep --colorauto wal [postgresserver2 data]$ 4.3 创建standby.signal文件
在新备库上(原主库) 完成 只需要创建文件即可不需要添加内容
[postgresserver1 data]$ touch standby.signal [postgresserver1 data]$ [postgresserver1 data]$ ls -ltr standby.signal -rw-r--r-- 1 postgres postgres 0 12月 17 22:49 standby.signal 4.4 调整新备库 postgresql.auto.conf内容
在新备库的 postgresql.auto.conf 文件中添加如下内容
primary_conninfo userrep1 passwordrep1 passfile/home/postgres/.pgpass channel_bindingdisable hostserver2 port5432 client_encodingGBK sslmodedisable sslcompression0 sslcertmodedisable sslsni1 ssl_min_protocol_versionTLSv1.2 gssencmodedisable krbsrvnamepostgres gssdelegation0 target_session_attrsany load_balance_hostsdisable
4.5 启动新备库 [postgresserver1 data]$ pg_ctl restart waiting for server to shut down.... done server stopped waiting for server to start....2023-12-17 22:53:08.352 CST [3340] LOG: redirecting log output to logging collector process 2023-12-17 22:53:08.352 CST [3340] HINT: Future log output will appear in directory pg_log. done server started
4.6 验证 新备库 [postgresserver1 data]$ pg_controldata |grep state Database cluster state: in archive recovery
新主库 [postgresserver2 data]$ pg_controldata |grep stat Database cluster state: in production [postgresserver2 data]$ psql psql (16.1) Type help for help.
postgres[local]:5432/postgres-11653#\x Expanded display is on. postgres[local]:5432/postgres-11653#select pid, usename,client_addr,state,sync_state,reply_time from pg_stat_replication; -[ RECORD 1 ]------------------------------ pid | 11644 usename | rep1 client_addr | 192.168.133.151 state | streaming sync_state | async reply_time | 2023-12-17 22:55:48.47688308
postgres[local]:5432/postgres-11653#select * from pg_stat_replication; -[ RECORD 1 ]---------------------------------- pid | 11644 usesysid | 16409 usename | rep1 application_name | walreceiver client_addr | 192.168.133.151 client_hostname | server1 client_port | 47196 backend_start | 2023-12-17 22:53:08.39534708 backend_xmin | state | streaming sent_lsn | 0/290001B8 write_lsn | 0/290001B8 flush_lsn | 0/290001B8 replay_lsn | 0/290001B8 write_lag | flush_lag | replay_lag | sync_priority | 0 sync_state | async reply_time | 2023-12-17 22:56:18.48904508 到此切换完成。
