Re: PostgreSQL Windows 8.4 tablespaces on remote servers

Lou,

Thank you for your comments. The only way I could create a tablespace on a
remote server was to use an iscsi. Mapped drives would not work and shared
folders (privileges set for postgres server machine, postgres superuser,
and owner of tablespace is postgres) also do not work. There may be a
different way to do this, but it appears that Postgres requires that a
drive be 'mounted', in this case an iscsi, to the postgres server. I have
found several posts on this same question so hopefully this information
will be of some use to others.

Thank you for your assistance,
Mike

From:
Lou Picciano <loupicciano(at)comcast(dot)net>
To:
pgsql-admin(at)postgresql(dot)org
Cc:
Michael O'Donnell <odonnellm(at)usgs(dot)gov>
Date:
11/09/2010 02:12 PM
Subject:
Re: [ADMIN] PostgreSQL Windows 8.4 tablespaces on remote servers

Michael,

Hello - and welcome to PostgreSQL (you're already on the right track!)

----- Original Message -----
From: "Michael O'Donnell" <odonnellm(at)usgs(dot)gov>
To: pgsql-admin(at)postgresql(dot)org
Sent: Tuesday, November 9, 2010 2:21:35 PM
Subject: [ADMIN] PostgreSQL Windows 8.4 tablespaces on remote servers

I am a relatively new user of PostgreSQL and I would like to create a
tablespace on a remote 2008 standard x86 server. I am using a 2008 x64 R2
server to host the PostgreSQL service. The host is a VM and the NTFS is
not.

I am able to create tablespaces on the Postgres server in various places.
Afterword, I can successfully add databases to these tablespaces. However,
when I try to create a tablespace on a different server, I can create part
of the tablespace but it seems there is an error with creating the
directories that are automatically generated. However, I do not get an
error during the create tablespace step. If I proceed to generate a
database on this table space, there is an error that certain folders do
not exist.

(Though I don't quite know what you mean by 'directories' here, it's
probably safe to assume you're talking about the System Catalogs, which
are indeed automatically created at a cluster's initialization). Have you
properly initialized the database(s) in question? At first glance, It
looks like you may not be accessing the database with sufficient
permissions and/or may be managing to connect to an incompletely- or
non-initialized cluster. (Your problem is interesting - wonder if such a
connection is even possible?)

One common gotcha: The default host-based access permissions file
'pg_hba.conf' allows for 'trusted' access by role 'postgres' from the
local machine only. You'd have to set up access permissions for other
machines/networks within the pg_hba.conf for each cluster.

I have permissions set up for the service account and the database account
for the folder on the remote server where I would like to create the
tablespace. Based on what I have read, I only need to have the service
account have permissions, but I have tried to open it up entirely for
testing. I can create files and folders from the postgres server when
navigating to the other server disk, and I have double checked the
security settings for all relevant accounts.

You should not need any acess at all to the server's disk itself, other
than to do the initial setup and to edit the configuration files.
Otherwise, all access is via the postgres daemons themselves.

What I am trying to figure out is why I cannot do this and whether I have
to use UNC paths. I have found some information on blogs, but no
solutions. I have found very little information in the manuals as to how
one sets up a tablespace on a remote server and whether UNC paths are
required or not.

I think you absolutely _do not_ need UNC paths. We are connecting to all
of our remote server(s) via secured port access.

Can anyone provide me with suggestions?

Thank you,
mike

Pls let me know if any of this helps,

Lou Picciano