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.

  • I cant agree more with you that the database files should not be on the same network, but I am kind of forced to use this approach. As per your suggestion, I was able to create the namespace. However I could not use the mapped network drive but with the actual location i.e. \\<remote-server>\<folder>\ however when I tried creating a database using this tablespace I got an invalid parameter error msg. Tried a couple of different combinations of usersnames and settings but nothing seems to work. If you can help me with this also(or some pointers), that would be really helpful & a great help. – Rajat Saini Jul 10 '17 at 10:29
  • Can you add the exact message you got? It sounds strange. Once you're able to create the tablespace, PostgreSQL already checked that it could write a file... – joanolo Jul 10 '17 at 19:25
  • ERROR: Directory"pg_tblspc/16394/PG_9.5_201510051/16398" not found: Invalid argument I got this error. Please note this is roughly translated from. This is my shared directory in which I created the tablespace Japanese\\1xx.xxx.x.xxx\SharedDir\PG_9.5_201510051 – Rajat Saini Jul 11 '17 at 6:19
  • 1
    I found the issue. Postgre creates a link inside '<datafolder>\pg_tblspc'. This link is created for the user created tablespaces and the link redirects to the location where tablesspace 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. – Rajat Saini Jul 12 '17 at 6:48
  • 1
    I'd suggest you add your finding as an answer, and accept it. It will probably help other in the future ;-) – joanolo Jul 12 '17 at 17:35

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.