Re: [HACKERS] Tablespaces

On Fri, 5 Mar 2004, Thomas Swan wrote:

> jearl(at)bullysports(dot)com wrote:
>
> ><tswan(at)idigx(dot)com> writes:
> >
> >
> >
> >>>tswan(at)idigx(dot)com wrote:
> >>>
> >>>
> >>>>>"Zeugswetter Andreas SB SD" <ZeugswetterA(at)spardat(dot)at> writes:
> >>>>>
> >>>>>
> >>>>>>>>My feeling is that we need not support tablespaces on OS's without
> >>>>>>>>symlinks.
> >>>>>>>>
> >>>>>>>>
> >>>>>>To create symlinked directories on Win2k NTFS see:
> >>>>>> http://www.sysinternals.com/ntw2k/source/misc.shtml#junction
> >>>>>>I think Win2000 or XP would be a reasonable restriction for Win32 PG
> >>>>>>installations that want tablespaces.
> >>>>>>
> >>>>>>
> >>>>>Oh, good --- symlinks for directories are all that we need for this
> >>>>>design. I think that settles it then.
> >>>>>
> >>>>>
> >>>>>
> >>>>What archival tools are there that would restore this to this back to
> >>>>the
> >>>>filesystem: tar? zip? What would happen if a symlink were removed or
> >>>>pointed to an invalid location while the postmaste was running?
> >>>>
> >>>>
> >>>Well, for backup, just run tar or find on /data with a flag to
> >>>follow symlinks, and you are done. Can't get much easier than
> >>>that.
> >>>
> >>>
> >>I'm ruferring to NTFS and the win32 platforms. How does tar handle
> >>these symlinks on the NTFS filesystem? What about if someone finds
> >>that FAT32 is significantly better for the database?
> >>
> >>
> >
> >tar doesn't know anything about PostgreSQL system catalogs. If we use
> >symlinks for tablespaces then it would be possible to backup downed
> >databases with a simple tar command on every platform *I* care about
> >(and probably Windows too). Using system catalogs for this stuff
> >would simply guarantee that I would have to read the system catalogs
> >and then back up each tablespace manually. In short, your idea would
> >trade off (maybe) having to backup tablespaces manually on a few
> >platforms for the certainty of having to backup tablespaces manually
> >on all platforms.
> >
> >How is that a win?
> >
> >
> >
> Apparently, I have failed tremendously in addressing a concern. The
> question is does PostgreSQL need to rely on symlinks and will that
> dependency introduce problems?
>
> There is an active win32 port underway (see this mailing list). One
> proposal was to try to use an OS specific filesystem feature to perform
> a symlink on NTFS. Can the special symlink that NTFS allegedly supports
> be archived the same way symlinks are archived on Unix? If so, is there
> a utility that can do this (zip, tar, etc). The backup operator would
> still need to know what directories needed to be archived in addtion to
> the pgdata directory. Is this symlink structure a normal/special file
> that can be archived by normal means (tar,zip, etc)?

According to this page:

http://www.linuxinfor.com/en/man1/ln.1.html

from the linux man pages,
"On existing implementations, if it is at all possible to make a hard link
to a directory, this may be done by the superuser only. POSIX forbids the
system call link(2) and the utility ln to make hard links to directories
(but does not forbid hard links to cross filesystem boundaries)."

and states that the command ln is "POSIX 1003.2. However, POSIX 1003.2
(1996) does not discuss soft links. Soft links were introduced by BSD, and
do not occur in System V release 3 (and older) systems."

I fear the more useful of the two would be soft links, but if soft links
are not a part of the POSIX standard, then postgresql probably shouldn't
base key features on them unless said features would be onerous to
implement without soft links.

Oddly enough though, Microsoft has now released their unix services
package for free, and it comes with the commands to create a symbolic
link, and runs on Windows NT 4.0. Windows 2000. Windows XP. and Windows
Server 2003.

So, soft links would appear to not be a real non-starter, as long as the
ability to make softlinks on those systems won't rely on having some
strange package installed (like MS's Unix services package.)

I imagine there's a standard OS call in modern MS OSes that will let you
create a symbolic link with no special libs installed, and if that's the
case, the the argument that maybe FAT would be a better file system comes
under the same heading as running your database on NFS. Neither is a good
idea, and PGSQL can't guarantee normal, reliable operation.

>
> Example:
>
> PGDATA is C:\pgdata
> I have a tablespace in Z:\1\ and Z:\2\
> There exists an alleged symlink in
> C:\pgdata\data\base\tablespaces\schmoo -> Z:\1
>
> Can I archive [ C:\pgdata, Z:\1, Z:\2 ], restore them, and have
> postgresql working just as before?

Have you tried building said structure and backing up and restoring it to
see?

> >We aren't talking about a "feature that work[s] on Linux on not on
> >FreeBSD." We are talking about a feature that works on every OS that
> >suports symlinks (which includes even operating systems like Windows
> >that PostgreSQL doesn't currently support).
>
> Hello? What was this response from Tom Lane? "My feeling is that we
> need not support tablespaces on OS's without symlinks." That seems to
> be indicative of a feature set restriction base on platform.

If every OS we run on supports sym links is that such a restriction?

Which OSes (I'm asking because I really don't know) that are currently
supported don't support symbolic links?

> >>Additionally, another developer noted the advantage of a text file
> >>is that it would be easy for someone to develop tools to help if it
> >>became difficult to edit or parse. Additionally, there could be a
> >>change away from a flat file format to an XML format to configure
> >>the tablespace area.
> >>
> >>
> >
> >The advantage of symlinks is that no tools would have to be written
> >and 'ls -l' would show everything you would need to know about where
> >your tablespaces actually were.
> >
> >
> >
> Where is 'ls -l' on a win32 box? If you will follow the discussion of
> symlinks under MinGW you will see that they don't work as commanded.
> And, postgresql is supposed to be compiled under MinGW, but not require
> it to run.

It's free from Microsoft. Postgresql wouldn't require it. It's a
standard file system level call on Windows, and has been since NT 4.0 was
a baby. That was 1996-1997 when I built an NT4.0 server, downloaded the
free gnu utils and found out that they worked.

Plain and simple, Windows has supported symbolic links, soft and hard, for
some time, and every flavor of unix known does as well. While MVS on a
mainframe or VMS might present some problems, those operating systems all
have LVMs and you could just mount and unmount space under your database.