Thread: File Handling in pgsql

On 12/04/10 17:17, Vikram A wrote:
> Hi,
> I would like to store the files(.doc, .xls , images, vedios) in the
> postgresql data base as a field.

If you really want to store them in the database your options are a
bytea field (warning: size limits) or a large object. See the manual.

Do you really need ACID-compliant access to your files? Have you
considered storing just the paths to the files in the database, and the
files themselves externally to the database in the file system? This is
MUCH more efficient if you don't need the facilities the database requires.

There has been lots of discussion of this in the past on this list, so
consider searching the mailing list archives and Google.

--
Craig Ringer

On 12/04/10 17:58, Vikram A wrote:
> Thank you for the valuable reply.
>
> If i go for the path store rather than the bytea. The disk sever [where
> files are stored] must be live always?

Well, if it's down or inaccessible then so are your files, but it won't
stop the database its self from working.

> Is it possible to keep the DB and the files on the same server for
> avoiding the n/w failure.

Yes, of course.

> Is it good to have NFS on red hat linux
> enterprise edition[5.x].

I very, very, very strongly suggest using nfs for this purpose unless
you are running an absolutely 100% trusted local network using
authenticated port access on your switch. NFS (v3) is completely insecure.

If you want to use NFS, use NFSv4 with kerberos and be very, very sure
to block NFSv3 access.

Alternatives include HTTP or WebDAV access to the files (with SSL and
HTTP BASIC auth) and lots more.

You can even access the files via the PostgreSQL connection, but I'm not
going to get into the details on how to do that safely and securely. If
you need it, you'll want to have read a lot more of the documentation first.

> And if it is bytea - the backup operation is only once, it is not needed
> to take the files along with DB backup.

If you use a bytea field or a pg large object to store the files, then
they'll get backed up with every database backup you run.

If you use external file system storage and just store the file paths in
the database, only the file paths get backed up with each database
backup and you must back up the files themselves separately (possibly on
a different schedule or incrementally).


--
Craig Ringer