Best practice for file storage?

From: Joe Kramer <cckramer(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Best practice for file storage?
Date: 2010-01-31 10:46:29
Message-ID: b4c00a111001310246u2cdc99b5h44321763da2a6945@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I need to store a lot of large files (thousands of 10-100 MB files)
uploaded through my web application and I find that storing them in
database as bytea field is not practical for backup purposes.
My database has full backup performed every 12 hours and backup is
encrypted and copied to server on another continent. Having all the
heavy binary data in database will make backups impossible.

So I am thinking of having a table just for metadata and file id
sequence and storing the file on file system:

CREATE TABLE business_logo
(
file_id bigserial NOT NULL,
file_name varchar,
file_date timestamp,
);

Storing file in path composed from serial id, e.g. file with id 2345
will be stored in
/webapp/files/2/3/4/5/2345

So I can backup files separately and database backup is still quick
and painless.

This is very simplistic and straightforward method.
I suppose there are better ways of doing it, using some virtual file system?
Anyone had a similar issue with avoiding of storing large files in
database, how did you solve it?

Thanks.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Mark Morgan Lloyd 2010-01-31 11:40:01 Re: Best practice for file storage?
Previous Message Craig Ringer 2010-01-31 05:25:42 Re: Ability to 'fork' a running transaction?