-
Notifications
You must be signed in to change notification settings - Fork 6
/
README.txt
166 lines (110 loc) · 5.25 KB
/
README.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
pov - PostgreSQL Object Version control system
DESCRIPTION
Take a snapshot or rollback all "non-data" objects in a PostgreSQL database.
Without losing any of your data, you can travel in time and restore the state of any snapshot taken.
RATIONALE
Before reading any further, ask yourselves the following questions.
1. Have you ever,
a) modified stored procedures in your production database and
b) thought it went OK because all your tests passed and
c) later on realized "something is wrong" and
d) not being able to find nor fix the bug immediately
leaving you no other option than to do a revert?
If so, go to step 2.
If not, go to step 4.
2. During the minutes/hours while your malfunctional patch made a mess
in the production database, was there any user activity causing
important writes to the database?
If so, go to step 3.
If not, go to step 4.
3. Did you enjoy the revert experience in step 1?
If so, go to step 4.
If not, go to step 5.
4. Are any of the following statements TRUE?
a) your application is not very database centric.
b) your users won't stop using your service if you lose their data.
c) your application is read-only.
d) your application does not have a lot of user traffic.
If so, lucky you!
If not, you probably have a good solution to my problem already,
I would highly appreciate if you wanted to share it with me,
please contact me at joel@gluefinance.com.
5. This proposed solution might be interesting for you.
I would highly appreciate your feedback on how to improve it,
please contact me at joel@gluefinance.com.
INTRODUCTION
pov can take a snapshot of all your database functions and objects
depending on them, such as constraints and views using functions.
pov can rollback to a previous snapshot without modifying any of your
data or tables. It will only execute the minimum set of drop/create commands
to carry out the rollback.
pov will use SHA1 if the pgcrypto contrib package is available,
otherwise MD5 will be used as the hash algorithm.
TERMINOLOGY
object type objects of the same type are created and dropped the same way,
i.e. they use the same functions to build proper create and
drop SQL-commands.
object is of an object type and has a hash of its content
consisting of two SQL-commands, one to create and another to
drop the object.
revision has a timestamp when it was created and a list of objects
snapshot has a timestamp when it was taken and has a revision
active snapshot the last snapshot taken
take snapshot create a new revision of all objects currently live in the
database and then create a new snapshot if the revision
is different compared to the active snapshot.
rollback restores a previously taken snapshot
SYNOPSIS
-- 1. Take a snapshot.
test=# SELECT * FROM pov();
_snapshotid | _revisionid
-------------+------------------------------------------
1 | 8ba39bf65949adc6b69aa356c29725cf06c77e26
(1 row)
-- 2. Take a snapshot.
test=# SELECT * FROM pov();
_snapshotid | _revisionid
-------------+------------------------------------------
1 | 8ba39bf65949adc6b69aa356c29725cf06c77e26
(1 row)
-- 3. We notice nothing changed between step 1 and 2.
-- 4. Modify your functions.
test=# CREATE FUNCTION myfunc() RETURNS VOID AS $$ $$ LANGUAGE sql;
CREATE FUNCTION
test=# \df myfunc
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+--------+------------------+---------------------+--------
public | myfunc | void | | normal
(1 row)
-- 5. Take a snapshot.
test=# SELECT * FROM pov();
_snapshotid | _revisionid
-------------+------------------------------------------
2 | 6c4c86015a45d9361889ce29908937b387e4dde0
(1 row)
-- 4. Rollback to pov 1.
test=# SELECT * FROM pov(1);
_snapshotid | _revisionid
-------------+------------------------------------------
3 | 8ba39bf65949adc6b69aa356c29725cf06c77e26
(1 row)
-- 5. We notice the function we created in step 4 has been dropped.
postgres=# \df myfunc
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+------+------------------+---------------------+------
(0 rows)
-- 6. Rollback to pov 2.
test=# SELECT * FROM pov(2);
_snapshotid | _revisionid
-------------+------------------------------------------
4 | 6c4c86015a45d9361889ce29908937b387e4dde0
(1 row)
-- 7. We notice the function we created in step 4 has been created.
postgres=# \df myfunc
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+--------+------------------+---------------------+--------
public | myfunc | void | | normal
(1 row)