Обсуждение: Turning a streaming standby into a hot-standby
Hi dbas, I changed hot_standby to "on" on the standby and learned on restart: "hot standby is not possible because wal_level was not set to ""hot_standby"" on the master server" After restarting the master with "wal_level = hot_standby", I'm still getting the same error message on the standby. This is 9.1.2. What am I doing wrong? Axel --- PGP-Key:29E99DD6 ☀ +49 151 2300 9283 ☀ computing @ chaos claudius
On Fri, Nov 9, 2012 at 1:13 AM, Axel Rau <axel.rau@chaos1.de> wrote: > Hi dbas, > > I changed hot_standby to "on" on the standby and learned on restart: > "hot standby is not possible because wal_level was not set to ""hot_standby"" on the master server" > After restarting the master with "wal_level = hot_standby", > I'm still getting the same error message on the standby. > This is 9.1.2. > > What am I doing wrong? After setting wal_level to hot_standby in the master, you need to take a fresh base backup from the master onto the standby, and start the standby from that backup. The standby with hot_standby=on needs the backup which was backed up from the master with wal_level=hot_standby. Regards, -- Fujii Masao
Am 08.11.2012 um 18:16 schrieb Fujii Masao: > On Fri, Nov 9, 2012 at 1:13 AM, Axel Rau <axel.rau@chaos1.de> wrote: >> Hi dbas, >> >> I changed hot_standby to "on" on the standby and learned on restart: >> "hot standby is not possible because wal_level was not set to ""hot_standby"" on the master server" >> After restarting the master with "wal_level = hot_standby", >> I'm still getting the same error message on the standby. >> This is 9.1.2. >> >> What am I doing wrong? > > After setting wal_level to hot_standby in the master, you need to take > a fresh base backup from the master onto the standby, and start the > standby from that backup. > > The standby with hot_standby=on needs the backup which was backed > up from the master with wal_level=hot_standby. Are you sure? I just turned off hot_standby, brought up the standby and let it catch up until it started streaming. I then turned on again hot_standby and the standby started successfully: --- 000,"recovery restart point at 8/C05E7AD8","last completed transaction was at log time 2012-11-08 17:24:27.007198+00",,,,,,,,"" 000,"database system is shut down",,,,,,,,,"" 00,"logger shutting down",,,,,,,,,"" ,"database system was shut down in recovery at 2012-11-08 17:24:37 GMT",,,,,,,,,"" ,"entering standby mode",,,,,,,,,"" 00,"checkpoint record is at 8/C05E7B10",,,,,,,,,"" 00,"redo record is at 8/C05E7AD8; shutdown FALSE",,,,,,,,,"" 00,"next transaction ID: 0/692227; next OID: 596645",,,,,,,,,"" 00,"next MultiXactId: 187; next MultiXactOffset: 388",,,,,,,,,"" 00,"oldest unfrozen transaction ID: 665, in database 1",,,,,,,,,"" 00,"transaction ID wrap limit is 2147484312, limited by database with OID 1",,,,,,,,,"" 00,"resetting unlogged relations: cleanup 1 init 0",,,,,,,,,"" 000,"initializing for hot standby",,,,,,,,,"" 0000,"redo starts at 8/C05E7AD8",,,,,,,,,"" ,00000,"recovery snapshots are now enabled",,,,,"xlog redo running xacts: nextXid 692227 latestCompletedXid 692226 oldestRunningXid692227",,,,"" 0000,"consistent recovery state reached at 8/C068C618",,,,,,,,,"" 0000,"invalid record length at 8/C068C618",,,,,,,,,"" ,"database system is ready to accept read only connections",,,,,,,,,"" ,"streaming replication successfully connected to primary",,,,,,,,,"" 0000,"connection received: host=[local]",,,,,,,,,"" --- Looks like a valid streaming standby to me. What do you mean? Axel --- PGP-Key:29E99DD6 ☀ +49 151 2300 9283 ☀ computing @ chaos claudius
On Fri, Nov 9, 2012 at 2:44 AM, Axel Rau <axel.rau@chaos1.de> wrote: > > Am 08.11.2012 um 18:16 schrieb Fujii Masao: > >> On Fri, Nov 9, 2012 at 1:13 AM, Axel Rau <axel.rau@chaos1.de> wrote: >>> Hi dbas, >>> >>> I changed hot_standby to "on" on the standby and learned on restart: >>> "hot standby is not possible because wal_level was not set to ""hot_standby"" on the master server" >>> After restarting the master with "wal_level = hot_standby", >>> I'm still getting the same error message on the standby. >>> This is 9.1.2. >>> >>> What am I doing wrong? >> >> After setting wal_level to hot_standby in the master, you need to take >> a fresh base backup from the master onto the standby, and start the >> standby from that backup. >> >> The standby with hot_standby=on needs the backup which was backed >> up from the master with wal_level=hot_standby. > Are you sure? > I just turned off hot_standby, brought up the standby and let it catch up until it started streaming. > I then turned on again hot_standby and the standby started successfully: > --- > 000,"recovery restart point at 8/C05E7AD8","last completed transaction was at log time 2012-11-08 17:24:27.007198+00",,,,,,,,"" > 000,"database system is shut down",,,,,,,,,"" > 00,"logger shutting down",,,,,,,,,"" > ,"database system was shut down in recovery at 2012-11-08 17:24:37 GMT",,,,,,,,,"" > ,"entering standby mode",,,,,,,,,"" > 00,"checkpoint record is at 8/C05E7B10",,,,,,,,,"" > 00,"redo record is at 8/C05E7AD8; shutdown FALSE",,,,,,,,,"" > 00,"next transaction ID: 0/692227; next OID: 596645",,,,,,,,,"" > 00,"next MultiXactId: 187; next MultiXactOffset: 388",,,,,,,,,"" > 00,"oldest unfrozen transaction ID: 665, in database 1",,,,,,,,,"" > 00,"transaction ID wrap limit is 2147484312, limited by database with OID 1",,,,,,,,,"" > 00,"resetting unlogged relations: cleanup 1 init 0",,,,,,,,,"" > 000,"initializing for hot standby",,,,,,,,,"" > 0000,"redo starts at 8/C05E7AD8",,,,,,,,,"" > ,00000,"recovery snapshots are now enabled",,,,,"xlog redo running xacts: nextXid 692227 latestCompletedXid 692226 oldestRunningXid692227",,,,"" > 0000,"consistent recovery state reached at 8/C068C618",,,,,,,,,"" > 0000,"invalid record length at 8/C068C618",,,,,,,,,"" > ,"database system is ready to accept read only connections",,,,,,,,,"" > ,"streaming replication successfully connected to primary",,,,,,,,,"" > 0000,"connection received: host=[local]",,,,,,,,,"" > --- > Looks like a valid streaming standby to me. > What do you mean? Yeah, that's another way to fix the situation that you encountered. IIRC, when the standby with hot_standby=on starts up, it needs the checkpoint WAL record which was generated by the master with wal_level=hot_standby. In your way, that checkpoint WAL record was streamed from the master to the standby while hot_standby is being disabled. So you can start up the standby with hot_standbfy=on. Regards, -- Fujii Masao