Thread: Hot standby replication stalled
Good morning,
I am running postgres 9.4.8. We have two instances of production postgresql logshipping records to disaster recovery,then to replication development databases. Things look good in the postgres logs and in the currency of transaction logs being log-shipped. But for some inexplicable reason the replication has been stalled in one instance. The time of stall according to a simple query of a table that gets heavily updated looks to be around 4:35 am Saturday Oct 6. :
select max(last_change_dtm) from queuenodes;
max
----------------------------
2018-10-06 04:35:58.799229
(1 row)
I have attached a snippet of the postgres logs documenting database activity around that time.
I have also attached the logshipping script that I use. You will see that that script services the logshipping to the dr site (i/p 10.248.156.47) then to our developer replication site (i/p 10.93.156.52). There have been no delays in log shipping: we are current (Monday, Oct 8 at 9:45 am) so I am at a loss as to what is going on. This has worked for about 8 months.
Another piece of information - we actively use pg_xlog_replay_pause() and pg_xlog_replay_resume() functions on our replication server but NOT on our dr server.
I did run pg_is_xlog_replay_paused() on both servers to verify that the replay has not been paused.
Any help appreciated.
-- Mark Steben
Database Administrator
@utoRevenue | Autobase
CRM division of Dominion Dealer Solutions
95D Ashley Ave.
West Springfield, MA 01089
t: 413.327-3045
f: 413.383-9567
Database Administrator
@utoRevenue | Autobase
CRM division of Dominion Dealer Solutions
95D Ashley Ave.
West Springfield, MA 01089
t: 413.327-3045
f: 413.383-9567
www.fb.com/DominionDealerSolutions
www.twitter.com/DominionDealer
www.drivedominion.com