Re: database files

gz(at)clozure(dot)com (Gail Zacharias) wrote:
> I am investigating the possibility of using pgsql as the database in
> an application. I have some unusual requirements that I'd like to
> ask you all about. I apologize in advance if my terminology is a
> little "off", I'm not familiar with pgsql (yet).

> I need to be able to move the database files, as normal user-visible
> files, between machines. I.e. given a database on machine A, I want
> to be able to copy either a single file (ideally) or a single
> directory (less ideal but still ok) to, say, a zip drive, bring it
> over to another machine (with pgsql also installed), start up my
> application and have it access the copied database through pgsql.
>
> Is this sort of thing possible?

Many things are possible. Not all are sensible. The approach you
seem to want to take appears to fit into the "not sensible" category.

> Is a database stored in a single file or multiple files?

Lots of files.

> Can the location of the file(s) be controlled?

Yes, to a degree, either by fancy footwork when the database is shut
down, or, in the case of specific data files, via how you create them.

> Are the files accessible and consistent while pgsql is running?

Only if you have some sort of logical volume manager around that can
copy a whole filesystem around atomically.

> I assume not all the time, but is there a reliable way to make them
> accessible (i.e. copyable) and consistent short of shutting down
> pgsql?

If you are storing all of the data atop some logical volume manager
system such as Veritas or Tru64 "AdvFs" or Linux LVM, then there is
probably a way, but I'm not sure there is any equivalent on Windows,
so it seems unlikely that this could be practical.

> Is the file format of the pgsql database files compatible between
> OS's? E.g. could I take some database files from Linux and use them
> on Windows?

Not generally, no.

It sounds as though the things you are trying to do are more or less
the exact opposite of what is generally considered "reasonable usage."

If you're at clozure, you're doubtless aware of the notion of taking
different approaches with different languages. Good Common Lisp code
isn't written the same way as colloquial Scheme which doesn't look at
all like colloquial C++ or Java.

There is an approach to doing this that _would_ provide consistent
copies, dumped into one file, of all of the data, that could indeed be
loaded onto another system without need to shut the database down.

Look at the documentation for pg_dump; that does what you _actually
want_, albeit not in the way you are asking to do it.
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','ntlug.org').
http://www.ntlug.org/~cbbrowne/spiritual.html
"... the most important thing in the programming language is the name. A
language will not succeed without a good name. I have recently
invented a very good name and now I am looking for a suitable
language." -- D. E. Knuth, 1967