PostgreSQL 9.3 beta2 stream replication primary standby switchover bug?

[更新]

已有patch. 请参见.

PostgreSQL 9.1,9.2,9.3 clean switchover Primary and Standby Patch.

http://blog.163.com/digoal@126/blog/static/16387704020136197354054/

打补丁前的测试 :

PostgreSQL 9.3 beta2 无法完成正常的主备角色切换.

Primary :

psql
checkpont;

pg_controldata

pg_control version number:            937
Catalog version number:               201306121
Database system identifier:           5899156703623933927
Database cluster state:               in production
pg_control last modified:             Thu 11 Jul 2013 11:05:38 AM CST
Latest checkpoint location:           0/C000060
Prior checkpoint location:            0/B000060
Latest checkpoint's REDO location:    0/C000028
Latest checkpoint's REDO WAL file:    00000004000000000000000C
Latest checkpoint's TimeLineID:       4
Latest checkpoint's PrevTimeLineID:   4
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0/1850
Latest checkpoint's NextOID:          32768
Latest checkpoint's NextMultiXactId:  1
Latest checkpoint's NextMultiOffset:  0
Latest checkpoint's oldestXID:        1674
Latest checkpoint's oldestXID's DB:   1
Latest checkpoint's oldestActiveXID:  1850
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 1
Time of latest checkpoint:            Thu 11 Jul 2013 11:05:38 AM CST
Fake LSN counter for unlogged rels:   0/1
Minimum recovery ending location:     0/0
Min recovery ending loc's timeline:   0
Backup start location:                0/0
Backup end location:                  0/0
End-of-backup record required:        no
Current wal_level setting:            hot_standby
Current max_connections setting:      10000
Current max_prepared_xacts setting:   0
Current max_locks_per_xact setting:   64
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 131072
WAL block size:                       16384
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996
Date/time type storage:               64-bit integers
Float4 argument passing:              by value
Float8 argument passing:              by value
Data page checksum version:           0

Standby :

pg_control version number:            937
Catalog version number:               201306121
Database system identifier:           5899156703623933927
Database cluster state:               in archive recovery
pg_control last modified:             Thu 11 Jul 2013 11:05:29 AM CST
Latest checkpoint location:           0/B000060
Prior checkpoint location:            0/B000060
Latest checkpoint's REDO location:    0/B000028
Latest checkpoint's REDO WAL file:    00000004000000000000000B
Latest checkpoint's TimeLineID:       4
Latest checkpoint's PrevTimeLineID:   4
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0/1850
Latest checkpoint's NextOID:          32768
Latest checkpoint's NextMultiXactId:  1
Latest checkpoint's NextMultiOffset:  0
Latest checkpoint's oldestXID:        1674
Latest checkpoint's oldestXID's DB:   1
Latest checkpoint's oldestActiveXID:  1850
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 1
Time of latest checkpoint:            Thu 11 Jul 2013 11:04:57 AM CST
Fake LSN counter for unlogged rels:   0/1
Minimum recovery ending location:     0/B0000F0
Min recovery ending loc's timeline:   4
Backup start location:                0/0
Backup end location:                  0/0
End-of-backup record required:        no
Current wal_level setting:            hot_standby
Current max_connections setting:      10000
Current max_prepared_xacts setting:   0
Current max_locks_per_xact setting:   64
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 131072
WAL block size:                       16384
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996
Date/time type storage:               64-bit integers
Float4 argument passing:              by value
Float8 argument passing:              by value
Data page checksum version:           0

Primary :

pg_ctl stop
waiting for server to shut down.... done
server stopped

pg_controldata

