Как найти последний раз, когда была обновлена ​​база данных PostgreSQL?

Ответ 1

Вы можете написать trigger для запуска каждый раз, когда вставка/обновление производится в конкретной таблице. Общее использование заключается в том, чтобы установить столбец "created" или "last_updated" строки в текущее время, но вы также можете обновить время в центральном местоположении, если вы не хотите изменять существующие таблицы.

Так, например, типичным способом является следующий:

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();

Затем, чтобы найти последнее время обновления, вам нужно выбрать "MAX (last_updated)" из каждой таблицы, которую вы отслеживаете, и взять наибольшее из них, например:

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

Для таблиц с последовательным (или аналогично сгенерированным) первичным ключом вы можете попытаться избежать последовательного сканирования, чтобы найти последнее время обновления, используя индекс первичного ключа, или вы создаете индексы на last_updated.

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

Обратите внимание, что это может дать несколько неправильные результаты в случае, если идентификаторы не являются достаточно последовательными, но насколько вам нужна точность? (Имейте в виду, что транзакции означают, что строки могут стать видимыми вам в другом порядке для их создания.)

Альтернативный подход, чтобы избежать добавления "обновленных" столбцов в каждую таблицу, - это иметь центральную таблицу для хранения временных меток времени обновления. Например:

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();

Это даст вам таблицу со строкой для каждого обновления таблицы: вы можете просто сделать:

SELECT MAX(updated) FROM update_log

Чтобы получить последнее время обновления. (Вы могли бы разделить это по таблице, если хотите). Разумеется, эта таблица будет постоянно расти: либо создайте индекс на "обновленный" (который должен сделать последнее очень быстрым), либо обрезайте его периодически, если это соответствует вашему используемому случаю (например, возьмите исключительную блокировку на столе, получить последнее время обновления, а затем усечь его, если вам нужно периодически проверять, были ли внесены изменения).

Альтернативный подход, который может быть тем, о чем говорят люди на форуме, - это установить "log_statement = mod" в конфигурации базы данных (глобально для кластера, или в базе данных или пользователя, которую нужно отслеживать), а затем все заявления, которые изменяют базу данных, будут записаны в журнал сервера. Затем вам нужно будет написать что-то за пределами базы данных, чтобы отсканировать журнал сервера, отфильтровать таблицы, которые вам не интересны, и т.д.

Ответ 2

Похоже, вы можете использовать pg_stat_database, чтобы получить счет транзакции и проверить, не изменилось ли это с одного резервного копирования на следующий - см. этот ответ dba.se и комментарии для более подробной информации

Ответ 3

Мне нравится подход Джека. Вы можете запросить статистику таблицы и узнать количество вставок, обновлений, удалений и так:

select n_tup_upd from pg_stat_user_tables  where relname = 'YOUR_TABLE';

каждое обновление увеличивает счет на 1.

В виду, что этот метод жизнеспособен, когда у вас есть одна БД. для нескольких экземпляров, вероятно, потребуется другой подход.

Ответ 4

См. следующую статью:

MySQL против PostgreSQL: добавление столбца "Последнее изменение времени" в таблицу http://www.pointbeing.net/weblog/2008/03/mysql-versus-postgresql-adding-a-last-modified-column-to-a-table.html

Ответ 5

Вы можете написать хранимую процедуру на "ненадежном языке" (например, plpythonu): Это позволяет получить доступ к файлам в "базовом" каталоге postgres. Верните большое количество файлов этих файлов в хранимую процедуру.

Но это только расплывчато, так как вакуум изменит эти файлы и mtime.