PG primary 和 slave 互换

Stella981
• 阅读 565

http://blog.sina.com.cn/s/blog_544a710b0101a122.html http://blog.51cto.com/heyiyi/1898506 https://blog.csdn.net/fjgui/article/details/47421609 https://blog.csdn.net/baiyinqiqi/article/details/47951687

  • 1.standby端，在$PGDATA/recovery里加上recovery_target_timeline = 'latest' pg9以后的官方文档有了这么一段话：

    Allow standby recovery to switch to a new timeline automatically (Heikki Linnakangas) Now standby servers scan the archive directory for new timelines periodically 什么是new timeline？后面就会看到

  • 2.关掉primary

    pg_ctl stop -D $PGDATA -m fast

    2018-11-27 17:23:01.059 CST,,,1624,,5bfcd2a7.658,1,,2018-11-27 13:14:15 CST,,0,LOG,00000,"shutting down",,,,,,,,,"" 2018-11-27 17:23:01.443 CST,,,1624,,5bfcd2a7.658,2,,2018-11-27 13:14:15 CST,,0,LOG,00000,"database system is shut down",,,,,,,,,"" 2018-11-27 17:23:01.672 CST,"repl","",3204,"172.16.10.142:58547",5bfd0cf5.c84,1,"",2018-11-27 17:23:01 CST,,0,FATAL,57P03,"the database system is shutting down",,,,,,,,,"" 2018-11-27 17:23:02.839 CST,"role1","pdb1",3205,"10.1.161.35:54606",5bfd0cf6.c85,1,"",2018-11-27 17:23:02 CST,,0,FATAL,57P03,"the database system is shutting down",,,,,,,,,""

  • 3.在standby端promote

    pg_ctl promote -D $PGDATA

    2018-11-27 17:25:02.448 CST,,,1940,,5bfd0d6e.794,1,,2018-11-27 17:25:02 CST,,0,FATAL,XX000,"could not connect to the primary server: could not connect to server: Connection refused Is the server running on host ""172.16.10.100"" and accepting TCP/IP connections on port 5432? ",,,,,,,,,"" 2018-11-27 17:25:03.792 CST,,,31753,,5bfd0874.7c09,7,,2018-11-27 17:03:48 CST,1/0,0,LOG,00000,"received promote request",,,,,,,,,"" 2018-11-27 17:25:03.792 CST,,,31753,,5bfd0874.7c09,8,,2018-11-27 17:03:48 CST,1/0,0,LOG,00000,"redo done at 0/19000028",,,,,,,,,"" 2018-11-27 17:25:03.792 CST,,,31753,,5bfd0874.7c09,9,,2018-11-27 17:03:48 CST,1/0,0,LOG,00000,"last completed transaction was at log time 2018-11-27 17:06:58.916715+08",,,,,,,,,"" 2018-11-27 17:25:03.794 CST,,,31753,,5bfd0874.7c09,10,,2018-11-27 17:03:48 CST,1/0,0,LOG,00000,"selected new timeline ID: 2",,,,,,,,,"" 2018-11-27 17:25:03.836 CST,,,31753,,5bfd0874.7c09,11,,2018-11-27 17:03:48 CST,1/0,0,FATAL,42501,"could not open file ""recovery.conf"": Permission denied",,,,,,,,,"" 2018-11-27 17:25:03.836 CST,,,31751,,5bfd0874.7c07,3,,2018-11-27 17:03:48 CST,,0,LOG,00000,"startup process (PID 31753) exited with exit code 1",,,,,,,,,"" 2018-11-27 17:25:03.836 CST,,,31751,,5bfd0874.7c07,4,,2018-11-27 17:03:48 CST,,0,LOG,00000,"terminating any other active server processes",,,,,,,,,"" 2018-11-27 17:25:03.836 CST,"postgres","pdb1",32068,"[local]",5bfd091d.7d44,1,"idle",2018-11-27 17:06:37 CST,3/0,0,WARNING,57P02,"terminating connection because of crash of another server process","The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.","In a moment you should be able to reconnect to the database and repeat your command.",,,,,,,"psql"

recovery.conf没权限更改，实例进程被终止，再开起来已经无法继续

