español - ¿Cómo encuentro la última vez que una base de datos PostgreSQL se ha actualizado?

postgresql español

Question

Estoy trabajando con una base de datos postgreSQL que se actualiza en lotes. Necesito saber cuándo se actualizará o modificará la última vez que la base de datos (o una tabla en la base de datos) funcionará.

Vi que alguien en el foro de postgeSQL había sugerido que usar el registro y consultar sus registros por el momento. Esto no funcionará para mí, ya que no tengo control sobre la base de código de los clientes.

Answer 1

Me gusta el enfoque de Jack. Puede consultar las estadísticas de la tabla y conocer el número de inserciones, actualizaciones, eliminaciones, etc.

select n_tup_upd from pg_stat_user_tables  where relname = ''YOUR_TABLE'';

Cada actualización aumentará el conteo en 1.

Ten en cuenta que este método es viable cuando tienes una base de datos única. Las instancias múltiples requerirán un enfoque diferente probablemente.

Answer 2

Parece que puede usar pg_stat_database para obtener un recuento de transacciones y verificar si esto cambia de una ejecución de copia de seguridad a la siguiente. Consulte la respuesta y los comentarios de dba.se para obtener más detalles.

Answer 3

Puede escribir un trigger para que se ejecute cada vez que se realice una inserción / actualización en una tabla en particular. El uso común es establecer una columna "creada" o "last_updated" de la fila a la hora actual, pero también puede actualizar la hora en una ubicación central si no desea cambiar las tablas existentes.

Así, por ejemplo, una forma típica es la siguiente:

CREATE FUNCTION stamp_updated() RETURNS TRIGGER LANGUAGE ''plpgsql'' AS $$
BEGIN
  NEW.last_updated := now();
  RETURN NEW;
END
$$;
-- repeat for each table you need to track:
ALTER TABLE sometable ADD COLUMN last_updated TIMESTAMP;
CREATE TRIGGER sometable_stamp_updated
  BEFORE INSERT OR UPDATE ON sometable
  FOR EACH ROW EXECUTE PROCEDURE stamp_updated();

Luego, para encontrar la última actualización, debe seleccionar "MAX (last_updated)" de cada tabla que está siguiendo y tomar la mejor de ellas, por ejemplo:

SELECT MAX(max_last_updated) FROM (
  SELECT MAX(last_updated) AS max_last_updated FROM sometable
  UNION ALL
  SELECT MAX(last_updated) FROM someothertable
) updates

Para las tablas con una clave principal en serie (o generada de manera similar), puede intentar evitar el escaneo secuencial para encontrar la última hora de actualización utilizando el índice de la clave primaria, o crear índices en last_updated.

-- get timestamp of row with highest id
SELECT last_updated FROM sometable ORDER BY sometable_id DESC LIMIT 1

Tenga en cuenta que esto puede dar resultados ligeramente erróneos en el caso de que las ID no sean muy secuenciales, pero ¿cuánta precisión necesita? (Tenga en cuenta que las transacciones significan que las filas pueden ser visibles para usted en un orden diferente al de las que se crean).

Un enfoque alternativo para evitar agregar columnas ''actualizadas'' a cada tabla es tener una tabla central para almacenar las marcas de tiempo de actualización en. Por ejemplo:

CREATE TABLE update_log(table_name text PRIMARY KEY, updated timestamp NOT NULL DEFAULT now());
CREATE FUNCTION stamp_update_log() RETURNS TRIGGER LANGUAGE ''plpgsql'' AS $$
BEGIN
  INSERT INTO update_log(table_name) VALUES(TG_TABLE_NAME);
  RETURN NEW;
END
$$;
-- Repeat for each table you need to track:
CREATE TRIGGER sometable_stamp_update_log
 AFTER INSERT OR UPDATE ON sometable
 FOR EACH STATEMENT EXECUTE stamp_update_log();

Esto le dará una tabla con una fila para cada actualización de la tabla: luego puede hacer:

SELECT MAX(updated) FROM update_log

Para obtener la última actualización. (Podrías dividir esto por tabla si quisieras). Esta tabla, por supuesto, seguirá creciendo: cree un índice en ''actualizado'' (que debería hacer que la última sea bastante rápido) o trundela periódicamente si se ajusta a su caso de uso (por ejemplo, tome un candado exclusivo en la mesa, obtenga la última hora de actualización, luego trúntela si necesita verificar periódicamente si se han realizado cambios).

Un enfoque alternativo, que podría ser lo que significó la gente en el foro, es establecer ''log_statement = mod'' en la configuración de la base de datos (ya sea globalmente para el clúster o en la base de datos o usuario que necesita rastrear) y luego todas las declaraciones que Modificar la base de datos se escribirá en el registro del servidor. Luego deberá escribir algo fuera de la base de datos para escanear el registro del servidor, filtrar las tablas que no le interesan, etc.

Answer 4

Puede escribir un procedimiento almacenado en un "idioma no confiable" (por ejemplo, plpythonu): Esto permite el acceso a los archivos en el directorio "base" de postgres. Devuelva el tiempo máximo de estos archivos en el procedimiento almacenado.

Pero esto es vago, ya que el vacío cambiará estos archivos y el mtime.

Answer 5

Vea el siguiente artículo:

MySQL versus PostgreSQL: Agregar una columna de ''Hora de última modificación'' a una tabla http://www.pointbeing.net/weblog/2008/03/mysql-versus-postgresql-adding-a-last-modified-column-to-a-table.html