Re: running postgresql database from loopback filesystem?

Jeff Davis wrote:
> Anyway, it occured to me that I could create a special file for each
> database user of limited size, and make a filesystem on top of that
> file. The file could be owned by the user. Then, each user could have a
> seperate database and that database would be at the location of the
> mountpoint of that filesystem.
>
> It seems like a strange thing to do (an understatement, to be sure), but
> is there anything wrong with it? Are there performance considerations
> when using loopback devices like that? Are there dangers of corruption?
> How does journaling (ext3, reiser, etc) work in that regard?

My experience is that too many filesystem layers increase the risk of
corruption (by amplifying the effects of bugs, there's nothing
inherently wrong with it).  For example, I tried running a loopback
filesystem (both with and without encryption) on top of an XFS partition
over LVM spread over four partitions.  I could build the filesystem
(ext2 or ext3) on the loopback filesystem, sync, unmount, (cleanly)
reboot, and mount again and the loopback fsys was always corrupt.  On
the other hand, a loopback fsys on top of ext3 without LVM seemed to
work fine, as did loopback over a raw disk partition (only useful for
cryptoloop, obviously).

I have no recent experience with reiserfs, I became an xfs convert long
ago.  I also have no experience with a pgsql database specifically on a
loopback filesystem, tho msql (eeew!) and mysql (customer's idea, not
mine) didn't seem to mind.  To the process a loopback filesystem *is* a
filesystem, it shouldn't be able to tell the difference.

A journalling filesystem on the underlying partition will treat changes
to the backing file just like changes to any other large file.  If the
loop filesys also has a journalling filesystem on it, unless you've
specified an alternate journal location, the journal is written onto the
backing file, and those changes (appearing as changes to a regular file)
are handled by the underlying filesystem.  One journaling filesys on top
of another does slow things down more than, say, ext2 over ext3.  I'd
hope sync is smart enough to do the loopback before the underlying, but
make sure and cleanly unmount the loopback before the underlying
partition just in case.

Performance is definitely reduced, although not nearly as bad as with
loopback AES encryption.  Making the loopback file on a fresh filesystem
helped performance substantially; if your backing file isn't reasonably
contiguous, the thrashing gets to be a bit much.

Are you referring to IP-based virtual hosting?  If so, you may wish to
look into linux virtual machines (i.e., kernels run in userspace).  It
seems to be more secure than chroot-based virtual hosting (or at least
easier to make secure), although memory consumption is much greater.  Of
particular interest to you would be the ability to limit disk usage on a
per-virt basis.

-- Bill