pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedSome replication-related notes and questions
2+ messages / 2 participants
[nested] [flat]
* Some replication-related notes and questions
@ 2013-04-30 19:42 Scott Whitney <scott@journyx.com>
0 siblings, 1 reply; 2+ messages in thread
From: Scott Whitney @ 2013-04-30 19:42 UTC (permalink / raw)
To: pgsql-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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Some replication-related notes and questions
@ 2013-04-30 21:36 Strahinja Kustudić <strahinjak@nordeus.com>
parent: Scott Whitney <scott@journyx.com>
0 siblings, 0 replies; 2+ messages in thread
From: Strahinja Kustudić @ 2013-04-30 21:36 UTC (permalink / raw)
To: Scott Whitney <swhitney@journyx.com>; +Cc: pgsql-admin
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.
>
> Thanks,
> Scott Whitney
>
>
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2013-04-30 21:36 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-04-30 19:42 Some replication-related notes and questions Scott Whitney <scott@journyx.com>
2013-04-30 21:36 ` Strahinja Kustudić <strahinjak@nordeus.com>
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