Re: Creating a tablespace on an external server in Windows

Regina,
Thank you very much for your suggestions! Unfortunately I was not able to get them to work for me.

I made sure the drive mapping was permanent and tried different iterations of logins, permissions, and folder ownership on the different servers. After doing more checking, your guess that what I am trying might not be possible looks like the case. Doing some more searching online, it seems that any program that does now allow UNC paths is pretty much unable to write to shared network drives in a Windows environment. Initially I had only checked on the issue using PostgreSQL as a necessary term in the hopes that someone using the program had discovered a work around.

I will just chalk this up to a Windows limitation and try and convince my IT folks to buy me some more storage for my server, or just do another PostgreSQL install and manage multiple server installations.

Thank you for the time you spent considering my problem!

-Mike

>>> "Obe, Regina" <robe(dot)dnd(at)cityofboston(dot)gov> 1/9/2008 6:28 AM >>>
Mike,

First I'm not sure if what you are trying to do is possible since I've
never tried this myself but here are a couple of guesses that may be
useful to you.

1) From your number 2, my guess is the postgres NT account can't see the
S drive or is denied access. Since postgresql service is using the
postgres NT account on the server, I think you need to make sure that
the postgres NT account sees the S drive. This means you have to log in
as the postgres NT account on your windows 2003 that his hosting the
postgres server and make sure the S drive is mapped and permanent
mapping and preferably have some sort of logon script for the postgres
account that insures it is mapped on each restart.

2) Are the two servers part of the same domain? or are they standalone
servers? If they are domain members, then it might work better if you
make the postgres account a domain account instead of a local account.
In theory for standalone servers, matching the passwords and names of
accounts should work since I have done that in similar (not PostgreSQL
situations).

Anyrate my guess would be you would be better off with an external drive
plugged into your postgres server (I think for a 1 TB ide they run about
$400-$500 and internal ones are even cheaper), but I understand that may
not be an option for you.

Hope that helps,
Regina

-----Original Message-----
From: pgsql-novice-owner(at)postgresql(dot)org
[mailto:pgsql-novice-owner(at)postgresql(dot)org] On Behalf Of Michael Smith
Sent: Tuesday, January 08, 2008 7:16 PM
To: pgsql-novice(at)postgresql(dot)org
Subject: [NOVICE] Creating a tablespace on an external server in Windows

I am having trouble using the Create_Tablespace command to create a
tablespace on a mapped drive. The reason I am attempting to do this has
to do with the storage capacity of my primary server, which I am
reserving for our most accessed data.

System setup:
Primary server - Windows 2003 with PostgreSQL installed
Secondary server - Windows 2003 with direct connection to separate data
array

I have looked through the documentation and mailing list archives and
the suggestions either assume the directory is local to the machine that
has PostgreSQL installed or is for linux/unix (they nicely include
external mounted drives as a local directory).

What I have attempted so far:
1. Created the directories on the secondary server using Windows
Explorer on my primary server. The directory where I want to create the
new tablespace is empty.

2. I have tried to create the tablespace using pgAdmin III (both 1.6.x
and 1.8.1). The error message is "ERROR: could not set permissions on
directory "S:/postgresql/transportation": No such file or directory"

3. Read the CREATE TABLESPACE page in the online PostgreSQL manual
(maybe should have done first, but it did not help anyway)

4. Created a postgres user on the secondary server and added that user
as an administrator to both directories in my location path (still
working with the initial install). On secondary server, have used
password for both the Windows postgres user and the PostgreSQL postgres
superuser in attempt to match the accounts on both servers and each
setup failed.

5. Though it says not to do it, I also tried giving a location path
using a UNC path and the server IP address, and as you may have guessed,
that failed as well.

Other info:
- Data array is connected to secondary server as G:
- Directories on data array available on primary server through a mapped
drive labeled S: (mapped to G: on secondary server)

Since I am a novice, I may just not know the correct terms when
searching the mailing list archives and documentation so any help would
be greatly appreciated.

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.