PITR with PostgreSQL 8

arizonahoopleData Management

Nov 28, 2012 (4 years and 8 months ago)

283 views

PITR
with
P
ostgreSQL 8
C
ra
sh rec
o
ver
y
.
I
t’
s not a matt
er of if
, but when.
Copyright Sean Chitt
enden 2005
Al
l Rights R
eser
ved.
PITR
in a N
utshel
l
I
f pg_dump is t
o DBAs what le
vel

zero backups are t
o Syst
em

A
dministrat
ors, then PITR
is t
o

DBAs what incremental backups are

t
o Syst
em
A
dministrat
ors.
I
s PITR
right for you?

PITR
is not for e
ver
yone: it adds

administrative c
omplexity and increa
sed disk

use and IO
.

S
m
a
l
l

d
a
t
a
b
a
s
e
s

m
a
y

f
i
n
d

p
e
r
f
o
r
m
i
n
g

f
r
e
q
u
e
n
t

f
u
l
l

d
u
m
p
s

v
i
a

p
g
_
d
u
m
p

t
o

b
e

m
o
r
e

a
c
c
e
p
t
a
b
l
e

t
h
a
n

i
m
p
l
e
m
e
n
t
i
n
g

a
n
d

d
e
p
l
o
y
i
n
g

P
I
T
R
.

F
or non-readonl
y databa
ses with 24/7

operation and DBAs who want a high degree

of rec
o
verability
, PITR
is a must!
I
s PITR
appropriat
e?
U
se PITR
if:

Obtaining a f
requent dump of the databa
se is

performanc
e or spac
e prohibitive.

I
t is not acc
eptable t
o loose data betw
een the

la
st dump and the time of a cra
sh.

F
ile syst
em snapshots aren

t appealing.

There is suffi
cient man pow
er a
vailable t
o

monit
or the health of a PITR
setup!
H
ow PITR
w
orks...

PITR
w
orks by invoking an ext
er
nal c
ommand

that archives
W
AL fi
les when they bec
ome

eligible t
o be recycling.

A

W
AL fi
le wil
l be recycled upon succ
essful

c
ompletion of the ext
er
nal c
ommand.

The postma
st
er wil
l execut
e the
W
AL fi
le

archival c
ommand a
s many times a
s is

nec
essar
y until it retur
ns non-zero.
postgresql.c
onf
archive_command = ‘/any/command’

%p
= absolut
e path

%f
= fi
lename onl
y

%%
=
The % charact
er

Example:
archive_command = ‘cp -n %p /nfs1/pitr/%f’
PITR
F
ul
l Backup P
roc
edure
1.
SELECT pg_start_backup(’my_backup’);
2.
tar --exclude $PGDATA/pg_xlog \
cvjpf pgbackup.tar.bz2 $PGDATA/
3.
SELECT pg_stop_backup();
Backups of $PGD
A
T
A
can now be done with

the databa
se online without the use or need for


lesyst
em snapshots.
PITR
Backup N
ot
es

Be mindful of tablespac
es and data direct
ories

outside $PGD
A
T
A

T
ime int
er
val betw
een issuing

pg_star
t_backup() and pg_end_backup()

stat
ements is not time sensitive

M
ak
e sure
W
AL archiving is w
orking before

star
ting this proc
edure
PITR
R
ec
o
ver
y

Star
t f
resh (ie: mo
ve $PGD
A
T
A
and its data

subdirect
ories - including table spac
es - and

instal
l c
opies of its c
onfi
gs


R
est
ore databa
se f
rom la
st ful
l dump/tar

Clean out pg_xlog

Copy unarchived
W
AL logs int
o $PGD
A
T
A/
pg_xlog/, if a
vailable

C
reat
e
recovery.conf
in $PGD
A
T
A/

Star
t postma
st
er
rec
o
ver
y
.c
onf

rest
ore_c
ommand = ‘/any/sh/c
ommand’

%p
= absolut
e path

%f
= fi
lename onl
y

%%
=
The % charact
er

Example:
restore_command = ‘cp -n /nfs1/pitr/%f %p’
R
ec
o
ver
y N
ot
es

rest
ore_c
ommand wil
l be a
sk
ed for fi
les that

don

t exist

Command must retur
n zero on succ
ess

Command must retur
n non-zero on non-
exist
ent fi
les

C
an rest
ore t
o any time a
ft
er a c
omplet
ed ful
l

dump t
o a given time or transaction ID
PIT
A
F
ood for PITR

Thought

T
est, practic
e, and verify that your sit
e’
s PITR

proc
edure w
orks before depending on its

rec
o
verability!

W
AL logs aren

t smal
l. Choose data expiration

policies for archived
W
AL data wisel
y (ie: onl
y

a
ft
er la
st ful
l backup c
omplet
es)

W
AL fi
les must archived fa
st
er than
W
AL fi
les

are generat
ed!!!

PITR

les should be k
ept with with ful
l

backups

Why not backup unarchived
W
AL fi
les onc
e a

minut
e via cron(8)? J
ust remember t
o discard

unoffi
cial
l
y archived
W
AL fi
les when the

offi
cial archival of a
W
AL fi
le happens.
F
inal
Thoughts

R
ead the docs, this wa
s a lightning talk t
o

familiarize and introduc
e the c
onc
ept and use

of PITR.
This is not meant t
o be used for

training or replac
e a tut
orial.

H
a
ving DBA
depts c
ontracting PITRitis is

not fatal, but it can be a PIT
A
t
o setup, but

pays huge dividends c
ome failure time.

Databa
se administration 101: I
t’
s not a matt
er

of if the databa
se hardware/disks cra
sh, it’
s a

matt
er of when. PITR
is the extra sa
fety belt.
Thank you!
Questions? Let me know!
Sean Chitt
enden
sean@giga
ve.c
om