Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a database running on postgres (8.4) and I want regular backups of parts of that data. We have very important data of which we need regular backups in case the hardware catches fire. But the same database contains a huge amount of binary data for each entry that grows really fast (easily more than a GB per day). Now, this data is not as critical. If we lose it, it is of course annoying, but it would be acceptable.

Is there an easy way to backup some of our tables with postgres, or do I have to manually write queries to copy everything somewhere before I can pgdump it?

Bonus: If the big binary table could just backup the last X entries, that would be perfect, but I am pretty sure I will have to do something as complex as this manually anway.

share|improve this question
5  
Are you aware of the "--table" option for pg_dump? Also, blobs don't get dumped when you use "--table" (unless you specify "--blobs"). –  Mark Wagner Oct 6 '10 at 22:28
    
Thank you, that solves my problem, I didn't realize there is such a parameter. If you had written this as an answer, I would accept it now. –  Kdansky Oct 7 '10 at 9:16
add comment

2 Answers

up vote 1 down vote accepted

I used the --table option for pg_dump, as suggested by Mark Wagner.

share|improve this answer
add comment

You can try the COPY command, but note that this sucks for incremental backups.

Another (possibly better depending) option may be a hot standby server that you shut down and take (filesystem-level) backups of periodically - This lets you use smarter tools which may take a binary diff of the DB table files and not have to suck down the whole thing every time you make a backup.
The other advantage is you have a well-knwon and well-supported recovery path this way :-)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.