pg_stat_activitypg_stat_replicationpg_stat_replication_slotspg_stat_wal_receiverpg_stat_recovery_prefetchpg_stat_subscriptionpg_stat_subscription_statspg_stat_sslpg_stat_gssapipg_stat_archiverpg_stat_iopg_stat_bgwriterpg_stat_checkpointerpg_stat_walpg_stat_databasepg_stat_database_conflictspg_stat_all_tablespg_stat_all_indexespg_statio_all_tablespg_statio_all_indexespg_statio_all_sequencespg_stat_user_functionspg_stat_slruPostgreSQL's cumulative statistics system supports collection and reporting of information about server activity. Presently, accesses to tables and indexes in both disk-block and individual-row terms are counted. The total number of rows in each table, and information about vacuum and analyze actions for each table are also counted. If enabled, calls to user-defined functions and the total time spent in each one are counted as well.
PostgreSQL also supports reporting dynamic information about exactly what is going on in the system right now, such as the exact command currently being executed by other server processes, and which other connections exist in the system. This facility is independent of the cumulative statistics system.
Since collection of statistics adds some overhead to query execution,
the system can be configured to collect or not collect information.
This is controlled by configuration parameters that are normally set in
postgresql.conf. (See Chapter 19 for
details about setting configuration parameters.)
The parameter track_activities enables monitoring of the current command being executed by any server process.
The parameter track_cost_delay_timing enables monitoring of cost-based vacuum delay.
The parameter track_counts controls whether cumulative statistics are collected about table and index accesses.
The parameter track_functions enables tracking of usage of user-defined functions.
The parameter track_io_timing enables monitoring of block read, write, extend, and fsync times.
The parameter track_wal_io_timing enables monitoring of WAL read, write and fsync times.
Normally these parameters are set in postgresql.conf so
that they apply to all server processes, but it is possible to turn
them on or off in individual sessions using the SET command. (To prevent
ordinary users from hiding their activity from the administrator,
only superusers are allowed to change these parameters with
SET.)
Cumulative statistics are collected in shared memory. Every
PostgreSQL process collects statistics locally,
then updates the shared data at appropriate intervals. When a server,
including a physical replica, shuts down cleanly, a permanent copy of the
statistics data is stored in the pg_stat subdirectory,
so that statistics can be retained across server restarts. In contrast,
when starting from an unclean shutdown (e.g., after an immediate shutdown,
a server crash, starting from a base backup, and point-in-time recovery),
all statistics counters are reset.
Several predefined views, listed in Table 27.1, are available to show the current state of the system. There are also several other views, listed in Table 27.2, available to show the accumulated statistics. Alternatively, one can build custom views using the underlying cumulative statistics functions, as discussed in Section 27.2.26.
When using the cumulative statistics views and functions to monitor
collected data, it is important to realize that the information does not
update instantaneously. Each individual server process flushes out
accumulated statistics to shared memory just before going idle, but not
more frequently than once per PGSTAT_MIN_INTERVAL
milliseconds (1 second unless altered while building the server); so a
query or transaction still in progress does not affect the displayed totals
and the displayed information lags behind actual activity. However,
current-query information collected by track_activities
is always up-to-date.
Another important point is that when a server process is asked to display
any of the accumulated statistics, accessed values are cached until the end
of its current transaction in the default configuration. So the statistics
will show static information as long as you continue the current
transaction. Similarly, information about the current queries of all
sessions is collected when any such information is first requested within a
transaction, and the same information will be displayed throughout the
transaction. This is a feature, not a bug, because it allows you to perform
several queries on the statistics and correlate the results without
worrying that the numbers are changing underneath you.
When analyzing statistics interactively, or with expensive queries, the
time delta between accesses to individual statistics can lead to
significant skew in the cached statistics. To minimize skew,
stats_fetch_consistency can be set to
snapshot, at the price of increased memory usage for
caching not-needed statistics data. Conversely, if it's known that
statistics are only accessed once, caching accessed statistics is
unnecessary and can be avoided by setting
stats_fetch_consistency to none.
You can invoke pg_stat_clear_snapshot() to discard the
current transaction's statistics snapshot or cached values (if any). The
next use of statistical information will (when in snapshot mode) cause a
new snapshot to be built or (when in cache mode) accessed statistics to be
cached.
A transaction can also see its own statistics (not yet flushed out to the
shared memory statistics) in the views
pg_stat_xact_all_tables,
pg_stat_xact_sys_tables,
pg_stat_xact_user_tables, and
pg_stat_xact_user_functions. These numbers do not act as
stated above; instead they update continuously throughout the transaction.
Some of the information in the dynamic statistics views shown in Table 27.1 is security restricted.
Ordinary users can only see all the information about their own sessions
(sessions belonging to a role that they are a member of). In rows about
other sessions, many columns will be null. Note, however, that the
existence of a session and its general properties such as its sessions user
and database are visible to all users. Superusers and roles with privileges of
built-in role pg_read_all_stats
can see all the information about all sessions.
Table 27.1. Dynamic Statistics Views
| View Name | Description |
|---|---|
pg_stat_activity
|
One row per server process, showing information related to
the current activity of that process, such as state and current query.
See
pg_stat_activity for details.
|
pg_stat_replication | One row per WAL sender process, showing statistics about
replication to that sender's connected standby server.
See
pg_stat_replication for details.
|
pg_stat_wal_receiver | Only one row, showing statistics about the WAL receiver from
that receiver's connected server.
See
pg_stat_wal_receiver for details.
|
pg_stat_recovery_prefetch | Only one row, showing statistics about blocks prefetched during recovery.
See
pg_stat_recovery_prefetch for details.
|
pg_stat_subscription | At least one row per subscription, showing information about
the subscription workers.
See
pg_stat_subscription for details.
|
pg_stat_ssl | One row per connection (regular and replication), showing information about
SSL used on this connection.
See
pg_stat_ssl for details.
|
pg_stat_gssapi | One row per connection (regular and replication), showing information about
GSSAPI authentication and encryption used on this connection.
See
pg_stat_gssapi for details.
|
pg_stat_progress_analyze | One row for each backend (including autovacuum worker processes) running
ANALYZE, showing current progress.
See Section 27.4.1.
|
pg_stat_progress_create_index | One row for each backend running CREATE INDEX or REINDEX, showing
current progress.
See Section 27.4.4.
|
pg_stat_progress_vacuum | One row for each backend (including autovacuum worker processes) running
VACUUM, showing current progress.
See Section 27.4.5.
|
pg_stat_progress_cluster | One row for each backend running
CLUSTER or VACUUM FULL, showing current progress.
See Section 27.4.2.
|
pg_stat_progress_basebackup | One row for each WAL sender process streaming a base backup, showing current progress. See Section 27.4.6. |
pg_stat_progress_copy | One row for each backend running COPY, showing current progress.
See Section 27.4.3.
|
Table 27.2. Collected Statistics Views
| View Name | Description |
|---|---|
pg_stat_archiver | One row only, showing statistics about the
WAL archiver process's activity. See
pg_stat_archiver for details.
|
pg_stat_bgwriter | One row only, showing statistics about the
background writer process's activity. See
pg_stat_bgwriter for details.
|
pg_stat_checkpointer | One row only, showing statistics about the
checkpointer process's activity. See
pg_stat_checkpointer for details.
|
pg_stat_database | One row per database, showing database-wide statistics. See
pg_stat_database for details.
|
pg_stat_database_conflicts |
One row per database, showing database-wide statistics about
query cancels due to conflict with recovery on standby servers.
See
pg_stat_database_conflicts for details.
|
pg_stat_io |
One row for each combination of backend type, context, and target object
containing cluster-wide I/O statistics.
See
pg_stat_io for details.
|
pg_stat_replication_slots | One row per replication slot, showing statistics about the
replication slot's usage. See
pg_stat_replication_slots for details.
|
pg_stat_slru | One row per SLRU, showing statistics of operations. See
pg_stat_slru for details.
|
pg_stat_subscription_stats | One row per subscription, showing statistics about errors and conflicts.
See
pg_stat_subscription_stats for details.
|
pg_stat_wal | One row only, showing statistics about WAL activity. See
pg_stat_wal for details.
|
pg_stat_all_tables |
One row for each table in the current database, showing statistics
about accesses to that specific table.
See
pg_stat_all_tables for details.
|
pg_stat_sys_tables | Same as pg_stat_all_tables, except that only
system tables are shown. |
pg_stat_user_tables | Same as pg_stat_all_tables, except that only user
tables are shown. |
pg_stat_xact_all_tables | Similar to pg_stat_all_tables, but counts actions
taken so far within the current transaction (which are not
yet included in pg_stat_all_tables and related views).
The columns for numbers of live and dead rows and vacuum and
analyze actions are not present in this view. |
pg_stat_xact_sys_tables | Same as pg_stat_xact_all_tables, except that only
system tables are shown. |
pg_stat_xact_user_tables | Same as pg_stat_xact_all_tables, except that only
user tables are shown. |
pg_stat_all_indexes |
One row for each index in the current database, showing statistics
about accesses to that specific index.
See
pg_stat_all_indexes for details.
|
pg_stat_sys_indexes | Same as pg_stat_all_indexes, except that only
indexes on system tables are shown. |
pg_stat_user_indexes | Same as pg_stat_all_indexes, except that only
indexes on user tables are shown. |
pg_stat_user_functions |
One row for each tracked function, showing statistics
about executions of that function. See
pg_stat_user_functions for details.
|
pg_stat_xact_user_functions | Similar to pg_stat_user_functions, but counts only
calls during the current transaction (which are not
yet included in pg_stat_user_functions). |
pg_statio_all_tables |
One row for each table in the current database, showing statistics
about I/O on that specific table.
See
pg_statio_all_tables for details.
|
pg_statio_sys_tables | Same as pg_statio_all_tables, except that only
system tables are shown. |
pg_statio_user_tables | Same as pg_statio_all_tables, except that only
user tables are shown. |
pg_statio_all_indexes |
One row for each index in the current database,
showing statistics about I/O on that specific index.
See
pg_statio_all_indexes for details.
|
pg_statio_sys_indexes | Same as pg_statio_all_indexes, except that only
indexes on system tables are shown. |
pg_statio_user_indexes | Same as pg_statio_all_indexes, except that only
indexes on user tables are shown. |
pg_statio_all_sequences |
One row for each sequence in the current database,
showing statistics about I/O on that specific sequence.
See
pg_statio_all_sequences for details.
|
pg_statio_sys_sequences | Same as pg_statio_all_sequences, except that only
system sequences are shown. (Presently, no system sequences are defined,
so this view is always empty.) |
pg_statio_user_sequences | Same as pg_statio_all_sequences, except that only
user sequences are shown. |
The per-index statistics are particularly useful to determine which indexes are being used and how effective they are.
The pg_stat_io and
pg_statio_ set of views are useful for determining
the effectiveness of the buffer cache. They can be used to calculate a cache
hit ratio. Note that while PostgreSQL's I/O
statistics capture most instances in which the kernel was invoked in order
to perform I/O, they do not differentiate between data which had to be
fetched from disk and that which already resided in the kernel page cache.
Users are advised to use the PostgreSQL
statistics views in combination with operating system utilities for a more
complete picture of their database's I/O performance.
pg_stat_activity #
The pg_stat_activity view will have one row
per server process, showing information related to
the current activity of that process.
Table 27.3. pg_stat_activity View
Column Type Description |
|---|
OID of the database this backend is connected to |
Name of the database this backend is connected to |
Process ID of this backend |
Process ID of the parallel group leader if this process is a parallel
query worker, or process ID of the leader apply worker if this process
is a parallel apply worker. |
OID of the user logged into this backend |
Name of the user logged into this backend |
Name of the application that is connected to this backend |
IP address of the client connected to this backend. If this field is null, it indicates either that the client is connected via a Unix socket on the server machine or that this is an internal process such as autovacuum. |
Host name of the connected client, as reported by a
reverse DNS lookup of |
TCP port number that the client is using for communication
with this backend, or |
Time when this process was started. For client backends, this is the time the client connected to the server. |
Time when this process' current transaction was started, or null
if no transaction is active. If the current
query is the first of its transaction, this column is equal to the
|
Time when the currently active query was started, or if
|
Time when the |
The type of event for which the backend is waiting, if any; otherwise NULL. See Table 27.4. |
Wait event name if backend is currently waiting, otherwise NULL. See Table 27.5 through Table 27.13. |
Current overall state of this backend. Possible values are:
|
Top-level transaction identifier of this backend, if any; see Section 67.1. |
The current backend's |
Identifier of this backend's most recent query. If
|
Text of this backend's most recent query. If
|
Type of current backend. Possible types are
|
The wait_event and state columns are
independent. If a backend is in the active state,
it may or may not be waiting on some event. If the state
is active and wait_event is non-null, it
means that a query is being executed, but is being blocked somewhere
in the system. To keep the reporting overhead low, the system does not
attempt to synchronize different aspects of activity data for a backend.
As a result, ephemeral discrepancies may exist between the view's columns.
Table 27.4. Wait Event Types
| Wait Event Type | Description |
|---|---|
Activity | The server process is idle. This event type indicates a process
waiting for activity in its main processing loop.
wait_event will identify the specific wait point;
see Table 27.5.
|
BufferPin | The server process is waiting for exclusive access to a data buffer. Buffer pin waits can be protracted if another process holds an open cursor that last read data from the buffer in question. See Table 27.6. |
Client | The server process is waiting for activity on a socket
connected to a user application. Thus, the server expects something
to happen that is independent of its internal processes.
wait_event will identify the specific wait point;
see Table 27.7.
|
Extension | The server process is waiting for some condition defined by an extension module. See Table 27.8. |
InjectionPoint | The server process is waiting for an injection point to reach an outcome defined in a test. See Section 36.10.14 for more details. This type has no predefined wait points. |
IO | The server process is waiting for an I/O operation to complete.
wait_event will identify the specific wait point;
see Table 27.9.
|
IPC | The server process is waiting for some interaction with
another server process. wait_event will
identify the specific wait point;
see Table 27.10.
|
Lock | The server process is waiting for a heavyweight lock.
Heavyweight locks, also known as lock manager locks or simply locks,
primarily protect SQL-visible objects such as tables. However,
they are also used to ensure mutual exclusion for certain internal
operations such as relation extension. wait_event
will identify the type of lock awaited;
see Table 27.11.
|
LWLock | The server process is waiting for a lightweight lock.
Most such locks protect a particular data structure in shared memory.
wait_event will contain a name identifying the purpose
of the lightweight lock. (Some locks have specific names; others
are part of a group of locks each with a similar purpose.)
See Table 27.12.
|
Timeout | The server process is waiting for a timeout
to expire. wait_event will identify the specific wait
point; see Table 27.13.
|
Table 27.5. Eventos de espera de tipo Activity
Evento de espera de Activity | Descripción |
|---|---|
ArchiverMain | Esperando en el bucle principal del proceso archiver. |
AutovacuumMain | Esperando en el bucle principal del proceso lanzador de autovacuum. |
BgwriterHibernate | Esperando en el proceso background writer, hibernando. |
BgwriterMain | Esperando en el bucle principal del proceso background writer. |
CheckpointerMain | Esperando en el bucle principal del proceso checkpointer. |
CheckpointerShutdown | Esperando a que se termine el proceso checkpointer. |
IoWorkerMain | Esperando en el bucle principal del proceso IO Worker. |
LogicalApplyMain | Esperando en el bucle principal del proceso de aplicación de replicación lógica. |
LogicalLauncherMain | Esperando en el bucle principal del proceso lanzador de replicación lógica. |
LogicalParallelApplyMain | Esperando en el bucle principal del proceso de aplicación paralela de replicación lógica. |
RecoveryWalStream | Esperando en el bucle principal del proceso de inicio a que llegue el WAL, durante la recuperación por flujo. |
ReplicationSlotsyncMain | Esperando en el bucle principal del trabajador de sincronización de ranuras. |
ReplicationSlotsyncShutdown | Esperando a que se apague el trabajador de sincronización de ranuras. |
SysloggerMain | Esperando en el bucle principal del proceso syslogger. |
WalReceiverMain | Esperando en el bucle principal del proceso receptor de WAL (WAL receiver). |
WalSenderMain | Esperando en el bucle principal del proceso emisor de WAL (WAL sender). |
WalSummarizerWal | Esperando en el resumidor de WAL a que se genere más WAL. |
WalWriterMain | Esperando en el bucle principal del proceso escritor de WAL (WAL writer). |
Table 27.6. Eventos de espera de tipo Bufferpin
Evento de espera de BufferPin | Descripción |
|---|---|
BufferPin | Esperando para adquirir una marca exclusiva (pin) en un búfer. |
Table 27.7. Eventos de espera de tipo Client
Evento de espera de Client | Descripción |
|---|---|
ClientRead | Esperando para leer datos del cliente. |
ClientWrite | Esperando para escribir datos al cliente. |
GssOpenServer | Esperando para leer datos del cliente mientras se establece una sesión GSSAPI. |
LibpqwalreceiverConnect | Esperando en el receptor de WAL para establecer una conexión con el servidor remoto. |
LibpqwalreceiverReceive | Esperando en el receptor de WAL para recibir datos del servidor remoto. |
SslOpenServer | Esperando por SSL mientras se intenta la conexión. |
WaitForStandbyConfirmation | Esperando a que el WAL sea recibido y vaciado por el servidor en espera (standby) físico. |
WalSenderWaitForWal | Esperando a que el WAL sea vaciado en el proceso emisor de WAL. |
WalSenderWriteData | Esperando cualquier actividad al procesar respuestas del receptor de WAL en el proceso emisor de WAL. |
Table 27.8. Eventos de espera de tipo Extension
Evento de espera de Extension | Descripción |
|---|---|
Extension | Esperando en una extensión. |
Table 27.9. Eventos de espera de tipo Io
Evento de espera de IO | Descripción |
|---|---|
AioIoCompletion | Esperando a que otro proceso complete la E/S. |
AioIoUringExecution | Esperando la ejecución de E/S a través de io_uring. |
AioIoUringSubmit | Esperando el envío de E/S a través de io_uring. |
BasebackupRead | Esperando a que la copia de seguridad base lea de un archivo. |
BasebackupSync | Esperando a que los datos escritos por una copia de seguridad base lleguen a un almacenamiento duradero. |
BasebackupWrite | Esperando a que la copia de seguridad base escriba en un archivo. |
BuffileRead | Esperando una lectura de un archivo con búfer (buffered file). |
BuffileTruncate | Esperando que se trunque un archivo con búfer. |
BuffileWrite | Esperando una escritura en un archivo con búfer. |
ControlFileRead | Esperando una lectura del archivo pg_control. |
ControlFileSync | Esperando que el archivo pg_control llegue a un almacenamiento duradero. |
ControlFileSyncUpdate | Esperando que una actualización del archivo pg_control llegue a un almacenamiento duradero. |
ControlFileWrite | Esperando una escritura en el archivo pg_control. |
ControlFileWriteUpdate | Esperando una escritura para actualizar el archivo pg_control. |
CopyFileCopy | Esperando una operación de copia de archivos. |
CopyFileRead | Esperando una lectura durante una operación de copia de archivos. |
CopyFileWrite | Esperando una escritura durante una operación de copia de archivos. |
DataFileExtend | Esperando a que se extienda un archivo de datos de relación. |
DataFileFlush | Esperando a que un archivo de datos de relación llegue a un almacenamiento duradero. |
DataFileImmediateSync | Esperando una sincronización inmediata de un archivo de datos de relación a un almacenamiento duradero. |
DataFilePrefetch | Esperando una prelectura asíncrona de un archivo de datos de relación. |
DataFileRead | Esperando una lectura de un archivo de datos de relación. |
DataFileSync | Esperando a que los cambios en un archivo de datos de relación lleguen a un almacenamiento duradero. |
DataFileTruncate | Esperando a que se trunque un archivo de datos de relación. |
DataFileWrite | Esperando una escritura en un archivo de datos de relación. |
DsmAllocate | Esperando a que se asigne un segmento de memoria compartida dinámica. |
DsmFillZeroWrite | Esperando para llenar con ceros un archivo de respaldo de memoria compartida dinámica. |
LockFileAddtodatadirRead | Esperando una lectura al agregar una línea al archivo de bloqueo del directorio de datos. |
LockFileAddtodatadirSync | Esperando que los datos lleguen a un almacenamiento duradero al agregar una línea al archivo de bloqueo del directorio de datos. |
LockFileAddtodatadirWrite | Esperando una escritura al agregar una línea al archivo de bloqueo del directorio de datos. |
LockFileCreateRead | Esperando leer mientras se crea el archivo de bloqueo del directorio de datos. |
LockFileCreateSync | Esperando que los datos lleguen a un almacenamiento duradero al crear el archivo de bloqueo del directorio de datos. |
LockFileCreateWrite | Esperando una escritura al crear el archivo de bloqueo del directorio de datos. |
LockFileRecheckdatadirRead | Esperando una lectura durante la nueva comprobación del archivo de bloqueo del directorio de datos. |
LogicalRewriteCheckpointSync | Esperando que los mapeos de reescritura lógica lleguen a un almacenamiento duradero durante un punto de control. |
LogicalRewriteMappingSync | Esperando que los datos de mapeo lleguen a un almacenamiento duradero durante una reescritura lógica. |
LogicalRewriteMappingWrite | Esperando una escritura de datos de mapeo durante una reescritura lógica. |
LogicalRewriteSync | Esperando que los mapeos de reescritura lógica lleguen a un almacenamiento duradero. |
LogicalRewriteTruncate | Esperando el truncado de los datos de mapeo durante una reescritura lógica. |
LogicalRewriteWrite | Esperando una escritura de mapeos de reescritura lógica. |
RelationMapRead | Esperando una lectura del archivo de mapa de relaciones. |
RelationMapReplace | Esperando el reemplazo duradero de un archivo de mapa de relaciones. |
RelationMapWrite | Esperando una escritura en el archivo de mapa de relaciones. |
ReorderBufferRead | Esperando una lectura durante la gestión del búfer de reordenamiento. |
ReorderBufferWrite | Esperando una escritura durante la gestión del búfer de reordenamiento. |
ReorderLogicalMappingRead | Esperando una lectura de un mapeo lógico durante la gestión del búfer de reordenamiento. |
ReplicationSlotRead | Esperando una lectura de un archivo de control de ranura de replicación. |
ReplicationSlotRestoreSync | Esperando que un archivo de control de ranura de replicación llegue a un almacenamiento duradero mientras se restaura en la memoria. |
ReplicationSlotSync | Esperando que un archivo de control de ranura de replicación llegue a un almacenamiento duradero. |
ReplicationSlotWrite | Esperando una escritura en un archivo de control de ranura de replicación. |
SlruFlushSync | Esperando que los datos SLRU lleguen a un almacenamiento duradero durante un punto de control o apagado de la base de datos. |
SlruRead | Esperando una lectura de una página SLRU. |
SlruSync | Esperando que los datos SLRU lleguen a un almacenamiento duradero después de una escritura de página. |
SlruWrite | Esperando una escritura de una página SLRU. |
SnapbuildRead | Esperando una lectura de una instantánea (snapshot) de catálogo histórico serializada. |
SnapbuildSync | Esperando que una instantánea de catálogo histórico serializada llegue a un almacenamiento duradero. |
SnapbuildWrite | Esperando una escritura de una instantánea de catálogo histórico serializada. |
TimelineHistoryFileSync | Esperando que un archivo de historial de línea de tiempo recibido a través de replicación por flujo llegue a un almacenamiento duradero. |
TimelineHistoryFileWrite | Esperando una escritura de un archivo de historial de línea de tiempo recibido a través de replicación por flujo. |
TimelineHistoryRead | Esperando una lectura de un archivo de historial de línea de tiempo. |
TimelineHistorySync | Esperando que un archivo de historial de línea de tiempo recién creado llegue a un almacenamiento duradero. |
TimelineHistoryWrite | Esperando una escritura de un archivo de historial de línea de tiempo recién creado. |
TwophaseFileRead | Esperando una lectura de un archivo de estado de dos fases. |
TwophaseFileSync | Esperando que un archivo de estado de dos fases llegue a un almacenamiento duradero. |
TwophaseFileWrite | Esperando una escritura de un archivo de estado de dos fases. |
VersionFileSync | Esperando que el archivo de versión llegue a un almacenamiento duradero mientras se crea una base de datos. |
VersionFileWrite | Esperando que se escriba el archivo de versión mientras se crea una base de datos. |
WalsenderTimelineHistoryRead | Esperando una lectura de un archivo de historial de línea de tiempo durante un comando timeline de walsender. |
WalBootstrapSync | Esperando que el WAL llegue a un almacenamiento duradero durante la inicialización (bootstrapping). |
WalBootstrapWrite | Esperando una escritura de una página WAL durante la inicialización. |
WalCopyRead | Esperando una lectura al crear un nuevo segmento de WAL copiando uno existente. |
WalCopySync | Esperando que un nuevo segmento de WAL creado al copiar uno existente llegue a un almacenamiento duradero. |
WalCopyWrite | Esperando una escritura al crear un nuevo segmento de WAL copiando uno existente. |
WalInitSync | Esperando que un archivo de WAL recién inicializado llegue a un almacenamiento duradero. |
WalInitWrite | Esperando una escritura al inicializar un nuevo archivo de WAL. |
WalRead | Esperando una lectura de un archivo de WAL. |
WalSummaryRead | Esperando una lectura de un archivo de resumen de WAL. |
WalSummaryWrite | Esperando una escritura en un archivo de resumen de WAL. |
WalSync | Esperando que un archivo de WAL llegue a un almacenamiento duradero. |
WalSyncMethodAssign | Esperando que los datos tengan la oportunidad de llegar a un almacenamiento duradero al asignar un nuevo método de sincronización de WAL. |
WalWrite | Esperando una escritura en un archivo de WAL. |
Table 27.10. Eventos de espera de tipo Ipc
Evento de espera de IPC | Descripción |
|---|---|
AppendReady | Esperando a que los nodos del subplan de un nodo de plan Append estén listos. |
ArchiveCleanupCommand | Esperando a que se complete archive_cleanup_command. |
ArchiveCommand | Esperando a que se complete archive_command. |
BackendTermination | Esperando la finalización de otro backend. |
BackupWaitWalArchive | Esperando a que los archivos WAL requeridos para una copia de seguridad se archiven correctamente. |
BgworkerShutdown | Esperando a que el trabajador en segundo plano (background worker) se apague. |
BgworkerStartup | Esperando a que el trabajador en segundo plano se inicie. |
BtreePage | Esperando a que esté disponible el número de página necesario para continuar un escaneo paralelo de árbol B. |
BufferIo | Esperando a que se complete la E/S de búfer. |
CheckpointDelayComplete | Esperando a un backend que bloquea la finalización de un punto de control. |
CheckpointDelayStart | Esperando a un backend que bloquea el inicio de un punto de control. |
CheckpointDone | Esperando a que se complete un punto de control. |
CheckpointStart | Esperando a que comience un punto de control. |
ExecuteGather | Esperando actividad de un proceso hijo mientras se ejecuta un nodo de plan Gather. |
HashBatchAllocate | Esperando a que un participante elegido de Parallel Hash asigne una tabla hash. |
HashBatchElect | Esperando elegir un participante de Parallel Hash para asignar una tabla hash. |
HashBatchLoad | Esperando a que otros participantes de Parallel Hash terminen de cargar una tabla hash. |
HashBuildAllocate | Esperando a que un participante elegido de Parallel Hash asigne la tabla hash inicial. |
HashBuildElect | Esperando elegir un participante de Parallel Hash para asignar la tabla hash inicial. |
HashBuildHashInner | Esperando a que otros participantes de Parallel Hash terminen de aplicar la función hash a la relación interna. |
HashBuildHashOuter | Esperando a que otros participantes de Parallel Hash terminen de particionar la relación externa. |
HashGrowBatchesDecide | Esperando elegir un participante de Parallel Hash para decidir sobre el crecimiento futuro de lotes. |
HashGrowBatchesElect | Esperando elegir un participante de Parallel Hash para asignar más lotes. |
HashGrowBatchesFinish | Esperando a que un participante elegido de Parallel Hash decida sobre el crecimiento futuro de lotes. |
HashGrowBatchesReallocate | Esperando a que un participante elegido de Parallel Hash asigne más lotes. |
HashGrowBatchesRepartition | Esperando a que otros participantes de Parallel Hash terminen de reparticionar. |
HashGrowBucketsElect | Esperando elegir un participante de Parallel Hash para asignar más contenedores (buckets). |
HashGrowBucketsReallocate | Esperando a que un participante elegido de Parallel Hash termine de asignar más contenedores. |
HashGrowBucketsReinsert | Esperando a que otros participantes de Parallel Hash terminen de insertar tuplas en los nuevos contenedores. |
LogicalApplySendData | Esperando a que un proceso de aplicación del líder de replicación lógica envíe datos a un proceso de aplicación paralelo. |
LogicalParallelApplyStateChange | Esperando a que un proceso de aplicación paralelo de replicación lógica cambie de estado. |
LogicalSyncData | Esperando a que un servidor remoto de replicación lógica envíe datos para la sincronización inicial de la tabla. |
LogicalSyncStateChange | Esperando a que un servidor remoto de replicación lógica cambie de estado. |
MessageQueueInternal | Esperando a que otro proceso se asocie a una cola de mensajes compartida. |
MessageQueuePutMessage | Esperando para escribir un mensaje de protocolo en una cola de mensajes compartida. |
MessageQueueReceive | Esperando recibir bytes de una cola de mensajes compartida. |
MessageQueueSend | Esperando enviar bytes a una cola de mensajes compartida. |
MultixactCreation | Esperando a que se complete la creación de una multixact. |
ParallelBitmapScan | Esperando a que se inicialice el escaneo de mapa de bits paralelo. |
ParallelCreateIndexScan | Esperando a que los trabajadores de CREATE INDEX en paralelo terminen el escaneo de heap. |
ParallelFinish | Esperando a que los trabajadores paralelos terminen de calcular. |
ProcarrayGroupUpdate | Esperando a que el líder del grupo borre el ID de transacción al finalizar la transacción. |
ProcSignalBarrier | Esperando a que un evento de barrera sea procesado por todos los backends. |
Promote | Esperando la promoción del servidor en espera (standby). |
RecoveryConflictSnapshot | Esperando la resolución de conflicto de recuperación para una limpieza de vacuum. |
RecoveryConflictTablespace | Esperando la resolución de conflicto de recuperación para la eliminación de un tablespace. |
RecoveryEndCommand | Esperando a que se complete recovery_end_command. |
RecoveryPause | Esperando a que se reanude la recuperación. |
ReplicationOriginDrop | Esperando a que un origen de replicación quede inactivo para poder eliminarlo. |
ReplicationSlotDrop | Esperando a que una ranura de replicación quede inactiva para poder eliminarla. |
RestoreCommand | Esperando a que se complete restore_command. |
SafeSnapshot | Esperando obtener una instantánea válida para una transacción READ ONLY DEFERRABLE. |
SyncRep | Esperando confirmación de un servidor remoto durante la replicación síncrona. |
WalReceiverExit | Esperando a que salga el receptor de WAL. |
WalReceiverWaitStart | Esperando a que el proceso de inicio envíe datos iniciales para la replicación por flujo. |
WalSummaryReady | Esperando a que se genere un nuevo resumen de WAL. |
XactGroupUpdate | Esperando a que el líder del grupo actualice el estado de la transacción al finalizar la transacción. |
Table 27.11. Eventos de espera de tipo Lock
Evento de espera de Lock | Descripción |
|---|---|
advisory | Esperando para adquirir un bloqueo de usuario consultivo (advisory). |
applytransaction | Esperando para adquirir un bloqueo en una transacción remota que está siendo aplicada por un suscriptor de replicación lógica. |
extend | Esperando para extender una relación. |
frozenid | Esperando para actualizar pg_database.datfrozenxid y pg_database.datminmxid. |
object | Esperando para adquirir un bloqueo en un objeto de base de datos que no sea una relación. |
page | Esperando para adquirir un bloqueo en una página de una relación. |
relation | Esperando para adquirir un bloqueo en una relación. |
spectoken | Esperando para adquirir un bloqueo de inserción especulativa. |
transactionid | Esperando a que termine una transacción. |
tuple | Esperando para adquirir un bloqueo en una tupla. |
userlock | Esperando para adquirir un bloqueo de usuario. |
virtualxid | Esperando para adquirir un bloqueo de ID de transacción virtual; consulta Section 67.1. |
Table 27.12. Eventos de espera de tipo Lwlock
Evento de espera de LWLock | Descripción |
|---|---|
AddinShmemInit | Esperando para gestionar la asignación de espacio de una extensión en la memoria compartida. |
AioUringCompletion | Esperando a que otro proceso complete la E/S a través de io_uring. |
AioWorkerSubmissionQueue | Esperando acceder a la cola de envío de los trabajadores AIO. |
AutoFile | Esperando para actualizar el archivo postgresql.auto.conf. |
Autovacuum | Esperando para leer o actualizar el estado actual de los trabajadores de autovacuum. |
AutovacuumSchedule | Esperando asegurar que una tabla seleccionada para autovacuum todavía necesita vacuum. |
BackgroundWorker | Esperando para leer o actualizar el estado del trabajador en segundo plano. |
BtreeVacuum | Esperando para leer o actualizar información relacionada con el vacuum para un índice de árbol B. |
BufferContent | Esperando acceder a una página de datos en memoria. |
BufferMapping | Esperando asociar un bloque de datos con un búfer en el buffer pool. |
CheckpointerComm | Esperando gestionar solicitudes de fsync. |
CommitTs | Esperando para leer o actualizar el último valor establecido para una marca de tiempo de confirmación de transacción. |
CommitTsBuffer | Esperando E/S en un búfer SLRU de marcas de tiempo de confirmación. |
CommitTsSLRU | Esperando acceder a la caché SLRU de marcas de tiempo de confirmación. |
ControlFile | Esperando para leer o actualizar el archivo pg_control o crear un nuevo archivo de WAL. |
DSMRegistry | Esperando para leer o actualizar el registro de memoria compartida dinámica. |
DSMRegistryDSA | Esperando acceder al asignador de memoria compartida dinámica del registro de memoria compartida dinámica. |
DSMRegistryHash | Esperando acceder a la tabla hash compartida del registro de memoria compartida dinámica. |
DynamicSharedMemoryControl | Esperando para leer o actualizar información de asignación de memoria compartida dinámica. |
InjectionPoint | Esperando para leer o actualizar información relacionada con puntos de inyección. |
LockFastPath | Esperando para leer o actualizar la información de bloqueo por vía rápida (fast-path) de un proceso. |
LockManager | Esperando para leer o actualizar información sobre bloqueos pesados (“heavyweight”). |
LogicalRepLauncherDSA | Esperando acceder al asignador de memoria compartida dinámica del lanzador de replicación lógica. |
LogicalRepLauncherHash | Esperando acceder a la tabla hash compartida del lanzador de replicación lógica. |
LogicalRepWorker | Esperando para leer o actualizar el estado de los trabajadores de replicación lógica. |
MultiXactGen | Esperando para leer o actualizar el estado compartido de multixact. |
MultiXactMemberBuffer | Esperando E/S en un búfer SLRU de miembros de multixact. |
MultiXactMemberSLRU | Esperando acceder a la caché SLRU de miembros de multixact. |
MultiXactOffsetBuffer | Esperando E/S en un búfer SLRU de desplazamientos (offsets) de multixact. |
MultiXactOffsetSLRU | Esperando acceder a la caché SLRU de desplazamientos de multixact. |
MultiXactTruncation | Esperando para leer o truncar información de multixact. |
NotifyBuffer | Esperando E/S en un búfer SLRU de mensajes de NOTIFY. |
NotifyQueue | Esperando para leer o actualizar mensajes de NOTIFY. |
NotifyQueueTail | Esperando para actualizar el límite en el almacenamiento de mensajes de NOTIFY. |
NotifySLRU | Esperando acceder a la caché SLRU de mensajes de NOTIFY. |
OidGen | Esperando para asignar un nuevo OID. |
ParallelAppend | Esperando elegir el siguiente subplan durante la ejecución del plan Parallel Append. |
ParallelBtreeScan | Esperando sincronizar trabajadores durante la ejecución del plan de escaneo de árbol B paralelo. |
ParallelHashJoin | Esperando sincronizar trabajadores durante la ejecución del plan de unión hash paralela (Parallel Hash Join). |
ParallelQueryDSA | Esperando la asignación de memoria compartida dinámica para la consulta paralela. |
ParallelVacuumDSA | Esperando la asignación de memoria compartida dinámica para el vacuum paralelo. |
PerSessionDSA | Esperando la asignación de memoria compartida dinámica para la consulta paralela. |
PerSessionRecordType | Esperando acceder a la información de una consulta paralela sobre tipos compuestos. |
PerSessionRecordTypmod | Esperando acceder a la información de una consulta paralela sobre modificadores de tipo que identifican tipos de registros anónimos. |
PerXactPredicateList | Esperando acceder a la lista de bloqueos de predicado mantenidos por la transacción serializable actual durante una consulta paralela. |
PgStatsData | Esperando el acceso a los datos de estadísticas en memoria compartida. |
PgStatsDSA | Esperando el acceso al asignador de memoria compartida dinámica para estadísticas. |
PgStatsHash | Esperando el acceso a la tabla hash de estadísticas en memoria compartida. |
PredicateLockManager | Esperando acceder a la información de bloqueos de predicado utilizada por transacciones serializables. |
ProcArray | Esperando acceder a las estructuras de datos compartidas por proceso (normalmente, para obtener una instantánea o informar el ID de transacción de una sesión). |
RelationMapping | Esperando para leer o actualizar un archivo pg_filenode.map (utilizado para rastrear las asignaciones de filenode de ciertos catálogos del sistema). |
RelCacheInit | Esperando para leer o actualizar un archivo de inicialización de caché de relaciones pg_internal.init. |
ReplicationOrigin | Esperando para crear, eliminar o usar un origen de replicación. |
ReplicationOriginState | Esperando para leer o actualizar el progreso de un origen de replicación. |
ReplicationSlotAllocation | Esperando para asignar o liberar una ranura de replicación. |
ReplicationSlotControl | Esperando para leer o actualizar el estado de la ranura de replicación. |
ReplicationSlotIO | Esperando E/S en una ranura de replicación. |
SerialBuffer | Esperando E/S en un búfer SLRU de conflictos de transacciones serializables. |
SerialControl | Esperando para leer o actualizar el estado compartido de pg_serial. |
SerializableFinishedList | Esperando acceder a la lista de transacciones serializables finalizadas. |
SerializablePredicateList | Esperando acceder a la lista de bloqueos de predicado mantenidos por transacciones serializables. |
SerializableXactHash | Esperando para leer o actualizar información sobre transacciones serializables. |
SerialSLRU | Esperando acceder a la caché SLRU de conflictos de transacciones serializables. |
SharedTidBitmap | Esperando acceder a un mapa de bits TID compartido durante un escaneo de índice de mapa de bits paralelo. |
SharedTupleStore | Esperando acceder a un almacén de tuplas compartido durante una consulta paralela. |
ShmemIndex | Esperando encontrar o asignar espacio en la memoria compartida. |
SInvalRead | Esperando recuperar mensajes de la cola de invalidación del catálogo compartida. |
SInvalWrite | Esperando agregar un mensaje a la cola de invalidación del catálogo compartida. |
SubtransBuffer | Esperando E/S en un búfer SLRU de subtransacciones. |
SubtransSLRU | Esperando acceder a la caché SLRU de subtransacciones. |
SyncRep | Esperando para leer o actualizar información sobre el estado de la replicación síncrona. |
SyncScan | Esperando seleccionar la ubicación inicial de un escaneo de tabla sincronizado. |
TablespaceCreate | Esperando para crear o eliminar un tablespace. |
TwoPhaseState | Esperando para leer o actualizar el estado de las transacciones preparadas. |
WaitEventCustom | Esperando para leer o actualizar la información de eventos de espera personalizados. |
WALBufMapping | Esperando reemplazar una página en los buffers de WAL. |
WALInsert | Esperando para insertar datos WAL en un búfer de memoria. |
WALSummarizer | Esperando para leer o actualizar el estado de la resumulación de WAL. |
WALWrite | Esperando a que los buffers de WAL se escriban en el disco. |
WrapLimitsVacuum | Esperando actualizar los límites en el ID de transacción y el consumo de multixact. |
XactBuffer | Esperando E/S en un búfer SLRU de estado de transacción. |
XactSLRU | Esperando acceder a la caché SLRU de estado de transacción. |
XactTruncation | Esperando para ejecutar pg_xact_status o actualizar el ID de transacción más antiguo disponible para ella. |
XidGen | Esperando para asignar un nuevo ID de transacción. |
Table 27.13. Eventos de espera de tipo Timeout
Evento de espera de Timeout | Descripción |
|---|---|
BaseBackupThrottle | Esperando durante la copia de seguridad base al regular la actividad. |
CheckpointWriteDelay | Esperando entre escrituras mientras se realiza un punto de control. |
PgSleep | Esperando debido a una llamada a pg_sleep o a una función hermana. |
RecoveryApplyDelay | Esperando para aplicar WAL durante la recuperación debido a una configuración de retraso. |
RecoveryRetrieveRetryInterval | Esperando durante la recuperación cuando los datos WAL no están disponibles en ninguna fuente (pg_wal, archivo o flujo). |
RegisterSyncRequest | Esperando mientras se envían solicitudes de sincronización al checkpointer, porque la cola de solicitudes está llena. |
SpinDelay | Esperando mientras se adquiere un spinlock con contención. |
VacuumDelay | Esperando en un punto de retraso de vacuum basado en costos. |
VacuumTruncate | Esperando adquirir un bloqueo exclusivo para truncar las páginas vacías al final de una tabla que ha sido objeto de vacuum. |
WalSummarizerError | Esperando después de un error del resumidor de WAL. |
Here are examples of how wait events can be viewed:
SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event is NOT NULL; pid | wait_event_type | wait_event ------+-----------------+------------ 2540 | Lock | relation 6644 | LWLock | ProcArray (2 rows)
SELECT a.pid, a.wait_event, w.description
FROM pg_stat_activity a JOIN
pg_wait_events w ON (a.wait_event_type = w.type AND
a.wait_event = w.name)
WHERE a.wait_event is NOT NULL and a.state = 'active';
-[ RECORD 1 ]------------------------------------------------------------------
pid | 686674
wait_event | WALInitSync
description | Waiting for a newly initialized WAL file to reach durable storage
Extensions can add Extension,
InjectionPoint, and LWLock events
to the lists shown in Table 27.8 and
Table 27.12. In some cases, the name
of an LWLock assigned by an extension will not be
available in all server processes. It might be reported as just
“extension” rather than the
extension-assigned name.
pg_stat_replication #
The pg_stat_replication view will contain one row
per WAL sender process, showing statistics about replication to that
sender's connected standby server. Only directly connected standbys are
listed; no information is available about downstream standby servers.
Table 27.14. pg_stat_replication View
Column Type Description |
|---|
Process ID of a WAL sender process |
OID of the user logged into this WAL sender process |
Name of the user logged into this WAL sender process |
Name of the application that is connected to this WAL sender |
IP address of the client connected to this WAL sender. If this field is null, it indicates that the client is connected via a Unix socket on the server machine. |
Host name of the connected client, as reported by a
reverse DNS lookup of |
TCP port number that the client is using for communication
with this WAL sender, or |
Time when this process was started, i.e., when the client connected to this WAL sender |
This standby's |
Current WAL sender state. Possible values are:
|
Last write-ahead log location sent on this connection |
Last write-ahead log location written to disk by this standby server |
Last write-ahead log location flushed to disk by this standby server |
Last write-ahead log location replayed into the database on this standby server |
Time elapsed between flushing recent WAL locally and receiving
notification that this standby server has written it (but not yet
flushed it or applied it). This can be used to gauge the delay that
|
Time elapsed between flushing recent WAL locally and receiving
notification that this standby server has written and flushed it
(but not yet applied it). This can be used to gauge the delay that
|
Time elapsed between flushing recent WAL locally and receiving
notification that this standby server has written, flushed and
applied it. This can be used to gauge the delay that
|
Priority of this standby server for being chosen as the synchronous standby in a priority-based synchronous replication. This has no effect in a quorum-based synchronous replication. |
Synchronous state of this standby server. Possible values are:
|
Send time of last reply message received from standby server |
The lag times reported in the pg_stat_replication
view are measurements of the time taken for recent WAL to be written,
flushed and replayed and for the sender to know about it. These times
represent the commit delay that was (or would have been) introduced by each
synchronous commit level, if the remote server was configured as a
synchronous standby. For an asynchronous standby, the
replay_lag column approximates the delay
before recent transactions became visible to queries. If the standby
server has entirely caught up with the sending server and there is no more
WAL activity, the most recently measured lag times will continue to be
displayed for a short time and then show NULL.
Lag times work automatically for physical replication. Logical decoding plugins may optionally emit tracking messages; if they do not, the tracking mechanism will simply display NULL lag.
The reported lag times are not predictions of how long it will take for
the standby to catch up with the sending server assuming the current
rate of replay. Such a system would show similar times while new WAL is
being generated, but would differ when the sender becomes idle. In
particular, when the standby has caught up completely,
pg_stat_replication shows the time taken to
write, flush and replay the most recent reported WAL location rather than
zero as some users might expect. This is consistent with the goal of
measuring synchronous commit and transaction visibility delays for
recent write transactions.
To reduce confusion for users expecting a different model of lag, the
lag columns revert to NULL after a short time on a fully replayed idle
system. Monitoring systems should choose whether to represent this
as missing data, zero or continue to display the last known value.
pg_stat_replication_slots #
The pg_stat_replication_slots view will contain
one row per logical replication slot, showing statistics about its usage.
Table 27.15. pg_stat_replication_slots View
Column Type Description |
|---|
A unique, cluster-wide identifier for the replication slot |
Number of transactions spilled to disk once the memory used by
logical decoding to decode changes from WAL has exceeded
|
Number of times transactions were spilled to disk while decoding changes from WAL for this slot. This counter is incremented each time a transaction is spilled, and the same transaction may be spilled multiple times. |
Amount of decoded transaction data spilled to disk while performing
decoding of changes from WAL for this slot. This and other spill
counters can be used to gauge the I/O which occurred during logical
decoding and allow tuning |
Number of in-progress transactions streamed to the decoding output
plugin after the memory used by logical decoding to decode changes
from WAL for this slot has exceeded
|
Número de veces que las transacciones en curso se transmitieron al plugin de salida de decodificación al decodificar los cambios del WAL para este slot. Este contador se incrementa cada vez que se transmite una transacción, y la misma transacción puede transmitirse varias veces. |
Cantidad de datos de transacción decodificados para la transmisión de transacciones en
curso al plugin de salida de decodificación al decodificar los cambios del
WAL para este slot. Este y otros contadores de transmisión para este slot se
pueden utilizar para ajustar |
Número de transacciones decodificadas enviadas al plugin de salida de decodificación para este slot. Esto cuenta solo transacciones de nivel superior, y no se incrementa para subtransacciones. Ten en cuenta que esto incluye las transacciones que se transmiten y/o se descargan (spilled). |
Cantidad de datos de transacciones decodificados para enviar transacciones al plugin de salida de decodificación al decodificar los cambios del WAL para este slot. Ten en cuenta que esto incluye los datos que se transmiten y/o se descargan. |
Hora en la que estas estadísticas se restablecieron por última vez |
pg_stat_wal_receiver #
La vista pg_stat_wal_receiver contendrá solo
una fila, mostrando estadísticas sobre el receptor de WAL (WAL receiver) desde el
servidor conectado a ese receptor.
Table 27.16. Vista pg_stat_wal_receiver
Tipo de columna Descripción |
|---|
ID de proceso del proceso receptor de WAL |
Estado de actividad del proceso receptor de WAL |
Primera ubicación del registro de escritura anticipada (WAL) utilizada cuando se inicia el receptor de WAL |
Primer número de línea de tiempo (timeline) utilizado cuando se inicia el receptor de WAL |
Última ubicación del registro de escritura anticipada (WAL) ya recibida y escrita en disco, pero no vaciada. Esto no debe usarse para comprobaciones de integridad de datos. |
Última ubicación del registro de escritura anticipada (WAL) ya recibida y vaciada a disco, siendo el valor inicial de este campo la primera ubicación de registro utilizada cuando se inicia el receptor de WAL |
Número de línea de tiempo (timeline) de la última ubicación del registro de escritura anticipada (WAL) recibida y vaciada a disco, siendo el valor inicial de este campo el número de línea de tiempo de la primera ubicación de registro utilizada cuando se inicia el receptor de WAL |
Hora de envío del último mensaje recibido desde el emisor de WAL (WAL sender) de origen |
Hora de recepción del último mensaje recibido desde el emisor de WAL de origen |
Última ubicación del registro de escritura anticipada (WAL) reportada al emisor de WAL de origen |
Hora de la última ubicación del registro de escritura anticipada (WAL) reportada al emisor de WAL de origen |
Nombre del slot de replicación utilizado por este receptor de WAL |
Host de la instancia de PostgreSQL a la que está conectado
este receptor de WAL. Puede ser un nombre de host, una dirección IP o una ruta de directorio
si la conexión es a través de un socket Unix. (El caso de la ruta se puede distinguir
porque siempre será una ruta absoluta, que comienza con |
Número de puerto de la instancia de PostgreSQL a la que está conectado este receptor de WAL. |
Cadena de conexión utilizada por este receptor de WAL, con los campos sensibles a la seguridad ocultos. |
pg_stat_recovery_prefetch #
La vista pg_stat_recovery_prefetch contendrá
una sola fila. Las columnas wal_distance,
block_distance e
io_depth muestran valores actuales, y las
otras columnas muestran contadores acumulativos que se pueden restablecer
con la función pg_stat_reset_shared.
Table 27.17. Vista pg_stat_recovery_prefetch
Tipo de columna Descripción |
|---|
|
Hora en la que estas estadísticas se restablecieron por última vez |
|
Número de bloques precargados (prefetched) porque no estaban en el pool de búferes (buffer pool) |
|
Número de bloques no precargados porque ya estaban en el pool de búferes |
|
Número de bloques no precargados porque se inicializarían a cero |
|
Número de bloques no precargados porque aún no existían |
|
Número de bloques no precargados porque se incluyó una imagen de página completa en el WAL |
|
Número de bloques no precargados porque ya se habían precargado recientemente |
|
Cuántos bytes por delante está buscando el prefetcher |
|
Cuántos bloques por delante está buscando el prefetcher |
|
Cuántas precargas se han iniciado pero aún no se sabe si se han completado |
pg_stat_subscription #Table 27.18. Vista pg_stat_subscription
Columna Tipo Descripción |
|---|
OID de la suscripción |
Nombre de la suscripción |
Tipo de proceso worker de la suscripción. Los tipos posibles son
|
ID de proceso del proceso worker de la suscripción |
ID de proceso del worker apply líder si este proceso es un worker parallel apply; NULL si este proceso es un worker apply líder o un worker de table synchronization |
OID de la relación que el worker está sincronizando; NULL para el worker apply líder y los workers parallel apply |
Última ubicación del write-ahead log recibida, siendo 0 el valor inicial de este campo; NULL para los workers parallel apply |
Hora de envío del último mensaje recibido desde el WAL sender de origen; NULL para los workers parallel apply |
Hora de recepción del último mensaje recibido desde el WAL sender de origen; NULL para los workers parallel apply |
Última ubicación del write-ahead log reportada al WAL sender de origen; NULL para los workers parallel apply |
Hora de la última ubicación del write-ahead log reportada al WAL sender de origen; NULL para los workers parallel apply |
pg_stat_subscription_stats #
La vista pg_stat_subscription_stats contendrá
una fila por suscripción.
Table 27.19. Vista pg_stat_subscription_stats
Columna Tipo Descripción |
|---|
OID de la suscripción |
Nombre de la suscripción |
Número de veces que ocurrió un error al aplicar cambios. Ten en cuenta que cualquier
conflicto que resulte en un error de aplicación se contará tanto en
|
Número de veces que ocurrió un error durante la sincronización inicial de tablas |
Número de veces que la inserción de una fila violó una restricción única
|
Número de veces que se aplicó una actualización a una fila que había sido modificada previamente por otra fuente durante la aplicación de cambios. Consulta la update_origin_differs para obtener detalles sobre este conflicto. |
Número de veces que un valor de fila actualizado violó una restricción única
|
Número de veces que no se encontró la tupla a actualizar durante la aplicación de cambios. Consulta la update_missing para obtener detalles sobre este conflicto. |
Número de veces que se aplicó una operación de eliminación a una fila que había sido modificada previamente por otra fuente durante la aplicación de cambios. Consulta la delete_origin_differs para obtener detalles sobre este conflicto. |
Número de veces que no se encontró la tupla a eliminar durante la aplicación de cambios. Consulta la delete_missing para obtener detalles sobre este conflicto. |
Número de veces que la inserción de una fila o los valores de una fila actualizada
violaron múltiples restricciones únicas |
Momento en el que se reiniciaron estas estadísticas por última vez |
pg_stat_ssl #
La vista pg_stat_ssl contendrá una fila por
proceso de backend o WAL sender, mostrando estadísticas sobre el uso de SSL en
esta conexión. Se puede asociar con pg_stat_activity
o pg_stat_replication en la columna
pid para obtener más detalles sobre la
conexión.
Table 27.20. Vista pg_stat_ssl
Columna Tipo Descripción |
|---|
ID de proceso de un proceso de backend o WAL sender |
True si se usa SSL en esta conexión |
Versión de SSL en uso, o NULL si no se usa SSL en esta conexión |
Nombre del cifrado SSL en uso, o NULL si no se usa SSL en esta conexión |
Número de bits en el algoritmo de cifrado utilizado, o NULL si no se usa SSL en esta conexión |
Campo Distinguished Name (DN) del certificado del cliente
utilizado, o NULL si no se proporcionó ningún certificado de cliente o si SSL
no está en uso en esta conexión. Este campo se trunca si el
campo DN es más largo que |
Número de serie del certificado del cliente, o NULL si no se proporcionó ningún certificado de cliente o si SSL no está en uso en esta conexión. La combinación del número de serie del certificado y el emisor del certificado identifica de forma única un certificado (a menos que el emisor reutilice erróneamente los números de serie). |
DN del emisor del certificado del cliente, o NULL si no se proporcionó ningún
certificado de cliente o si SSL no está en uso en esta conexión.
Este campo se trunca al igual que |
pg_stat_gssapi #
La vista pg_stat_gssapi contendrá una fila por
backend, mostrando información sobre el uso de GSSAPI en esta conexión. Se puede
asociar con pg_stat_activity o
pg_stat_replication en la columna
pid para obtener más detalles sobre la
conexión.
Table 27.21. Vista pg_stat_gssapi
Columna Tipo Descripción |
|---|
ID de proceso de un backend |
True si se usó la autenticación GSSAPI para esta conexión |
Principal utilizado para autenticar esta conexión, o NULL
si no se usó GSSAPI para autenticar esta conexión. Este
campo se trunca si el principal es más largo que
|
True si el cifrado GSSAPI está en uso en esta conexión |
True si las credenciales GSSAPI fueron delegadas en esta conexión. |
pg_stat_archiver #
La vista pg_stat_archiver siempre tendrá una
única fila, que contiene datos sobre el proceso archiver del cluster.
Table 27.22. Vista pg_stat_archiver
Columna Tipo Descripción |
|---|
Número de archivos WAL que se han archivado correctamente |
Nombre del archivo WAL archivado correctamente más reciente |
Hora de la operación de archivado exitosa más reciente |
Número de intentos fallidos de archivado de archivos WAL |
Nombre del archivo WAL de la operación de archivado fallida más reciente |
Hora de la operación de archivado fallida más reciente |
Momento en el que se reiniciaron estas estadísticas por última vez |
Normalmente, los archivos WAL se archivan en orden, del más antiguo al más nuevo, pero eso
no está garantizado y no se cumple en circunstancias especiales como al
promover un standby o después de la recuperación de una caída. Por lo tanto, no es seguro
asumir que todos los archivos anteriores a
last_archived_wal también se han archivado correctamente.
pg_stat_io #
The pg_stat_io view will contain one row for each
combination of backend type, target I/O object, and I/O context, showing
cluster-wide I/O statistics. Combinations which do not make sense are
omitted.
Currently, I/O on relations (e.g. tables, indexes) and WAL activity are tracked. However, relation I/O which bypasses shared buffers (e.g. when moving a table from one tablespace to another) is currently not tracked.
Table 27.23. pg_stat_io View
|
Column Type Description |
|---|
|
Type of backend (e.g. background worker, autovacuum worker). See
|
|
Target object of an I/O operation. Possible values are:
|
|
The context of an I/O operation. Possible values are:
|
|
Number of read operations. |
|
The total size of read operations in bytes. |
|
Time spent waiting for read operations in milliseconds (if
track_io_timing is enabled and
|
|
Number of write operations. |
|
The total size of write operations in bytes. |
|
Time spent waiting for write operations in milliseconds (if
track_io_timing is enabled and
|
|
Number of units of size |
|
Time spent waiting for writeback operations in milliseconds (if track_io_timing is enabled, otherwise zero). This includes the time spent queueing write-out requests and, potentially, the time spent to write out the dirty data. |
|
Number of relation extend operations. |
|
The total size of relation extend operations in bytes. |
|
Time spent waiting for extend operations in milliseconds. (if
track_io_timing is enabled and
|
|
The number of times a desired block was found in a shared buffer. |
|
Number of times a block has been written out from a shared or local buffer in order to make it available for another use.
In |
|
The number of times an existing buffer in a size-limited ring buffer
outside of shared buffers was reused as part of an I/O operation in the
|
|
Number of |
|
Time spent waiting for fsync operations in milliseconds (if
track_io_timing is enabled and
|
|
Time at which these statistics were last reset. |
Some backend types never perform I/O operations on some I/O objects and/or
in some I/O contexts. These rows are omitted from the view. For example, the
checkpointer does not checkpoint temporary tables, so there will be no rows
for backend_type checkpointer and
object temp relation.
In addition, some I/O operations will never be performed either by certain
backend types or on certain I/O objects and/or in certain I/O contexts.
These cells will be NULL. For example, temporary tables are not
fsynced, so fsyncs will be NULL for
object temp relation. Also, the
background writer does not perform reads, so reads will
be NULL in rows for backend_type background
writer.
For the object wal,
fsyncs and fsync_time track the
fsync activity of WAL files done in issue_xlog_fsync.
writes and write_time
track the write activity of WAL files done in
XLogWrite.
See Section 28.5 for more information.
pg_stat_io can be used to inform database tuning.
For example:
A high evictions count can indicate that shared
buffers should be increased.
Client backends rely on the checkpointer to ensure data is persisted to
permanent storage. Large numbers of fsyncs by
client backends could indicate a misconfiguration of
shared buffers or of the checkpointer. More information on configuring
the checkpointer can be found in Section 28.5.
Normally, client backends should be able to rely on auxiliary processes like the checkpointer and the background writer to write out dirty data as much as possible. Large numbers of writes by client backends could indicate a misconfiguration of shared buffers or of the checkpointer. More information on configuring the checkpointer can be found in Section 28.5.
Columns tracking I/O wait time will only be non-zero when
track_io_timing is enabled. The user should be
careful when referencing these columns in combination with their
corresponding I/O operations in case track_io_timing
was not enabled for the entire time since the last stats reset.
pg_stat_bgwriter #
The pg_stat_bgwriter view will always have a
single row, containing data about the background writer of the cluster.
Table 27.24. pg_stat_bgwriter View
Column Type Description |
|---|
Number of buffers written by the background writer |
Number of times the background writer stopped a cleaning scan because it had written too many buffers |
Number of buffers allocated |
Time at which these statistics were last reset |
pg_stat_checkpointer #
The pg_stat_checkpointer view will always have a
single row, containing data about the checkpointer process of the cluster.
Table 27.25. pg_stat_checkpointer View
Column Type Description |
|---|
Number of scheduled checkpoints due to timeout |
Number of requested checkpoints |
Number of checkpoints that have been performed |
Number of scheduled restartpoints due to timeout or after a failed attempt to perform it |
Number of requested restartpoints |
Number of restartpoints that have been performed |
Total amount of time that has been spent in the portion of processing checkpoints and restartpoints where files are written to disk, in milliseconds |
Total amount of time that has been spent in the portion of processing checkpoints and restartpoints where files are synchronized to disk, in milliseconds |
Number of shared buffers written during checkpoints and restartpoints |
Number of SLRU buffers written during checkpoints and restartpoints |
Time at which these statistics were last reset |
Checkpoints may be skipped if the server has been idle since the last one.
num_timed and
num_requested count both completed and skipped
checkpoints, while num_done tracks only
the completed ones. Similarly, restartpoints may be skipped
if the last replayed checkpoint record is already the last restartpoint.
restartpoints_timed and
restartpoints_req count both completed and
skipped restartpoints, while restartpoints_done
tracks only the completed ones.
pg_stat_wal #
The pg_stat_wal view will always have a
single row, containing data about WAL activity of the cluster.
Table 27.26. pg_stat_wal View
Column Type Description |
|---|
Total number of WAL records generated |
Total number of WAL full page images generated |
Total amount of WAL generated in bytes |
Number of times WAL data was written to disk because WAL buffers became full |
Time at which these statistics were last reset |
pg_stat_database #
The pg_stat_database view will contain one row
for each database in the cluster, plus one for shared objects, showing
database-wide statistics.
Table 27.27. pg_stat_database View
Column Type Description |
|---|
OID of this database, or 0 for objects belonging to a shared relation |
Name of this database, or |
Number of backends currently connected to this database, or
|
Number of transactions in this database that have been committed |
Number of transactions in this database that have been rolled back |
Number of disk blocks read in this database |
Number of times disk blocks were found already in the buffer cache, so that a read was not necessary (this only includes hits in the PostgreSQL buffer cache, not the operating system's file system cache) |
Number of live rows fetched by sequential scans and index entries returned by index scans in this database |
Number of live rows fetched by index scans in this database |
Number of rows inserted by queries in this database |
Number of rows updated by queries in this database |
Number of rows deleted by queries in this database |
Number of queries canceled due to conflicts with recovery
in this database. (Conflicts occur only on standby servers; see
|
Number of temporary files created by queries in this database. All temporary files are counted, regardless of why the temporary file was created (e.g., sorting or hashing), and regardless of the log_temp_files setting. |
Total amount of data written to temporary files by queries in this database. All temporary files are counted, regardless of why the temporary file was created, and regardless of the log_temp_files setting. |
Number of deadlocks detected in this database |
Number of data page checksum failures detected in this database (or on a shared object), or NULL if data checksums are disabled. |
Time at which the last data page checksum failure was detected in this database (or on a shared object), or NULL if data checksums are disabled. |
Time spent reading data file blocks by backends in this database, in milliseconds (if track_io_timing is enabled, otherwise zero) |
Time spent writing data file blocks by backends in this database, in milliseconds (if track_io_timing is enabled, otherwise zero) |
Time spent by database sessions in this database, in milliseconds (note that statistics are only updated when the state of a session changes, so if sessions have been idle for a long time, this idle time won't be included) |
Time spent executing SQL statements in this database, in milliseconds
(this corresponds to the states |
Time spent idling while in a transaction in this database, in milliseconds
(this corresponds to the states |
Total number of sessions established to this database |
Number of database sessions to this database that were terminated because connection to the client was lost |
Number of database sessions to this database that were terminated by fatal errors |
Number of database sessions to this database that were terminated by operator intervention |
Number of parallel workers planned to be launched by queries on this database |
Number of parallel workers launched by queries on this database |
Time at which these statistics were last reset |
pg_stat_database_conflicts #
The pg_stat_database_conflicts view will contain
one row per database, showing database-wide statistics about
query cancels occurring due to conflicts with recovery on standby servers.
This view will only contain information on standby servers, since
conflicts do not occur on primary servers.
Table 27.28. pg_stat_database_conflicts View
Column Type Description |
|---|
OID of a database |
Name of this database |
Number of queries in this database that have been canceled due to dropped tablespaces |
Number of queries in this database that have been canceled due to lock timeouts |
Number of queries in this database that have been canceled due to old snapshots |
Number of queries in this database that have been canceled due to pinned buffers |
Number of queries in this database that have been canceled due to deadlocks |
Number of uses of logical slots in this database that have been canceled due to old snapshots or too low a wal_level on the primary |
pg_stat_all_tables #
The pg_stat_all_tables view will contain
one row for each table in the current database (including TOAST
tables), showing statistics about accesses to that specific table. The
pg_stat_user_tables and
pg_stat_sys_tables views
contain the same information,
but filtered to only show user and system tables respectively.
Table 27.29. pg_stat_all_tables View
Column Type Description |
|---|
OID of a table |
Name of the schema that this table is in |
Name of this table |
Number of sequential scans initiated on this table |
The time of the last sequential scan on this table, based on the most recent transaction stop time |
Number of live rows fetched by sequential scans |
Number of index scans initiated on this table |
The time of the last index scan on this table, based on the most recent transaction stop time |
Number of live rows fetched by index scans |
Total number of rows inserted |
Total number of rows updated. (This includes row updates
counted in |
Total number of rows deleted |
Number of rows HOT updated. These are updates where no successor versions are required in indexes. |
Number of rows updated where the successor version goes onto a
new heap page, leaving behind an original
version with a
|
Estimated number of live rows |
Estimated number of dead rows |
Estimated number of rows modified since this table was last analyzed |
Estimated number of rows inserted since this table was last vacuumed
(not counting |
Last time at which this table was manually vacuumed
(not counting |
Last time at which this table was vacuumed by the autovacuum daemon |
Last time at which this table was manually analyzed |
Last time at which this table was analyzed by the autovacuum daemon |
Number of times this table has been manually vacuumed
(not counting |
Number of times this table has been vacuumed by the autovacuum daemon |
Number of times this table has been manually analyzed |
Number of times this table has been analyzed by the autovacuum daemon |
Total time this table has been manually vacuumed, in milliseconds
(not counting |
Total time this table has been vacuumed by the autovacuum daemon, in milliseconds. (This includes the time spent sleeping due to cost-based delays.) |
Total time this table has been manually analyzed, in milliseconds. (This includes the time spent sleeping due to cost-based delays.) |
Total time this table has been analyzed by the autovacuum daemon, in milliseconds. (This includes the time spent sleeping due to cost-based delays.) |
pg_stat_all_indexes #
The pg_stat_all_indexes view will contain
one row for each index in the current database,
showing statistics about accesses to that specific index. The
pg_stat_user_indexes and
pg_stat_sys_indexes views
contain the same information,
but filtered to only show user and system indexes respectively.
Table 27.30. pg_stat_all_indexes View
Column Type Description |
|---|
OID of the table for this index |
OID of this index |
Name of the schema this index is in |
Name of the table for this index |
Name of this index |
Number of index scans initiated on this index |
The time of the last scan on this index, based on the most recent transaction stop time |
Number of index entries returned by scans on this index |
Number of live table rows fetched by simple index scans using this index |
Indexes can be used by simple index scans, “bitmap” index scans,
and the optimizer. In a bitmap scan
the output of several indexes can be combined via AND or OR rules,
so it is difficult to associate individual heap row fetches
with specific indexes when a bitmap scan is used. Therefore, a bitmap
scan increments the
pg_stat_all_indexes.idx_tup_read
count(s) for the index(es) it uses, and it increments the
pg_stat_all_tables.idx_tup_fetch
count for the table, but it does not affect
pg_stat_all_indexes.idx_tup_fetch.
The optimizer also accesses indexes to check for supplied constants
whose values are outside the recorded range of the optimizer statistics
because the optimizer statistics might be stale.
The idx_tup_read and idx_tup_fetch counts
can be different even without any use of bitmap scans,
because idx_tup_read counts
index entries retrieved from the index while idx_tup_fetch
counts live rows fetched from the table. The latter will be less if any
dead or not-yet-committed rows are fetched using the index, or if any
heap fetches are avoided by means of an index-only scan.
Index scans may sometimes perform multiple index searches per execution.
Each index search increments pg_stat_all_indexes.idx_scan,
so it's possible for the count of index scans to significantly exceed the
total number of index scan executor node executions.
This can happen with queries that use certain SQL
constructs to search for rows matching any value out of a list or array of
multiple scalar values (see Section 9.25). It
can also happen to queries with a
column_name =
value1 OR
column_name =
value2 ...
EXPLAIN ANALYZE outputs the total number of index
searches performed by each index scan node. See
Section 14.1.2 for an example demonstrating how
this works.
pg_statio_all_tables #
The pg_statio_all_tables view will contain
one row for each table in the current database (including TOAST
tables), showing statistics about I/O on that specific table. The
pg_statio_user_tables and
pg_statio_sys_tables views
contain the same information,
but filtered to only show user and system tables respectively.
Table 27.31. pg_statio_all_tables View
Column Type Description |
|---|
OID of a table |
Name of the schema that this table is in |
Name of this table |
Number of disk blocks read from this table |
Number of buffer hits in this table |
Number of disk blocks read from all indexes on this table |
Number of buffer hits in all indexes on this table |
Number of disk blocks read from this table's TOAST table (if any) |
Number of buffer hits in this table's TOAST table (if any) |
Number of disk blocks read from this table's TOAST table indexes (if any) |
Number of buffer hits in this table's TOAST table indexes (if any) |
pg_statio_all_indexes #
The pg_statio_all_indexes view will contain
one row for each index in the current database,
showing statistics about I/O on that specific index. The
pg_statio_user_indexes and
pg_statio_sys_indexes views
contain the same information,
but filtered to only show user and system indexes respectively.
Table 27.32. pg_statio_all_indexes View
Column Type Description |
|---|
OID of the table for this index |
OID of this index |
Name of the schema this index is in |
Name of the table for this index |
Name of this index |
Number of disk blocks read from this index |
Number of buffer hits in this index |
pg_statio_all_sequences #
The pg_statio_all_sequences view will contain
one row for each sequence in the current database,
showing statistics about I/O on that specific sequence.
Table 27.33. pg_statio_all_sequences View
Column Type Description |
|---|
OID of a sequence |
Name of the schema this sequence is in |
Name of this sequence |
Number of disk blocks read from this sequence |
Number of buffer hits in this sequence |
pg_stat_user_functions #
The pg_stat_user_functions view will contain
one row for each tracked function, showing statistics about executions of
that function. The track_functions parameter
controls exactly which functions are tracked.
Table 27.34. pg_stat_user_functions View
Column Type Description |
|---|
OID of a function |
Name of the schema this function is in |
Name of this function |
Number of times this function has been called |
Total time spent in this function and all other functions called by it, in milliseconds |
Total time spent in this function itself, not including other functions called by it, in milliseconds |
pg_stat_slru #
PostgreSQL accesses certain on-disk information
via SLRU (simple least-recently-used)
caches.
The pg_stat_slru view will contain
one row for each tracked SLRU cache, showing statistics about access
to cached pages.
For each SLRU cache that's part of the core server,
there is a configuration parameter that controls its size, with the suffix
_buffers appended.
Table 27.35. pg_stat_slru View
Column Type Description |
|---|
Name of the SLRU |
Number of blocks zeroed during initializations |
Number of times disk blocks were found already in the SLRU, so that a read was not necessary (this only includes hits in the SLRU, not the operating system's file system cache) |
Number of disk blocks read for this SLRU |
Number of disk blocks written for this SLRU |
Number of blocks checked for existence for this SLRU |
Number of flushes of dirty data for this SLRU |
Number of truncates for this SLRU |
Time at which these statistics were last reset |
Other ways of looking at the statistics can be set up by writing
queries that use the same underlying statistics access functions used by
the standard views shown above. For details such as the functions' names,
consult the definitions of the standard views. (For example, in
psql you could issue \d+ pg_stat_activity.)
The access functions for per-database statistics take a database OID as an
argument to identify which database to report on.
The per-table and per-index functions take a table or index OID.
The functions for per-function statistics take a function OID.
Note that only tables, indexes, and functions in the current database
can be seen with these functions.
Additional functions related to the cumulative statistics system are listed in Table 27.36.
Table 27.36. Additional Statistics Functions
Using pg_stat_reset() also resets counters that
autovacuum uses to determine when to trigger a vacuum or an analyze.
Resetting these counters can cause autovacuum to not perform necessary
work, which can cause problems such as table bloat or out-dated
table statistics. A database-wide ANALYZE is
recommended after the statistics have been reset.
pg_stat_get_activity, the underlying function of
the pg_stat_activity view, returns a set of records
containing all the available information about each backend process.
Sometimes it may be more convenient to obtain just a subset of this
information. In such cases, another set of per-backend statistics
access functions can be used; these are shown in Table 27.37.
These access functions use the session's backend ID number, which is a
small integer (>= 0) that is distinct from the backend ID of any
concurrent session, although a session's ID can be recycled as soon as
it exits. The backend ID is used, among other things, to identify the
session's temporary schema if it has one.
The function pg_stat_get_backend_idset provides a
convenient way to list all the active backends' ID numbers for
invoking these functions. For example, to show the PIDs and
current queries of all backends:
SELECT pg_stat_get_backend_pid(backendid) AS pid,
pg_stat_get_backend_activity(backendid) AS query
FROM pg_stat_get_backend_idset() AS backendid;
Table 27.37. Per-Backend Statistics Functions
Function Description |
|---|
Returns the text of this backend's most recent query. |
Returns the time when the backend's most recent query was started. |
Returns the IP address of the client connected to this backend. |
Returns the TCP port number that the client is using for communication. |
Returns the OID of the database this backend is connected to. |
Returns the set of currently active backend ID numbers. |
Returns the process ID of this backend. |
Returns the time when this process was started. |
Returns a record of information about the subtransactions of the
backend with the specified ID.
The fields returned are |
Returns the OID of the user logged into this backend. |
Returns the wait event name if this backend is currently waiting, otherwise NULL. See Table 27.5 through Table 27.13. |
Returns the wait event type name if this backend is currently waiting, otherwise NULL. See Table 27.4 for details. |
Returns the time when the backend's current transaction was started. |