2018-11-28 10:12:51.648 CST,,,18795,,5bfdf855.496b,7,,2018-11-28 10:07:17 CST,1/0,0,LOG,00000,"received promote request",,,,,,,,,""
2018-11-28 10:12:51.648 CST,,,18795,,5bfdf855.496b,8,,2018-11-28 10:07:17 CST,1/0,0,LOG,00000,"redo done at 0/1A000028",,,,,,,,,""
2018-11-28 10:12:51.648 CST,,,18795,,5bfdf855.496b,9,,2018-11-28 10:07:17 CST,1/0,0,LOG,00000,"last completed transaction was at log time 2018-11-28 10:10:28.375684+08",,,,,,,,,""
2018-11-28 10:12:51.649 CST,,,18795,,5bfdf855.496b,10,,2018-11-28 10:07:17 CST,1/0,0,LOG,00000,"selected new timeline ID: 2",,,,,,,,,""
2018-11-28 10:12:51.697 CST,,,18795,,5bfdf855.496b,11,,2018-11-28 10:07:17 CST,1/0,0,LOG,00000,"archive recovery complete",,,,,,,,,""
2018-11-28 10:12:51.715 CST,,,18795,,5bfdf855.496b,12,,2018-11-28 10:07:17 CST,1/0,0,LOG,00000,"MultiXact member wraparound protections are now enabled",,,,,,,,,""
2018-11-28 10:12:51.716 CST,,,18793,,5bfdf855.4969,3,,2018-11-28 10:07:17 CST,,0,LOG,00000,"database system is ready to accept connections",,,,,,,,,""
2018-11-28 10:12:51.716 CST,,,19752,,5bfdf9a3.4d28,1,,2018-11-28 10:12:51 CST,,0,LOG,00000,"autovacuum launcher started",,,,,,,,,""
2018-11-28 10:12:51.760 CST,,,19753,,5bfdf9a3.4d29,1,,2018-11-28 10:12:51 CST,,0,LOG,00000,"archive command failed with exit code 1","The failed archive command was: test ! -f /mysqldata/
pg/pgarch/00000002.history && cp pg_xlog/00000002.history /mysqldata/pg/pgarch/00000002.history",,,,,,,,""
2018-11-28 10:12:52.763 CST,,,19753,,5bfdf9a3.4d29,2,,2018-11-28 10:12:51 CST,,0,LOG,00000,"archive command failed with exit code 1","The failed archive command was: test ! -f /mysqldata/
pg/pgarch/00000002.history && cp pg_xlog/00000002.history /mysqldata/pg/pgarch/00000002.history",,,,,,,,""
2018-11-28 10:12:53.766 CST,,,19753,,5bfdf9a3.4d29,3,,2018-11-28 10:12:51 CST,,0,LOG,00000,"archive command failed with exit code 1","The failed archive command was: test ! -f /mysqldata/
pg/pgarch/00000002.history && cp pg_xlog/00000002.history /mysqldata/pg/pgarch/00000002.history",,,,,,,,""
2018-11-28 10:12:53.766 CST,,,19753,,5bfdf9a3.4d29,4,,2018-11-28 10:12:51 CST,,0,WARNING,01000,"archiving transaction log file ""00000002.history"" failed too many times, will try again l
ater",,,,,,,,,""

[postgres@mycat02 ~]$ pg_controldata
pg_control version number:            942
Catalog version number:               201409291
Database system identifier:           6583145462094845370
Database cluster state:               in production

