PostgreSQL | 查LAST DDL TIME,PG的三種方法

2021-03-04 勵志成為PostgreSQL大神
前言

今天遇到一個小問題,如果有人 DDL修改了表(例如drop了一個索引後),如何查詢最後的last ddl time。原本以為這是個小問題,結果發現這個問題居然還有點小麻煩。

PG沒有LAST DDL TIME

對Oracle而言,當drop index命令在表上執行完畢後。將在dba_objects的LAST_DDL_TIME中記錄修改時間。但是,對於PostgreSQL,我在社區裡面搜索了相關的信息,發現它是不保存的。以下是兩個建議

DDL日誌

首先,我們嘗試一下DDL日誌,看能否解決這個問題。DDL日誌主要是參數log_statement控制,它有4個選項:none、ddl、mod、all。一般建議開啟ddl,它會記錄 create、alter和drop相關的語句,但不記錄truncate。truncate在文檔中屬於 mod。這倒是讓我有點驚訝。稍後我查了下stackoverflow,發現這主要是針對不同的資料庫有著不同的行為。truncate在Oracle中屬於DDL語句,在PostgreSQL中屬於DML語句。因此,當我們使用DDL日誌記錄語句時,無法記錄到Truncate。這個需要引起注意。因此,當有人通過truncate做了壞事,仍然是無法記錄的。

與DDL log相關的另一個參數是log_line_prefix,它可以幫助我們記錄附加的信息,例如應用程式名、客戶端IP位址等等。

postgres=# alter system set log_statement='ddl' ;
ALTER SYSTEM
postgres=# alter system set log_line_prefix='%t [%p]:user=%u,db=%d,app=%a,client=%r';
ALTER SYSTEM

修改完參數重啟一下資料庫。這裡配置了%t,執行時間。%p,進程ID。%u,用戶名。%d,資料庫名字。%a,應用程式名字。%r,遠程主機名或者ip地址,還有遠程的埠號。

然後我們來創建一張表,插入一些數據。

[postgres@freebsd-test ~]$ psql -U postgres -h 192.168.56.143 -p 5432 -W
Password: 
psql (13.2)
Type "help" for help.

postgres=# create table test(id numeric);
CREATE TABLE
postgres=# insert into test select generate_series(1,100000);
INSERT 0 100000

查看後臺log日誌。

2021-03-02 01:26:41 CST [8330]:user=postgres,db=postgres,app=psql,client=192.168.56.143(65217)LOG:  statement: create table test(id numeric); 

如果誰執行了DDL語句,可以完美的抓到,這個功能非常有用。讓我們試試刪除索引。

postgres=# create index idx_t1 on test(id);
CREATE INDEX
postgres=# drop index idx_t1;
DROP INDEX

2021-03-02 01:30:20 CST [8330]:user=postgres,db=postgres,app=psql,client=192.168.56.143(65217)LOG:  statement: create index idx_t1 on test(id);
2021-03-02 01:30:45 CST [8330]:user=postgres,db=postgres,app=psql,client=192.168.56.143(65217)LOG:  statement: drop index idx_t1;

現在誰執行 DDL語句就看得很清楚了。

事件觸發器

PostgreSQL從9.3版本開始就支持事件觸發器,現在支持的事件主要有ddl_command_start,ddl_command_end,table_rewrite和sql_drop。目前我們要實現的功能是 LAST DDLTIME。需要使用到DDL_COMMAND_START事件。

我們來簡單測試一下。

CREATE TABLE TAB_EVENT_LOGS(
  DATE_TIME TIMESTAMP,
  EVENT_NAME TEXT,
  REMARKS TEXT
);

CREATE OR REPLACE FUNCTION FN_LOG_EVENT() RETURNS EVENT_TRIGGER
  AS 
  $$
  BEGIN
    INSERT INTO TAB_EVENT_LOGS(DATE_TIME,EVENT_NAME,REMARKS) VALUES(NOW(),TG_TAG,'Event Logging');
  end;
  $$
LANGUAGE plpgsql;

CREATE EVENT TRIGGER TRG_LOG_EVENT ON DDL_COMMAND_START EXECUTE PROCEDURE FN_LOG_EVENT();

