Thread: [GENERAL] How to check streaming replication status

Hello,

I have a question about master - slave replication.
My version on both servers is : PostgreSQL 9.6.4 on
x86_64-slackware-linux-gnu, compiled by x86_64-slackware-linux-gcc (GCC)
7.2.0, 64-bit

Here is the story:

Today I create a table space and move all indexes on nvmi drives. So far
so good.

Master server is configured as replica and start sending replication wal
files to slave server,
after a hour I get error message on slave server:

LOG:  restored log file "000000010000008B000000DC" from archive
LOG:  restored log file "000000010000008B000000DD" from archive
cp: can get attribute '/archive/000000010000008B000000DE': No such file
or directory
LOG:  started streaming WAL from primary at 8B/DD000000 on timeline 1



Question coming in my mind: Did my slave is up to date ?

I read https://wiki.postgresql.org/wiki/Streaming_Replication and know I
can check status with:

$ psql -c "select pg_last_xlog_receive_location()" -h192.168.0.20
(standby host)

and after I did it, got:

STATEMENT:  SELECT pg_current_xlog_location()
ERROR:  recovery is in progress
HINT:  WAL control functions cannot be executed during recovery.

My question is: How I can check the replication status when the slave
does not accept connections ?
I know if there have some different in configurations slave does not
accept connections, but in my case
slave have different hardware so is normal to have differences in config
files.


Regards,
Hristo S

On 31-08-2017 11:24, Glyn Astill wrote:
>> From: Condor <condor@stz-bg.com>
>> To: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
>> Sent: Thursday, 31 August 2017, 08:36:19 GMT+1
>>
>> after a hour I get error message on slave server:
>>
>> LOG:  restored log file "000000010000008B000000DC" from archive
>> LOG:  restored log file "000000010000008B000000DD" from archive
>> cp: can get attribute '/archive/000000010000008B000000DE': No such
> file or directory
>> LOG:  started streaming WAL from primary at 8B/DD000000 on timeline
> 1
>
> So it read all the log from the archive then started streaming, if
> there are no futrher messages you're ok.
>
> ...
>
>> and after I did it, got:
>>
>> STATEMENT:  SELECT pg_current_xlog_location()
>> ERROR:  recovery is in progress
>> HINT:  WAL control functions cannot be executed during recovery.
>>
>> My question is: How I can check the replication status when the
> slave
>> does not accept connections ?
>
> That's right for a server in recovery you need to call
> pg_last_xlog_receive_location() or pg_last_xlog_replay_location() to
> get the current xlog position.


Yes,
but my question is how to call them when Im unable to connect with slave
even when
replication is over. How I can ask the slave server: Are you in recovery
mode ?
What is the last wal file send from master, which file you processing
now ?
How far behind you ?

As I ask:  My question is: How I can check the replication status when
the slave does not accept connections ?

On 31-08-2017 12:14, Glyn Astill wrote:
>> From: Condor <condor@stz-bg.com>
>> To: Glyn Astill <glynastill@yahoo.co.uk>
>> Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>;
> "pgsql-general-owner@postgresql.org"
> <pgsql-general-owner@postgresql.org>
>> Sent: Thursday, 31 August 2017, 09:42:17 GMT+1
>> Subject: Re: [GENERAL] How to check streaming replication status
>
>>>> My question is: How I can check the replication status when the
>>> slave
>>>> does not accept connections ?
>>>
>>> That's right for a server in recovery you need to call
>>> pg_last_xlog_receive_location() or pg_last_xlog_replay_location()
> to
>>> get the current xlog position.
>>
>>
>> Yes,
>> but my question is how to call them when Im unable to connect with
> slave
>> even when
>> replication is over. How I can ask the slave server: Are you in
> recovery
>> mode ?
>>
>
> Define "unable to connect", in your previous example you appeared to
> be connected to the slave and attempting to call
> pg_current_xlog_location() ...
>
> If you want to know if postgres is in recovery call
> pg_is_in_recovery()
>
> https://www.postgresql.org/docs/current/static/functions-admin.html
>
>>
>> What is the last wal file send from master, which file you processing
>> now ?
>> How far behind you ?
>>
>> As I ask:  My question is: How I can check the replication status
> when
>> the slave does not accept connections ?
>
> Again I think you need to define "the slave does not accept
> connections".
>
> If you've not configured the slave to be a hot standby, then try
> setting hot_standby=on in postgresql.conf on the slave.  If you don't
> want to do that you can run the pg_controldata executable on the slave
> to see the cluster state.
>
> You should also be able to see streaming replication slave lag on the
> master by looking at pg_stat_replication and using
> pg_xlog_location_diff()
>
> hth


Yes,
it's seems my mistake. I did not change the first part of ip address and
trying to query test slave server which is connected to different master
server.
It's will be wondering if it work ...

Sorry