Thread: scheduled backup

* Apu Islam (apuislam@gmail.com) 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

Apu Islam <apuislam@gmail.com> 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

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

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.

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.