Re: pg_last_xlog_receive_location()

In my understanding pg_last_xlog_receive_location() returns NULL if
applied to non standby server:

Get last transaction log location received and synced to disk by
streaming replication. While streaming replication is in progress this
will increase monotonically. But when streaming replication is
restarted this will back off to the replication starting position,
typically the beginning of the WAL file containing the current replay
location. If recovery has completed this will remain static at the
value of the last WAL record received and synced to disk during
recovery. If streaming replication is disabled, or if it has not yet
started, the function returns NULL.

However my primary server returns non NULL. Am I missing something?
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

On Wed, Mar 30, 2011 at 10:13 AM, Tatsuo Ishii <ishii(at)postgresql(dot)org> wrote:
> In my understanding pg_last_xlog_receive_location() returns NULL if
> applied to non standby server:
>
> Get last transaction log location received and synced to disk by
> streaming replication. While streaming replication is in progress this
> will increase monotonically. But when streaming replication is
> restarted this will back off to the replication starting position,
> typically the beginning of the WAL file containing the current replay
> location. If recovery has completed this will remain static at the
> value of the last WAL record received and synced to disk during
> recovery. If streaming replication is disabled, or if it has not yet
> started, the function returns NULL.
>
> However my primary server returns non NULL. Am I missing something?

In the primary server which was brought up from the standby,
pg_last_xlog_receive_location() returns non-NULL.

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

> In the primary server which was brought up from the standby,
> pg_last_xlog_receive_location() returns non-NULL.

Thanks.

BTW, is there any reliable way to know a standby is promoting to
primary?
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

On Wed, Mar 30, 2011 at 11:08 AM, Tatsuo Ishii <ishii(at)postgresql(dot)org> wrote:
> BTW, is there any reliable way to know a standby is promoting to
> primary?

You can know that from the server log. But there is no way to do that
via SQL, I think.

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

>> BTW, is there any reliable way to know a standby is promoting to
>> primary?
>
> You can know that from the server log. But there is no way to do that
> via SQL, I think.

Ok. It seems I need to patch PostgreSQL.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

On Tue, Mar 29, 2011 at 9:08 PM, Tatsuo Ishii <ishii(at)postgresql(dot)org> wrote:
>> In the primary server which was brought up from the standby,
>> pg_last_xlog_receive_location() returns non-NULL.
>
> Thanks.
>
> BTW, is there any reliable way to know a standby is promoting to
> primary?

if you know the server was a standby and pg_is_in_recovery() returns
false then it was promoted.
is that what you want to know? or i misunderstood you?

--
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte y capacitación de PostgreSQL

On Wed, Mar 30, 2011 at 11:45 AM, Tatsuo Ishii <ishii(at)postgresql(dot)org> wrote:
>>> BTW, is there any reliable way to know a standby is promoting to
>>> primary?
>>
>> You can know that from the server log. But there is no way to do that
>> via SQL, I think.
>
> Ok. It seems I need to patch PostgreSQL.

Why is that feature required? It's for pgpool-II?

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

>> Ok. It seems I need to patch PostgreSQL.
>
> Why is that feature required? It's for pgpool-II?

Yes. And probably any automated failover management tool will require
the functionality.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp