Thread: Backups

I need to back up a production database every night
on FreeBSD 7.2, running Postgresql 8.3.

Any good backup tips I should be aware of.

Typically, I make a backup based on the current day,
and rotate the seven days in the backup file name
 (eg; sat_backup, sun_backup etc).

Thanks for all the chatter.

Bret Stern

Personally I prefer multiple stages of backups off-site.
So for my production webserver database, I have slony replicating the database
to a different location. In addition I run full dumps every 12 hours which in
turn I replicate using rdist to a remote system. That way, whatever happens,
the max data loss in the worst case scenario is about 12 hours - which for my
application is acceptable.
Oh, yes, forgot to mention: the replicated database also gets dumped and goes
onto a tape every day.



On Saturday 07 November 2009 11:41:55 pm Bret wrote:
> I need to back up a production database every night
> on FreeBSD 7.2, running Postgresql 8.3.
>
> Any good backup tips I should be aware of.
>
> Typically, I make a backup based on the current day,
> and rotate the seven days in the backup file name
>  (eg; sat_backup, sun_backup etc).
>
> Thanks for all the chatter.
>
> Bret Stern

On 2009-11-08, Bret <bret_stern@machinemanagement.com> wrote:
>
> I need to back up a production database every night
> on FreeBSD 7.2, running Postgresql 8.3.
>
> Any good backup tips I should be aware of.

I backup using pgdump and move the dumps offsite using

rsync --compress

overwriting a copy of yesterdays backup

in this way I get better than 20:1 compression

once offsite the backups are compressed and rotated,

I keep 6 dailys and 6 weeklys,