Thread: Add a hint when postgresql.conf hot_standby = 'off' and recovery.conf standby = 'on'

<div class="">If you do the following sequence, the server gives the least helpful error message:</div><div
class=""><brclass="" /></div><div class="">----</div><div class=""><font class="" face="Courier New">initdb
data</font></div><divclass=""><font class="" face="Courier New">pg_ctl -D data -l logfile start</font></div><div
class=""><fontclass="" face="Courier New"><br class="" /></font></div><div class=""><font class="" face="Courier New">#
Thefollowing reconfigs are obvious based on error message if you try to take a base backup</font></div><div
class=""><fontclass="" face="Courier New">echo 'local  replication  all  trust’ >>
data/pg_hba.conf</font></div><divclass=""><font class="" face="Courier New">sed -i 's/#wal_level = minimal/wal_level =
hot_standby/'data/postgresql.conf</font></div><div class=""><font class="" face="Courier New">sed -i
's/#max_wal_senders= 0/max_wal_senders = 5/' data/postgresql.conf</font></div><div class=""><font class=""
face="CourierNew"><br class="" /></font></div><div class=""><font class="" face="Courier New"># Backup and
start</font></div><divclass=""><font class="" face="Courier New">pg_basebackup -D data_5434 -R</font></div><div
class=""><fontclass="" face="Courier New">pg_ctl -D data_5434 -l logfile_5434 -o ‘-p 5434’ start</font></div><div
class=""><brclass="" /></div><div class=""><font class="" face="Courier New"># Attempt psql</font></div><div
class=""><fontclass="" face="Courier New">psql -p 5434</font></div><div class=""><font class="" face="Courier
New">> psql:FATAL:  the database system is starting up</font></div><div class="">----</div><div class="">It doesn’t
matterwhat log level you put the server log in either, it providers no additional helpful information.</div><div
class=""><brclass="" /></div><div class="">The problem of course is that:</div><div
class="">postgresql.conf: hot_standby= ‘off'</div><div class="">recovery.conf: standby = ‘on'</div><div class=""><br
class=""/></div><div class="">Why anybody in practice would want hot_standby off while in standby mode eludes me, but
theseare our default values (recovery.conf was generated by pg_basebackup -R).</div><div class=""><br class=""
/></div><divclass="">It seems worth adding a hint and/or changing the error message to be more descriptive when in this
state. Any options about what should be logged before I start putting together a patch?</div><div class=""><br
class="Apple-interchange-newline"/><span class="" style="color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px;
font-style:normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans:
auto;text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px;
-webkit-text-stroke-width:0px; display: inline !important; float: none;">- Matt K</span></div><br class="" /> 

On 05/22/2015 06:53 AM, Matthew Kelly wrote:
> Why anybody in practice would want hot_standby off while in standby
> mode eludes me, but these are our default values (recovery.conf was
> generated by pg_basebackup -R).

That's how you set up a cold standby system.

> It seems worth adding a hint and/or changing the error message to be
> more descriptive when in this state.  Any options about what should
> be logged before I start putting together a patch?

Yeah, might be worthwhile. Perhaps:

FATAL:  the database system is in standby mode and hot_standby is not 
enabled

Or just:

FATAL:  the database system is in cold standby mode

It would be useful to distinguish that state, where you could connect if 
hot standby was enabled, from the state where it's starting up and 
hasn't reached min-recovery-point yet and you couldn't connect even if 
hot_standby was enabled. Not sure how much signalling you'd need between 
postmaster and the startup process for that.

- Heikki

On Fri, May 22, 2015 at 4:22 PM, Heikki Linnakangas <hlinnaka@iki.fi> wrote:
> On 05/22/2015 06:53 AM, Matthew Kelly wrote:
>>
>> Why anybody in practice would want hot_standby off while in standby
>> mode eludes me, but these are our default values (recovery.conf was
>> generated by pg_basebackup -R).
>
>
> That's how you set up a cold standby system.
>
>> It seems worth adding a hint and/or changing the error message to be
>> more descriptive when in this state.  Any options about what should
>> be logged before I start putting together a patch?
>
>
> Yeah, might be worthwhile. Perhaps:
>
> FATAL:  the database system is in standby mode and hot_standby is not
> enabled

