Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am using Postgres to store a variety of application data for a webapp. Part of the application involves storing and retrieving user uploaded files. I am storing the files in the filesystem with some associated metadata in the database.

I am trying to come up with a backup and archive strategy so that I can effectively backup and archive/restore the database and the linked files. Here are the things I want to accomplish.

  1. Perform routine backups that can be used for recovery from failures and which include all DB data and the linked files. Ideally, this backup would be done while the app is running. Live backup is certainly possible with a DB but I am not sure how to keep the linked files consistent with the database during the backup process

  2. Archive chunks of data as they become "old". These chunks must includes the database data plus any linked files. It should be possible to put the archived data back into production again. It would be ideal if it were easy to determine which ranges of objects were stored in each chunk.

Do you have any advice for how to accomplish these goals? If the files were in the database as BLOBS these tasks would be much easier since normal database backup and restore functionality would handle this. I am not sure how to accomplish the same thing when file data is linked to database rows.

Note: I asked this question on ServerFault but I did not get any response there. The link to that question is: http://serverfault.com/q/284218/85719

share|improve this question
    
What's up with PostgreSQL Large Objects API? Can you use that? –  alex Jul 2 '11 at 5:04
    
@alex Yes, I could use Blobs in postgres but I have decided against that. I have read and I believe I understand the various arguments posed in other questions regarding this topic. I am considering the Postgres large object API, TOAST, and bytea all as different flavors of Blob storage. –  busyspin Jul 4 '11 at 4:43
add comment

2 Answers

up vote 1 down vote accepted

If you store your files by hash to and link with the hash to the database row, you can simplify maintaining consistency.

In that case you can either:

  • keep on incrementally backing up the filesystem (eg with rsync) and never delete

    or

  • if you need to free up space, keep an audit of link count and when the last link was deleted - you can then periodically purge the filesystem of files not linked to in your recovery window. Something like rsnapshot might help you archive these long-term in a space efficient way
share|improve this answer
    
Thank you, Jack. This seems like a good solution. You are right that if I don't delete files from the filesystem right away when they are deleted from the database then backups would still be fine even if they are hot backups. I was considering using the primary id of the linked database row as the id of the file since I know it is unique. The idea of using the hash is a good one to so I will have to consider that. I am already computing a SHA-1 hash on each file. I did worry about hash collisions when I considered using the hash as the id but I guess that is probably a bit paranoid. –  busyspin Jul 5 '11 at 4:29
add comment

LVM + snapshots may be your best bet, if you want a clean and easy restore. It doesn't provide Point-in-time recovery, as the changes that are reverted or overwritten between snapshots are invisible, but at least your files and database are backed up in consistent state.

http://tldp.org/HOWTO/LVM-HOWTO/snapshots_backup.html

http://www.howtoforge.com/linux_lvm_snapshots

share|improve this answer
    
Thank you, Bryan. I need to support Windows and Linux right now and possibly OSX in the future so I don't think I can rely on LVM being available on all platforms. –  busyspin Jul 5 '11 at 4:25
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.