Обсуждение: 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.
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.
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.
Can anyone provide me with suggestions?
Thank you,
mike
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
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
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@comcast.net> |
| To: | pgsql-admin@postgresql.org |
| Cc: | Michael O'Donnell <odonnellm@usgs.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@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