Issues with WAL Timelines on follower

From: Eloy Coto Pereiro <eloy(dot)coto(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Issues with WAL Timelines on follower
Date: 2016-11-01 17:25:56
Message-ID: CAFZDoUBBtuPXmkQ-MWB1Ejd288zn4ZGc63a7H=0nz_w-Gy621g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hey,

I'm happy postgresql user. Today I want to achieve a SQL query to add in
our health check system. Sometimes we change our leader server in a
hot-standby platform, so the timeline changes and the followers stop to
receive wal data under timeline problems.

This is the log message:

`timeline 4 forked off current database system timeline 3 before current
recovery point 0/84000098`

If I restart, this is going to work correctly. But I want to make an SQL
query (in the follower) to now if replication is working or not. Nowadays
I'm using this:

```
SELECT
CASE
WHEN pg_last_xlog_receive_location() =
pg_last_xlog_replay_location() THEN 0
ELSE EXTRACT (EPOCH FROM now() -
pg_last_xact_replay_timestamp())::INTEGER
END
AS replication_lag;
```

So, if the timeline is different, the result of the lag in replication is
always 0. So I can't monitor that the server is in another TL.

Any idea? I tried pg_xlogfile_name() but can't be used during the recovery.
Any idea?

PS: From the leader and pg_stat_replication can't get that it's not sync
too.

Regards

Browse pgsql-admin by date

  From Date Subject
Next Message Poul Kristensen 2016-11-01 19:31:26 Re: FSM corruption and standby servers
Previous Message Joshua D. Drake 2016-11-01 16:46:39 Re: Too many Idle Connections