Обсуждение: Portable database ... ?

Dear community,

I'm not sure whether this is the appropriate forum (maybe it's
actually better asked on the developers' list), but as my impression
grows my question might be naive, I thought I should probably duly
start as a novice:

Is there any way to have the Linux and the Windows versions of
PostgreSQL use the same database? (On a double-boot machine.)

When trying this, I ran into trouble as soon as I tried to initiate a
database on a VFAT partition while running Linux. That seemed to fail
because VFAT does not support (sym)links:

   PANIC: could not link file "<root>/pg_xlog/xlogtemp.3846" to
   "<root>/pg_xlog/000000010000000000000000" (initialization of log
   file 0, segment 0): the operation is not permitted

("<root>" is the directory I specified to initdb using the -D option).

What made me a little curious about this is that when I initiate a
database on an ext3 partition, which of course succeeds, it definitely
does not seem to contain any hard links or symlinks, just regular
files (and none of the regular files has a link count >1).

Of course a host of other issues could prevent such a "dual-OS", or
"portable" database. I actually would just like to ask whether the
route is worth pursuing at all ...

Thanks for any input!

Florian v. Savigny

"Florian v. Savigny" <lorian@fsavigny.de> writes:
> Is there any way to have the Linux and the Windows versions of
> PostgreSQL use the same database? (On a double-boot machine.)

In principle that could work ...

> When trying this, I ran into trouble as soon as I tried to initiate a
> database on a VFAT partition while running Linux. That seemed to fail
> because VFAT does not support (sym)links:

... but you need to use a real file system ;-).  On Windows I believe
that PG's data directory must be on an NTFS partition.  So the question
becomes whether your Linux system is able to access an NTFS partition.

You might have better luck with a remotely mounted data directory
(across NFS or some such).  There are reliability gotchas with that
sort of setup, but frankly if you're doing something like this I doubt
that 100% bullet proofness is achievable anyway.

            regards, tom lane

Hi Tom,

thanks a lot for your reply!

  > In principle that could work ...

  > ... but you need to use a real file system ;-). [...]  So the
  > question becomes whether your Linux system is able to access an
  > NTFS partition.

I see ... I understand people have been trying to reverse-engineer
NTFS for more than a decade now, and yet no solution is really mature
(I am inferring this from the fact that it is not a standard option
with any Linux distro). So I've given up on that. That is, the support
I do have is read access and write access limited to changing the
contents of files without changing their length or name (imagine that
- but I even have one real application for this peculiar write
feature!). (This is what the standard kernel offers.)

But for real work, I have resigned myself to VFAT (which isn't so much
of a problem really, if you have a single-user machine).

  > You might have better luck with a remotely mounted data directory
  > (across NFS or some such).  There are reliability gotchas with
  > that sort of setup, but frankly if you're doing something like
  > this I doubt that 100% bullet proofness is achievable anyway.

I would actually tend to think that if I had a network, the better
solution may be to have the database running on the remote machine -
but anyway, my environment is simply a double-boot machine.

I seem to have found a real solution in the meantime. I should
probably have mentioned this, but mine would be a single-user
database, with rather modest requirements on complexity, and certainly
very low requirements on throughput, so PostgreSQL would be quite
high-end. I simply had not found anything simpler, and I liked the way
PostgreSQL is presented. But at last, I have found out about SQLite,
which seems to provide a type of database ideally suited to what I
need: just one machine-independent file, very simple to use - it's not
even a server application. Nevertheless, it is an SQL implementation,
if a bit restricted in some respects and unusually liberal in others.

So I probably won't pursue the PostgreSQL route further for the time
being, but it's nevertheless good to know it could work on an NTFS
partition. Who knows in what kinds of trouble I'll run next ...

Many thanks again,

Florian

Florian v. Savigny wrote:
> Dear community,
>
> I'm not sure whether this is the appropriate forum (maybe it's
> actually better asked on the developers' list), but as my impression
> grows my question might be naive, I thought I should probably duly
> start as a novice:
>
> Is there any way to have the Linux and the Windows versions of
> PostgreSQL use the same database? (On a double-boot machine.)
>
> When trying this, I ran into trouble as soon as I tried to initiate a
> database on a VFAT partition while running Linux. That seemed to fail
> because VFAT does not support (sym)links:
>
>    PANIC: could not link file "<root>/pg_xlog/xlogtemp.3846" to
>    "<root>/pg_xlog/000000010000000000000000" (initialization of log
>    file 0, segment 0): the operation is not permitted
>
> ("<root>" is the directory I specified to initdb using the -D option).
>
> What made me a little curious about this is that when I initiate a
> database on an ext3 partition, which of course succeeds, it definitely
> does not seem to contain any hard links or symlinks, just regular
> files (and none of the regular files has a link count >1).
>
> Of course a host of other issues could prevent such a "dual-OS", or
> "portable" database. I actually would just like to ask whether the
> route is worth pursuing at all ...

I would suspect permission issues before the symlink problems. I don't
believe VFAT supports any of the security models that PG can use.

Bob McConnell
N2SPP

Florian v. Savigny wrote:
> Of course a host of other issues could prevent such a "dual-OS", or
> "portable" database. I actually would just like to ask whether the
> route is worth pursuing at all ...


I believe there exist Windows drivers to access some non-Windows partitions.

On Fri, 2009-02-20 at 18:44 +0100, Florian v. Savigny wrote:
>
> I see ... I understand people have been trying to reverse-engineer
> NTFS for more than a decade now, and yet no solution is really mature
> (I am inferring this from the fact that it is not a standard option
> with any Linux distro).

I don't use MS Windows, however I use ntfs-3g on my Fedora to transfer
some files to my wife's USB drive, and I did not have any issues over
the last few months:

http://www.ntfs-3g.org/

Of course, everyone will question its reliability for a database server
(I do it, too).

Regards,
--
Devrim GÜNDÜZ, RHCE
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
                   http://www.gunduz.org