Thread: Tablespaces with Windows 2000

Hi,

I'm having a hard time trying to create a tablespace with Windows 2000
and PostgreSQL 8. Is there a How-To somewhere on the Net? I only find
tablespace issues with Linux. One article even claimed it was impossible
with Windows, but since I've tried it successfully in a localhost
installation with WinXP, I know this isn't true.

The equivalent of the "owner" of a directory in Windows seems to be the
one who created it. IOW, when I created a tablespace with WinXP in a
directory that the owner of the postmaster service had created, it
worked. In another directory, it wouldn't work. However, somehow I can't
get it to work in a client-server environment with a Win 2000 server.
Any suggestions?

Thanks,

Stefan

> I'm having a hard time trying to create a tablespace with
> Windows 2000 and PostgreSQL 8. Is there a How-To somewhere on
> the Net? I only find tablespace issues with Linux. One
> article even claimed it was impossible with Windows, but
> since I've tried it successfully in a localhost installation
> with WinXP, I know this isn't true.
>
> The equivalent of the "owner" of a directory in Windows seems
> to be the one who created it. IOW, when I created a
> tablespace with WinXP in a directory that the owner of the
> postmaster service had created, it worked. In another
> directory, it wouldn't work. However, somehow I can't get it
> to work in a client-server environment with a Win 2000 server.
> Any suggestions?

Picking this one up very late - perhaps you've already got a response.
If not, here's my take:

In general, it's the service account that has to have permissions. When
you say client/service, do you mean you want to create a tablespace on a
network drive? If so, not supported. If you mean you connect with psql
(or pgadmin, or whatever) across the network and create a tablespace
that's local to the server, that should work fine.
If it's the second, verify that the service user has permissions on *all
parent directories to the tablespace directory*. The service account wil
nee dread access all the way up to the final directory, where it needs
full control.
Oh, and make sure all the filesystems are NTFS - tablesspaces is not
supported on any other filesystems.

//Magnus

Hi Magnus,

Magnus Hagander schrieb:
>>I'm having a hard time trying to create a tablespace with
>>Windows 2000 and PostgreSQL 8.

> In general, it's the service account that has to have permissions. When
> you say client/service, do you mean you want to create a tablespace on a
> network drive? If so, not supported.

I guess this was the original problem. Unfortunately, the PG
documentation does not mention this (not in the Tablespace chapter
anyway), so I spent some time with "try&error" until it worked.

> create a tablespace
> that's local to the server, that should work fine.

It works fine, but only if the Windows user who runs the service is also
the one who _creates_ the tablespace directory. Having R/W permission is
not enough. That's bad because the service-running user account is often
one specially created for PG and never really being logged in... well,
but once you know it, there's at least a way to work it out.

> Oh, and make sure all the filesystems are NTFS - tablesspaces is not
> supported on any other filesystems.

Yep, I had checked this.

Thanks for your reply.

Regards,
Stefan