pg_control version number:            937
Catalog version number:               201306121
Database system identifier:           5899156703623933927
Database cluster state:               shut down
pg_control last modified:             Thu 11 Jul 2013 11:06:31 AM CST
Latest checkpoint location:           0/D000028
Prior checkpoint location:            0/C000060
Latest checkpoint's REDO location:    0/D000028
Latest checkpoint's REDO WAL file:    00000004000000000000000D
Latest checkpoint's TimeLineID:       4
Latest checkpoint's PrevTimeLineID:   4
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0/1850
Latest checkpoint's NextOID:          32768
Latest checkpoint's NextMultiXactId:  1
Latest checkpoint's NextMultiOffset:  0
Latest checkpoint's oldestXID:        1674
Latest checkpoint's oldestXID's DB:   1
Latest checkpoint's oldestActiveXID:  0
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 1
Time of latest checkpoint:            Thu 11 Jul 2013 11:06:31 AM CST
Fake LSN counter for unlogged rels:   0/1
Minimum recovery ending location:     0/0
Min recovery ending loc's timeline:   0
Backup start location:                0/0
Backup end location:                  0/0
End-of-backup record required:        no
Current wal_level setting:            hot_standby
Current max_connections setting:      10000
Current max_prepared_xacts setting:   0
Current max_locks_per_xact setting:   64
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 131072
WAL block size:                       16384
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996
Date/time type storage:               64-bit integers
Float4 argument passing:              by value
Float8 argument passing:              by value
Data page checksum version:           0

standby :

pg_controldata
pg_control version number:            937
Catalog version number:               201306121
Database system identifier:           5899156703623933927
Database cluster state:               in archive recovery
pg_control last modified:             Thu 11 Jul 2013 11:05:29 AM CST
Latest checkpoint location:           0/B000060
Prior checkpoint location:            0/B000060
Latest checkpoint's REDO location:    0/B000028
Latest checkpoint's REDO WAL file:    00000004000000000000000B
Latest checkpoint's TimeLineID:       4
Latest checkpoint's PrevTimeLineID:   4
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0/1850
Latest checkpoint's NextOID:          32768
Latest checkpoint's NextMultiXactId:  1
Latest checkpoint's NextMultiOffset:  0
Latest checkpoint's oldestXID:        1674
Latest checkpoint's oldestXID's DB:   1
Latest checkpoint's oldestActiveXID:  1850
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 1
Time of latest checkpoint:            Thu 11 Jul 2013 11:04:57 AM CST
Fake LSN counter for unlogged rels:   0/1
Minimum recovery ending location:     0/B0000F0
Min recovery ending loc's timeline:   4
Backup start location:                0/0
Backup end location:                  0/0
End-of-backup record required:        no
Current wal_level setting:            hot_standby
Current max_connections setting:      10000
Current max_prepared_xacts setting:   0
Current max_locks_per_xact setting:   64
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 131072
WAL block size:                       16384
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996
Date/time type storage:               64-bit integers
Float4 argument passing:              by value
Float8 argument passing:              by value
Data page checksum version:           0

pg_ctl promote

server promoting

pg_controldata

pg_control version number:            937
Catalog version number:               201306121
Database system identifier:           5899156703623933927
Database cluster state:               in production
pg_control last modified:             Thu 11 Jul 2013 11:07:37 AM CST
Latest checkpoint location:           0/D000090
Prior checkpoint location:            0/B000060
Latest checkpoint's REDO location:    0/D000058
Latest checkpoint's REDO WAL file:    00000005000000000000000D
Latest checkpoint's TimeLineID:       5
Latest checkpoint's PrevTimeLineID:   5
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0/1851
Latest checkpoint's NextOID:          32768
Latest checkpoint's NextMultiXactId:  1
Latest checkpoint's NextMultiOffset:  0
Latest checkpoint's oldestXID:        1674
Latest checkpoint's oldestXID's DB:   1
Latest checkpoint's oldestActiveXID:  1851
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 1
Time of latest checkpoint:            Thu 11 Jul 2013 11:07:37 AM CST
Fake LSN counter for unlogged rels:   0/1
Minimum recovery ending location:     0/0
Min recovery ending loc's timeline:   0
Backup start location:                0/0
Backup end location:                  0/0
End-of-backup record required:        no
Current wal_level setting:            hot_standby
Current max_connections setting:      10000
Current max_prepared_xacts setting:   0
Current max_locks_per_xact setting:   64
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 131072
WAL block size:                       16384
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996
Date/time type storage:               64-bit integers
Float4 argument passing:              by value
Float8 argument passing:              by value
Data page checksum version:           0

primary :

 cd $PGDATA
 mv recovery.done recovery.conf
 pg_ctl start

log :

