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.
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.
