Building Journaling Databases with PostgreSQL Cybertec Geschwinde

1
Building Journaling Databases with
PostgreSQLCybertec Geschwinde
SchoenigHans-Juergen Schoenig(office_at_cybertec.a
t)www.postgresql.atwww.cybertec.at
2
Common Problems
downtimes can be caused by - hardware
failures - network failures - software bugs -
troubles caused by users history and
documentation I don't remember what we have
done Who has changed this? What was the
value before the change?
3
What about the users?
data can be deleted accidentally by clicking
on a wrong button data is changed to something
wrong gt These problems cannot be solved by
redudant hardwaregt restoring data can take a
lot of time
4
Downtime vs. Uptime
99.9 uptime 8 ½ hours downtime/year Assumption
100 people are affected by a problem 1
hour downtime costs 50/person average time
for recovery 2 hours 1 problem caused by a
user per year gt costs 10.000 per year or 200
hours Reducing downtimes by 50 saves 5.000
5
The Idea Of Journaling
Every version of a record is stored
Every version of a record has - a database
wide unique id - a timestamp - a flag
telling if a record is still valid or not
- a transaction id to see which records
have been changed within the same
transaction - a user who has modified the
record
6
Benefits of Journaling
You can ... undo transactions restore
a snapshot of the database within minutes
and without having to use your backup
monitor the changes made to your data
benchmark your employees build a history of
your data
7
Journaling And Performance
Journaling databases are slower than normal
databases due to overhead and a higher
consumption of storage. In most cases this will
NOT cause real performance problems.
8
A Case Study
The Cybertec Intranet Journaling helps us
to - keep track of the changes - increase
the security of our data - see what has
happened when - prevent errors
1 / 2
9
How does it work?
SELECT, INSERT, UPDATE, DELETE
Current Data View
Functions And Rules
COPY, Triggers
Historical Current Data
1 / 3
10
How does it work?
CREATE TABLE t_mother ( globid int8, transid int
4 DEFAULT getxid(), transtime timestamp without
time zone DEFAULT
now(), realtime timestamp without time
zone, status char DEFAULT 't', username text
DEFAULT current_user() )
Historical Current Data
2 / 3
11
How does it work?
Current Data View - a recent version of the
data is provided - the application can perform
all common operations - the data is provided
by a view Functions And Rules - data structure
is managed by rules - Functions are needed for
managing serials, integrity, etc.
Functions and Rules Current Data View
3 / 3
12
Time Warps
Restoring Old Data - old snapshots can be
restored using a function called recovery -
no data is lost during a Time Warp - recovery
is based on functions
Functions and Rules Current Data View
3 / 3
13
Is it difficult?

oreilly SELECT recovery('2002-05-06
234926') recovery ---------- t (1 row)
14
Any Questions?
office_at_cybertec.at