Re: [GENERAL] 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 <[EMAIL PROTECTED]> 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 tablesfrom a skel schema.Is there some functional equivalent in the schema universe fortemplating one schema from another as the is for create database from template? That would be very useful if there isn't one already.
Re: [GENERAL] scheduled backup
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. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] 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. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] scheduled backup
* Apu Islam ([EMAIL PROTECTED]) 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 ? PITR. Stephen signature.asc Description: Digital signature
Re: [GENERAL] scheduled backup
Am Freitag, den 21.10.2005, 12:22 -0500 schrieb Apu Islam: > 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 ? If you have big amount of data changes, continous backup of the WAL files for point-in-time recovery is probably a good solution. Especially if you can afford a 2nd box where the data is continously replayed. See: http://www.postgresql.org/docs/current/static/backup-online.html ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] scheduled backup
Apu Islam <[EMAIL PROTECTED]> writes: > 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 ? If you're using 8.0.X, you should be able to use rsync along with WAL shipping to save the data files and the WAL files on a remote machine. You can then use the PITR mechanism to restore if you need to--see the docs. BTW, pg_dump doesn't lock any tables. -Doug ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] scheduled backup
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 ?