Since we may connect to the server even during an archive recovery (e.g.,
recovery_target_action = pause case), this message should be changed
as follows?

FATAL:  the database system is in standby or archive recovery mode and
hot_standby is not enabled

>
> Or just:
>
> FATAL:  the database system is in cold standby mode
>
> It would be useful to distinguish that state, where you could connect if hot
> standby was enabled, from the state where it's starting up and hasn't
> reached min-recovery-point yet and you couldn't connect even if hot_standby
> was enabled. Not sure how much signalling you'd need between postmaster and
> the startup process for that.

Probably we need to make the startup process send the SIGUSR1 signal to
postmaster when it reaches the consistent point even if hot_standby is not
enabled.

Regards,

-- 
Fujii Masao

Heikki Linnakangas <hlinnaka@iki.fi> writes:
> On 05/22/2015 06:53 AM, Matthew Kelly wrote:
>> It seems worth adding a hint and/or changing the error message to be
>> more descriptive when in this state.  Any options about what should
>> be logged before I start putting together a patch?

> Yeah, might be worthwhile. Perhaps:

> FATAL:  the database system is in standby mode and hot_standby is not 
> enabled

> Or just:

> FATAL:  the database system is in cold standby mode

> It would be useful to distinguish that state, where you could connect if 
> hot standby was enabled, from the state where it's starting up and 
> hasn't reached min-recovery-point yet and you couldn't connect even if 
> hot_standby was enabled. Not sure how much signalling you'd need between 
> postmaster and the startup process for that.

Another angle worth considering is whether PQping can or should
distinguish this state from "database is fully up".  (I do not
recall what it does right now.)
        regards, tom lane

On 05/22/2015 12:22 AM, Heikki Linnakangas wrote:
>> It seems worth adding a hint and/or changing the error message to be
>> more descriptive when in this state.  Any options about what should
>> be logged before I start putting together a patch?
> 
> Yeah, might be worthwhile. Perhaps:
> 
> FATAL:  the database system is in standby mode and hot_standby is not
> enabled
> 
> Or just:
> 
> FATAL:  the database system is in cold standby mode

Warm Standby is what we called it in the past, so I think we should be
consistent.  Otherwise +1.

The additional benefit of this is that it would (hopefully) allow us to
distinguish between a warm standby which was still reading its own xlogs
(i.e. in crash recovery) and a warm standby which was using the
restore_command (i.e. standby-ing).  For that reason, it would be ideal
if the new message only displayed once the restore_command starts being
used.

That is:
Cold Standby == DB Snapshot and a huge folder of WAL files (i.e. Barman)
Warm Standby == hot_standby=off, recoveryconf.standby=on
Hot Standby == hot_standby=on, recoveryconf.standby=on

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On 05/22/2015 11:01 AM, Josh Berkus wrote:
>
> On 05/22/2015 12:22 AM, Heikki Linnakangas wrote:
>>> It seems worth adding a hint and/or changing the error message to be
>>> more descriptive when in this state.  Any options about what should
>>> be logged before I start putting together a patch?
>>
>> Yeah, might be worthwhile. Perhaps:
>>
>> FATAL:  the database system is in standby mode and hot_standby is not
>> enabled
>>
>> Or just:
>>
>> FATAL:  the database system is in cold standby mode
>
> Warm Standby is what we called it in the past, so I think we should be
> consistent.  Otherwise +1.

As I recall, warm standby is actually what it is. A cold standby is not 
applying logs. A warm standby is. So if there is a recovery.conf and 
more importantly PostgreSQL is running, it is going to be a warm standby 
not a cold.

Other than that, absolute +1.

JD


-- 
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing "I'm offended" is basically telling the world you can't
control your own emotions, so everyone else should do it for you.