1

I have a specific requirement where I need to store all the database files on a network drive.

I have tried creating a new tablespace using the following command

CREATE TABLESPACE test
  OWNER postgres
  LOCATION E'z:\\test';

Here z is a network mapped drive on a windows system. I can create a new tablespace on usb drive, but not on network drive. Is it at all possible to store database files on a network location for Postgres. I am getting permission denied error for network location.

2

Storing database files on network storage is probably not a good idea with regard to performance; and probably security. Make sure your network is fast enough, and the connection very reliable.

Anyhow, it is feasible.


You seem to be working on Windows. Depending on version and mode of installation, the user running your PostgreSQL service might be different.

First thing to do: check which one it is. Use the "Services" administration tool, find out a service with a name similar to Postgres, and find out which user is the one that will be running PostgreSQL. In most recent versions and installation modes, it is commmon that this user is "Local System" or "NetworkService". In previous versions, it used to be a Postgres local user (i.e.: not an Active Directory domain user).

At this point, you have two options:

  1. Change the user (and password) to another one that has permissions to access your network share. Create one if you must. This user must not have administrative privileges; and it should have privileges only to be able to handle the database (i.e.: access to the part of the filesystem that it requires, which will be the PostgreSQL data and log directories that you chose while installing and all the tablespaces you define).

  2. Give the existing user read/write/modify permissions to access the network share. You shouldn't (and you probably just won't be able to) if the user is one of the standard Windows, that would most probably open the whole Windows to lots of security risks.

0
0

I found the issue. Postgres creates a link inside '<datafolder>\pg_tblspc'. This link is created for the user-created tablespaces, and the link redirects to the location where tablespace resides. For some reason this link was not working. I deleted and recreated this link with the same name, using the mklink command. Everything works perfectly now.

Originally left as a comment by the question author

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.