I can answer your first question. The way I check the replication delay is by running this query on the replication server:
SELECT now() - pg_last_xact_replay_timestamp();
Of course you need to configure hot standby replication, which you should if you are not.
Regards,
Strahinja
On Tue, Apr 30, 2013 at 9:42 PM, Scott Whitney <scott@journyx.com> wrote:
We recently moved to PG 9.2.4 (from 8.4.4) to take advantage of replication, and I have to say it's pretty awesome.
I ran into some things that I was hoping someone could clarify.
a) There appears to be no way to tell how "far behind" my standby servers are. That is, I can find a checkpoint with pg_controldata. I can find the sending/receiving WAL processes via ps or replay/receive_location(). However there seems to be no correlation to real-world times or dates as in "how many seconds/minutes/hours behind are my standby servers."
b) This segues nicely into the archive_status/*.done files. The _only_ files in my archive_status directory on my standby servers are .done files. Will these clean up on their own? Can I stat the last one to know the maximum discrepancy between my master and slave? If they do NOT clean up on their own, is it safe to remove them? I couldn't find any information on what that directory specifically does or whether .done files are required.
c) It would appear that a FULL vacuum can hose replication (in the case of a long-standing hanging transaction). Has anyone else run into this? Specifically, my standby was up and running and replicating. Sunday morning (I do full vacuums on Saturday due to an old bug in an old database) my standby was trying to replay a WAL file that didn't exist because I had hanging transactions that began before my oldest WAL segment. Since the replication was up and running on Saturday, I was wondering whether full vacuum could have been to blame for the servers getting out ot sync.