Auswahl  

Flashback Funktionen in Postgres 

Postgres
Postgres
02.08.21 (MP)
02.08.21
Postgres, Flashback

Passende Schulungen zum Thema

Leider hat Postgres keine eingebaute Flashback Funktion. Für Flashback Database könnte man sich als Alternative ein Komplettbackup der DB erstellen (z.B. mittels pg_basebackup)
Im Fehlerfall würde man dann mit einem zeitbasierten Recovery wieder auf den gewünschten Zeitpunkt zuückstellen.

Wir möchten uns heute jedoch eine Alternative für Flashback Transaktion mittels eines Update und Delete Trigger basteln:


Beispieltabelle erstellen:
CREATE TABLE scott.emp (
    empno     numeric CONSTRAINT PK_EMP PRIMARY KEY,
    ename     character varying(10),
    job     character varying(9),
    mgr     numeric,
    hiredate date,
    sal     numeric(7,2),
    comm     numeric(7,2),
    deptno     numeric(2,0));

INSERT INTO scott.emp VALUES(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO scott.emp VALUES(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO scott.emp VALUES(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO scott.emp VALUES(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO scott.emp VALUES(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO scott.emp VALUES(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO scott.emp VALUES(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO scott.emp VALUES(7788,'SCOTT','ANALYST',7566,to_date('13-6-1987','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO scott.emp VALUES(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO scott.emp VALUES(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO scott.emp VALUES(7876,'ADAMS','CLERK',7788,to_date('13-6-1987','dd-mm-yyyy'),1100,NULL,20);
INSERT INTO scott.emp VALUES(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO scott.emp VALUES(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO scott.emp VALUES(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
CREATE TABLE scott.emp_flashback AS SELECT * FROM scott.emp
WHERE 1=2;

Für die Flashbacktabelle werden drei weitere Spalten hinzugefügt
ALTER TABLE scott.emp_flashback
ADD column xid INT,
ADD column zeit TIMESTAMP,
ADD column aktion char(1);

Ein Trigger in Postgres verwendet immer eine seperate Procedure oder Function:
CREATE OR REPLACE PROCEDURE emp_trg_proc()
AS $$ DECLARE
dml_type CHAR(1);
BEGIN
CASE TG_OP          
   WHEN 'UPDATE' THEN
      dml_type:='U';
   ELSE
      dml_type:='D';
   END CASE;
INSERT INTO scott.emp_flashback(empno,ename,job,mgr,hiredate,sal,comm,deptno,xid,zeit,aktion)
VALUES (old.empno,old.ename,old.job,old.mgr,old.hiredate,old.sal,old.comm,old.deptno,txid_current(),now(),dml_type);
END $$ LANGUAGE plpgsql;

Nun wird der Trigger erstellt:
CREATE TRIGGER emp_trg
AFTER UPDATE OR DELETE
ON scott.emp
FOR EACH ROW
EXECUTE PROCEDURE emp_trg_proc();

Hinweis: Wenn Sie einen Trigger wieder löschen möchten, verwendet man folgende Syntax:
DROP TRIGGER emp_trg ON scott.emp;

Beispieltransaktion:
update scott.emp set sal=sal+1 where deptno=10;

Prüfen der Flashback-Tabelle:
select empno,sal,zeit from scott.emp_flashback;

Jetzt kommt der spannende Teil: Welchen Wert hatt eine Gehalt zu einem bestimmten Zeitpunkt in der Vergangenheit mittels unserer selbst gebauten Flashback Funktion:
with flash as (select '2021-08-02 08:30:00'::timestamp as back)
select empno,sal from scott.emp_flashback
where zeit=(select max(zeit) from flash,scott.emp_flashback where zeit<flash.back)
UNION ALL
select empno,sal from scott.emp
where empno not in (select empno from scott.emp_flashback
where zeit=(select max(zeit) from flash,scott.emp_flashback where zeit<flash.back));

Wenn Sie die Flashback Funktion für Ihre Umgebung nachbauen möchten, müssen Sie hier den Primärschlüssel empno durch Ihren Primärschlüssel austauschen.
Weitere tolle Tipps erhalten Sie in einem von unseren Postgres Kursen.
 

Weitere Interessante Kurs-Tipps zum Thema

Trigger Beispiele in Postgres
Postgres Backup einer Datenbank mit tar
Postgres utl_file zum Lesen und Schreiben von Dateien
Partitionierte Tabellen von Postgres (ab Version 10)
PostgreSQL Listener Adresse ändern und Zugriff regulieren

Besuchen Sie uns doch bei einer unsere über 50 Oracle Schulungen in München - Unterhaching, oder verfolgen Sie die Training per Videostreaming bequem vom Home Office oder Büro aus.