pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedhot standby in Postgresql 12
19+ messages / 7 participants
[nested] [flat]
* hot standby in Postgresql 12
@ 2021-01-19 03:20 Ankush Chawla <ankushchawla03@gmail.com>
0 siblings, 2 replies; 19+ messages in thread
From: Ankush Chawla @ 2021-01-19 03:20 UTC (permalink / raw)
To: pgsql-admin
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
--
Best Regards,
Ankush Chawla
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: hot standby in Postgresql 12
@ 2021-01-19 03:31 David G. Johnston <david.g.johnston@gmail.com>
parent: Ankush Chawla <ankushchawla03@gmail.com>
1 sibling, 1 reply; 19+ messages in thread
From: David G. Johnston @ 2021-01-19 03:31 UTC (permalink / raw)
To: Ankush Chawla <ankushchawla03@gmail.com>; +Cc: pgsql-admin
On Monday, January 18, 2021, Ankush Chawla <ankushchawla03@gmail.com> wrote:
> 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
>
>
The database is starting up, this is expected. You need to consult the
server logs for relevant information pertaining to the startup/restore
process.
David J.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: hot standby in Postgresql 12
@ 2021-01-19 03:33 Ankush Chawla <ankushchawla03@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 19+ messages in thread
From: Ankush Chawla @ 2021-01-19 03:33 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-admin
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
2021-01-18 07:31:10.242 UTC postgres postgres [21082]FATAL: the database
system is starting up
2021-01-18 07:53:42.915 UTC postgres postgres [21160]FATAL: the database
system is starting up
On Tue, Jan 19, 2021 at 9:01 AM David G. Johnston <
david.g.johnston@gmail.com> wrote:
> On Monday, January 18, 2021, Ankush Chawla <ankushchawla03@gmail.com>
> wrote:
>
>> 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
>>
>>
> The database is starting up, this is expected. You need to consult the
> server logs for relevant information pertaining to the startup/restore
> process.
>
> David J.
>
>
--
Best Regards,
Ankush Chawla
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: hot standby in Postgresql 12
@ 2021-01-19 03:48 David G. Johnston <david.g.johnston@gmail.com>
parent: Ankush Chawla <ankushchawla03@gmail.com>
0 siblings, 3 replies; 19+ messages in thread
From: David G. Johnston @ 2021-01-19 03:48 UTC (permalink / raw)
To: Ankush Chawla <ankushchawla03@gmail.com>; +Cc: pgsql-admin
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.)
David J.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: hot standby in Postgresql 12
@ 2021-01-19 04:14 Ankush Chawla <ankushchawla03@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
2 siblings, 1 reply; 19+ messages in thread
From: Ankush Chawla @ 2021-01-19 04:14 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-admin
Ok, thanks.
But what is the logic behind that ... does the database only comes up after
a switch
On Tue, Jan 19, 2021 at 9:18 AM David G. Johnston <
david.g.johnston@gmail.com> 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.)
>
> David J.
>
>
--
Best Regards,
Ankush Chawla
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: hot standby in Postgresql 12
@ 2021-01-19 04:24 David G. Johnston <david.g.johnston@gmail.com>
parent: Ankush Chawla <ankushchawla03@gmail.com>
0 siblings, 0 replies; 19+ messages in thread
From: David G. Johnston @ 2021-01-19 04:24 UTC (permalink / raw)
To: Ankush Chawla <ankushchawla03@gmail.com>; +Cc: pgsql-admin
On Monday, January 18, 2021, Ankush Chawla <ankushchawla03@gmail.com> wrote:
> Ok, thanks.
> But what is the logic behind that ... does the database only comes up
> after a switch
>
Don’t know...just reading the documentation and it says (I think) that the
next segment is required before the server will accept connections.
David J.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: hot standby in Postgresql 12
@ 2021-01-19 05:48 Paul Förster <paul.foerster@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
2 siblings, 1 reply; 19+ messages in thread
From: Paul Förster @ 2021-01-19 05:48 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Ankush Chawla <ankushchawla03@gmail.com>; pgsql-admin
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_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.)
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 recommend forcing 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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: hot standby in Postgresql 12
@ 2021-01-19 06:17 Ankush Chawla <ankushchawla03@gmail.com>
parent: Paul Förster <paul.foerster@gmail.com>
0 siblings, 1 reply; 19+ messages in thread
From: Ankush Chawla @ 2021-01-19 06:17 UTC (permalink / raw)
To: Paul Förster <paul.foerster@gmail.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-admin
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*
On Tue, Jan 19, 2021 at 11:18 AM Paul Förster <paul.foerster@gmail.com>
wrote:
> 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_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.)
>
> 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 recommend forcing 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
--
Best Regards,
Ankush Chawla
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: hot standby in Postgresql 12
@ 2021-01-19 06:26 David G. Johnston <david.g.johnston@gmail.com>
parent: Ankush Chawla <ankushchawla03@gmail.com>
0 siblings, 1 reply; 19+ messages in thread
From: David G. Johnston @ 2021-01-19 06:26 UTC (permalink / raw)
To: Ankush Chawla <ankushchawla03@gmail.com>; +Cc: Paul Förster <paul.foerster@gmail.com>; pgsql-admin
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.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: hot standby in Postgresql 12
@ 2021-01-19 09:02 Dominik Lehnen <dominik.lehnen@gameduell.de>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 19+ messages in thread
From: Dominik Lehnen @ 2021-01-19 09:02 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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 19.01.21 07:26, David G. Johnston wrote:
>
> On Monday, January 18, 2021, Ankush Chawla <ankushchawla03@gmail.com
> <mailto: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)
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: hot standby in Postgresql 12
@ 2021-01-19 09:19 Ganesh Korde <ganeshakorde@gmail.com>
parent: Ankush Chawla <ankushchawla03@gmail.com>
1 sibling, 2 replies; 19+ messages in thread
From: Ganesh Korde @ 2021-01-19 09:19 UTC (permalink / raw)
To: Ankush Chawla <ankushchawla03@gmail.com>; +Cc: pgsql-admin
Looks like you have mentioned "hot_standby= on" on both places i.e
recovery.conf and postgresql.conf. keep it at only one place and check.
On Tue, 19 Jan 2021, 8:51 am Ankush Chawla, <ankushchawla03@gmail.com>
wrote:
> 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
>
> --
> Best Regards,
> Ankush Chawla
>
>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: hot standby in Postgresql 12
@ 2021-01-19 09:20 Ankush Chawla <ankushchawla03@gmail.com>
parent: Ganesh Korde <ganeshakorde@gmail.com>
1 sibling, 0 replies; 19+ messages in thread
From: Ankush Chawla @ 2021-01-19 09:20 UTC (permalink / raw)
To: Ganesh Korde <ganeshakorde@gmail.com>; +Cc: pgsql-admin
it is there only in postgresql.conf .
I am usign postgresql 12 so not using recovery.conf but using standby.signal
On Tue, Jan 19, 2021 at 2:49 PM Ganesh Korde <ganeshakorde@gmail.com> wrote:
> Looks like you have mentioned "hot_standby= on" on both places i.e
> recovery.conf and postgresql.conf. keep it at only one place and check.
>
> On Tue, 19 Jan 2021, 8:51 am Ankush Chawla, <ankushchawla03@gmail.com>
> wrote:
>
>> 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
>>
>> --
>> Best Regards,
>> Ankush Chawla
>>
>>
--
Best Regards,
Ankush Chawla
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: hot standby in Postgresql 12
@ 2021-01-19 09:32 Ankush Chawla <ankushchawla03@gmail.com>
parent: Ganesh Korde <ganeshakorde@gmail.com>
1 sibling, 0 replies; 19+ messages in thread
From: Ankush Chawla @ 2021-01-19 09:32 UTC (permalink / raw)
To: Ganesh Korde <ganeshakorde@gmail.com>; +Cc: pgsql-admin
this was steps followed at standby site
1. Create file standby.signal
2. Edit postgresql.conf In Standby
hot_standby=on (off by default will not allow queries to run only for
high availability)
primary_conninfo = 'user=postgres password=<masterdbpassword>
host=172.31.37.228 port=5333'
primary_slot_name = 'standby'
recovery_target_timeline = 'latest'
3. Save and start the Standby
Pg_Ctl start –D <>-l logfile
On Tue, Jan 19, 2021 at 2:49 PM Ganesh Korde <ganeshakorde@gmail.com> wrote:
> Looks like you have mentioned "hot_standby= on" on both places i.e
> recovery.conf and postgresql.conf. keep it at only one place and check.
>
> On Tue, 19 Jan 2021, 8:51 am Ankush Chawla, <ankushchawla03@gmail.com>
> wrote:
>
>> 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
>>
>> --
>> Best Regards,
>> Ankush Chawla
>>
>>
--
Best Regards,
Ankush Chawla
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: hot standby in Postgresql 12
@ 2021-01-19 09:32 Laurenz Albe <laurenz.albe@cybertec.at>
parent: David G. Johnston <david.g.johnston@gmail.com>
2 siblings, 1 reply; 19+ messages in thread
From: Laurenz Albe @ 2021-01-19 09:32 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; Ankush Chawla <ankushchawla03@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: hot standby in Postgresql 12
@ 2021-01-19 15:11 David G. Johnston <david.g.johnston@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 19+ messages in thread
From: David G. Johnston @ 2021-01-19 15:11 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Ankush Chawla <ankushchawla03@gmail.com>; pgsql-admin
On Tuesday, January 19, 2021, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>
> > > 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.
>
I took the error from the cp command to mean that this was in fact a warm
standby regardless of any claims by the poster. I’ll readily admit I am
not that familiar with this stuff though.
David J.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: hot standby in Postgresql 12
@ 2021-01-19 21:55 Rajni Baliyan <saan654@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 19+ messages in thread
From: Rajni Baliyan @ 2021-01-19 21:55 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; Ankush Chawla <ankushchawla03@gmail.com>; pgsql-admin
As David highlighted,
>> I took the error from the cp command to mean that this was in fact a
warm standby regardless of any claims by the poster. I’ll readily admit I
am not that familiar with this stuff though.
Seems your archive command is not working properly. Set it first and then
try again.
Also, please share your postgresql.auto.conf ( remove confidential
information like - password , username , IP etc. , if any)
Regards
Rajni
On Wed, Jan 20, 2021 at 2:11 AM David G. Johnston <
david.g.johnston@gmail.com> wrote:
> On Tuesday, January 19, 2021, Laurenz Albe <laurenz.albe@cybertec.at>
> wrote:
>
>>
>> > > 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.
>>
>
> I took the error from the cp command to mean that this was in fact a warm
> standby regardless of any claims by the poster. I’ll readily admit I am
> not that familiar with this stuff though.
>
> David J.
>
>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: hot standby in Postgresql 12
@ 2021-01-19 22:03 David G. Johnston <david.g.johnston@gmail.com>
parent: Rajni Baliyan <saan654@gmail.com>
0 siblings, 1 reply; 19+ messages in thread
From: David G. Johnston @ 2021-01-19 22:03 UTC (permalink / raw)
To: Rajni Baliyan <saan654@gmail.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; Ankush Chawla <ankushchawla03@gmail.com>; pgsql-admin
On Tue, Jan 19, 2021 at 2:56 PM Rajni Baliyan <saan654@gmail.com> wrote:
> As David highlighted,
> >> I took the error from the cp command to mean that this was in fact a
> warm standby regardless of any claims by the poster. I’ll readily admit I
> am not that familiar with this stuff though.
> Seems your archive command is not working properly. Set it first and then
> try again.
>
Don't think so - as Laurenz noted the last line reads:
"2021-01-18 07:31:01.043 UTC [21079]LOG: started streaming WAL from
primary at 0/35000000 on timeline 3"
Which indicates that streaming is supposedly working even though the
expected data hasn't made it from the primary to the standby.
David J.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: hot standby in Postgresql 12
@ 2021-01-19 23:15 Rajni Baliyan <saan654@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 19+ messages in thread
From: Rajni Baliyan @ 2021-01-19 23:15 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; Ankush Chawla <ankushchawla03@gmail.com>; pgsql-admin
Yes David - the problem here is expected data hasn't made it from the
primary to the standby even after streaming is started , and there could be
many reasons for it.
I will be interested to know -
- How user created standby (pg_basebackup etc.)
- Was there any manual cleanup done on the standby pg_xlog directory?
- Is the walreceiver process running on standby? --> ps -ef | grep postgres
- Is the walsender process running on master ? --> ps -ef | grep postgres
- postgresql.auto.conf file
- Database system identifier for both master and standby ( use utility
- pg_controldata -D <master/standby data dir> liek - pg_controldata
-D /user/app/master)
I would do below to narrow down the issue-
- Stop postgres on standby or kill the postmaster process.
- Increase logging on standby
- Restart standby.
- Check standby log for status and error.
- Check connectivity between both primary and standby server- try
connecting to primary as per credentials in primary_conninfo ( psql -h
172.31.37.228
-p 5333 -U postgres -d postgres)
- Check walreceiver process running on standby and the walsender process
running on master.
- Checkpoint and switch wal on master
- Check logs and standby status
Share how above goes and also log files from both master and standby.
Regards
Rajni
On Wed, Jan 20, 2021 at 9:04 AM David G. Johnston <
david.g.johnston@gmail.com> wrote:
> On Tue, Jan 19, 2021 at 2:56 PM Rajni Baliyan <saan654@gmail.com> wrote:
>
>> As David highlighted,
>> >> I took the error from the cp command to mean that this was in fact a
>> warm standby regardless of any claims by the poster. I’ll readily admit I
>> am not that familiar with this stuff though.
>> Seems your archive command is not working properly. Set it first and then
>> try again.
>>
>
> Don't think so - as Laurenz noted the last line reads:
>
> "2021-01-18 07:31:01.043 UTC [21079]LOG: started streaming WAL from
> primary at 0/35000000 on timeline 3"
>
> Which indicates that streaming is supposedly working even though the
> expected data hasn't made it from the primary to the standby.
>
> David J.
>
>
>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: hot standby in Postgresql 12
@ 2021-01-20 03:10 Ankush Chawla <ankushchawla03@gmail.com>
parent: Rajni Baliyan <saan654@gmail.com>
0 siblings, 0 replies; 19+ messages in thread
From: Ankush Chawla @ 2021-01-20 03:10 UTC (permalink / raw)
To: Rajni Baliyan <saan654@gmail.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-admin
Streaming is working as expected
Because when I promote the slave all the data has been replicated
Even I was also monitoring the pg_stat_replication and it is streaming the
last wal file
On Wed, 20 Jan 2021 at 4:46 AM, Rajni Baliyan <saan654@gmail.com> wrote:
>
> Yes David - the problem here is expected data hasn't made it from the
> primary to the standby even after streaming is started , and there could be
> many reasons for it.
>
> I will be interested to know -
> - How user created standby (pg_basebackup etc.)
> - Was there any manual cleanup done on the standby pg_xlog directory?
> - Is the walreceiver process running on standby? --> ps -ef | grep
> postgres
> - Is the walsender process running on master ? --> ps -ef | grep
> postgres
> - postgresql.auto.conf file
> - Database system identifier for both master and standby ( use utility
> - pg_controldata -D <master/standby data dir> liek - pg_controldata
> -D /user/app/master)
>
>
> I would do below to narrow down the issue-
> - Stop postgres on standby or kill the postmaster process.
> - Increase logging on standby
> - Restart standby.
> - Check standby log for status and error.
> - Check connectivity between both primary and standby server- try
> connecting to primary as per credentials in primary_conninfo ( psql -h 172.31.37.228
> -p 5333 -U postgres -d postgres)
> - Check walreceiver process running on standby and the walsender process
> running on master.
> - Checkpoint and switch wal on master
> - Check logs and standby status
>
> Share how above goes and also log files from both master and standby.
>
> Regards
> Rajni
>
>
>
> On Wed, Jan 20, 2021 at 9:04 AM David G. Johnston <
> david.g.johnston@gmail.com> wrote:
>
>> On Tue, Jan 19, 2021 at 2:56 PM Rajni Baliyan <saan654@gmail.com> wrote:
>>
>>> As David highlighted,
>>> >> I took the error from the cp command to mean that this was in fact a
>>> warm standby regardless of any claims by the poster. I’ll readily admit I
>>> am not that familiar with this stuff though.
>>> Seems your archive command is not working properly. Set it first and
>>> then try again.
>>>
>>
>> Don't think so - as Laurenz noted the last line reads:
>>
>> "2021-01-18 07:31:01.043 UTC [21079]LOG: started streaming WAL from
>> primary at 0/35000000 on timeline 3"
>>
>> Which indicates that streaming is supposedly working even though the
>> expected data hasn't made it from the primary to the standby.
>>
>> David J.
>>
>>
>> --
Best Regards,
Ankush Chawla
^ permalink raw reply [nested|flat] 19+ messages in thread
end of thread, other threads:[~2021-01-20 03:10 UTC | newest]
Thread overview: 19+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-01-19 03:20 hot standby in Postgresql 12 Ankush Chawla <ankushchawla03@gmail.com>
2021-01-19 03:31 ` David G. Johnston <david.g.johnston@gmail.com>
2021-01-19 03:33 ` Ankush Chawla <ankushchawla03@gmail.com>
2021-01-19 03:48 ` David G. Johnston <david.g.johnston@gmail.com>
2021-01-19 04:14 ` Ankush Chawla <ankushchawla03@gmail.com>
2021-01-19 04:24 ` David G. Johnston <david.g.johnston@gmail.com>
2021-01-19 05:48 ` Paul Förster <paul.foerster@gmail.com>
2021-01-19 06:17 ` Ankush Chawla <ankushchawla03@gmail.com>
2021-01-19 06:26 ` David G. Johnston <david.g.johnston@gmail.com>
2021-01-19 09:02 ` Dominik Lehnen <dominik.lehnen@gameduell.de>
2021-01-19 09:32 ` Laurenz Albe <laurenz.albe@cybertec.at>
2021-01-19 15:11 ` David G. Johnston <david.g.johnston@gmail.com>
2021-01-19 21:55 ` Rajni Baliyan <saan654@gmail.com>
2021-01-19 22:03 ` David G. Johnston <david.g.johnston@gmail.com>
2021-01-19 23:15 ` Rajni Baliyan <saan654@gmail.com>
2021-01-20 03:10 ` Ankush Chawla <ankushchawla03@gmail.com>
2021-01-19 09:19 ` Ganesh Korde <ganeshakorde@gmail.com>
2021-01-19 09:20 ` Ankush Chawla <ankushchawla03@gmail.com>
2021-01-19 09:32 ` Ankush Chawla <ankushchawla03@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox