[TriLUG] Using gzip to backup postgres to tape

[Brian Henning]

All right, I think I've seen people mention the points I would have 
tried to make (and I don't know any of this for sure, but it's worth a try).

Recapping the valid points:

o The tape drive wants tar.  Anything involving tar-formatted stuff 
going directly to tape works; hence tar -cvf /dev/nst0 or pg_dump -F t > 
/dev/nst0 functioning as expected.

o gzip doesn't care.  It'll zip anything.  So piping any of that through 
gzip into a file is just fine.

o A gzipped tar isn't tar format; it's gzip format.  The tape drive 
doesn't know what to do with gzip and chokes.

Additional point: It appears that your version of tar doesn't like the 
syntax for reading from stdin (at least not within an su command 
string).  This'll be a problem for making it all happen in a one-liner, 
but if you don't mind a bit of scripting and keeping some staging area 
on your hard drive, script the following steps:

1) Dump from postgre through gzip into a file.  Don't worry about tar 
format; dump it however postgre likes it when it's time to restore.

   pg_dump --make-restore-easier mydatabase | gzip [-9] tempdump.gz

(where --make-restore-easier is whatever set of flags and options makes 
restoration easiest, and [-9] means optionally specify maximum gzip 
compression)

2) Tar the gzipped file onto the tape drive

   tar -cvf /dev/nst0 tempdump.gz

3) Delete the temporary file

   rm tempdump.gz


Restoration is the reverse of backup:

Since in this stage, tar is a source rather than a sink, you should be 
able to achive restoration as a one-liner:

   tar -Oxf /dev/nst0 | gunzip -C | postgre_command

If that chokes for some reason, follow the reverse of backup:

1) Retrieve the gzipped file from the tape.

   tar -xvf /dev/nst0

2) gunzip the result and pipe it into your favorite postgre interpreter.

   gunzip -c tempdump.gz | postgre_command

3) delete the temporary file

   rm tempdump.gz


Hopefully that's at least somewhat clear. :-)  And helpful.

Cheers,
~Brian


Andrew Perrin wrote:
> perrin:~# su postgres -c '/usr/bin/pg_dump leted | tar -czf /dev/nst0 -'
> tar: -: Cannot stat: No such file or directory
> tar: Error exit delayed from previous errors
> 
> 
> ----------------------------------------------------------------------
> Andrew J Perrin - andrew_perrin (at) unc.edu - http://perrin.socsci.unc.edu
> Assistant Professor of Sociology; Book Review Editor, _Social Forces_
> University of North Carolina - CB#3210, Chapel Hill, NC 27599-3210 USA
> New Book: http://www.press.uchicago.edu/cgi-bin/hfs.cgi/00/178592.ctl
> 
> 
> 
> On Mon, 27 Nov 2006, Joseph Mack NA3T wrote:
> 
>> On Mon, 27 Nov 2006, Andrew Perrin wrote:
>>
>>> Thanks, but gzip actually handles tar format fine;
>>
>> yes of course (doh).
>>
>> o gzip handles tar format
>>
>> o the tape drive wants tar format.
>>
>> I don't have a tape drive to test this on, so I hope I'm not wasting 
>> your time, but you didn't tell me what happened with
>>
>>>> /usr/bin/pg_dump leted | tar -czf /dev/nst0 -
>>
>> Joe
>> -- 
>> Joseph Mack NA3T EME(B,D), FM05lw North Carolina
>> jmack (at) wm7d (dot) net - azimuthal equidistant map
>> generator at http://www.wm7d.net/azproj.shtml
>> Homepage http://www.austintek.com/ It's GNU/Linux!
>> -- 
>> TriLUG mailing list        : 
>> http://www.trilug.org/mailman/listinfo/trilug
>> TriLUG Organizational FAQ  : http://trilug.org/faq/
>> TriLUG Member Services FAQ : http://members.trilug.org/services_faq/
>>

-- 
----------------
Brian A. Henning
strutmasters.com
336.597.2397x238
----------------