建立日誌表來存放DDL語句記錄,建立函數FN_LOG_EVENT來將資料插入到日誌表。接著指定DDL_COMMAND_START事件,執行FN_LOG_EVENT函數。

我們執行一些DDL語句。

postgres=# create table t(id numeric);
CREATE TABLE
postgres=# select * from TAB_EVENT_LOGS;
         date_time          |  event_name  |    remarks    
---+----+
 2021-03-02 04:16:42.910641 | CREATE TABLE | Event Logging
(1 row)

postgres=# alter table t add column name varchar(20); 
ALTER TABLE
postgres=# select * from TAB_EVENT_LOGS;              
         date_time          |  event_name  |    remarks    
---+----+
 2021-03-02 04:16:42.910641 | CREATE TABLE | Event Logging
 2021-03-02 04:18:08.580202 | ALTER TABLE  | Event Logging
(2 rows)

可見ddl操作已被完全記錄下來。在這裡,我只是簡單地記錄一下時間和DDL事件。也可以將用戶名、表名等記錄下來。更多高級用法需要查閱官方文檔 https://www.postgresql.org/docs/current/event-trigger-definition.html。

還有別的辦法嗎?

當然也有其他方法,這個方法就是開啟track_commit_timestamp,它可以用來跟蹤記錄事務提交的時間戳。在我們對表進行DDL操作的同時會對pg_class基表也進行更新。現在,我們可以跟蹤pg_class基表,查看對應數據行的 xmin值,然後將它轉換為時間。

postgres=# create table t(id numeric);
CREATE TABLE
postgres=# select pg_xact_commit_timestamp(xmin), oid, relname from pg_class where relname ='t';
   pg_xact_commit_timestamp    |  oid  | relname 
-+--+----
 2021-03-02 04:24:35.582231+08 | 16428 | t
(1 row)

下面為表t添加一列。再次查看pg_class表,會發現事務的xmin時間已經更新。

postgres=# alter table t add column name varchar(20); 
ALTER TABLE
postgres=# select pg_xact_commit_timestamp(xmin), oid, relname from pg_class where relname ='t';
   pg_xact_commit_timestamp    |  oid  | relname 
-+--+----
 2021-03-02 04:27:14.296483+08 | 16428 | t
(1 row)

使用此方法,我們實現了查看表的last ddl time。

尾聲

上述三種方式都屬於簡單好用的,如果想要追求源碼體驗,可以使用HOOK來實現。


