Re: hot standby in Postgresql 12

[Ankush Chawla <ankushchawla03@xxxxxxxxx>]

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@xxxxxxxxx> 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@xxxxxxxxx> wrote:

On Tue, Jan 19, 2021 at 2:56 PM Rajni Baliyan <saan654@xxxxxxxxx> 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