这时standby已经转为primary了，到$PGDATA下可以看到recovery.conf变为了recovery.done

  • 4.把原来的primary恢复，成为新环境下的standby

    cd $PGDATA mv recovery.done recovery.conf standby_mode = on # 指定为从库 primary_conninfo = 'host=172.16.10.143 port=5432 user=repl password=mall%9K0924' # 对应的主库信息 recovery_target_timeline = 'latest' # 这个说明这个流复制同步到最新的数据 vi postgres.conf hot_standby = on

    新从库上

    [postgres@mysql56 pg_log]$ pg_controldata pg_control version number: 942 Catalog version number: 201409291 Database system identifier: 6583145462094845370 Database cluster state: in archive recovery

  • 5.级联状态

    master_172.16.10.143 --> slave01_172.16.10.100 --> slave02_172.16.10.142

    master

    postgres=# select * from pg_stat_replication; -[ RECORD 1 ]----+------------------------------ pid | 20456 usesysid | 16426 usename | repl application_name | walreceiver client_addr | 172.16.10.100 client_hostname | client_port | 39208 backend_start | 2018-11-28 10:17:55.837594+08 backend_xmin | state | streaming sent_location | 0/1A000348 write_location | 0/1A000348 flush_location | 0/1A000348 replay_location | 0/1A000348 sync_priority | 0 sync_state | async

    slave01

    pdb1=# select * from pg_stat_replication; -[ RECORD 1 ]----+------------------------------ pid | 8202 usesysid | 16426 usename | repl application_name | walreceiver client_addr | 172.16.10.142 client_hostname | client_port | 60725 backend_start | 2018-11-28 10:17:55.108761+08 backend_xmin | 1892 state | streaming sent_location | 0/1A000348 write_location | 0/1A000348 flush_location | 0/1A000348 replay_location | 0/1A000348 sync_priority | 0 sync_state | async

logcstrecovery前端前端
点赞
收藏
评论区
推荐文章
blmius blmius
•
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时，报错：原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等，这样可以更容易地在不同的环境中使用MySQL。全局s
4252
0
0
helloworld_34035044 helloworld_34035044
•
2年前
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时，如何在新增行里自动增加id呢？能新增整数排序id吗？目前可以在新增行里自动增加id，但只能用uuid函数增加UUID编码，不能新增整数排序id。uuid函数说明：获取一个UUID，可以在填报表中用来创建数据ID语法：uuid()或uuid(sep)参数说明：sep布尔值，生成的uuid中是否包含分隔符''，缺省为
1214
0
0
待兔 待兔
•
5个月前
手写Java HashMap源码
HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22
1137
0
0
Jacquelyn38 Jacquelyn38
•
3年前
2020年前端实用代码段，为你的工作保驾护航
有空的时候，自己总结了几个代码段，在开发中也经常使用，谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
2448
0
0
Stella981 Stella981
•
3年前
AndroidStudio封装SDK的那些事
<divclass"markdown\_views"<!flowchart箭头图标勿删<svgxmlns"http://www.w3.org/2000/svg"style"display:none;"<pathstrokelinecap"round"d"M5,00,2.55,5z"id"raphael
1191
0
0
Wesley13 Wesley13
•
3年前
P2P技术揭秘.P2P网络技术原理与典型系统开发
Modular.Java(2009.06)\.Craig.Walls.文字版.pdf:http://www.t00y.com/file/59501950(https://www.oschina.net/action/GoToLink?urlhttp%3A%2F%2Fwww.t00y.com%2Ffile%2F59501950)\More.E
1149
0
0
Wesley13 Wesley13
•
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败，请联系管理员！');中国在东8区所以加8方法二：selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
1382
0
0
Wesley13 Wesley13
•
3年前
00：Java简单了解
浅谈Java之概述Java是SUN(StanfordUniversityNetwork)，斯坦福大学网络公司)1995年推出的一门高级编程语言。Java是一种面向Internet的编程语言。随着Java技术在web方面的不断成熟，已经成为Web应用程序的首选开发语言。Java是简单易学，完全面向对象，安全可靠，与平台无关的编程语言。
1316
0
0
Stella981 Stella981
•
3年前
Django中Admin中的一些参数配置
设置在列表中显示的字段，id为django模型默认的主键list_display('id','name','sex','profession','email','qq','phone','status','create_time')设置在列表可编辑字段list_editable
1237
0
0
Wesley13 Wesley13
•
3年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
1588
0
0
Python进阶者 Python进阶者
•
11个月前
Excel中这日期老是出来00：00：00，怎么用Pandas把这个去除
大家好，我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下：这日期老是出来00：00：00，怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下：pd.toexcel之前把这
559
0
0
Stella981
Stella981
Lv1
接天莲叶无穷碧，映日荷花别样红。
文章
1.7w
粉丝
42
获赞
28
热门文章
OpenVPN下载、安装、配置及使用详解
GitHub神器，一个可以白嫖全网无损音乐的神器
OpenWrt 路由器过滤广告的N种方法
Python—执行系统命令的四种方法（os.system、os.popen、commands、subprocess）
SS端加密以及obfs混淆