2013-07-11 11:10:11.386 CST,,,14911,,51de2213.3a3f,1,,2013-07-11 11:10:11 CST,,0,LOG,00000,"database system was shut down in recovery at 2013-07-11 11:09:51 CST",,,,,,,,"StartupXLOG, xlog.c:4895",""
2013-07-11 11:10:11.387 CST,,,14911,,51de2213.3a3f,2,,2013-07-11 11:10:11 CST,,0,LOG,00000,"entering standby mode",,,,,,,,"StartupXLOG, xlog.c:4968",""
2013-07-11 11:10:11.391 CST,,,14911,,51de2213.3a3f,3,,2013-07-11 11:10:11 CST,1/0,0,LOG,00000,"consistent recovery state reached at 0/D000090",,,,,,,,"CheckRecoveryConsistency, xlog.c:6187",""
2013-07-11 11:10:11.391 CST,,,14911,,51de2213.3a3f,4,,2013-07-11 11:10:11 CST,1/0,0,LOG,00000,"record with zero length at 0/D000090",,,,,,,,"ReadRecord, xlog.c:3285",""
2013-07-11 11:10:11.392 CST,,,14909,,51de2213.3a3d,1,,2013-07-11 11:10:11 CST,,0,LOG,00000,"database system is ready to accept read only connections",,,,,,,,"sigusr1_handler, postmaster.c:4658",""
2013-07-11 11:10:11.407 CST,,,14915,,51de2213.3a43,1,,2013-07-11 11:10:11 CST,,0,LOG,00000,"fetching timeline history file for timeline 5 from primary server",,,,,,,,"WalRcvFetchTimeLineHistoryFiles, walreceiver.c:666",""
2013-07-11 11:10:11.411 CST,,,14915,,51de2213.3a43,2,,2013-07-11 11:10:11 CST,,0,LOG,00000,"primary server contains no more WAL on requested timeline 4",,,,,,,,"WalReceiverMain, walreceiver.c:529",""
2013-07-11 11:10:11.411 CST,,,14911,,51de2213.3a3f,5,,2013-07-11 11:10:11 CST,1/0,0,LOG,00000,"new timeline 5 forked off current database system timeline 4 before current recovery point 0/D000090",,,,,,,,"rescanLatestTimeLine, xlog.c:3441",""
2013-07-11 11:10:11.412 CST,,,14915,,51de2213.3a43,3,,2013-07-11 11:10:11 CST,,0,LOG,00000,"primary server contains no more WAL on requested timeline 4",,,,,,,,"WalReceiverMain, walreceiver.c:529",""
2013-07-11 11:10:11.412 CST,,,14911,,51de2213.3a3f,6,,2013-07-11 11:10:11 CST,1/0,0,LOG,00000,"new timeline 5 forked off current database system timeline 4 before current recovery point 0/D000090",,,,,,,,"rescanLatestTimeLine, xlog.c:3441",""
2013-07-11 11:10:16.407 CST,,,14915,,51de2213.3a43,4,,2013-07-11 11:10:11 CST,,0,LOG,00000,"primary server contains no more WAL on requested timeline 4",,,,,,,,"WalReceiverMain, walreceiver.c:529",""
2013-07-11 11:10:16.407 CST,,,14911,,51de2213.3a3f,7,,2013-07-11 11:10:11 CST,1/0,0,LOG,00000,"new timeline 5 forked off current database system timeline 4 before current recovery point 0/D000090",,,,,,,,"rescanLatestTimeLine, xlog.c:3441",""
2013-07-11 11:10:21.402 CST,,,14915,,51de2213.3a43,5,,2013-07-11 11:10:11 CST,,0,LOG,00000,"primary server contains no more WAL on requested timeline 4",,,,,,,,"WalReceiverMain, walreceiver.c:529",""
2013-07-11 11:10:21.402 CST,,,14911,,51de2213.3a3f,8,,2013-07-11 11:10:11 CST,1/0,0,LOG,00000,"new timeline 5 forked off current database system timeline 4 before current recovery point 0/D000090",,,,,,,,"rescanLatestTimeLine, xlog.c:3441",""
2013-07-11 11:10:26.398 CST,,,14915,,51de2213.3a43,6,,2013-07-11 11:10:11 CST,,0,LOG,00000,"primary server contains no more WAL on requested timeline 4",,,,,,,,"WalReceiverMain, walreceiver.c:529",""

同主机上,将数据库版本更换为9.2测试切换正常.

时间： 2024-10-27 16:54:52

PostgreSQL 9.3 beta2 stream replication primary standby switchover bug?的相关文章

PostgreSQL HOT STANDBY using Stream replication

