--0016363ba756a9a9ef046fc2e1a9
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
On Tue, Jul 28, 2009 at 1:46 AM, Albert Shih <Albert.Shih [at] obspm.fr> wrote:
> Hi all
>
> I'm not sure I understand
>
>
> http://www.postgresql.org/docs/8.3/interactive/continuous-ar chiving.html
>
> My problem:
>
> I've huge database ~ 1To (soon 2 To) and I need backup.
> I can use pgdump because it's too long to do the backup.
> So I like to use =ABcontinous-archiving=BB.
>
> *SNIP*
> before this moment I use =ABcontinuous-archiving=BB by put in my c=
rontab
> something like (every hours)
>
> rsync -av /databases/pgsql /databases/Archives
>
> is that correct ?
>
> And more important : How long I can keep this the rsync ? I mean after ho=
w
> long it's good to make a new =ABbig backup=BB ?
>
Up to the rsync in your crontab your configuration looked good. Lose the
cron'd rsync.
The first rsync executed between pg_start_backup() and pg_stop_backup()
creates the baseline / "full" backup of the database. Be sure you get
tablespace directories outside of the default PGDATA! The archive_command
adds the WAL logs as needed after and are the "incrementals".
That's all you need for the backups.
How often do you need to create the baseline? Depends how many WAL logs ar=
e
being created and how quickly you want the recovery to be. I have a 800 GB
database and in one week over 4,000 WAL logs are created. This database
setup takes about 16-24 hours to recover. but at least 8 hours of that is
restoring the "full" and the remainder is applying the WAL logs.
FWIW, I send all backup data (baseline & archived WALs) to a server which i=
s
responsible for backing it up to tape. This allows the database server to
be a database server without busying itself with actual backups. The
standby server, eventually, will be used as a warm standby in case the
primary crashes.
Greg
--0016363ba756a9a9ef046fc2e1a9
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
<div class=3D"gmail_quote">On Tue, Jul 28, 2009 at 1:46 AM, Albert Shih <sp=
an dir=3D"ltr"><<a href=3D"mailto:Albert.Shih [at] obspm.fr">Albert.Shih [at] obsp=
m.fr</a>></span> wrote:<br><blockquote class=3D"gmail_quote" style=3D"bo=
rder-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding=
-left: 1ex;">
Hi all<br>
<br>
I'm not sure I understand<br>
<br>
=A0 =A0 =A0 =A0<a href=3D"http://www.postgresql.org/docs/8.3/interactive/c=
ontinuous-archiving.html" target=3D"_blank">http://www.postgresql.org/docs/=
8.3/interactive/continuous-archiving.html</a><br>
<br>
My problem:<br>
<br>
=A0 =A0 =A0 =A0I've huge database ~ 1To (soon 2 To) and I need backup.=
<br>
=A0 =A0 =A0 =A0I can use pgdump because it's too long to do the backup=
..<br>
=A0 =A0 =A0 =A0So I like to use =ABcontinous-archiving=BB.<br>
<br>
*SNIP*<br>
=A0 =A0 =A0 =A0before this moment I use =ABcontinuous-archiving=BB by put =
in my crontab<br>
=A0 =A0 =A0 =A0something like (every hours)<br>
<br>
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0rsync -av /databases/pgsql /databases/Archi=
ves<br>
<br>
is that correct ?<br>
<br>
And more important : How long I can keep this the rsync ? I mean after how<=
br>
long it's good to make a new =ABbig backup=BB ?<br></blockquote></div><=
br><br>Up to the rsync in your crontab your configuration looked good.=A0 L=
ose the cron'd rsync.<br><br>The first rsync executed between pg_start_=
backup() and pg_stop_backup() creates the baseline / "full" backu=
p of the database.=A0 Be sure you get tablespace directories outside of the=
default PGDATA!=A0 The archive_command adds the WAL logs as needed after a=
nd are the "incrementals".<br>
<br>That's all you need for the backups.<br><br>How often do you need t=
o create the baseline?=A0 Depends how many WAL logs are being created and h=
ow quickly you want the recovery to be.=A0 I have a 800 GB database and in =
one week over 4,000 WAL logs are created.=A0 This database setup takes abou=
t 16-24 hours to recover.=A0 but at least 8 hours of that is restoring the =
"full" and the remainder is applying the WAL logs.<br>
<br>FWIW, I send all backup data (baseline & archived WALs) to a server=
which is responsible for backing it up to tape.=A0 This allows the databas=
e server to be a database server without busying itself with actual backups=
..=A0 The standby server, eventually, will be used as a warm standby in case=
the primary crashes.<br>
<br>Greg<br><br>
--0016363ba756a9a9ef046fc2e1a9--
