system views for walsender activity

On Fri, Jun 18, 2010 at 04:33, Takahiro Itagaki
<itagaki.takahiro@oss.ntt.co.jp> wrote:

Hi,

We don't have any statistic views for walsenders in SR's master server
in 9.0, but such views would be useful to monitor and manage standby
servers from the master server. I have two ideas for the solution -
adding a new system view or recycling pg_stat_activity:

1. Add another system view for walsenders, ex. "pg_stat_replication".
  It would show pid, remote host, and sent location for each walsender.

2. Make pg_stat_activity to show walsenders. We could use current_query
  to display walsender-specific information, like:
   =# SELECT * FROM my_stat_activity ;
   -[ RECORD 1 ]----+---------------------------------
   datid            | 16384
   <snip>
   current_query    | SELECT * FROM my_stat_activity ;
   -[ RECORD 2 ]----+---------------------------------
   datid            | 0
   datname          |
   procpid          | 4300
   usesysid         | 10
   usename          | itagaki
   application_name |
   client_addr      | ::1
   client_port      | 37710
   backend_start    | 2010-06-16 16:47:35.646486+09
   xact_start       |
   query_start      |
   waiting          | f
   current_query    | walsender: sent=0/701AAA8

The attached patch is a WIP codes for the case 2, but it might be
better to design management policy via SQL in 9.1 before such detailed
implementation.  Comments welcome.

I like version 1 much better. It'll be a lot easier for a management
tool to get the data in proper columns and not have to parse it out of
an arbitrary string field.

