Message-ID: <1512712155.2390.6.camel@cybertec.at>
Subject: Re: "FATAL: the database system is starting up" and hot_standby = on
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Ilnur Khanov <khanov.i@gmail.com>, pgsql-admin@lists.postgresql.org
Date: Fri, 08 Dec 2017 06:49:15 +0100
In-Reply-To: 
 <CAN+PxfTGDLDkTih-DmOhzwJbuaUne9Gv8cW-6n35BRCFyLPSPQ@mail.gmail.com>
References: 
	<CAN+PxfQwWM5j4LvJNV1jERuvLV4x-Oi6w6JKQadPft_AztQBfQ@mail.gmail.com>
	 <1512634318.2188.3.camel@europecamions-interactive.com>
	 <CAN+PxfQjAC_GiSZ7WHZxsYNQTx0f=k8C2k3HMhRifsWKYHPVQg@mail.gmail.com>
	 <1512668252.2915.17.camel@cybertec.at>
	 <CAN+PxfTGDLDkTih-DmOhzwJbuaUne9Gv8cW-6n35BRCFyLPSPQ@mail.gmail.com>
Organization: Cybertec
Content-Type: text/plain; charset="UTF-8"
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit
Precedence: bulk

Ilnur Khanov wrote:
> I've just disabled hot_standby and restarted server. Everything is OK now.
> 
> I assume there was continuous recovery mode.
> 
> > You cannot modify the database, right?
> 
> No, I can. As long it was in this recovery mode with hot_standby=on I was able query and modify database, with hot_standby=off I couldn't even connect.
> Here is another my wonder: relying on manual, it had to be read-only mode.

Now that would be really strange if you could modify the database while in recovery mode.
Unfortunately you cannot test that now...

> That is the question: If I can't connect with "FATAL: the database system is starting up" how I can check pg_is_in_recovery()?

If you get the fatal message you *know* the system is recovering.
That query is to ascertain you are still in recovery if you set hot_standby=on.

> So my conclusion is:
>   If postgres says "FATAL: the database system is starting up" after failure without any other errors in logs and postgres output, than you can:
>      - wait for some long period of time
>          or
>      - you can turn on hot_standby and use your database with care while it's recovering and turn hot_standby off when it finishes.
> 
> Hoping this thread helps someone sometime.

In my opinion the main question is why crash recovery took so long.
The server log should be able to tell you more about it.

Is your checkpoint_timeout very high?
Was there a backup_label file in the data directory (now backup_label.old)?

Yours,
Laurenz Albe