The sixth chapter of PostgreSQL Replication Monitoring your settings (2)

6.2 Check Pg_stat_replication

Inspection archives and Archive_command are primarily used for instant recovery (pitr,point-in-time-

Recovery). If you want to monitor a stream-based setting, it is recommended that you pay attention to the view that is called pg_stat_replication on the system. 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,postgresql of our system connected by a stream, only one row of data will be returned. You will see in detail what your slave is doing.

6.2.1 Pg_stat_replication related fields

The following fields can be used to monitor the system. Let's discuss these fields in more detail:

PID: This represents the process ID of the wal_receiver process that is responsible for streaming connections. If you check your process table on your operating system, you should find a PostgreSQL process with that number.

Usesysid: Each internal user has a unique number. The system works much like Unix. Usesysid is the unique identifier of the (PostgreSQL) user connected to the system.

usename: (not user name, note less R) It stores the name of the USESYSID associated with the user. This is what the client puts into the connection string.

Application_name: This is the usual setting for synchronous replication. It can be passed through the connection string to master.

CLIENT_ADDR: It will tell you where the stream connection came from. It has the IP address of the client.

Client_hostname: In addition to the client's IP, you can also do this by identifying the client with its hostname. You can enable DNS reverse lookups through Log_hostname in postgresql.conf on master.

Client_port: This is the TPC port number used by clients to communicate with Walsender. If no local UNIX sockets are used, 1 will be displayed.

Backend_start: It tells us what time slave created the stream connection.

state: This column tells us the connection status of the data. If things go according to plan, it should contain flow information.

Sent_location: This represents the location of the last transaction log sent to the connection.

Write_location: This is the last transaction log location written to the standby system disk.

Flush_location: This is the last position to be flushed to the standby system.

Note Here the difference between write and refresh. Writing does not mean refreshing (see the section on durability requirements).

Replay_location: This is the last transaction log location to replay on slave.

sync_priority: This field is unique and synchronous replication-related. Each synchronous replication will select a priority-sync_priority-will tell you to choose that priority.

sync_state: In the end you will see slave in which state. This state can be

Async, sync, or potential. When there is a synchronous slave with a higher priority, PostgreSQL marks the slave as potential.

Keep in mind that each record in this system view represents only one slave. Therefore, you see who is connected and what tasks are being done. Pg_stat_replication is also a good way to check whether the slave is connected.

The sixth chapter of PostgreSQL Replication Monitoring your settings (2)