Hot standby replication stalled

From: Mark Steben <mark(dot)steben(at)drivedominion(dot)com>
To: pgsql-admin <pgsql-admin(at)postgresql(dot)org>
Subject: Hot standby replication stalled
Date: 2018-10-08 14:00:35
Message-ID: CADyzmyzTSeRvuuk2dRn5GVfB1g7NL5heEQih2ivTK2iRPy2mwA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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 <http://www.autorevenue.com/> | Autobase
<http://www.autobase.net/>
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 <http://www.autorevenue.com/>

<http://autobasedigital.net/marketing/DD12_sig.jpg>

--
<https://www.drivedominion.com/transform-your-vision/>

Attachment Content-Type Size
logship copy script.txt text/plain 13.1 KB
legacy dr log Oct6.txt text/plain 20.0 KB

Browse pgsql-admin by date

  From Date Subject
Next Message Tom Lane 2018-10-08 14:49:49 Re: effective_cache_size
Previous Message Joel Benelli 2018-10-08 13:53:37 effective_cache_size