Re: Database backup mechanism - Mailing list pgsql-hackers

From Doug Knight
Subject Re: Database backup mechanism
Date
Msg-id 1171026974.9560.211.camel@arc-dknightlx.wsicorp.com
Whole thread Raw
In response to Database backup mechanism  (RaviKumar.Mandala@versata.com)
Responses Re: Database backup mechanism
Re: Database backup mechanism
List pgsql-hackers
I would also be interested in any "creative" ways to reduce the size and time to backup databases/clusters. We were
justhaving a conversation about this yesterday. We were mulling over things like using rsync to only backup files in
thedatabase directory tree that actually changed. Or maybe doing a selective backup of files based on modified times,
etc,but were unsure if this would be a safe, reliable way to backup a reduced set of data.<br /><br /> Doug Knight<br
/>WSI Inc.<br /> Andover, MA<br /><br /> On Fri, 2007-02-09 at 12:45 +0530, RaviKumar.Mandala@versata.com wrote:<br
/><blockquotetype="CITE"><br /><font size="2"><font color="#000000">Hi Folks,</font></font><font color="#000000">
</font><br/><br /><font size="2"><font color="#000000">We have a requirement to deal with large databases of the size
Terabyteswhen we go into production. What is the best database back-up mechanism and possible
issues?</font></font><fontcolor="#000000"> </font><br /><br /><font size="2"><font color="#000000">pg_dump can back-up
databasebut the dump file is limited by OS file-size limit. What about the option of compressing the dump file? How
muchtime does it generally take for large databases? I heard, that it would be way too long (even one or days). I
haven'ttried it out, though.</font></font><font color="#000000"> </font><br /><br /><font size="2"><font
color="#000000">Whatabout taking zipped back-up of the database directory? We tried this out but the checkpoint data in
pg_xlogsdirectory is also being backed-up. Since these logs keeps on increasing from day1 of database creation, the
back_upsize if increasing drastically.</font></font><font color="#000000"> </font><br /><font size="2"><font
color="#000000">Canwe back-up certain subdirectories without loss of information or consistency..?</font></font><font
color="#000000"></font><br /><br /><font size="2"><font color="#000000">Any quick comments/suggestions in this regard
wouldbe very helpful.</font></font><font color="#000000"> </font><br /><br /><font size="2"><font
color="#000000">Thanksin advance,</font></font><font color="#000000"> </font><br /><font size="2"><font
color="#000000">RaviKumar Mandala</font></font></blockquote> 

pgsql-hackers by date:

Previous
From: Heikki Linnakangas
Date:
Subject: Re: Database backup mechanism
Next
From: Heikki Linnakangas
Date:
Subject: Re: HOT for PostgreSQL 8.3