Re: scheduled backup

Seems like WAL backup is the way to go with PITR. database will not have to
be exhaused over and over with pg_dump and if I do rsync hourly, I could
minimize the network traffic significantly as well. I already like the term
'hot standby' from the doc already.
Thanks for your help.
-apu

On 10/21/05, Scott Marlowe <smarlowe(at)g2switchworks(dot)com> wrote:
>
> On Fri, 2005-10-21 at 13:38, Scott Marlowe wrote:
> > 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.
>
> Better yet, just rename the public schema to daily20051021 etc and then
> create a new schema called public and populate it with all the tables
> from a skel schema.
>
> Is there some functional equivalent in the schema universe for
> templating one schema from another as the is for create database from
> template? That would be very useful if there isn't one already.
>