Thread: Would a PostgreSQL database on a DVD be usable?
If a data analyst needed to perform analysis (select statements only, no updates, creates, etc) while traveling, would it be feasible to put a database on a DVD? Could PostgreSQL use it? Does the use of oid's eliminate such possibilities? Andrew Gould __________________________________________________ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/
Andrew Gould <andrewgould@yahoo.com> writes:
> If a data analyst needed to perform analysis (select
> statements only, no updates, creates, etc) while
> traveling, would it be feasible to put a database on a
> DVD? Could PostgreSQL use it?
Since Postgres just stores its data in Unix files, you can put a
database on anything that Unix thinks is a (non-read-only) filesystem.
I'd be a tad concerned about the longevity of a database on DVD; aren't
DVDs rated to support only about 10000 write cycles on any given block?
But for a short term, mostly-reading kind of scenario it'd probably
work. To be on the safe side, you might want to move pg_log to a plain
magnetic storage medium via a symlink --- that file gets rewritten a
large number of times per block.
> Does the use of oid's eliminate such possibilities?
AFAICS, oids have nothing to do with it.
regards, tom lane
Andrew Gould writes: > If a data analyst needed to perform analysis (select > statements only, no updates, creates, etc) while > traveling, would it be feasible to put a database on a > DVD? Could PostgreSQL use it? Does the use of oid's > eliminate such possibilities? This won't work. You need to be able to write to the medium for a number of reasons. -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
Peter Eisentraut wrote: > Andrew Gould writes: > > > If a data analyst needed to perform analysis (select > > statements only, no updates, creates, etc) while > > traveling, would it be feasible to put a database on a > > DVD? Could PostgreSQL use it? Does the use of oid's > > eliminate such possibilities? > > This won't work. You need to be able to write to the medium for a number > of reasons. Is the reason that PostgreSQL must have log files etc. in the same directory as the data files (it does not appear to support,for example, the Oracle LOGFILE syntax on CREATE DATABASE)? (Is there any good reason for this, btw.? The major vendors [Oracle, SQL Server, Sybase] support having logfiles (txn, redo,archive) on separate file systems/devices for reliability and performance.) Presumably you could do, say, an MS SQL Server database on a CD ROM? The system tables would have to be on hard disk, aswould the logs (and there are issues around having consistent paths on different systems, but nevermind), but it *should*work? Allan.
Allan Engelhardt <allane@cybaea.com> writes:
> Peter Eisentraut wrote:
>> This won't work. You need to be able to write to the medium for a number
>> of reasons.
> Is the reason that PostgreSQL must have log files etc. in the same
> directory as the data files
Look again --- they're *not* in the same directory as the data files.
I had interpreted Andrew's question as being about a read-mostly
storage medium, but you're probably right that he wanted it to be
read-only.
I believe it would work to copy individual table files to a read-only
medium after VACUUMing them. You could copy a whole database directory
that way too, but only if you were willing to put up with a completely
read-only database (including no temp tables, for example). It'd
probably make more sense to copy over just the biggest user tables to a
DVD, keeping the system tables on regular read/write media. Right now
this requires hand mucking-about with symlinks for the moved-over
tables, but it's quite doable. We have a TODO item to support
tablespaces, which'd make the bookkeeping a lot easier.
What definitely won't work is to put pg_log or the WAL files on
read-only media ...
regards, tom lane
On Mon, 6 Aug 2001, Allan Engelhardt wrote:
> Peter Eisentraut wrote:
>
> > This won't work. You need to be able to write to the medium for a number
> > of reasons.
>
> Is the reason that PostgreSQL must have log files etc. in the same
> directory as the data files (it does not appear to support, for
> example, the Oracle LOGFILE syntax on CREATE DATABASE)?
Grabbing at some low hanging fruit could the data, index, etc. files
(ie. the read only files) be links to the DVD (or CDROM) files?
I have a slightly different application in mind so keeping the logs on
RW media is OK. In fact, as I type this, I seem to remember a
discussion on placing log files on other devices. Time to dive into the
archives.
Cheers,
Rod
--
Remove the word 'try' from your vocabulary ...
Don't try. Do it or don't do it ...
Steers try!
Don Aslett
Lee Harr wrote: > > (Is there any good reason for this, btw.? The major vendors [Oracle, SQL > Server, Sybase] support having logfiles (txn, redo, archive) on separate file > systems/devices for reliability and performance.) > > > > My understanding is that you _can_ do this, by shutting down the server > moving the files to the places where you want them, and linking to them > with symlinks from their original locations. thanks to Lee and tom: I found the pg_xlog subdirectory. No I can go symlink mad :-) I still think it sould be possible to set this from within the RDBMS environment. You can use WITH LOCATION on CREATE DATABASE; why not have a WITH LOGFILE (-DIRECTORY?) option as well, a la Oracle? Presumablythere is no real speed gain, but it would kind of make it symmetric and obvious. And, let's face it: for a *production* system, when would you *ever* want the data and the logs on the same physical disk(or even on the same RAID channel)? Maybe I'm being overly optimistic on behalf of the optimizations that PostgrSQLperforms here.....but if so, think future-proof :-) Allan.
> (Is there any good reason for this, btw.? The major vendors [Oracle, SQL Server, Sybase] support having logfiles (txn, redo, archive) on separate file systems/devices for reliability and performance.) > My understanding is that you _can_ do this, by shutting down the server moving the files to the places where you want them, and linking to them with symlinks from their original locations.
Thank you all for your help! Am I understanding this correctly that it can (should) work if: 1. I burn the database directory to the CD/DVD; and 2. The user mounts the CD/DVD and creates a symbolic link in /usr/local/pgsql/data/base/ to the database directory on the CD/DVD; and uses the existing log directory; and 3. Restarts the postmaster daemon? Andrew Gould --- Tom Lane <tgl@sss.pgh.pa.us> wrote: > Allan Engelhardt <allane@cybaea.com> writes: > > Peter Eisentraut wrote: > >> This won't work. You need to be able to write to > the medium for a number > >> of reasons. > > > Is the reason that PostgreSQL must have log files > etc. in the same > > directory as the data files > > Look again --- they're *not* in the same directory > as the data files. > > I had interpreted Andrew's question as being about a > read-mostly > storage medium, but you're probably right that he > wanted it to be > read-only. > > I believe it would work to copy individual table > files to a read-only > medium after VACUUMing them. You could copy a whole > database directory > that way too, but only if you were willing to put up > with a completely > read-only database (including no temp tables, for > example). It'd > probably make more sense to copy over just the > biggest user tables to a > DVD, keeping the system tables on regular read/write > media. Right now > this requires hand mucking-about with symlinks for > the moved-over > tables, but it's quite doable. We have a TODO item > to support > tablespaces, which'd make the bookkeeping a lot > easier. > > What definitely won't work is to put pg_log or the > WAL files on > read-only media ... > > regards, tom lane > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html __________________________________________________ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/
> And, let's face it: for a *production* system, when would you *ever* > want the data and the logs on the same physical disk (or even on the > same RAID channel)? Maybe I'm being overly optimistic on behalf of > the optimizations that PostgrSQL performs here.....but if so, think > future-proof :-) In fact, the entire WAL mechanism should have a more flexible logger. It should be able to log strings to a file anywhere on the FS, and it should be able to log to another database in fact. Where is the WAL code? I might look into doing this.
Andrew Gould <andrewgould@yahoo.com> writes:
> Am I understanding this correctly that it can (should)
> work if:
> 1. I burn the database directory to the CD/DVD; and
> 2. The user mounts the CD/DVD and creates a symbolic
> link in /usr/local/pgsql/data/base/ to the database
> directory on the CD/DVD; and uses the existing log
> directory; and
> 3. Restarts the postmaster daemon?
I believe so, *but* be sure to do a complete VACUUM and then shut down
the postmaster (CHECKPOINT should be sufficient in theory, but shutdown
is a lot safer) before you copy the database directory to CD. You need
the VACUUM to update on-row tuple status bits, else you will get runtime
failures when the system tries to update out-of-date status bits in what
it will find is a read-only file.
A possible problem is that Postgres will try to open the files in
read-write mode, even if it never actually issues a write. If your
kernel only accepts read-only opens for files on a read-only volume,
this might not work.
regards, tom lane