By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,304 Members | 3,172 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,304 IT Pros & Developers. It's quick & easy.

running postgresql database from loopback filesystem?

P: n/a
I have virtual hosted users on a server and some of them have a
postgresql database. I'm concerned about the outside possibility that a
user could create an infinite loop and fill up the partition on which
everyone's database resides.

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?

Regards,
Jeff Davis
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #2

P: n/a

Jeff Davis <jd**********@empires.org> writes:
I have virtual hosted users on a server and some of them have a
postgresql database. I'm concerned about the outside possibility that a
user could create an infinite loop and fill up the partition on which
everyone's database resides.


It seems like the obvious solution would be quotas. You would have to set up
each database as a separate postgres postmaster running under a different uid
though.

--
greg
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.