Re: scheduled backup

On Fri, 2005-10-21 at 12:22, Apu Islam wrote:
> I need suggestion to backup my postgresql server. The database is
> growing 1/2 Gb a day and I am looking for a solution that would push
> the data off to the backup server periodically with minimal amount of
> load to the server (I am trying to avoid table locking). I am thinking
> to use rsync to sync the data directories periodically. will that
> allow me to retrieve the data in case of a disaster ? what is the best
> practise ?

I take it you need to MOVE the data out of the primary server to
conserve space, not just back it up? If that's the case, you might be
able to build something from scratch pretty easily. Just make a schema
every night at midnight named for today, like:

create schema daily20051021;

And then push all of today's data into that schema, and create select
only views in another schema that union all those tables in those
schemas together. That way, each day is nicely contained in a single
schema, and you just backup that one schema and empty the old ones as
needed to save space.