Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 41AA01337B68 for ; Tue, 9 Nov 2010 17:12:28 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 63068-07 for ; Tue, 9 Nov 2010 21:12:21 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from qmta02.westchester.pa.mail.comcast.net (qmta02.westchester.pa.mail.comcast.net [76.96.62.24]) by mail.postgresql.org (Postfix) with ESMTP id F26C51337B53 for ; Tue, 9 Nov 2010 17:12:19 -0400 (AST) Received: from omta18.westchester.pa.mail.comcast.net ([76.96.62.90]) by qmta02.westchester.pa.mail.comcast.net with comcast id V00F1f0071wpRvQ529CLVa; Tue, 09 Nov 2010 21:12:20 +0000 Received: from sz0093.wc.mail.comcast.net ([76.96.58.151]) by omta18.westchester.pa.mail.comcast.net with comcast id V9CL1f0063FmDic3e9CLj6; Tue, 09 Nov 2010 21:12:20 +0000 Date: Tue, 9 Nov 2010 21:12:19 +0000 (UTC) From: Lou Picciano To: pgsql-admin@postgresql.org Cc: Michael O'Donnell Message-ID: <606845867.250606.1289337139942.JavaMail.root@sz0093a.westchester.pa.mail.comcast.net> In-Reply-To: <451224531.250551.1289337105550.JavaMail.root@sz0093a.westchester.pa.mail.comcast.net> Subject: Re: PostgreSQL Windows 8.4 tablespaces on remote servers MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_250605_530873283.1289337139941" X-Originating-IP: [76.117.184.231] X-Mailer: Zimbra 6.0.5_GA_2431.RHEL5_64 (ZimbraWebClient - SAF3 (Mac)/6.0.5_GA_2427.RHEL4) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.908 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001, RFC_ABUSE_POST=0.001, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201011/67 X-Sequence-Number: 36033 ------=_Part_250605_530873283.1289337139941 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit Michael, Hello - and welcome to PostgreSQL (you're already on the right track!) ----- Original Message ----- From: "Michael O'Donnell" To: pgsql-admin@postgresql.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 ------=_Part_250605_530873283.1289337139941 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 7bit
Michael,

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

----- Original Message -----
From: "Michael O'Donnell" <odonnellm@usgs.gov>
To: pgsql-admin@postgresql.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
------=_Part_250605_530873283.1289337139941--