相關焦點

  • PostgreSQL Timelines and History File:Master Guide
    , I have done a pointin time recovery to some point in 000000010000000000000007 wal, before secondinsert.so I gave recovery target lsn as『0/07000060』 in postgresql.conf file.
  • 將函數索引從Oracle遷移到PostgreSQL
    通過查詢pg_proc,可以確認函數類型,例如sysdate函數。SELECT proname, provolatile, proargtypes, proargtypes[0]::regtype AS argtype, prosrc FROM pg_proc WHERE proname like 『sysdate%』;
  • PostgreSQL類型bit和varbit報錯引起的日誌不可讀
    postgres=# CREATE FOREIGN TABLE pg_csvlog ( log_time timestamp with time zone, user_name text, db_name text, proc_id integer, conn_from text, session_id text, session_line
  • 滲透中利用postgresql getshell及注入技巧
    所以本人打算將公開課資源分成8個小部分給大家學習,目前總大小250G 左右,都是優質資源,夠你白嫖了吧領取方法:後臺回復關鍵詞「大佬您好」即可獲得!3.B站在線學習連結由於分享的資源連結經常被和諧,所以必須遷移到B站上供大家學習B站up主官方帳號:VMYKnetwork團隊0x00 前言研究postgresql資料庫如何getshell是在滲透中遇到一個pgAdmin的web管理頁面可以直接操作postgresql且通過網上的文章沒有達到9.6版本getshell的效果所以便有了以下文章。
  • PostgreSQL 12 首個版本說明草案發布
    對於從任意舊版本遷移到 PostgreSQL 12 的用戶,需要使用 pg_dumpall 或 pg_upgrade 進行 dump/restore(備份和恢復) 操作。 刪除數據類型abstime,reltime和tinterval 刪除時間段擴展(timetravel extension) 將recovery.conf設置移動至postgresql.conf
  • PostgreSQL函數
    2.查看支持的語言mytest=# select lanname from pg_language; lanname   internal c sql plpgsql(4 rows)其他的語言諸如PL/Tcl、PL/Perl、PL/Python等請參照官網:https://www.postgresql.org
  • postgresql email列表對NVM WAL BUFFER的討論
    [1]https://www.postgresql.org/message-id/83eafbfd-d9c5-6623-2423-7cab1be3888c%40iki.fi[2]https://www.postgresql.org/message-id/2aec6e2a-6a32-0c39-e4e2-aad854543aa8%40iki.fi[3] https
  • 去哪兒PostgreSQL指南
    主要內容命名規範Column設計Constraints 設計Index 設計關於NULL開發相關規範管理相關規範1 命名規範1.DB object: database, schema, table
  • PostgreSQL 11 beta 2 發布,bug 修復版本
    generating incorrect paths for partitionwise aggregatesFix for potential replica server crashes where a replica would attempt to read a recycled WAL segmentFixes for pg_replication_slot_advance
  • PG原生解碼工具pg_recvlogical的使用-在腦裂時幫我們找回丟失的數據
    pg有很多邏輯解碼的插件,其中pg原生的邏輯解碼工具pg_recvlogical就可以使用,它使用默認的test_decoding的插件,該插件位於pg源碼contrib/test_decoding目錄下,需要先對該目錄進行安裝,安裝完後會在pg安裝目錄的lib目錄下創建test_decoding連結庫文件。
  • 【戀與電臺人】-The last time
    If I knew it would be the last time I'd see you fall asleep, 假如我知道這是最後一次看到你進入夢鄉, I would tuck you in more tightly and "pray the
  • LANY - 如果這是最後一次 if this is the last time
    我也不願哭泣I'm bad at goodbye我不擅長告別If this is the last time如果真的是最後一次And let's do the things we always我也不願哭泣I'm bad at goodbye我不擅長告別If this is the last time若真的是最後一次And let's do the things we always
  • PostgreSQL 10.2, 9.6.7, 9.5.11, 9.4.16 和 9.3.21 發布
    crash and potential disclosure of backend memory when processing partition keys containing multiple expressionsFix potential disclosure of temporary files containing database passwords created by pg_upgrade
  • PostgreSQL 13 正式發布 - OSCHINA - 中文開源技術交流社區
    PostgreSQL 13 還增加了更多讓管理員可以監控資料庫活動的方法,包括從 EXPLAIN 中查看 WAL 使用情況的統計信息、流式基礎備份的進度,以及 ANALYZE 命令的進度。此外,可以使用新的 pg_verifybackup 命令驗證 pg_basebackup 命令輸出的完整性。
  • Jason Jones:Second time in the last
    Second time in the last few possessions Bagley has ended up defending Westbrook. He's scored both times. if ($weibo['img_link']): ?
  • 《PostgreSQL 指南:內幕探索》之基礎備份與時間點恢復(上)
    pg_start_backup開始為製作基礎備份進行準備工作。恢復過程從重做點開始,因此pg_start_backup必須執行檢查點,以便在製作基礎備份的開始時刻顯式創建一個重做點。此外,這次檢查點的位置必須保存在非pg_control的其他文件中,因為在備份期間可能會執行多次常規檢查點。
  • Ora2pg 18.2 發布,Oracle和MySQL 轉 PostgreSQL 工具
    This last changee forbids    ora2pg_scanner to overwrite a report if the same schema name is    found in several databases.添加了若干新的配置選項:  - Add USE_ORAFCE configuration directive that can be enabled if
  • 英文歌曲:One Last Time -Kelly Mack
    would always watch in wonderAs you grew from boy to manAnd if you were ever in tearsI'd kiss you till the pain would disappearIf only I could hold you nowIn my arms one last
  • PostgreSQL Tips: 動態SQL
    前言前幾天 Postgres 群裡有人問如何在 Postgres 裡實現動態列的行轉列,於是我整理了PostgreSQL 實現動態行轉列的方法匯總然後被問到如何直接通過一個函數返回結果,而不是返回一個待執行的 SQL 語句,於是我參考下面的教程,實現了一個函數返回 json,先把代碼附上