Обсуждение: hot standby in Postgresql 12

Preparing hot standby in PostgreSQL 12

I have created a standby.signal file and added the parameter hot_standby='on' in postgresql.conf file but i am still not able to query the standby database 

Database is not getting open for reading.

postgres@ip-172-31-31-150:/u01/db10/pg_wal> psql -p 5490
psql: error: FATAL:  the database system is starting up

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

Ok, thanks. 

But what is the logic behind that ... does the database only comes up after a switch 

Hi David,

> On 19. Jan, 2021, at 04:48, David G. Johnston <david.g.johnston@gmail.com> wrote:
>
> And now the standby is waiting for the next wal file to be archived by the primary.  You should probably set
archive_timeoutto a non-zero value since the primary doesn’t seem very busy (though you also still need to do at least
onewrite, empty wal files don’t get rotated out and archived.) 

you should be able to get around your problem by doing the following on the primary database:

checkpoint;
select pg_switch_wal();

The pg_switch_wal() alone will not trigger WAL file generation if no transaction has been going on. That's why I
recommendforcing a checkpoint before that (same with Oracle by the way). 

As soon as a WAL is generated, it should get shipped to the standby database. Then it should open.

Cheers,
Paul

Still the same :

checkpoint;
select pg_switch_wal(); 

loading few tables and did a switch as well , still the same, not able to connect to standby database

psql: error: FATAL:  the database system is starting up

Hey,

maybe your needed wal files were already archived and are not available anymore?
In this case you have to set wal_keep_size to a proper value to keep the needed wal files on your primary.

Best,

Dom

On Monday, January 18, 2021, Ankush Chawla <ankushchawla03@gmail.com> wrote:

Still the same :

checkpoint;
select pg_switch_wal(); 

loading few tables and did a switch as well , still the same, not able to connect to standby database

psql: error: FATAL:  the database system is starting up

And again, server log file?

David J.

-- 

Dominik Lehnen
Dev-Operations

t. +49 (0)30 288 768 855
e. dominik.lehnen@gameduell.de

GameDuell
Bringing people together to have a good time with games.

GameDuell GmbH, Taubenstraße 24-25, 10117 Berlin, Germany

We are looking for people to join our team!
http://inside.gameduell.com

HAUPTSITZ DER GESELLSCHAFT / HEADQUARTERS:
BERLIN, DEUTSCHLAND / GERMANY
GESCHÄFTSFÜHRER / MANAGING DIRECTORS:
KAI BOLIK & BORIS WASMUTH
HANDELSREGISTER / COMMERCIAL REGISTER:
AG BERLIN-CHARLOTTENBURG, HRB NR: 90891
UMSATZSTEUER-IDENTIFIKATIONSNUMMER / VAT ID: DE 813829498

(US Office: GameDuell Inc., 1156 Clement St., San Francisco, CA 94118, USA)

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

> > 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.