The downside is that version 1 will require an initdb, and not version
2, right? In that light, #2 is probably the only one we can do for 9.0
(unless we stumble upon other initdb-forcing changes), so maybe we
should do that one as a temporary measure (and if so, note it both in
the documentation and code that it's a temporary thing).

Wouldn't we also need something similar for the receiving end?

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

On Fri, 2010-06-18 at 11:33 +0900, Takahiro Itagaki wrote:

1. Add another system view for walsenders, ex. "pg_stat_replication".
It would show pid, remote host, and sent location for each walsender.

I prefer this option. I consider it an omission that we should correct.

Not sure I understand why you block up access to the WALSendPtr and then
propose re-accessing it in text form a few days later. Whatever else you
do you should revoke the commit that did that.

--
Simon Riggs www.2ndQuadrant.com

On 18/06/10 13:41, Simon Riggs wrote:

On Fri, 2010-06-18 at 11:33 +0900, Takahiro Itagaki wrote:

1. Add another system view for walsenders, ex. "pg_stat_replication".
It would show pid, remote host, and sent location for each walsender.

I prefer this option. I consider it an omission that we should correct.

Not sure I understand why you block up access to the WALSendPtr and then
propose re-accessing it in text form a few days later. Whatever else you
do you should revoke the commit that did that.

Are you referring to the dead extern declaration of
GetOldestWALSendPointer()? That was indeed dead, the function itself was
already #ifdeffed out. If we go with the pg_state_replication view as
suggested above, it would've been useless anyway because it returns only
one value, not one for each walsender.

Let's discuss what the best possible user interface for the information
would be first, and then decide if we need/want to force an initdb for
that. We have pg_upgrade now, that makes initdb less painful, and if
it's just a new view it might be possible to just add a note in the
release notes that you'll have to run the CREATE VIEW manually if upgrading.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:

Let's discuss what the best possible user interface for the information
would be first, and then decide if we need/want to force an initdb for
that. We have pg_upgrade now, that makes initdb less painful, and if
it's just a new view it might be possible to just add a note in the
release notes that you'll have to run the CREATE VIEW manually if upgrading.

The view would presumably depend on a C-language SRF, which isn't there
either.

I'm of the opinion that this is a 9.1 problem. It needs more thought
than we can put into it now --- one obvious question is what about
monitoring on the slave side? Another is who should be able to see the
data?

regards, tom lane

Magnus Hagander <magnus@hagander.net> wrote:

The downside is that version 1 will require an initdb, and not version
2, right?

Unfortunately, 2 also requires initdb because pg_stat_activity will
use LEFT JOIN instead of normal JOIN not to hide rows with databaseid = 0.
All of them are items for 9.1.

Regards,
---
Takahiro Itagaki
NTT Open Source Software Center

Tom Lane <tgl@sss.pgh.pa.us> wrote:

I'm of the opinion that this is a 9.1 problem. It needs more thought
than we can put into it now --- one obvious question is what about
monitoring on the slave side? Another is who should be able to see the
data?

Sure. We should research user's demands for monitoring and management
of replication. I'll report some voices from users as of this moment:

* Managers often ask DBAs "How long standby servers are behind the master?"
We should provide such methods for DBAs. We have pg_xlog_location()
functions, but they should be improved for:
- The returned values are "xxx/yyy" texts, but more useful information
is the difference of two values. Subtraction functions are required.
- For easier management, the master server should provide not only
sent/flush locations but also received/replayed locations for each
standby servers. Users don't want to access both master and slaves.

* Some developers want to pause and restart replication from the master
server. They're going to use replication for application version
managements. They'll pause all replications, and test their new features
at the master, and restart replication to spread the changes to slaves.

Regards,
---
Takahiro Itagaki
NTT Open Source Software Center

Le 22/06/2010 06:40, Takahiro Itagaki a �crit :

[...]
Tom Lane <tgl@sss.pgh.pa.us> wrote:

I'm of the opinion that this is a 9.1 problem. It needs more thought
than we can put into it now --- one obvious question is what about
monitoring on the slave side? Another is who should be able to see the
data?

Sure. We should research user's demands for monitoring and management
of replication. I'll report some voices from users as of this moment:

* Managers often ask DBAs "How long standby servers are behind the master?"
We should provide such methods for DBAs. We have pg_xlog_location()
functions, but they should be improved for:
- The returned values are "xxx/yyy" texts, but more useful information
is the difference of two values. Subtraction functions are required.
- For easier management, the master server should provide not only
sent/flush locations but also received/replayed locations for each
standby servers. Users don't want to access both master and slaves.

* Some developers want to pause and restart replication from the master
server. They're going to use replication for application version
managements. They'll pause all replications, and test their new features
at the master, and restart replication to spread the changes to slaves.

I agree on these two.

Something I found lacking when I added support for Hot Standby /
Streaming Replication in pgAdmin (that was a really small patch, there
was not a lot to do) was that one cannot get the actual value of each
recovery.conf parameter. Try a "SHOW primary_conninfo;" and it will
juste reply that primary_conninfo is an unknown parameter. I already
talked about this to Heikki, but didn't get a chance to actually look at
the code.

--
Guillaume
http://www.postgresql.fr
http://dalibo.com

On Tue, 2010-06-22 at 09:54 +0200, Guillaume Lelarge wrote:

I added support for Hot Standby /
Streaming Replication in pgAdmin (that was a really small patch, there
was not a lot to do)

Well done.

Does this mean that pgAdmin has a read only mode now?

What are the details of that support? I couldn't easily see the commits
in the pgadmin list.

--
Simon Riggs www.2ndQuadrant.com

Le 22/06/2010 11:41, Simon Riggs a �crit :

On Tue, 2010-06-22 at 09:54 +0200, Guillaume Lelarge wrote:

I added support for Hot Standby /
Streaming Replication in pgAdmin (that was a really small patch, there
was not a lot to do)

Well done.

Does this mean that pgAdmin has a read only mode now?

Nope, it does not really have one. Though I intend to work on having
pgAdmin more aware of the actual rights of the connected user (allowing
him to get to display the "create table" dialog when we should already
know he cannot is an issue, at least to me).

What are the details of that support? I couldn't easily see the commits
in the pgadmin list.

Shamely simple : I only added some informations on the server's
properties. See
http://www.pgadmin.org/images/visualtour12/visualtour08.jpg. We only
display the fact that the server is (or isn't) in recovery, and the
result of the two admin functions (receive and replay location). Too bad
the other admin functions aren't there, I could have used them (and hope
to do so in 9.1). Too bad also we cannot know the primary server from a
connection to the slave (that's why I would love to get the value of
primary_conninfo, to found the alias/IP of the primary server).

--
Guillaume
http://www.postgresql.fr
http://dalibo.com

On Tue, 2010-06-22 at 12:19 +0200, Guillaume Lelarge wrote:

Shamely simple : I only added some informations on the server's
properties. See
http://www.pgadmin.org/images/visualtour12/visualtour08.jpg. We only
display the fact that the server is (or isn't) in recovery, and the
result of the two admin functions (receive and replay location).

If you store the is-in-Recovery result you could set the .enabled
property of many of the dialog boxes. I think its going to be painful
for people to attempt to submit a DDL command and get an error.

Too bad the other admin functions aren't there, I could have used them
(and hope to do so in 9.1). Too bad also we cannot know the primary
server from a connection to the slave (that's why I would love to get
the value of
primary_conninfo, to found the alias/IP of the primary server).

Agreed

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Training and Services

Le 22/06/2010 12:42, Simon Riggs a �crit :

On Tue, 2010-06-22 at 12:19 +0200, Guillaume Lelarge wrote:

Shamely simple : I only added some informations on the server's
properties. See
http://www.pgadmin.org/images/visualtour12/visualtour08.jpg. We only
display the fact that the server is (or isn't) in recovery, and the
result of the two admin functions (receive and replay location).

If you store the is-in-Recovery result you could set the .enabled
property of many of the dialog boxes. I think its going to be painful
for people to attempt to submit a DDL command and get an error.

That's what I first thought. But it would be weird that we disabled all
the OK button of the dialog properties only for hotstandby servers, but
not when a user doesn't have the permission. At least, that was the
reasonning I had at the time.

Too bad the other admin functions aren't there, I could have used them
(and hope to do so in 9.1). Too bad also we cannot know the primary
server from a connection to the slave (that's why I would love to get
the value of
primary_conninfo, to found the alias/IP of the primary server).

Agreed

:)

--
Guillaume
http://www.postgresql.fr
http://dalibo.com

On Tue, Jun 22, 2010 at 06:18, Takahiro Itagaki
<itagaki.takahiro@oss.ntt.co.jp> wrote:

Magnus Hagander <magnus@hagander.net> wrote:

The downside is that version 1 will require an initdb, and not version
2, right?

Unfortunately, 2 also requires initdb because pg_stat_activity will
use LEFT JOIN instead of normal JOIN not to hide rows with databaseid = 0.
All of them are items for 9.1.

Did this one end up on the floor?

We definitely need the very basic level for 9.1, and we can always
improve on it later :-) Do you want to keep working on it, or do you
want me to pick it up?

Any of the suggestions that includes the master showing data from the
slaves requires some kind of feedback going in from the slave, making
things a lot more complex (the slave is no longer passive) - let's
leave those for now. (you can use the 2ndquadrant replmgr to get some
of that :P)

I'm not sure it makes much sense to add walsenders to pg_stat_activity
- a lot of the fields would no longer make any sense (statement start?
query start?) - I think we're better off with a separate view for
pg_stat_walsender. It would then only need the columns for procpid,
usesysid, usename, client_addr, client_port, and the WALsender
specific fields.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

On Tue, Dec 28, 2010 at 21:46, Magnus Hagander <magnus@hagander.net> wrote:

Unfortunately, 2 also requires initdb because pg_stat_activity will
use LEFT JOIN instead of normal JOIN not to hide rows with databaseid = 0.
All of them are items for 9.1.

Did this one end up on the floor?

We definitely need the very basic level for 9.1, and we can always
improve on it later :-) Do you want to keep working on it, or do you
want me to pick it up?

