Re: Is my standby fully connected?

In 9.1+ you can monitor the state of your slave easily with
the standby_state field of pg_stat_replication:
SELECT standby_pid,
standby_usesysid,
standby_usename,
standby_client_addr,
standby_client_port,
standby_state
FROM pg_stat_replication;

If the standby is not connected at all the query will not return any result
for that standby.
If the standby is connected possible values of standby_state are streaming
(ideal state), catchup (standby has fallen behind), startup, backup.

*Will J. Dunn*
*willjdunn.com <http://willjdunn.com>*

On Mon, Jun 1, 2015 at 2:13 PM, Guillaume Lelarge <guillaume(at)lelarge(dot)info>
wrote:

> Please, keep the list posted.
> 2015-06-01 20:09 GMT+02:00 Ivann Ruiz <ivann(dot)jprog(at)gmail(dot)com>:
>
>> I get 1 row, a walreceiver and my slave IP,
>>
>>
> Then all is good.
>
>
>> i was expecting something like:
>>
>> pg_current_xlog_location
>> --------------------------
>> 0/2000000
>> (1 row)
>>
>>
>> When I ran -> psql -c "SELECT pg_current_xlog_location()"
>> -h192.168.0.10 (primary host)
>>
>
> Well, you can't execute it on a slave. You can use
> pg_last_xlog_receive_location() and pg_last_xlog_replay_location() on a
> slave.
>
>
>>
>> On Mon, Jun 1, 2015 at 2:04 PM, Guillaume Lelarge <guillaume(at)lelarge(dot)info
>> > wrote:
>>
>>> Hi,
>>>
>>> 2015-06-01 19:57 GMT+02:00 Ivann Ruiz <ivann(dot)jprog(at)gmail(dot)com>:
>>>
>>>> I just setup my databases and got this LOG:
>>>>
>>>> LOG: database system was shut down in recovery at 2015-06-01 13:31:21
>>>> EDT
>>>> LOG: entering standby mode
>>>> LOG: redo starts at 0/67000278
>>>> LOG: consistent recovery state reached at 0/67000350
>>>> LOG: record with zero length at 0/67000350
>>>> LOG: database system is ready to accept read only connections
>>>> LOG: started streaming WAL from primary at 0/67000000 on timeline 1
>>>>
>>>>
>>> Sounds good to me.
>>>
>>>
>>>> And it always stays like this, i never get a message like:
>>>> LOG: streaming replication successfully connected to primary
>>>>
>>>> Additionally, i cannot run queries such as:
>>>> psql -c "SELECT pg_current_xlog_location()" -h192.168.0.10
>>>>
>>>> ...to calculate replication lag, I get the following message:
>>>> ERROR: recovery is in progress
>>>> HINT: WAL control functions cannot be executed during recovery
>>>>
>>>> Can someone please tell me what is going on?
>>>>
>>>
>>> This is just what it's supposed to be. And it tells you exactly that.
>>> You cannot use pg_current_xlog_location() on a slave.
>>>
>>> Check pg_stat_replication view on the master.
>>>
>>> --
>>> Guillaume.
>>> http://blog.guillaume.lelarge.info
>>> http://www.dalibo.com
>>>
>>
>>
>
>
> --
> Guillaume.
> http://blog.guillaume.lelarge.info
> http://www.dalibo.com
>