Обсуждение: [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
> 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.
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 ?
>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
>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
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