注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

PG Hot Standby 从库宕机案例  

2011-02-23 12:49:51|  分类: PG案例分析 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

 
      今天在一台主机上创建数据库时犯了一个错误,导致 standby 库 PG Server 宕掉。具体是这样的。
   
1--背景
    今天业务需求,需要在原有主机上新增一个业务库,并分配新的表空间。已经有的主机上已经做了
Postgresql Hot standby 高可用。当时由于疏忽,忘记了重要的操作,下面是具体过程。

2--主库上创始建用户
CREATE ROLE mydb LOGIN  ENCRYPTED PASSWORD '***********'
nosuperuser noinherit nocreatedb nocreaterole ;

3--主库上创建表空间
mkdir -p /database/pgdata/pg_tbs/tbs_mydb
create tablespace tbs_mydb owner mydb LOCATION '/database/pgdata/pg_tbs/tbs_mydb';

4--主库上创建数据库
CREATE DATABASE mydb
WITH  OWNER = mydb
      TEMPLATE = template0
      ENCODING = 'UTF8'
      TABLESPACE = tbs_mydb;  

5--到从库主机上验证
     在主库上创建好数据库后, 准备到从库上观察新建的库是否已经同步到备库。此时,发现 Postgresql 已经 down了
当时还以为是谁停掉的呢,接下来查看日志。

6--从库CSV日志
2011-02-23 11:10:25.909 CST,,,6591,,4d35195a.19bf,12,,2011-01-18 12:38:50 CST,1/0,0,FATAL,58P01,"directory ""/database/pgdata/pg_tbs/tbs_mydb"" does not exist",,,,,"xlog redo create ts: 3046144 ""/database/pgdata/pg_tbs/tbs_mydb""",,,,""
2011-02-23 11:10:26.050 CST,,,6589,,4d351959.19bd,2,,2011-01-18 12:38:49 CST,,0,LOG,00000,"startup process (PID 6591) exited with exit code 1",,,,,,,,,""
2011-02-23 11:10:26.050 CST,,,6589,,4d351959.19bd,3,,2011-01-18 12:38:49 CST,,0,LOG,00000,"terminating any other active server processes",,,,,,,,,""

     重要信息""/database/pgdata/pg_tbs/tbs_mydb"", 完了,原来是自己在主库上创建表空间目录时,
忘记在备机上创建相同的目录了,这么重要的一点居然忘记,汗,大意了, 想到库刚停不久,新建好
这个目录,并启动备库,备库应该能赶得上主库。

7--解决方法
   在从库上创建目录 /database/pgdata/pg_tbs/tbs_mydb, 并启动 PG Server。

8--再次查看 CSV日志
2011-02-23 11:38:09.981 CST,,,15156,,4d648121.3b34,1,,2011-02-23 11:38:09 CST,,0,LOG,00000,"database system was interrupted while in recovery at log time 2011-02-23 10:59:21 CST",,"If this has occurred more than once some data might be corrupted and you might need to choose an earlier recovery target.",,,,,,,""
2011-02-23 11:38:09.981 CST,,,15156,,4d648121.3b34,2,,2011-02-23 11:38:09 CST,,0,LOG,00000,"could not open tablespace directory ""pg_tblspc/16386/PG_9.0_201004261"": No such file or directory",,,,,,,,,""
2011-02-23 11:38:09.981 CST,,,15156,,4d648121.3b34,3,,2011-02-23 11:38:09 CST,,0,LOG,00000,"could not open tablespace directory ""pg_tblspc/16394/PG_9.0_201004261"": No such file or directory",,,,,,,,,""
2011-02-23 11:38:09.982 CST,,,15156,,4d648121.3b34,4,,2011-02-23 11:38:09 CST,,0,LOG,00000,"could not open tablespace directory ""pg_tblspc/19507/PG_9.0_201004261"": No such file or directory",,,,,,,,,""
2011-02-23 11:38:09.982 CST,,,15156,,4d648121.3b34,5,,2011-02-23 11:38:09 CST,,0,LOG,00000,"could not open tablespace directory ""pg_tblspc/16392/PG_9.0_201004261"": No such file or directory",,,,,,,,,""
2011-02-23 11:38:09.982 CST,,,15156,,4d648121.3b34,6,,2011-02-23 11:38:09 CST,,0,LOG,00000,"could not open tablespace directory ""pg_tblspc/16396/PG_9.0_201004261"": No such file or directory",,,,,,,,,""
2011-02-23 11:38:09.982 CST,,,15156,,4d648121.3b34,7,,2011-02-23 11:38:09 CST,,0,LOG,00000,"could not open tablespace directory ""pg_tblspc/17048/PG_9.0_201004261"": No such file or directory",,,,,,,,,""
2011-02-23 11:38:09.982 CST,,,15156,,4d648121.3b34,8,,2011-02-23 11:38:09 CST,,0,LOG,00000,"entering standby mode",,,,,,,,,""
2011-02-23 11:38:10.054 CST,,,15156,,4d648121.3b34,9,,2011-02-23 11:38:09 CST,1/0,0,LOG,00000,"redo starts at 60/ADC26678",,,,,,,,,""
2011-02-23 11:38:10.347 CST,,,15156,,4d648121.3b34,10,,2011-02-23 11:38:09 CST,1/0,0,LOG,00000,"consistent recovery state reached at 60/AF50F140",,,,,,,,,""
2011-02-23 11:38:10.347 CST,,,15154,,4d648121.3b32,1,,2011-02-23 11:38:09 CST,,0,LOG,00000,"database system is ready to accept read only connections",,,,,,,,,""
2011-02-23 11:38:10.387 CST,,,15156,,4d648121.3b34,11,,2011-02-23 11:38:09 CST,1/0,0,LOG,00000,"invalid record length at 60/AF51E888",,,,,,,,,""
2011-02-23 11:38:10.393 CST,,,15160,,4d648122.3b38,1,,2011-02-23 11:38:10 CST,,0,LOG,00000,"streaming replication successfully connected to primary",,,,,,,,,""
  
       信息"streaming replication successfully connected to primary" 说明库已经恢复正常。
这次还好是备库菪机,对业务没有影响,而且发现及时,如果发现晚了,从库可能要重做,好险。
生产库上操作一定要谨慎,谨慎,再谨慎。  
  
9--查看从库已经同步过了。
postgres=# \l
                                    List of databases
     Name     |    Owner     | Encoding | Collation | Ctype |      Access privileges     
--------------+--------------+----------+-----------+-------+-----------------------------
 postgres     | postgres     | UTF8     | C         | C     |
 template0    | postgres     | UTF8     | C         | C     | =c/postgres                +
              |              |          |           |       | postgres=CTc/postgres
 template1    | postgres     | UTF8     | C         | C     | =c/postgres                +

 mydb      | mydb      | UTF8     | C         | C     |


总结:在PG主从模式环境下,新增业务库的方法如下
      1 在主库上创建用户。
      2 在主库主机上创建表空间目录。
      3 在从库主机上创建与主库上相同表空间目录。( 非常重要 )
      4 在主库上创建表空间
      5 在主库上创建数据库。

  评论这张
 
阅读(27778)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016