OK, I'll work for it.

I'm not sure it makes much sense to add walsenders to pg_stat_activity
- a lot of the fields would no longer make any sense (statement start?
query start?) - I think we're better off with a separate view for
pg_stat_walsender. It would then only need the columns for procpid,
usesysid, usename, client_addr, client_port, and the WALsender
specific fields.

+1 for the separate view. "backend_start" (or replication_start?)
might be also reasonable.

--
Itagaki Takahiro

On Tue, Dec 28, 2010 at 14:14, Itagaki Takahiro
<itagaki.takahiro@gmail.com> wrote:

On Tue, Dec 28, 2010 at 21:46, Magnus Hagander <magnus@hagander.net> wrote:

Unfortunately, 2 also requires initdb because pg_stat_activity will
use LEFT JOIN instead of normal JOIN not to hide rows with databaseid = 0.
All of them are items for 9.1.

Did this one end up on the floor?

We definitely need the very basic level for 9.1, and we can always
improve on it later :-) Do you want to keep working on it, or do you
want me to pick it up?

OK, I'll work for it.

Great.

I'm not sure it makes much sense to add walsenders to pg_stat_activity
- a lot of the fields would no longer make any sense (statement start?
query start?) - I think we're better off with a separate view for
pg_stat_walsender. It would then only need the columns for procpid,
usesysid, usename, client_addr, client_port, and the WALsender
specific fields.

+1 for the separate view. "backend_start" (or replication_start?)
might be also reasonable.

Yeah, agreed. backend_start is probably the best one -
replication_start may be considered conceptually different if the
connection was dropped and reconnected. backend_start is more
explicit.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

On Tue, Dec 28, 2010 at 22:17, Magnus Hagander <magnus@hagander.net> wrote:

We definitely need the very basic level for 9.1, and we can always
improve on it later :-)

pg_stat_walsender. It would then only need the columns for procpid,
usesysid, usename, client_addr, client_port, and the WALsender
specific fields.

Yeah, agreed. backend_start is probably the best one

Here are patches for pg_stat_walsender.
I split the feature into two pieces:

* get_host_and_port.patch
It separates host and port formatter as a subroutine from pg_stat_activity.
In addition, make pg_stat_get_backend_client_addr/port() functions to
use the subroutine to reduce duplicated codes.

* pg_stat_walsender.patch
It adds pg_stat_walsender system view. It has subset columns of
pg_stat_activity and only one additional WAL sender specific information
via WALSndStatus(). I named the column "sending location" because
standby servers might not have received the WAL record; if we had
synchronous replication, a new "sent location" wold be added.
But the naming is still an open question. Comments welcome.

