How can I change the PostgreSQL data folder to a shared folder location while using VirtualBox?

Question

I'm following this tutorial that explains how to move a PostgreSQL data folder location. Basically, I just need to copy the files inside /var/lib/postgresql to a different location... Something like:

sudo rsync -av /var/lib/postgresql /mnt/volume

And then inside the file /etc/postgresql/9.5/main/postgresql.conf I change the line data_directory = '/mnt/volume/postgresql/12/main'...

This solution works perfectly fine, the only condition is that the copied folder needs to have postgres:postgres permissions recursively.

The problem starts when I use VirtualBox with a shared folder location between the host and the guest, let's say my shared folder location is in /home/SharedFolder and I have PostgreSQL installed in my virtual machine. These VirtualBox shared folders always appear inside the guest with root:vboxsf permissions, those permissions won't change with chmod or chown and that makes it impossible for me to create a new folder on this shared folder with postgres:postgres permissions. I've tried adding postgres to the groups vboxsf and root (I know, that's not recommended), but it doesn't work as well. It just works when all the folders inside /home/SharedFolder/postgres have postgres:postgres permissions. Is there any workaround to that problem? How can I change the data folder location of PostgreSQL when I can't control the permissions of its destination folder (in this case, the VirtualBox shared folder location)?

Answer 1

I ended up finding a solution by mounting the shared folder directly on /etc/fstab, we can force permissions to stay as we want from there. Basically, I've shared a folder with no automount activated on the host:

Then I added the following line in /etc/fstab guest file:

postgresql /home/path/guest/postgresql/ vboxsf uid=postgres,gid=postgres 0 0

And finally I've changed the data_directory variable inside /etc/postgresql/12/main/postgresql.conf to:

data_directory = '/home/path/guest/postgresql/12/main'

As long as the data inside postgresql is a copy of the original postgresql folder it'll work fine.