Re: Nicer error when connecting to standby with hot_standby=off

On 2020/08/01 5:18, James Coleman wrote:
> On Wed, Jul 29, 2020 at 11:24 AM Fujii Masao
> <masao(dot)fujii(at)oss(dot)nttdata(dot)com> wrote:
>>
>>
>>
>> On 2020/04/03 22:49, James Coleman wrote:
>>> On Thu, Apr 2, 2020 at 5:53 PM David Zhang <david(dot)zhang(at)highgo(dot)ca> wrote:
>>>>
>>>> The following review has been posted through the commitfest application:
>>>> make installcheck-world: not tested
>>>> Implements feature: tested, passed
>>>> Spec compliant: not tested
>>>> Documentation: not tested
>>>>
>>>> I applied the patch to the latest master branch and run a test below. The error messages have been separated. Below is the test steps.
>>>>
>>>> ### setup primary server
>>>> initdb -D /tmp/primary/data
>>>> mkdir /tmp/archive_dir
>>>> echo "archive_mode='on'" >> /tmp/primary/data/postgresql.conf
>>>> echo "archive_command='cp %p /tmp/archive_dir/%f'" >> /tmp/primary/data/postgresql.conf
>>>> pg_ctl -D /tmp/primary/data -l /tmp/primary-logs start
>>>>
>>>> ### setup host standby server
>>>> pg_basebackup -p 5432 -w -R -D /tmp/hotstandby
>>>> echo "primary_conninfo='host=127.0.0.1 port=5432 user=pgdev'" >> /tmp/hotstandby/postgresql.conf
>>>> echo "restore_command='cp /tmp/archive_dir/%f %p'" >> /tmp/hotstandby/postgresql.conf
>>>> echo "hot_standby = off" >> /tmp/hotstandby/postgresql.conf
>>>> pg_ctl -D /tmp/hotstandby -l /tmp/hotstandby-logs -o "-p 5433" start
>>>>
>>>> ### keep trying to connect to hot standby server in order to get the error messages in different stages.
>>>> while true; do echo "`date`"; psql postgres -p 5433 -c "SELECT txid_current_snapshot();" sleep 0.2; done
>>>>
>>>> ### before the patch
>>>> psql: error: could not connect to server: FATAL: the database system is starting up
>>>> ...
>>>>
>>>> ### after the patch, got different messages, one message indicates hot_standby is off
>>>> psql: error: could not connect to server: FATAL: the database system is starting up
>>>> ...
>>>> psql: error: could not connect to server: FATAL: the database system is up, but hot_standby is off
>>>> ...
>>>
>>> Thanks for the review and testing!
>>
>> Thanks for the patch! Here is the comment from me.
>>
>> + else if (!FatalError && pmState == PM_RECOVERY)
>> + return CAC_STANDBY; /* connection disallowed on non-hot standby */
>>
>> Even if hot_standby is enabled, pmState seems to indicate PM_RECOVERY
>> until recovery has reached a consistent state. No? So, if my understanding
>> is right, "FATAL: the database system is up, but hot_standby is off" can
>> be logged even when hot_standby is on. Which sounds very confusing.
>
> That's a good point. I've attached a corrected version.

Thanks for updating the patch! But it failed to be applied to the master branch
cleanly because of the recent commit 0038f94387. So could you update the patch
again?

Regards,

--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION