3

Apart from using it for swap space, I'm going to be using the temporary storage available in my Azure VM (Ubuntu 14.04 OS) for storing Postgresql temporary files as well.

To enable that, I've to create a symlink at /$PGDATA/base/pgsql_tmp that points to /mnt/pgsql_tmp. So that should be ln -s /mnt/pgsql_tmp $PGDATA/base/pgsql_tmp.

This reasonably ensures all temp files are stored and read from the SSD-based temporary storage. However, one hitch: the user postgres does NOT have permissions to write to /mnt:

drwxr-xr-x   4 root root 4.0K Mar 18 12:40 mnt

How do I ensure postgres also has write permission on /mnt? Should I just do sudo chmod -R 777 /mnt and be done with it?

I know this is elementary, but since it's a production server and I've already got swap set up in /mnt I don't want to misconfigure it and run into problems tomorrow.


p.s. this is how the temporary storage currently looks like:

Filesystem      Size  Used Avail Use% Mounted on
/dev/sdb1       221G  9.9G  200G   5% /mnt
4
ln -sTf /mnt/pgsql_tmp $PGDATA/base/pgsql_tmp  # did you get that correct?
chown -R postgres              /mnt/pgsql_tmp
chmod o+x    /mnt          # note: no -R,  this turns 750 to 751 for /mnt only

The effect:

 su - postgres
 echo 1 > /mnt/file   # fails
 ls   /mnt            # fails
 cat /mnt/anything    # fails
 echo 1 >  /mnt/pgsql_tmp/file   # works
 ls /mnt/pgsql_tmp               # works
 cat /mnt/pgsql_tmp/file         # works

The chmod o+x means to give [o]thers the +e[x]ecute bit, which gives them a permission to traverse across /mnt into any subdirectory. Other permissions are not necessary for /mnt only for /mnt/pgsql_tmp

| improve this answer | |
  • 1
    After >10 years of doing this, it still seems counterintuitive. I try to remember the order is the same for ln as for cp – kubanczyk Mar 19 '17 at 10:59
  • Quick clarification: before doing this in production, I was trying it out locally on test folders. I created /mnt and /pgsql_tmp on my desktop, and then tried ln -sTf /home/hassan/Desktop/mnt/pgsql_tmp /home/hassan/Desktop/pgsql_tmp. This ends up giving me ln: ‘/home/hassan/Desktop/pgsql_tmp’: cannot overwrite directory. What am I doing wrong? – Hassan Baig Mar 19 '17 at 14:58
  • Don't create /mnt/pgsql_tmp as a directory. If it's a directory, it cannot be a symlink at the same time. If it's a symlink, it can be a symlink either to a file, a directory, or to another symlink. – kubanczyk Mar 19 '17 at 17:33
  • Uhuh, guess I have it the other way around. I was thinking I'd delete the directory where pgsql temporary files are created, i.e. pgsql_tmp inside $PGDATA/base/, then create the directory pgsql_tmp inside /mnt, and then finally do ln -sf /mnt/pgsql_tmp $PGDATA/base/pgsql_tmp – Hassan Baig Mar 20 '17 at 9:37
  • Use ln -sTf instead of ln -sf, it's important in your case. The rest seems very much correct. – kubanczyk Mar 20 '17 at 11:29
3

In order to allow user postgres to write to a folder, it would be preferred to make him be the owner of the folder instead of giving read-write access to everyone.

If /mnt should be used only by postgres then you should make postgres the owner of /mnt folder and all its sub-folders

sudo chown -R /mnt postgres

You might want to do this only for a sub-folder of /mnt e.g. to /mnt/pgsql_tmp

sudo chown -R /mnt/pgsql_tmp postgres
| improve this answer | |
  • So wouldn't that curtail root from writing to /mnt? – Hassan Baig Mar 19 '17 at 10:46
  • root has full read/write access to any folder owned by any user – Yaron Mar 19 '17 at 10:47
  • Yep, just wanted to be sure since this is production, 5-times more stressful than making changes in development :-) – Hassan Baig Mar 19 '17 at 10:50

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.