Checking the archive and the archive_command is primarily for Point-In-Time-Recovery. If you want to monitor a streaming-based setup, it is suggested to keep an eye on a system view called pg_stat_replication. This view contains the following information:
test=# d pg_stat_replication
View "pg_catalog.pg_stat_replication"
Column | Type | Modifiers
------------------+--------------------------+----------
pid | integer |
usesysid | oid |
usename | name |
application_name | text |
client_addr | inet |
client_hostname | text |
client_port | integer |
backend_start | timestamp with time zone |
state | text |
sent_location | text |
write_location | text |
flush_location | text |
replay_location | text |
sync_priority | integer |
sync_state | text | For each slave connected to our system via streaming, PostgreSQL will return exactly one line of data. You will see precisely what your slaves are doing.
The following fields are available to monitor the system. Let us discuss these fields in detail:
pid: This represents the process ID of thewal_receiverprocess in charge of this streaming connection. If you check your process table on your operating system, you should find a PostgreSQL process with exactly that number.usesysid: Internally every user has a unique number. The system works pretty much like on UNIX. Theusesysidis a unique identifier for the (PostgreSQL) user connecting to the system.usename: This (not username, mind the missing r) stores the name of the user related to theusesysid. This is what the client has put into the connection string.application_name: This is usually set when people decide to go for synchronous replication. It can be passed to the master through the connection string.client_addr: This will tell you where the streaming connection comes from. It holds the IP address of the client.client_hostname: In addition to the client's IP you can also identify a client via its hostname if you chose to do so. You can enable reverse DNS lookups by turninglog_hostnameon inpostgresql.confon the master.client_port: This is the TCP port number the client is using for communication with this WAL sender.-1will be shown if local UNIX sockets are used.backend_start: This tells us when this streaming connection has been created by the slave.state: This column informs us about the state of the database connection.If things are going as planned the column should containstreaming.sent_location: This represents the last transaction log position sent to the connection.write_location: This is the last transaction log position written to disk on the standby system.flush_location: This is the last location that has been flushed to the standby system. Mind the difference between writing and flushing here. Writing does not imply flushing (see the section about durability requirements).replay_location: This is the last transaction log position that has been replayed on the slave.sync_priority: This field is only relevant if you are replicating synchronously. Each sync replica will chose a priority—sync_priority—that will tell you which priority has been chosen.sync_state: Finally you can see in which state the slave is. The state can beasync,sync, orpotential. PostgreSQL will mark a slave aspotentialwhen there is a sync slave with higher priority.
Remember that each record in this system view represents exactly one slave. So, you can see at first glance who is connected and doing what task. pg_stat_replication is also a good way to check if a slave is connected at all.
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.