From: Scott Whitney <scott@journyx.com>
To: pgsql-admin@postgresql.org
Subject: Some replication-related notes and questions
Date: Tue, 30 Apr 2013 14:42:58 -0500 (CDT)
Message-ID: <6985793.109694.1367350978060.JavaMail.root@mail.int.journyx.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
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.
Thanks,
Scott Whitney
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: scott@journyx.com
Subject: Re: Some replication-related notes and questions
In-Reply-To: <6985793.109694.1367350978060.JavaMail.root@mail.int.journyx.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox