Re: hot standby in Postgresql 12

On Mon, 2021-01-18 at 20:48 -0700, David G. Johnston wrote:
> On Monday, January 18, 2021, Ankush Chawla <ankushchawla03(at)gmail(dot)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