Thread: Turning a streaming standby into a hot-standby

Turning a streaming standby into a hot-standby

From
Axel Rau
Date:
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



Re: Turning a streaming standby into a hot-standby

From
Fujii Masao
Date:
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


Re: Turning a streaming standby into a hot-standby

From
Axel Rau
Date:
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



Re: Turning a streaming standby into a hot-standby

From
Fujii Masao
Date:
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