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/>
Attachment | Content-Type | Size |
---|---|---|
logship copy script.txt | text/plain | 13.1 KB |
legacy dr log Oct6.txt | text/plain | 20.0 KB |
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 |