pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedReplication lag from transaction logs
9+ messages / 4 participants
[nested] [flat]
* Replication lag from transaction logs
@ 2018-06-18 12:43 Debraj Manna <subharaj.manna@gmail.com>
2018-06-18 12:56 ` Re: Replication lag from transaction logs Fabio Pardi <f.pardi@portavita.eu>
0 siblings, 1 reply; 9+ messages in thread
From: Debraj Manna @ 2018-06-18 12:43 UTC (permalink / raw)
To: pgsql-admin
Is it possible to figure out the replication lag from transaction log files
in the slave and master without querying postgres on the slave? Basically
figuring out the replication lag in a slave when the postgres is down in
that slave.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Replication lag from transaction logs
2018-06-18 12:43 Replication lag from transaction logs Debraj Manna <subharaj.manna@gmail.com>
@ 2018-06-18 12:56 ` Fabio Pardi <f.pardi@portavita.eu>
2018-06-18 13:22 ` Re: Replication lag from transaction logs Keith <keith@keithf4.com>
0 siblings, 1 reply; 9+ messages in thread
From: Fabio Pardi @ 2018-06-18 12:56 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
That I know, if standby is down, you cannot know exactly the last applied transaction log file.
But you could get agross estimate if you check how many WAL files is the standby behind, just listing the WAL files on the master and on the standby.
Given that you can login on the standby machine.
regards,
fabio pardi
On 18/06/18 14:43, Debraj Manna wrote:
> Is it possible to figure out the replication lag from transaction log files in the slave and master without querying postgres on the slave? Basically figuring out the replication lag in a slave when the postgres is down in that slave.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Replication lag from transaction logs
2018-06-18 12:43 Replication lag from transaction logs Debraj Manna <subharaj.manna@gmail.com>
2018-06-18 12:56 ` Re: Replication lag from transaction logs Fabio Pardi <f.pardi@portavita.eu>
@ 2018-06-18 13:22 ` Keith <keith@keithf4.com>
2018-06-18 13:59 ` Re: Replication lag from transaction logs Debraj Manna <subharaj.manna@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Keith @ 2018-06-18 13:22 UTC (permalink / raw)
To: Fabio Pardi <f.pardi@portavita.eu>; +Cc: pgsql-admin@lists.postgresql.org
You can use pg_controldata on any data directory to get its rough position
in WAL replay, even when the database isn't running
https://www.postgresql.org/docs/current/static/app-pgcontroldata.html
On Mon, Jun 18, 2018 at 8:56 AM, Fabio Pardi <f.pardi@portavita.eu> wrote:
> That I know, if standby is down, you cannot know exactly the last applied
> transaction log file.
>
> But you could get a gross estimate if you check how many WAL files is the
> standby behind, just listing the WAL files on the master and on the
> standby.
>
> Given that you can login on the standby machine.
>
> regards,
>
> fabio pardi
>
>
> On 18/06/18 14:43, Debraj Manna wrote:
>
> Is it possible to figure out the replication lag from transaction log
> files in the slave and master without querying postgres on the slave?
> Basically figuring out the replication lag in a slave when the postgres is
> down in that slave.
>
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Replication lag from transaction logs
2018-06-18 12:43 Replication lag from transaction logs Debraj Manna <subharaj.manna@gmail.com>
2018-06-18 12:56 ` Re: Replication lag from transaction logs Fabio Pardi <f.pardi@portavita.eu>
2018-06-18 13:22 ` Re: Replication lag from transaction logs Keith <keith@keithf4.com>
@ 2018-06-18 13:59 ` Debraj Manna <subharaj.manna@gmail.com>
2018-06-18 14:09 ` Re: Replication lag from transaction logs Keith <keith@keithf4.com>
0 siblings, 1 reply; 9+ messages in thread
From: Debraj Manna @ 2018-06-18 13:59 UTC (permalink / raw)
To: Keith <keith@keithf4.com>; +Cc: Fabio Pardi <f.pardi@portavita.eu>; pgsql-admin@lists.postgresql.org
Thanks Keith I am looking something like the solution you suggested.
Once I run pg_controldata on the slave data directory it will be give me
the latest checkpoint like below. Can you let me know a bit more detail on
how can I get the approximate lag in the slave in bytes from this?
Latest checkpoint location: 2F9/B38DE758
On Mon 18 Jun, 2018, 6:53 PM Keith, <keith@keithf4.com> wrote:
> You can use pg_controldata on any data directory to get its rough position
> in WAL replay, even when the database isn't running
>
> https://www.postgresql.org/docs/current/static/app-pgcontroldata.html
>
> On Mon, Jun 18, 2018 at 8:56 AM, Fabio Pardi <f.pardi@portavita.eu> wrote:
>
>> That I know, if standby is down, you cannot know exactly the last
>> applied transaction log file.
>>
>> But you could get a gross estimate if you check how many WAL files is
>> the standby behind, just listing the WAL files on the master and on the
>> standby.
>>
>> Given that you can login on the standby machine.
>>
>> regards,
>>
>> fabio pardi
>>
>>
>> On 18/06/18 14:43, Debraj Manna wrote:
>>
>> Is it possible to figure out the replication lag from transaction log
>> files in the slave and master without querying postgres on the slave?
>> Basically figuring out the replication lag in a slave when the postgres is
>> down in that slave.
>>
>>
>>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Replication lag from transaction logs
2018-06-18 12:43 Replication lag from transaction logs Debraj Manna <subharaj.manna@gmail.com>
2018-06-18 12:56 ` Re: Replication lag from transaction logs Fabio Pardi <f.pardi@portavita.eu>
2018-06-18 13:22 ` Re: Replication lag from transaction logs Keith <keith@keithf4.com>
2018-06-18 13:59 ` Re: Replication lag from transaction logs Debraj Manna <subharaj.manna@gmail.com>
@ 2018-06-18 14:09 ` Keith <keith@keithf4.com>
2018-06-18 15:56 ` Re: Replication lag from transaction logs Debraj Manna <subharaj.manna@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Keith @ 2018-06-18 14:09 UTC (permalink / raw)
To: Debraj Manna <subharaj.manna@gmail.com>; +Cc: Fabio Pardi <f.pardi@portavita.eu>; pgsql-admin@lists.postgresql.org
The portion before the slash is the actual WAL file itself that it got to
start replaying, usually the last few characters of the WAL file name. The
portion after the slash is the position in the WAL file itself. Default
size of each WAL is 16MB, so if that first portion doesn't match the
primary, you can count back that many WAL files x16MB. If it's on the same
WAL file, I'm not really sure how to get a more precise byte lag than that.
Keith
On Mon, Jun 18, 2018 at 9:59 AM, Debraj Manna <subharaj.manna@gmail.com>
wrote:
> Thanks Keith I am looking something like the solution you suggested.
>
> Once I run pg_controldata on the slave data directory it will be give me
> the latest checkpoint like below. Can you let me know a bit more detail on
> how can I get the approximate lag in the slave in bytes from this?
>
> Latest checkpoint location: 2F9/B38DE758
>
>
>
>
> On Mon 18 Jun, 2018, 6:53 PM Keith, <keith@keithf4.com> wrote:
>
>> You can use pg_controldata on any data directory to get its rough
>> position in WAL replay, even when the database isn't running
>>
>> https://www.postgresql.org/docs/current/static/app-pgcontroldata.html
>>
>> On Mon, Jun 18, 2018 at 8:56 AM, Fabio Pardi <f.pardi@portavita.eu>
>> wrote:
>>
>>> That I know, if standby is down, you cannot know exactly the last
>>> applied transaction log file.
>>>
>>> But you could get a gross estimate if you check how many WAL files is
>>> the standby behind, just listing the WAL files on the master and on the
>>> standby.
>>>
>>> Given that you can login on the standby machine.
>>>
>>> regards,
>>>
>>> fabio pardi
>>>
>>>
>>> On 18/06/18 14:43, Debraj Manna wrote:
>>>
>>> Is it possible to figure out the replication lag from transaction log
>>> files in the slave and master without querying postgres on the slave?
>>> Basically figuring out the replication lag in a slave when the postgres is
>>> down in that slave.
>>>
>>>
>>>
>>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Replication lag from transaction logs
2018-06-18 12:43 Replication lag from transaction logs Debraj Manna <subharaj.manna@gmail.com>
2018-06-18 12:56 ` Re: Replication lag from transaction logs Fabio Pardi <f.pardi@portavita.eu>
2018-06-18 13:22 ` Re: Replication lag from transaction logs Keith <keith@keithf4.com>
2018-06-18 13:59 ` Re: Replication lag from transaction logs Debraj Manna <subharaj.manna@gmail.com>
2018-06-18 14:09 ` Re: Replication lag from transaction logs Keith <keith@keithf4.com>
@ 2018-06-18 15:56 ` Debraj Manna <subharaj.manna@gmail.com>
2018-06-18 17:03 ` Re: Replication lag from transaction logs Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 1 reply; 9+ messages in thread
From: Debraj Manna @ 2018-06-18 15:56 UTC (permalink / raw)
To: Keith <keith@keithf4.com>; +Cc: Fabio Pardi <f.pardi@portavita.eu>; pgsql-admin@lists.postgresql.org
Thanks Keith this is useful.
One more query if I need to know that if a have fallen too far behind and
the WAL is not available. I guess I can do this. Let me know if my
understanding is correct.
- Run pg_controldata <DATA_DIR> on the slave node which has been down
for long.
- It will output the details about the WAL along with the WAL file name
from where it will start the replication. (Field to look for in the output–
'Latest checkpoint's REDO WAL file')
- Then check if the file mentioned in ` 'Latest checkpoint's REDO WAL
file' is present in master `pg_wal` directory. If not then slave have
fallen too far behind and will not be able to recover from WAL.
On Mon, Jun 18, 2018 at 7:39 PM, Keith <keith@keithf4.com> wrote:
> The portion before the slash is the actual WAL file itself that it got to
> start replaying, usually the last few characters of the WAL file name. The
> portion after the slash is the position in the WAL file itself. Default
> size of each WAL is 16MB, so if that first portion doesn't match the
> primary, you can count back that many WAL files x16MB. If it's on the same
> WAL file, I'm not really sure how to get a more precise byte lag than that.
>
> Keith
>
>
> On Mon, Jun 18, 2018 at 9:59 AM, Debraj Manna <subharaj.manna@gmail.com>
> wrote:
>
>> Thanks Keith I am looking something like the solution you suggested.
>>
>> Once I run pg_controldata on the slave data directory it will be give me
>> the latest checkpoint like below. Can you let me know a bit more detail on
>> how can I get the approximate lag in the slave in bytes from this?
>>
>> Latest checkpoint location: 2F9/B38DE758
>>
>>
>>
>>
>> On Mon 18 Jun, 2018, 6:53 PM Keith, <keith@keithf4.com> wrote:
>>
>>> You can use pg_controldata on any data directory to get its rough
>>> position in WAL replay, even when the database isn't running
>>>
>>> https://www.postgresql.org/docs/current/static/app-pgcontroldata.html
>>>
>>> On Mon, Jun 18, 2018 at 8:56 AM, Fabio Pardi <f.pardi@portavita.eu>
>>> wrote:
>>>
>>>> That I know, if standby is down, you cannot know exactly the last
>>>> applied transaction log file.
>>>>
>>>> But you could get a gross estimate if you check how many WAL files is
>>>> the standby behind, just listing the WAL files on the master and on
>>>> the standby.
>>>>
>>>> Given that you can login on the standby machine.
>>>>
>>>> regards,
>>>>
>>>> fabio pardi
>>>>
>>>>
>>>> On 18/06/18 14:43, Debraj Manna wrote:
>>>>
>>>> Is it possible to figure out the replication lag from transaction log
>>>> files in the slave and master without querying postgres on the slave?
>>>> Basically figuring out the replication lag in a slave when the postgres is
>>>> down in that slave.
>>>>
>>>>
>>>>
>>>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Replication lag from transaction logs
2018-06-18 12:43 Replication lag from transaction logs Debraj Manna <subharaj.manna@gmail.com>
2018-06-18 12:56 ` Re: Replication lag from transaction logs Fabio Pardi <f.pardi@portavita.eu>
2018-06-18 13:22 ` Re: Replication lag from transaction logs Keith <keith@keithf4.com>
2018-06-18 13:59 ` Re: Replication lag from transaction logs Debraj Manna <subharaj.manna@gmail.com>
2018-06-18 14:09 ` Re: Replication lag from transaction logs Keith <keith@keithf4.com>
2018-06-18 15:56 ` Re: Replication lag from transaction logs Debraj Manna <subharaj.manna@gmail.com>
@ 2018-06-18 17:03 ` Scott Ribe <scott_ribe@elevated-dev.com>
2018-06-18 17:57 ` Re: Replication lag from transaction logs Keith <keith@keithf4.com>
0 siblings, 1 reply; 9+ messages in thread
From: Scott Ribe @ 2018-06-18 17:03 UTC (permalink / raw)
To: Debraj Manna <subharaj.manna@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
> On Jun 18, 2018, at 9:56 AM, Debraj Manna <subharaj.manna@gmail.com> wrote:
>
> Thanks Keith this is useful.
>
> One more query if I need to know that if a have fallen too far behind and the WAL is not available. I guess I can do this. Let me know if my understanding is correct.
> • Run pg_controldata <DATA_DIR> on the slave node which has been down for long.
> • It will output the details about the WAL along with the WAL file name from where it will start the replication. (Field to look for in the output– 'Latest checkpoint's REDO WAL file')
> • Then check if the file mentioned in ` 'Latest checkpoint's REDO WAL file' is present in master `pg_wal` directory. If not then slave have fallen too far behind and will not be able to recover from WAL.
You could also try bringing the slave back up, and monitoring the log for the error about needed WAL file not being available--this avoids the race condition between checking that all WAL is available and restarting the slave.
--
Scott Ribe
scott_ribe@elevated-dev.com
https://www.linkedin.com/in/scottribe/
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Replication lag from transaction logs
2018-06-18 12:43 Replication lag from transaction logs Debraj Manna <subharaj.manna@gmail.com>
2018-06-18 12:56 ` Re: Replication lag from transaction logs Fabio Pardi <f.pardi@portavita.eu>
2018-06-18 13:22 ` Re: Replication lag from transaction logs Keith <keith@keithf4.com>
2018-06-18 13:59 ` Re: Replication lag from transaction logs Debraj Manna <subharaj.manna@gmail.com>
2018-06-18 14:09 ` Re: Replication lag from transaction logs Keith <keith@keithf4.com>
2018-06-18 15:56 ` Re: Replication lag from transaction logs Debraj Manna <subharaj.manna@gmail.com>
2018-06-18 17:03 ` Re: Replication lag from transaction logs Scott Ribe <scott_ribe@elevated-dev.com>
@ 2018-06-18 17:57 ` Keith <keith@keithf4.com>
2018-06-18 18:27 ` Re: Replication lag from transaction logs Keith <keith@keithf4.com>
0 siblings, 1 reply; 9+ messages in thread
From: Keith @ 2018-06-18 17:57 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Debraj Manna <subharaj.manna@gmail.com>; pgsql-admin@lists.postgresql.org
Yes, in general using pg_basebackup is not ideal to monitor for replica
lag. I was just providing a means to do so from an offline instance. For
normal monitoring, if you want to monitor byte lag, you can query that from
the primary system. Or you can also check for lag from the replica as well.
https://www.keithf4.com/monitoring_streaming_slave_lag/
Keith
On Mon, Jun 18, 2018 at 1:03 PM, Scott Ribe <scott_ribe@elevated-dev.com>
wrote:
>
> > On Jun 18, 2018, at 9:56 AM, Debraj Manna <subharaj.manna@gmail.com>
> wrote:
> >
> > Thanks Keith this is useful.
> >
> > One more query if I need to know that if a have fallen too far behind
> and the WAL is not available. I guess I can do this. Let me know if my
> understanding is correct.
> > • Run pg_controldata <DATA_DIR> on the slave node which has been
> down for long.
> > • It will output the details about the WAL along with the WAL file
> name from where it will start the replication. (Field to look for in the
> output– 'Latest checkpoint's REDO WAL file')
> > • Then check if the file mentioned in ` 'Latest checkpoint's REDO
> WAL file' is present in master `pg_wal` directory. If not then slave have
> fallen too far behind and will not be able to recover from WAL.
>
> You could also try bringing the slave back up, and monitoring the log for
> the error about needed WAL file not being available--this avoids the race
> condition between checking that all WAL is available and restarting the
> slave.
>
> --
> Scott Ribe
> scott_ribe@elevated-dev.com
> https://www.linkedin.com/in/scottribe/
>
>
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Replication lag from transaction logs
2018-06-18 12:43 Replication lag from transaction logs Debraj Manna <subharaj.manna@gmail.com>
2018-06-18 12:56 ` Re: Replication lag from transaction logs Fabio Pardi <f.pardi@portavita.eu>
2018-06-18 13:22 ` Re: Replication lag from transaction logs Keith <keith@keithf4.com>
2018-06-18 13:59 ` Re: Replication lag from transaction logs Debraj Manna <subharaj.manna@gmail.com>
2018-06-18 14:09 ` Re: Replication lag from transaction logs Keith <keith@keithf4.com>
2018-06-18 15:56 ` Re: Replication lag from transaction logs Debraj Manna <subharaj.manna@gmail.com>
2018-06-18 17:03 ` Re: Replication lag from transaction logs Scott Ribe <scott_ribe@elevated-dev.com>
2018-06-18 17:57 ` Re: Replication lag from transaction logs Keith <keith@keithf4.com>
@ 2018-06-18 18:27 ` Keith <keith@keithf4.com>
0 siblings, 0 replies; 9+ messages in thread
From: Keith @ 2018-06-18 18:27 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Debraj Manna <subharaj.manna@gmail.com>; pgsql-admin@lists.postgresql.org
Sorry, I mean pg_controldata, not pg_basebackup.
On Mon, Jun 18, 2018 at 1:57 PM, Keith <keith@keithf4.com> wrote:
> Yes, in general using pg_basebackup is not ideal to monitor for replica
> lag. I was just providing a means to do so from an offline instance. For
> normal monitoring, if you want to monitor byte lag, you can query that from
> the primary system. Or you can also check for lag from the replica as well.
>
> https://www.keithf4.com/monitoring_streaming_slave_lag/
>
> Keith
>
> On Mon, Jun 18, 2018 at 1:03 PM, Scott Ribe <scott_ribe@elevated-dev.com>
> wrote:
>
>>
>> > On Jun 18, 2018, at 9:56 AM, Debraj Manna <subharaj.manna@gmail.com>
>> wrote:
>> >
>> > Thanks Keith this is useful.
>> >
>> > One more query if I need to know that if a have fallen too far behind
>> and the WAL is not available. I guess I can do this. Let me know if my
>> understanding is correct.
>> > • Run pg_controldata <DATA_DIR> on the slave node which has been
>> down for long.
>> > • It will output the details about the WAL along with the WAL
>> file name from where it will start the replication. (Field to look for in
>> the output– 'Latest checkpoint's REDO WAL file')
>> > • Then check if the file mentioned in ` 'Latest checkpoint's REDO
>> WAL file' is present in master `pg_wal` directory. If not then slave have
>> fallen too far behind and will not be able to recover from WAL.
>>
>> You could also try bringing the slave back up, and monitoring the log for
>> the error about needed WAL file not being available--this avoids the race
>> condition between checking that all WAL is available and restarting the
>> slave.
>>
>> --
>> Scott Ribe
>> scott_ribe@elevated-dev.com
>> https://www.linkedin.com/in/scottribe/
>>
>>
>>
>>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2018-06-18 18:27 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-06-18 12:43 Replication lag from transaction logs Debraj Manna <subharaj.manna@gmail.com>
2018-06-18 12:56 ` Fabio Pardi <f.pardi@portavita.eu>
2018-06-18 13:22 ` Keith <keith@keithf4.com>
2018-06-18 13:59 ` Debraj Manna <subharaj.manna@gmail.com>
2018-06-18 14:09 ` Keith <keith@keithf4.com>
2018-06-18 15:56 ` Debraj Manna <subharaj.manna@gmail.com>
2018-06-18 17:03 ` Scott Ribe <scott_ribe@elevated-dev.com>
2018-06-18 17:57 ` Keith <keith@keithf4.com>
2018-06-18 18:27 ` Keith <keith@keithf4.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