pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedDetect when streaming replication stops streaming.
4+ messages / 2 participants
[nested] [flat]
* Detect when streaming replication stops streaming.
@ 2018-10-04 10:13 Anders Wegge Keller <wegge@wegge.dk>
0 siblings, 1 reply; 4+ messages in thread
From: Anders Wegge Keller @ 2018-10-04 10:13 UTC (permalink / raw)
To: pgsql-admin
Is there a reliable way to detect, on the standby, that continuous recovery
have stopped for any reason? I'm adding a postgres 9.2(1) database to an
industrial control system that have to run unattended. Thus, I need to plan
for some really exotic failure modes, like e.g. loss of network connectivity
in an indefinitely long period.
On the master system, I can check for the existence of the proper row in
pg_stat_replication. If that is missing, I can report a degraded status on
the whole system. However, on the standby side, I've been unable to find
something similar. Especially the failure mode where the standby return from
a long period of no network connectivity, where the master no longer have
the WAL segments needed to catch up. Snooping for FATAL in
pg_log/Postgres-xxx is error prone, and I'd rather not go the route of
polling the master system for a situation that should be detectable on the
standby instance.
--
//Wegge
1. Yes, I know it's no longer supported, but that's the way of redhat.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Detect when streaming replication stops streaming.
@ 2018-10-04 14:31 Keith Fiske <keith.fiske@crunchydata.com>
parent: Anders Wegge Keller <wegge@wegge.dk>
0 siblings, 1 reply; 4+ messages in thread
From: Keith Fiske @ 2018-10-04 14:31 UTC (permalink / raw)
To: wegge@wegge.dk; +Cc: pgsql-admin
On Thu, Oct 4, 2018 at 6:13 AM Anders Wegge Keller <wegge@wegge.dk> wrote:
> Is there a reliable way to detect, on the standby, that continuous recovery
> have stopped for any reason? I'm adding a postgres 9.2(1) database to an
> industrial control system that have to run unattended. Thus, I need to plan
> for some really exotic failure modes, like e.g. loss of network
> connectivity
> in an indefinitely long period.
>
> On the master system, I can check for the existence of the proper row in
> pg_stat_replication. If that is missing, I can report a degraded status on
> the whole system. However, on the standby side, I've been unable to find
> something similar. Especially the failure mode where the standby return
> from
> a long period of no network connectivity, where the master no longer have
> the WAL segments needed to catch up. Snooping for FATAL in
> pg_log/Postgres-xxx is error prone, and I'd rather not go the route of
> polling the master system for a situation that should be detectable on the
> standby instance.
>
>
> --
> //Wegge
>
> 1. Yes, I know it's no longer supported, but that's the way of redhat.
>
>
Checking replication status from just the replica itself isn't that
straight forward. This makes sense, though, since how is the replica
supposed to know that it's behind if it doesn't compare itself to something
else? PostgreSQL replication works by simply replaying the WAL stream from
another database. When that WAL stream stops, the replication stops. This
could be normal if the primary hasn't gotten any writes.
I wrote a brief blog post on monitoring replica lag:
https://www.keithf4.com/monitoring_streaming_slave_lag/
There is a query you can run on the replica, but as I said above and in the
blog post, this can cause false positives when there's no writes on the
primary. But if you're always supposed to be getting writes to the primary,
it's the easiest method to check, and could even help indicate when there's
a problem if your primary isn't getting writes as it should be. I highly
recommend also monitoring your primary for byte lag as well, so you can at
least know when the replica is throwing a false positive.
Another option would be, as you hinted, to watch the replica for expected
recent data. But again, if there's no writes, it can cause false positives.
So it's really not much different than just checking for the last WAL
replay.
If you're worried about the replica falling behind due to network outages
or something similar, I recommend looking into WAL shipping in addition to
streaming replication. If postgres doesn't get a successful run result from
the archive_command, it keeps the WAL file around until it does. You will
have to monitor for disk space usage issues on the primary if it backs up
too much. If this happens a lot, you may be better off shipping them to a
third-party server, preferably where you're storing your backups. You can
then have the replica fetch its WAL stream from there if streaming
replication fails. pgbackrest is one tool that can do this for you, as well
as help manage backups. https://pgbackrest.org/
If you're able to upgrade to 9.4, postgres did introduce replication slots
to help the primary actually be aware of the state of its replicas and keep
WAL files around automatically until all its replicas are caught up. While
9.2 is the default version of PG that comes with Redhat/CentOS, the
community does provide repositories with more recent rpm versions.
https://www.postgresql.org/download/linux/redhat/ I would highly recommend
looking into this since Redhat is not going to be updating their default
version for a very long time.
--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Detect when streaming replication stops streaming.
@ 2018-10-04 21:06 Anders Wegge Keller <wegge@wegge.dk>
parent: Keith Fiske <keith.fiske@crunchydata.com>
0 siblings, 1 reply; 4+ messages in thread
From: Anders Wegge Keller @ 2018-10-04 21:06 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On Thu, 4 Oct 2018 10:31:27 -0400
Keith Fiske <keith.fiske@crunchydata.com> wrote:
> Checking replication status from just the replica itself isn't that
> straight forward. This makes sense, though, since how is the replica
> supposed to know that it's behind if it doesn't compare itself to something
> else? PostgreSQL replication works by simply replaying the WAL stream from
> another database. When that WAL stream stops, the replication stops. This
> could be normal if the primary hasn't gotten any writes.
>
> I wrote a brief blog post on monitoring replica lag:
>
> https://www.keithf4.com/monitoring_streaming_slave_lag/
>
> There is a query you can run on the replica, but as I said above and in the
> blog post, this can cause false positives when there's no writes on the
> primary. But if you're always supposed to be getting writes to the primary,
> it's the easiest method to check, and could even help indicate when there's
> a problem if your primary isn't getting writes as it should be. I highly
> recommend also monitoring your primary for byte lag as well, so you can at
> least know when the replica is throwing a false positive.
>
> Another option would be, as you hinted, to watch the replica for expected
> recent data. But again, if there's no writes, it can cause false positives.
> So it's really not much different than just checking for the last WAL
> replay.
I can see that I'm really overthinking things. Adding a table on the master
with a timestamp that's updated on a regular basis is enough to cover the
cases where I want the system to detect a lack of replication. Hat tip to
Fernando Souza, who made this suggestion in a mail that ended in my inbox,
rather than this list. And thanks for the blog post.
> If you're worried about the replica falling behind due to network outages
> or something similar, I recommend looking into WAL shipping in addition to
> streaming replication.
If the systems are offline for long enough that the 10 WAL segments it's
configured to keep presently have rolled over, they're about three days out
of touch, and in a situation where it's a lighter network load to restart
from a fresh backup.
> If postgres doesn't get a successful run result from
> the archive_command, it keeps the WAL file around until it does. You will
> have to monitor for disk space usage issues on the primary if it backs up
> too much.
The system we're replacing here was originally deployed on IBM 330 servers
in 1999. With modern server hardware, we're so ridiculously overprovisioned,
that we could keep at least a year of WAL around, before reaching 50% disk
use. So resource usage is luckily not one of the things I have to worry
about :)
...
> If you're able to upgrade to 9.4, postgres did introduce replication slots
> to help the primary actually be aware of the state of its replicas and keep
> WAL files around automatically until all its replicas are caught up. While
> 9.2 is the default version of PG that comes with Redhat/CentOS, the
> community does provide repositories with more recent rpm versions.
> https://www.postgresql.org/download/linux/redhat/ I would highly recommend
> looking into this since Redhat is not going to be updating their default
> version for a very long time.
I'll take a look at 9.4 for when we make the next iteration of our "Base"
OS Media. With the present project, however, I'm stuck with what's in RHEL
7.5 And even going there was a sort of emergency deal because of hardware
incompatibilities.
Thank you very much for taking your time to answer my question. I can see I
still have much to learn, when thinking about databases in this context. And
my apologies if I sound hostile towards your suggestions above. I value
them, but I also want to explain why some of them are impossible to follow.
--
//Wegge
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Detect when streaming replication stops streaming.
@ 2018-10-04 22:37 Keith Fiske <keith.fiske@crunchydata.com>
parent: Anders Wegge Keller <wegge@wegge.dk>
0 siblings, 0 replies; 4+ messages in thread
From: Keith Fiske @ 2018-10-04 22:37 UTC (permalink / raw)
To: wegge@wegge.dk; +Cc: pgsql-admin@lists.postgresql.org
On Thu, Oct 4, 2018 at 5:14 PM Anders Wegge Keller <wegge@wegge.dk> wrote:
> On Thu, 4 Oct 2018 10:31:27 -0400
> Keith Fiske <keith.fiske@crunchydata.com> wrote:
>
> > Checking replication status from just the replica itself isn't that
> > straight forward. This makes sense, though, since how is the replica
> > supposed to know that it's behind if it doesn't compare itself to
> something
> > else? PostgreSQL replication works by simply replaying the WAL stream
> from
> > another database. When that WAL stream stops, the replication stops. This
> > could be normal if the primary hasn't gotten any writes.
> >
> > I wrote a brief blog post on monitoring replica lag:
> >
> > https://www.keithf4.com/monitoring_streaming_slave_lag/
> >
> > There is a query you can run on the replica, but as I said above and in
> the
> > blog post, this can cause false positives when there's no writes on the
> > primary. But if you're always supposed to be getting writes to the
> primary,
> > it's the easiest method to check, and could even help indicate when
> there's
> > a problem if your primary isn't getting writes as it should be. I highly
> > recommend also monitoring your primary for byte lag as well, so you can
> at
> > least know when the replica is throwing a false positive.
> >
> > Another option would be, as you hinted, to watch the replica for expected
> > recent data. But again, if there's no writes, it can cause false
> positives.
> > So it's really not much different than just checking for the last WAL
> > replay.
>
> I can see that I'm really overthinking things. Adding a table on the
> master
> with a timestamp that's updated on a regular basis is enough to cover the
> cases where I want the system to detect a lack of replication. Hat tip to
> Fernando Souza, who made this suggestion in a mail that ended in my inbox,
> rather than this list. And thanks for the blog post.
>
>
> > If you're worried about the replica falling behind due to network outages
> > or something similar, I recommend looking into WAL shipping in addition
> to
> > streaming replication.
>
> If the systems are offline for long enough that the 10 WAL segments it's
> configured to keep presently have rolled over, they're about three days out
> of touch, and in a situation where it's a lighter network load to restart
> from a fresh backup.
>
> > If postgres doesn't get a successful run result from
> > the archive_command, it keeps the WAL file around until it does. You will
> > have to monitor for disk space usage issues on the primary if it backs up
> > too much.
>
> The system we're replacing here was originally deployed on IBM 330 servers
> in 1999. With modern server hardware, we're so ridiculously
> overprovisioned,
> that we could keep at least a year of WAL around, before reaching 50% disk
> use. So resource usage is luckily not one of the things I have to worry
> about :)
>
> ...
>
> > If you're able to upgrade to 9.4, postgres did introduce replication
> slots
> > to help the primary actually be aware of the state of its replicas and
> keep
> > WAL files around automatically until all its replicas are caught up.
> While
> > 9.2 is the default version of PG that comes with Redhat/CentOS, the
> > community does provide repositories with more recent rpm versions.
> > https://www.postgresql.org/download/linux/redhat/ I would highly
> recommend
> > looking into this since Redhat is not going to be updating their default
> > version for a very long time.
>
> I'll take a look at 9.4 for when we make the next iteration of our "Base"
> OS Media. With the present project, however, I'm stuck with what's in RHEL
> 7.5 And even going there was a sort of emergency deal because of hardware
> incompatibilities.
>
>
I would suggest going straight to whatever the latest stable is whenever
you're able to use the community repositories. 9.3 is going out of support
as soon as version 11 is released later this month. So that would only
leave you with at most 1 year of support on 9.4. Currently stable is 10.5.
Note that the version numbering changed as of version 10. It used to be 3
digits (9.2.24 is the latest 9.2) and now it's down to 2 digits. They
basically dropped the middle number, so from now on, the point releases are
just patch/bugfix releases.
>
> Thank you very much for taking your time to answer my question. I can see I
> still have much to learn, when thinking about databases in this context.
> And
> my apologies if I sound hostile towards your suggestions above. I value
> them, but I also want to explain why some of them are impossible to follow.
>
> --
>
> //Wegge
>
>
>
No worries. We all have limitations we need to work around as DBAs!
--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2018-10-04 22:37 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-10-04 10:13 Detect when streaming replication stops streaming. Anders Wegge Keller <wegge@wegge.dk>
2018-10-04 14:31 ` Keith Fiske <keith.fiske@crunchydata.com>
2018-10-04 21:06 ` Anders Wegge Keller <wegge@wegge.dk>
2018-10-04 22:37 ` Keith Fiske <keith.fiske@crunchydata.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