There is O(max_wal_senders^2) complexity in the view, But I think
it is not so serious problem because we can expect max_wal_senders
is 10 or so at most.

CREATE VIEW pg_stat_walsender AS
SELECT
S.procpid,
S.usesysid,
U.rolname AS usename,
S.client_addr,
S.client_port,
S.backend_start,
S.xlog_sending
FROM pg_stat_get_walsender(NULL) AS S, pg_authid U
WHERE S.usesysid = U.oid;

--
Itagaki Takahiro

On Tue, 2011-01-04 at 15:51 +0900, Itagaki Takahiro wrote:

On Tue, Dec 28, 2010 at 22:17, Magnus Hagander <magnus@hagander.net> wrote:

We definitely need the very basic level for 9.1, and we can always
improve on it later :-)

pg_stat_walsender. It would then only need the columns for procpid,
usesysid, usename, client_addr, client_port, and the WALsender
specific fields.

Yeah, agreed. backend_start is probably the best one

Here are patches for pg_stat_walsender.
I split the feature into two pieces:

* get_host_and_port.patch
It separates host and port formatter as a subroutine from pg_stat_activity.
In addition, make pg_stat_get_backend_client_addr/port() functions to
use the subroutine to reduce duplicated codes.

* pg_stat_walsender.patch
It adds pg_stat_walsender system view. It has subset columns of
pg_stat_activity and only one additional WAL sender specific information
via WALSndStatus(). I named the column "sending location" because
standby servers might not have received the WAL record; if we had
synchronous replication, a new "sent location" wold be added.
But the naming is still an open question. Comments welcome.

There is O(max_wal_senders^2) complexity in the view, But I think
it is not so serious problem because we can expect max_wal_senders
is 10 or so at most.

CREATE VIEW pg_stat_walsender AS
SELECT
S.procpid,
S.usesysid,
U.rolname AS usename,
S.client_addr,
S.client_port,
S.backend_start,
S.xlog_sending
FROM pg_stat_get_walsender(NULL) AS S, pg_authid U
WHERE S.usesysid = U.oid;

Just seen you started working on this again. Very good.

I enclose some snippets of code I was working on, which I am removing
from my patch in favour of your work as a separate commit.

The way I coded it was a new SRF that joins to the existing
pg_stat_activity. So no initdb required, and this can also easily be
included as an external module for 9.0.

Please notice also that my coding of the new SRF does not have the O^2
issue you mention, which I was keen to avoid.

The sent pointer is needed whether or not we have sync rep. We should
also include application name, since the user may set that in the
standby for all the same reasons it is set elsewhere.

Small point: please lets not call this pg_stat_walsender?
pg_stat_replication_sent and pg_stat_replication_received would be
easier for normal humans to understand.

I would very much appreciate it if one of you could complete something
here and commit in the next few days. That would then allow me to extend
the view with sync rep specific info for monitoring and patch testing.

--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services

On Tue, Jan 4, 2011 at 12:48 PM, Simon Riggs <simon@2ndquadrant.com> wrote:

The sent pointer is needed whether or not we have sync rep. We should
also include application name, since the user may set that in the
standby for all the same reasons it is set elsewhere.

Small point: please lets not call this pg_stat_walsender?
pg_stat_replication_sent and pg_stat_replication_received would be
easier for normal humans to understand.

Eh... I may be showing my status as a non-normal human, but I know
exactly what pg_stat_walsender is (it's the view that shows you the
status of the WAL senders you've allowed by configuring
max_wal_senders>0) but I have no idea what pg_stat_replication_sent
and pg_stat_replication_received are supposed to be. Why two views?
*scratches head in confusion*

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Eh... I may be showing my status as a non-normal human, but I know
exactly what pg_stat_walsender is (it's the view that shows you the
status of the WAL senders you've allowed by configuring
max_wal_senders>0) but I have no idea what pg_stat_replication_sent
and pg_stat_replication_received are supposed to be. Why two views?
*scratches head in confusion*

How about one view, called "pg_stat_replication"? This would be clear
even to newbies, which none of the other view names would ...

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com

On Tue, 2011-01-04 at 10:50 -0800, Josh Berkus wrote:

Eh... I may be showing my status as a non-normal human, but I know
exactly what pg_stat_walsender is (it's the view that shows you the
status of the WAL senders you've allowed by configuring
max_wal_senders>0) but I have no idea what pg_stat_replication_sent
and pg_stat_replication_received are supposed to be. Why two views?
*scratches head in confusion*

How about one view, called "pg_stat_replication"? This would be clear
even to newbies, which none of the other view names would ...

hmmm I think "pg_stat_standby" might be more relevant but I definitely
agree something more newbie appropriate is in order.

Joshua D. Drake

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt