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.

  • 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

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


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 :-)

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

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