案例解析二.PostgreSQL HOT STANDBY by stream replication 测试:一.准备硬件1. 主节点硬件配置DISK : 146GB*6MEM : 14GBCPU : 2.83GHz*82. standby节点硬件配置DISK : 146GB*4MEM : 8GBCPU : 2.0GHz*8 二.准备环境1. 系统Red Hat Enterprise Linux Server release 5.5 (Tikanga) x642. 时钟同步8 * * * * /u

pgpool find primary node in master/slave stream replication mode

今天一位群里的兄弟遇到的pgpool的问题, pgpool的后端是两个数据库, 分别是master和standby. (用stream replication搭建好的两个数据库). pgpool 和主库放在同一个服务器上. pgpool 的几个关键配置摘录一下 : listen_addresses = '*' port = 9999 socket_dir = '/tmp' pcp_timeout = 1000 pcp_port = 9898 pcp_socket_dir = '/tmp' bac

Oracle Stream Replication技术简介

Stream 是Oracle 的消息队列(也叫Oracle Advanced Queue)技术的一种扩展应用. Oracle 的消息队列是通过发布/订阅的方式来解决事件管理.流复制(Stream replication)只是基于它的一个数据共享技术,也可以被用作一个可灵活定制的高可用性方案. 它可以实现两个数据库之间数据库级,schema级,Table级的数据同步,并且这种同步可以是双向的. Oracle Stream也是通过数据冗余来提高可用性,这一点和Data Guard 类型. Oracl

Data Guard Physical Standby Switchover

Data Guard Physical Standby Switchover 一.确保主库相关参数文件,设置正确如下为主数据库相关参数 DB_NAME=pridb DB_UNIQUE_NAME=pridb LOG_ARCHIVE_CONFIG='DG_CONFIG=(pridb,stdb)' CONTROL_FILES='/arch1/chicago/control1.ctl', '/arch2/chicago/control2.ct' LOG_ARCHIVE_DEST_1= 'LOCATIO

PostgreSQL 9.1,9.2,9.3 clean switchover Primary and Standby Patch.

前段时间在使用9.3 beta2的流复制时发现无法完成主备数据库的角色互相切换. 原因是备节点的receiver进程在知晓主节点的sender进程关闭后也会自动关闭,可能导致无法接收primary节点关闭时完全的xlog信息. 因此角色无法互相切换. 9.3beta2发布的第二天这个补丁就提交了, 如下 : Support clean switchover. author Fujii Masao <fujii@postgresql.org> Tue, 25 Jun 2013 17:14:3

PostgreSQL patch: Allow a streaming replication standby to follow a timeline switch

这个补丁不错, 省去了主从切换手工复制history文件的烦恼. Before this patch, streaming replication would refuse to start replicating if the timeline in the primary doesn't exactly match the standby. The situation where it doesn't match is when you have a master, and two sta

PostgreSQL 9.4 BDR(Bi-Directional Replication), LLSR(Logical Log Stream Replication)

非常感谢2ND团队的辛勤付出, PostgreSQL 9.4的逻辑流复制模块BDR已经小有所成, 本文将演示一下bdr的使用. bi-directional replication顾名思义, 就是互相复制的意思(也称为逻辑流复制), 同样需要利用WAL, 目前为单进程, 以后可能会改进到多进程, 不过即使单进程, 也比目前的其他第三方复制快很多(如slony-I, londiste3). 目前BDR逻辑流复制的最小粒度为数据库级别, 而PG9.0以来的物理流复制是整个集群级别的. 注意目前BD

PostgreSQL client's startup packet different between logical and normal stream replication

我们知道PostgreSQL 9.4新增了逻辑流复制的功能, 在客户端连接数据库服务器时, 通过发送给数据库的startup packet来判断是否要数据库启动wal sender, 并且如何来识别是逻辑复制还是普通的流复制. 数据库处理启动包的代码9.3和9.4的区别 : PostgreSQL 9.4的 ProcessStartupPacket@src/backend/postmaster/postmaster.c /* * Read a client's startup packet an

PostgreSQL fail-back able stream replication configuration

几天前测试过一个PostgreSQL 的fail-back补丁, 发现fail-back未成功, http://blog.163.com/digoal@126/blog/static/16387704020137551911457/ 这个bug实际上是另一个补丁引起的. http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=91c3613d3748d881706c3e60d8221ea92833ac1a Fix assert