Message-ID: <cf454328754a7dda342d643c157c71a475ce05e8.camel@cybertec.at>
Subject: Re: hot standby in Postgresql 12
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: "David G. Johnston" <david.g.johnston@gmail.com>, Ankush Chawla
	 <ankushchawla03@gmail.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Date: Tue, 19 Jan 2021 10:32:59 +0100
In-Reply-To: 
 <CAKFQuway06_MQp3iG2dpOfi+bJ-c7g1hZC1bBXHroraM9Kz0ww@mail.gmail.com>
References: 
	<CAOY8JhBPpZ8JvZ8PGUzAbpz17Ra4yJ4w1s=oja0qzsY3sHdSOg@mail.gmail.com>
	 <CAKFQuwbMPfZAcS6tgy3LR9ZVXQMewFkPGfUr4M5nY-4LfChhmg@mail.gmail.com>
	 <CAOY8JhA114Ra77p5b5q7xiU6CgxksbX+wzQRcoT_sRQ1O20Kwg@mail.gmail.com>
	 <CAKFQuway06_MQp3iG2dpOfi+bJ-c7g1hZC1bBXHroraM9Kz0ww@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.36.5 (3.36.5-2.fc32) 
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Precedence: bulk

On Mon, 2021-01-18 at 20:48 -0700, David G. Johnston wrote:
> On Monday, January 18, 2021, Ankush Chawla <ankushchawla03@gmail.com> wrote:
> > log files ;
> > 
> > 
> > 2021-01-18 07:31:00.946 UTC   [21072]LOG:  database system was interrupted; last known up at 2021-01-18 07:30:07 UTC
> > cp: cannot stat '/u01/archive/00000004.history': No such file or directory
> > 2021-01-18 07:31:00.963 UTC   [21072]LOG:  entering standby mode
> > 2021-01-18 07:31:00.966 UTC   [21072]LOG:  restored log file "00000003.history" from archive
> > 2021-01-18 07:31:00.981 UTC   [21072]LOG:  restored log file "000000030000000000000034" from archive
> > 2021-01-18 07:31:01.029 UTC   [21072]LOG:  redo starts at 0/34000028
> > 2021-01-18 07:31:01.030 UTC   [21072]LOG:  consistent recovery state reached at 0/34000138
> > cp: cannot stat '/u01/archive/000000030000000000000035': No such file or directory
> > 2021-01-18 07:31:01.043 UTC   [21079]LOG:  started streaming WAL from primary at 0/35000000 on timeline 3
> > 
> 
> And now the standby is waiting for the next wal file to be archived by the primary.
> You should probably set archive_timeout to a non-zero value since the primary doesn’t
>  seem very busy (though you also still need to do at least one write, empty wal files
>  don’t get rotated out and archived.)

If the standby is *streaming* WAL, it is *not* waiting for a WAL segment to be archived.
WAL is streamed to the standby right away.

What the standby needs to become available for connections are two events in the WAL stream:
- the WAL entry that ends the base backup (BACKUP_END)
- a WAL entry with the running transactions on the primary (RUNNING_XACTS)

Since we see the message "consistent recovery state reached", the first condition
is satisfied.  So the standby is waiting for a RUNNING_XACTS.

These get written every couple of seconds by the primary server if all is well.
So it would be interesting to know what the primary is doing.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com